MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘MAMP’ Category

PHP Function Primer

without comments

Added a new PHP Function Primer for my students. This one’s not large but fits as a page better. If you’re interested it’s here.

It covers:

  • Function Definitions
  • Creating & Using Functions
  • Pass-by-value Functions
  • Pass-by-reference Functions
  • Dynamic Functions
  • Function Default Parameters
  • Variable-length Parameter Lists

Written by maclochlainn

April 2nd, 2010 at 10:17 pm

Posted in LAMP,MAMP,PHP

PHP Tutorial Available

with 5 comments

I finally got around to writing that PHP Tutorial. It’s a bit large and takes about 10 seconds to load or longer depending on your connection and machine. It covers the basics from writing your first page to loops. It was too large for a blog post, so it’s a blog page. Click on the link if you’d like to check it out.

I plan others on functions, objects, and files. Then, I’ll get to tutorials against databases.

Naturally, suggestions are always welcome.

Written by maclochlainn

March 29th, 2010 at 11:11 am

Posted in LAMP,MAMP,OPAL,PHP

MySQL Standard Group By

with 7 comments

Teaching SQL is interesting because folks try syntax that experience would tell you shouldn’t work. It was interesting when I discovered what should be broken from my perspective but was expected behavior in MySQL. It became clearer to me why it’s there as I did some experimenting with it enabled and disabled. While I’d still argue it’s broken, it’s the only way to get support for advanced aggregation concepts.

The reason that I find that it broken is a matter of perspective not standards. The fact that you can select a set of non-aggregated columns with an aggregated column, and exclude one, more than one, or all of the non-aggregated columns from the GROUP BY clause seemed like a broken behavior. The MySQL behavior is explained in Chapter 11.12.3 of the MySQL 5.1 Reference. What wasn’t clear from the documentation when I read it was why the behavior is allowed. Gary’s comment on the original post made me look more deeply into the behavior.

If you check the referenced documentation, the process is called GROUP BY hidden columns. That label didn’t immediately resonate with me. That’s probably of my background with IBM’s DB2 and Oracle. I’m so accustom to how they work, that sometimes I think they set the standards but they don’t. It turns out that the GROUP BY clause in the ANSI SQL standards doesn’t allow for expressions.

The default configuration of MySQL allows for the expressions (functions) in the GROUP BY clause. The documentation refers to hidden columns, which are in point of fact expressions in the GROUP BY clause. MySQL does support expressions in the GROUP BY by default, and requires, like Oracle, that when you use an expression in the SELECT clause that you mirror it in the GROUP BY clause. This means you group on the result of the expression, not a column in the table or view.

In the default configuration, you have three options. They are determinate results, indeterminate results, and hidden column results. The following cover these components.

Determinate Results

Determinate results are straight forward. They require that all non-aggregated columns in the SELECT clause are mirrored in the GROUP BY clause. This means that the non-aggregated column values are the key upon which results are aggregated.

An example of determinate results is:

1
2
3
4
5
6
SELECT   key_one
,        key_two
,        SUM(counter)
FROM     GROUPING
GROUP BY key_one
,        key_two;

Indeterminate Results

Indeterminate results isn’t straight forward but isn’t hard to grasp. An indeterminate result set is returned when one or more non-aggregated columns in a SELECT clause aren’t listed in the GROUP BY clause. The columns listed in the SELECT clause but excluded from the GROUP BY clause return meaningless values because they’re column values chosen indeterminately from all pre-aggregated rows.

The following query runs in a generically configured MySQL instance without an error. It returns a meaningless key_two column value from the pre-aggregated row set. In the example, the aggregation column counts the unique key_one column values. This behavior makes key_one a determinate value, and key_two an indeterminate value.

1
2
3
4
5
SELECT   key_one
,        key_two
,        SUM(counter)
FROM     GROUPING
GROUP BY key_one;

You can fix this mixed return set by adding the key_two column to the GROUP BY clause, which would return a determinate set. Alternatively, you can prevent the default behavior for the GROUP BY clause by adding the ONLY_FULL_GROUP_BY mode variable to your SQL_MODE system variable.

After setting the SQL_MODE> variable, a GROUP BY must contain all non-aggregated columns. When you make this change to the SQL_MODE system variable, you also disable any queries that use an expression in their GROUP BY clause. Those queries with expressions in the group by will now raise an ERROR 1055 exception, like this:

ERROR 1055 (42000): 'sampledb.grouping.key_two' isn't in GROUP BY

If you want to prevent indeterminate results and don't use expressions in the GROUP BY clause, you can add the ONLY_FULL_GROUP_BY mode to your SQL_MODE system variable. You can do that during a session with the following syntax:

SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));

Or, you can start the mysqld with the following option:

mysqld --sql_mode="sql_mode1,sql_mode2, ... ,sql_mode(n+1)"

A better alternative, is to add it to the my.cnf configuration file on Linux, or my.ini configuration file on Windows. You can add it to this line, which is done at the end of the line.

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"

If you opt for changing it at the server configuration file, you must stop and restart the mysqld process. You can do that on Windows from the command line, like this on Windows provide the service name is mysql. If you've set the Windows service to mysql51, then you need to substitute mysql51 for mysql.

Hidden Column Results

A hidden column result, is a result generated by an expression in the GROUP BY clause. The following is a query that lets you sum transactions by the month name. This is supported by the default behavior of hidden columns. The following is an approach that you might find in Oracle because they don't support aggregation by only part of the non-aggregated columns in a query.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT   il.MONTH AS "MON-YEAR"
,        il.base AS "BASE"
,        il.plus10 AS "10_PLUS"
,        il.plus20 AS "20_PLUS"
FROM    (SELECT   CONCAT(UPPER(SUBSTRING(MONTHNAME(t.transaction_date),1,3)),'-',EXTRACT(YEAR FROM t.transaction_date)) AS MONTH
         ,        MONTH(t.transaction_date) AS sortkey
         ,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.0,2)),10,' ') AS base
         ,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.1,2)),10,' ') AS plus10
         ,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.2,2)),10,' ') AS plus20
         FROM     TRANSACTION t
         WHERE    EXTRACT(YEAR FROM t.transaction_date) = 2009
         GROUP BY CONCAT(UPPER(SUBSTRING(MONTHNAME(t.transaction_date),1,3)),'-',EXTRACT(YEAR FROM t.transaction_date))
         ,        MONTH(t.transaction_date)) il
ORDER BY il.sortkey;

The interesting thing about MySQL's approach is that the Oracle equivalent of an inline view is unnecessary. You can simply switch the functions like the exmaple below.

1
2
3
4
5
6
7
8
9
10
SELECT   DATE_FORMAT(t.transaction_date,'%m-%Y') AS MONTH
,        MONTH(t.transaction_date) AS sortkey
,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.0,2)),10,' ') AS base
,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.1,2)),10,' ') AS plus10
,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.2,2)),10,' ') AS plus20
FROM     TRANSACTION t
WHERE    EXTRACT(YEAR FROM t.transaction_date) = 2009
GROUP BY DATE_FORMAT(t.transaction_date,'%m-%Y')
,        MONTH(t.transaction_date)
ORDER BY sortkey;

When you add the ONLY_FULL_GROUP_BY mode to your SQL_MODE system variable, you disallow this type of behavior and the query would no longer work. It would return the following error:

ERROR 1055 (42000): 'sampledb.t.transaction_date' isn't in GROUP BY

Written by maclochlainn

March 10th, 2010 at 12:54 am

Posted in LAMP,MAMP,MySQL,sql

Debugging MySQL Procedures

with 2 comments

In my second database class we focus on PL/SQL but we’ve begun highlighting the alternatives in MySQL and SQL Server. A student asked how they could debug runtime variable values in a MySQL Stored Procedure (or subroutines according to some documentation). You can see this post for how to create an equivalent solutions for MySQL functions.

In Oracle, we debug with the DBMS_OUTPUT package. Packages, like DBMS_OUTPUT hold related functions and procedures, and are a corollary to System.out.println() in Java.

Before you can see the output at the command-line in Oracle (that is if you’re not using SQL*Developer or Toad), you must set a SQL*Plus environment variable. These variables don’t exist in MySQL or SQL Server command-line tools because they never served the function of a report writer like SQL*Plus.

You enable output display in Oracle by setting this in SQL*Plus:

SQL> SET SERVEROUTPUT ON SIZE 1000000

You can test your anonymous or named block. Since MySQL doesn’t support anonymous named block, the examples using a trivial procedure that prints Hello World! (orginal, right ;-)).

1
2
3
4
5
6
7
8
9
10
11
12
-- Create a procedure in Oracle.
CREATE OR REPLACE PROCEDURE hello_world IS
BEGIN
  -- Print a word without a line return.
  DBMS_OUTPUT.put('Hello ');
  -- Print the rest of the phrase and a line return.
  DBMS_OUTPUT.put_line('World!');
END;
/
 
-- Call the procedure.
EXECUTE hello_world;

It’s seems useless to print the output because it should be evident. MySQL procedures are a bit different because there’s no OR REPLACE syntax. The equivalent to calling the DBMS_OUTPUT package procedures in MySQL is to simply select a string. Now you can do this with or without the FROM dual clause in MySQL, don’t we wish we could do the same thing in Oracle. :-)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Conditionally drop the procedure.
SELECT 'DROP PROCEDURE hello_world' AS "Statement";
DROP PROCEDURE IF EXISTS hello_world;
 
-- Reset the delimiter to write a procedure.
DELIMITER $$
 
-- Create a procedure in Oracle.
CREATE PROCEDURE hello_world()
BEGIN
  -- Print the phrase and a line return.
  SELECT 'Hello World!';
END;
$$
 
-- Reset the delimiter back to a semicolon to work again.
DELIMITER ;
 
-- Call the procedure.
SELECT 'CALL hello_world' AS "Statement";
CALL hello_world();

Originally, I tried to keep this short but somebody wanted an example in a loop. Ouch, loops are so verbose in MySQL. Since I was modifying this post, it seemed like a good idea to put down some guidelines for successful development too.

Guidelines for Development of Procedures

Declaration Guidelines

The sequencing of components in MySQL procedures is important. Unlike, PL/SQL, there’s no declaration block, declarations must be at the top of the execution block. They also must appear in the following order:

  1. Variable declarations must go first, you can assign initial values with the DEFAULT keyword. While not required, you should:
  • Consider using something like lv_ to identify them as local variables for clarity and support of your code.
  • Consider grouping local variables that relate to handlers at the bottom of the list of variables.
  1. After local variables and before handlers, you put your cursor definitions. You should note that MySQL doesn’t support explicit dynamic cursors, which means you can’t define one with a formal signature. However, you do have prepared statements and they mimic dynamic cursor behaviors.
  2. Last in your declaration block, you declare your handler events.

Execution Guidelines

  1. Variable assignments are made one of two ways:
  • You should start each execution block with a START TRANSACTION and then a SAVEPOINT, which ensures the procedure acts like a cohesive programming unit.
  • You assign a left_operand = right_operand; as a statement.
  • You use the SELECT column INTO variable; syntax to filter a value through SQL functions and assign the resulting expression to a local variable.
  • You assign a single row cursor output to variables using a SELECT column INTO variable FROM ....
  1. You must assign values from cursors called in a loop into local variables when you want to use the results in nested SQL statements or loops.
  2. You must reset looping variables, like the fetched control variable at the end of the loop to reuse the handler variable in subsequent loops.
  3. You must assign values to local variables if you want to use them in the exception handler.
  4. If you’ve started a transaction, don’t forget to COMMIT your work.

Exception Guidelines

  1. Leave out the exception handler until you’ve tested all outcomes, and make sure you document them and add them as potential handlers.
  2. When you deploy exception blocks, they’re the last element at the bottom of the exception block.
  3. You should consider explicit exception handlers for each error unless the action taken is the same.
  4. You should consider grouping all exception handlers when the action taken is the same.
  5. You should include a ROLLBACK whenever you’ve performed two or more SQL statements that may modify data.

Below is an example for putting debug code inside a loop.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
-- Conditionally drop a sample table.
SELECT 'DROP TABLE IF EXISTS sample' AS "Statement";
DROP TABLE IF EXISTS sample;
 
-- Create a table.
CREATE TABLE sample
( sample_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, sample_msg   VARCHAR(20));
 
-- Insert into sample.
INSERT INTO sample (sample_msg) VALUES
 ('Message #1')
,('Message #2')
,('Message #3');
 
-- Conditionally drop the procedure.
SELECT 'DROP PROCEDURE debug_loop' AS "Statement";
DROP PROCEDURE IF EXISTS debug_loop;
 
-- Reset the delimiter to write a procedure.
DELIMITER $$
 
-- Create a procedure in Oracle.
CREATE PROCEDURE debug_loop()
BEGIN
 
  /* Declare a counter variable. */
  DECLARE lv_counter INT DEFAULT 1;
 
  /* Declare local control loop variables. */
  DECLARE lv_sample_id  INT;
  DECLARE lv_sample_msg VARCHAR(20);
 
  /* Declare a local variable for a subsequent handler. */
  DECLARE duplicate_key INT DEFAULT 0;
  DECLARE fetched INT DEFAULT 0;
 
  /* Declare a SQL cursor fabricated from local variables. */  
  DECLARE sample_cursor CURSOR FOR
    SELECT * FROM sample;
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Start transaction context. */
  START TRANSACTION;
 
  /* Set savepoint. */  
  SAVEPOINT all_or_none;
 
  /* Open a sample cursor. */
  OPEN sample_cursor;
  cursor_sample: LOOP
 
    /* Fetch a row at a time. */  
    FETCH sample_cursor
    INTO  lv_sample_id
    ,     lv_sample_msg;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_sample; END IF;
 
    -- Print the cursor values.
    SELECT CONCAT('Row #',lv_counter,' [',lv_sample_id,'][',lv_sample_msg,']') AS "Rows";
 
    -- Increment counter variable.
    SET lv_counter = lv_counter + 1;
 
  END LOOP cursor_sample;
  CLOSE sample_cursor;
 
  /* This acts as an exception handling block. */  
  IF duplicate_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
 
  END IF;
 
END;
$$
 
-- Reset the delimiter back to a semicolon to work again.
DELIMITER ;
 
-- Call the procedure.
SELECT 'CALL debug_loop' AS "Statement";
CALL debug_loop();

This post certainly answers the student question. Hopefully, it also helps other who must migrate Oracle skills to MySQL. Since IBM DB2 has introduced a PL/SQL equivalent, wouldn’t it be nice if Oracle did that for MySQL. That is, migrate PL/SQL to MySQL. Don’t tell me if you think that’s a pipe dream, I’d like to hope for that change.

Written by maclochlainn

February 27th, 2010 at 11:01 pm

MySQL Boolean Data Type

with 11 comments

Somebody posted a quick question about the outcome of defining a table with a bool data type in PHPMyAdmin. They were subsequently surprised when they checked the MySQL database and found that it was actually a tinyint(1). The natural question they had was: “What do you enter – true/false or 1/0?”

I promised to post an answer tonight, and morning counts too. You can enter a true or false because they’re synonyms for a 1 or 0 respectively. TINYINT is the supported data type, and BIT, BOOL, and BOOLEAN are synonyms for the base data type.

Here’s an example in MySQL:

mysql> CREATE TABLE data_type (TYPE bool);
 
mysql> DESCRIBE data_type;
+-------+------------+------+-----+---------+-------+
| FIELD | TYPE       | NULL | KEY | DEFAULT | Extra |
+-------+------------+------+-----+---------+-------+
| TYPE  | tinyint(1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
 
mysql> INSERT INTO data_type VALUES (TRUE),(FALSE);
 
mysql> SELECT * FROM data_type;
+------+
| TYPE |
+------+
|    1 |
|    0 |
+------+

The comment below raises the question of what happens with values in the valid range of TINYINT that aren’t 0 or 1, like 5. The simple answer is they’re not valid when compared against the true and false constants, as you can see by creating the following example.

-- Create a test table.
CREATE TABLE verify
( verify_id  INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, range_1    tinyint UNSIGNED
, range_2    tinyint );
 
-- Insert test values.
INSERT INTO verify
VALUES
 (NULL, 0, 0)
,(NULL, 1, 1)
,(NULL, 1,-1)
,(NULL, 2, 2);
 
-- Query results.
SELECT range_1 AS "Value"
,      CASE
         WHEN range_1 = TRUE THEN 'True'
         WHEN range_1 = FALSE THEN 'False'
         ELSE 'Invalid'
       END AS "Unsigned"
,      range_2 AS "Value"
,      CASE
         WHEN range_2 = TRUE  THEN 'True'
         WHEN range_2 = FALSE THEN 'False'
         ELSE 'Invalid'
       END AS "Signed"
FROM   verify;

The results of the test demonstrate that only a 0 or 1 value validates against the false or true constants, as shown:

+-------+----------+-------+---------+
| Value | Unsigned | Value | Signed  |
+-------+----------+-------+---------+
|     0 | False    |     0 | False   |
|     1 | True     |     1 | True    |
|     1 | True     |    -1 | Invalid |
|     2 | Invalid  |     2 | Invalid |
+-------+----------+-------+---------+

Based on the comment, the 256 value range of TINYINT is found here.

Written by maclochlainn

February 26th, 2010 at 2:32 am

Posted in MAMP,MySQL,PHPMyAdmin,sql

PHP Binding a Wildcard

with 8 comments

Somebody asked me about how you bind a parameter in mysqli when a multiple character wildcard needs to be next to the variable value. They’d found that it worked when creating a SQL statement by concatenation (string gluing), like this:

$sql = "SELECT item_title FROM item WHERE item_title LIKE '%".$title."%'";

However, they tried to bind the variable following the same pattern, and found that it failed. They used the following type of syntax:

$sql = "SELECT item_title FROM item WHERE item_title LIKE '%?%'";

It raised the following error:

Warning: mysqli_stmt_bind_param() [function.mysqli-stmt-bind-param]: Number of variables doesn't match number of parameters in prepared statement in program_name on line #

The reason is the parser, it expects variables to be independent tokens in the SQL string. You can’t bind a string into the shell of an apostrophe delimited string. You could naturally, make an assignment to the local PHP variable before binding it to the variable, like this:

$title = '%'.$title.'%';

It’s actually a better idea to concatenate the multiple character wildcard operator inside the SQL statement. The correct syntax requires that you use the CONCAT() function. You could reset to use piped concatenation but generally you should avoid that on the MySQL platform (see this post for an explanation of SQL concatenation on Oracle, MySQL, and SQL Server).

This is the required statement when using a MySQL database:

$sql = "SELECT item_title FROM item WHERE item_title LIKE CONCAT('%',?,'%')";

Written by maclochlainn

February 21st, 2010 at 1:13 am

Posted in LAMP,MAMP,MySQL,PHP

The ereg() function is gone

with one comment

Alas, poor ereg() I abused you well. PHP 5.3 has deprecated ereg() and now we must move forward with preg_match(). Along with that change, somebody asked me to show how to upload images to the file system as opposed to the database. Personally, I think they should be stored in the database.

With my bias toward databases, I threw in a virtual directory mapping in a MySQL database because it doesn’t natively support an Oracle equivalent BFILE data type. You can see this older post how to use the DBA_DIRECTORIES view in Oracle to mimic this behavior.

Naturally, MySQL is the preferred database of the person asking the question. You could also implement this exactly the same in Oracle but you really don’t want to do so. Using Oracle’s virtual directories has it’s own pre-built set of security features. They provide a more robust solution.

The code is presented as follows (setup for MySQL instructions):

  1. Create and seed the DIRECTORY table in MySQL:
-- Create a directory table.
CREATE TABLE directory
( directory_id   INT PRIMARY KEY AUTO_INCREMENT
, virtual_name   VARCHAR(30)
, directory_name VARCHAR(60));
 
-- Seed the table with a virtual directory mapping.
INSERT INTO directory VALUES ( NULL,'CMS_IMAGES','C:\\Data' );
  1. Create a MySQLCredentials.inc credentails file for inclusion in the PHP program:
1
2
3
4
5
6
7
<?php
  // Connection variables.
  define('HOSTNAME',"localhost");
  define('USERNAME',"student");
  define('PASSWORD',"student");
  define('DATABASE',"sampledb");
?>
  1. Create the PHP uploading program, named MySQLFileUpload.php:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
<?php
  // Set database credentials.
  include_once("MySQLCredentials.inc");
 
  // Declare input variables.
  $id = (isset($_POST['id'])) ? $_POST['id'] : 1021;
 
  // Upload a file to server in a mapped physical drive location.
  if (process_uploaded_file(map_virtual_directory($id)))
    print "Successfully Uploaded the file.<br />";
 
  // Map a virtual directory to a physical directory.
  function map_virtual_directory($virtual) {
 
    // Return successful attempt to connect to the database.
    if (!$c = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) {
 
      // Print user message.
      print "Sorry! The connection to the database failed. Please try again later.";
 
      // Return error message.
      print mysqli_error();
 
      // Kill the resource.
      die();
    }
    else {
 
      // Initialize a statement in the scope of the connection.
      $stmt = mysqli_stmt_init($c);
 
      // Declare a case insensitive dynamic SQL statement.
      $sql = "SELECT directory_name FROM directory WHERE virtual_name = UCASE(?)";
 
      // Prepare the statement.
      if (mysqli_stmt_prepare($stmt,$sql)) {
 
        // Bind the input parameter to the prepared statement.
        mysqli_stmt_bind_param($stmt,'s',$virtual);
 
        // Execute the prepared statement.
        if (mysqli_stmt_execute($stmt)) {
 
          // Bind the result to a local variable.
          mysqli_stmt_bind_result($stmt,$directory);
 
          // FetchPrepare statement and link it to a connection.
          while (mysqli_stmt_fetch($stmt))
            return $directory;
        }
        else
          // Return error message.
          print mysqli_error();
      }
      else
        // Return error message.
        print mysqli_error();
 
          // Disconnect from database.
      mysqli_close($c);
    }
  }
 
  // Manage file upload.
  function process_uploaded_file($directory) {
 
    /* Assume the application may allow a virtual directory with a trailing backslash or forward
       slash to be stored in the database, and manage both scenarios across Windows and Linux. */
    if (preg_match(".Win32.",$_SERVER["SERVER_SOFTWARE"]))
      if (preg_match("/\b\\\/",$directory));
      else if (preg_match("/\b\//",$directory)) {
        $directory = substr($directory,0,strlen($directory)-1);
        $directory = $directory."\\";
      }
      else $directory = $directory."\\";
    else
      if (preg_match("/\b\//",$directory))
        $directory = substr($directory,0,strlen($directory)-1);
      else
        $directory = $directory."/";  
 
    // Check for, move uploaded file, and confirm processing.
    if (is_uploaded_file($_FILES['userfile']['tmp_name'])) {
 
      // Move temporary cache into a file directory with the uploaded file name.
      move_uploaded_file($_FILES['userfile']['tmp_name'],$directory.$_FILES['userfile']['name']);
 
      // Remove this from real code, it's here for example only. ;-)
      print "Uploaded [".$_FILES['userfile']['name']."] to".$directory."<br />";
 
      // Return true to encapsulate the functional logic on success.
	    return true;
    }
    else
      // Return false to encapsulate the functional logic on failure.
	    return false;
  }
?>
  1. Create a web page to test it:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<html>
<head>
<title>
  UploadFileFormMySQL.htm
</title>
</head>
<body>
<form id="uploadForm"
      action="MySQLFileUpload.php"
      enctype="multipart/form-data"
      method="post">
  <table border=0 cellpadding=0 cellspacing=0>
    <tr>
      <td width=125>Item Number</td>
      <td>
        <input id="id" name="id" type="text">
      </td>
    </tr>
    <tr>
      <td width=125>Select File</td>
      <td>
        <input id="uploadfilename" name="userfile" type="file">
      </td>
    </tr>
    <tr>
      <td width=125>Click Button to</td>
      <td><input type="submit" value="Upload File"></td>
    </tr>
  </table>
</form>
</body>
</html>

Hope this helps a few folks. I imagine that the prepared statement with bound variables may help a few folks because it’s not found (at writing) on the php.net web site.

Written by maclochlainn

December 29th, 2009 at 2:03 am

Posted in LAMP,Linux,MAMP,PHP,Zend

Toad for MySQL Freeware

without comments

While SQL Developer is a nice tool and free, Toad is still an awesome development platform, albeit for Windows. In fact, it was annoying to have to install the Microsoft .NET Framework before installing it. It is free for MySQL!

Since my students have to do all their work in Oracle and then port it to MySQL, I demonstrate Quest’s Toad for MySQL’s at the end of the term. I don’t want them to leverage the automatic ERD diagramming while they’re learning how to do it.

There’s only one real trick to making automatic ERD diagramming work. That trick requires that you write your loading scripts for the Inno DB and use referential integrity constraints. My sample Video Store scripts for my database class are updated for MySQL referential integrity.

ToadERDModel

Unlike the friendly CASCADE CONSTRAINTS clause you can use in Oracle, MySQL won’t let you create a re-runnable script with only DDL statements. Actually, the constraint comes from the InnoDB engine. You must issue a specialized InnoDB command before running your script:

11
12
13
-- This enables dropping tables with foreign key dependencies.
-- It is specific to the InnoDB Engine.
SET FOREIGN_KEY_CHECKS = 0;

Primary keys are a bit different from Oracle and it appears you can’t name them, at least I couldn’t see how to do it. Here’s an example of primary and foreign key constraints in MySQL. The primary key is inline and the foreign key constraints are out of line. This example from the downloadable scripts uses self referencing foreign key constraints.

24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
CREATE TABLE system_user
( system_user_id              INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, system_user_name            CHAR(20)     NOT NULL
, system_user_group_id        INT UNSIGNED NOT NULL
, system_user_type            INT UNSIGNED NOT NULL
, first_name                  CHAR(20)
, middle_name                 CHAR(20)
, last_name                   CHAR(20)
, created_by                  INT UNSIGNED NOT NULL
, creation_date               DATE         NOT NULL
, last_updated_by             INT UNSIGNED NOT NULL
, last_update_date            DATE         NOT NULL
, KEY system_user_fk1 (created_by)
, CONSTRAINT system_user_fk1 FOREIGN KEY (created_by)
  REFERENCES system_user (system_user_id)
, KEY system_user_fk2 (last_updated_by)
, CONSTRAINT system_user_fk2 FOREIGN KEY (last_updated_by)
  REFERENCES system_user (system_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Once installed (instructions are here) and connected to the MySQL database, you simply click the ERD icon in the top panel and drag the tables onto the canvas. You’ll see something like this (by the way click on the image to see its full size):

ToadQuery

Have fun with it. It’ll be interesting to see how Oracle positions MySQL when they own it. My hunch is that they’ll continue to sell it and provide it as an open source product.

Written by maclochlainn

July 11th, 2009 at 5:03 pm

PHP for loading a BLOB

with 2 comments

Sometimes you chalk something up as straightforward because you’ve done it a while. I did that in a lab assignment recently. It asked my students to upload a large text file and image to the MySQL database, store them in a TEXT and MEDIUMBLOB column, and read them back out of the database, like this:

mysql_lob4

The trick was that I wanted them to read the file into a string and then load the string. There wasn’t a single code example to do this out there, except some that might exist behind an account and credit card payment. I put together a complete example like the Oracle LOB processing page. You can find it here in the MySQL LOB processing blog page. More or less, it shows you how to stream an image into a MySQL database in chunks (I chose 8 K chunks).

The general tricks to upload a string require you enclose to enclose them with the addslashes() function before assigning a binary stream to a variable, then stripslashes() function by segment before you load it to the database. You really don’t need to do that. It’s a myth. The binary stream doesn’t require that extra handling. In fact, you can corrupt certain images when you use the addslashes() and stripslashes() functions; they should be avoided in this context.

You should do it in streams (at least when they’re larger than 1 MB), I chose the procedural mysqli to demonstrate it because there wasn’t an example that I or my students could find on the web. Just for information, some laptops don’t have the resources to accommodate LARGEBLOB datatypes on the Windows OS. The BLOB or MEDIUMBLOB should work fine, especially for this little example.

46
47
48
49
50
51
52
53
54
55
56
57
58
    // Declare a PL/SQL execution command.
    $sql = "UPDATE item SET item_blob = ? WHERE item_id =  ?";
 
    // Prepare statement and link it to a connection.
    if (mysqli_stmt_prepare($stmt,$sql)) {
      mysqli_stmt_bind_param($stmt,"bi",$item_blob,$id);
 
      $start = 0;
      $chunk = 8192;
      while ($start < strlen($item_blob)) {
        mysqli_send_long_data($stmt,0,substr($item_blob,$start,$chunk));
        $start += $chunk;
      }

You can find the code in that blog page referenced. Hope it helps some folks.

Written by maclochlainn

July 9th, 2009 at 2:57 am

Posted in LAMP,MAMP,MySQL,PHP

Zend Java Bridge 32-bit

with 2 comments

I just wanted to see how Zend Server Community Edition might be broken. Performing a full feature install on Windows x64, I confirmed that Zend Server’s Java Bridge depends on the 32-bit JRE (common sense prevails). Installing it against the JRE 64-bit jvm.dll did raised an exception but none of the instructions address the problem.

It’s one of those simplifying assumptions – everybody knows 32-bit software works with 32-bit software. Anybody running on Windows XP x64 should know that they may need a JDK 64-bit and both a JRE 64-bit and JRE 32-bit for some applications. For those who don’t know this, like my students and other newbies, when you run Windows XP the 64-bit stuff goes in the C:\Program Files directory and the 32-bit stuff goes in the C:\Program Files (x86) directory. This lets you develop 32-bit or 64-bit Java applications on the same 64-bit machine.

zendbroken

Another tidbit of interest, don’t choose a full install if you’ve already installed MySQL. The Zend Community Server isn’t smart enough to alter the configuration to another port, and their my.ini points to a 3306 listener port. This causes the MySQL_ZendServer51 service to fail. It also doesn’t uninstall well. If you don’t want to clean the Windows Registry, don’t choose to install a second MySQL.

As an FYI, the Zend installation of MySQL doesn’t put a password on the root account. Don’t forget to add one after the install if you go down the full product road. This has the Zend Server Community Edition installation instructions.

Written by maclochlainn

July 7th, 2009 at 9:39 pm