MacLochlainns Weblog

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('%',?,'%')";

<?php
  // Connection variables.
  define('HOSTNAME',"localhost");
  define('USERNAME',"student");
  define('PASSWORD',"student");
  define('DATABASE',"sampledb");
?>

<?php
  // Set database credentials.
  include_once("MySQLCredentials.inc");
 
  // 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.";
 
    // Assign the OCI error and format double and single quotes.
    print mysqli_error();
 
    // Kill the resource.
    die();
  }
  else {
    // Declare input variables.
    $title = (isset($_GET['title'])) ? $_GET['title'] : $title = "RoboCop";
 
    query_insert($title);
  }
 
  // Query results afret an insert.
  function query_insert($title)
  {
    // 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.";
 
      // Assign the OCI error and format double and single quotes.
      print mysqli_error();
 
      // Kill the resource.
      die();
    }
    else {
 
      // Initialize a statement in the scope of the connection.
      $stmt = mysqli_stmt_init($c);
 
      // Declare a SQL SELECT statement returning a CLOB.
      $sql = "SELECT item_title FROM item WHERE item_title LIKE CONCAT('%',?,'%')";
 
      // Prepare statement.
      if (mysqli_stmt_prepare($stmt,$sql)) {
        mysqli_stmt_bind_param($stmt,"s",$title);
 
        // Execute it and print success or failure message.
        if (mysqli_stmt_execute($stmt)) {
 
          // Store result.
          mysqli_stmt_store_result($stmt);
 
          // Bind result to local variable.
          mysqli_stmt_bind_result($stmt,$item_title);
 
          // Open HTML table and print header.
          $out = '<table border="1" cellpadding="3" cellspacing="0">';
          $out .= '<tr>';
          $out .= '<th align="center" style="font-weight:bold;padding:2px;margin:1px;background:#8DB3E2;width:300px">Item Title</th>';
          $out .= '</tr>';
 
          // Read result.
          while (mysqli_stmt_fetch($stmt)) {
            $out .= '<tr>';
            $out .= '<td style="padding:2px;margin:1px;background:#DBE5F1;">'.$item_title.'</td>';
            $out .= '</tr>';
          }
 
          // Close the HTML table.
          $out .= '</table>';
 
 
          // Print the HTML table.
          print $out;
        }
      }
 
      // Free system resources.
      mysqli_stmt_free_result($stmt);
 
      // Disconnect from database.
      mysqli_close($c);
    }
  }
?>

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' );

2. 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");
?>

3. 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;
  }
?>

4. 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.

We got away with a bit of lazy PHP, at least through PHP 5.29. Especially when we worked with LOB columns. All that is at an end with PHP 5.3. You must now call the mysqli_stmt_store_results($stmt); function (line number 76 in the program snippet below from source) between the execute and bind result phrases.

The function transfers a result set from a prepared statement. You need to use it when performing a SELECT, SHOW, DESCRIBE, or EXPLAIN statement that you will call by the mysqli_stmt_fetch($stmt); function.

Without using it, you may only see part of a MEDIUMTEXT column displayed. Likewise, an image from a MEDIUMBLOB column won’t be displayed at all unless you store their results too, by using the same function call.

68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

// Prepare statement.
if (mysqli_stmt_prepare($stmt,$sql)) {
  mysqli_stmt_bind_param($stmt,"i",$id);
 
  // Execute it and print success or failure message.
  if (mysqli_stmt_execute($stmt)) {
 
    // Store result, which eliminates memory cutting off lob streams.
    mysqli_stmt_store_result($stmt);
 
    // Bind result to local variable.
    mysqli_stmt_bind_result($stmt, $desc);
 
    // Read result.
    mysqli_stmt_fetch($stmt);
 
    // Print result.
    print $desc;
  }
}

Also, that oldie ereg() function is deprecated for preg_match() in PHP 5.3 and removed in PHP 6. Don’t forget to make those changes if you were lazy like me before updating to PHP 5.3.

Installed Zend Community Edition on Windows 7 64-bit. It worked easily. You just need to remember to install the JSDK 32-bit version for the Java Bridge. Clear notation about phpMyAdmin and MySQL being separate downloads has been added to the new Zend Community Edition Server (4.0.6), and it clearly does support Windows 7.

If you plan on installing MySQL and Oracle, I would recommend you install MySQL after you install Oracle and the Zend Community Server. However, it doesn’t matter because both ways work.

That completes my WAMP (Windows, Apache, MySQL, Perl, PHP, or Python) and OPAW (Oracle, Perl, PHP, or Python, Apache, Windows) installations. Actually, I’m not sure there is an OPAW acronym for a LAMP stack running Oracle on a Windows platform. OPAL is the acronym for a LAMP stack running an Oracle database, but I’ve never seen one before for Windows. Therefore, I created one.

My two cents worth …

I’d vote for clearer guidance on these acronyms. After all, they’re only purpose appears to be how to market variants of LAMP. The variants that I’ve seen for LAMP (Linux) are: MAMP (Mac OS X), SCAMP (Santa Cruz Operation), SAMP (Solaris), OAMP (OpenBSD, and WAMP (Windows) for MySQL database versions. The key seems to be swapping the first letter. I’ve only seen OPAL (Linux) officially for a LAMP stack that uses an Oracle database on a Linux platform. While my OPAW leverages what I perceive as a possible pattern, it may be wrong. Does anybody know what the right way to label these is?

I put up a contact page (@Contact Me) for two reasons. Too many folks wanted to ask something and could only do so through a comment. I would review the comment, email them, et cetera. Reason two is simpler, I should have done it from the outset.

There were a few gotchas beyond installing and securing your public and private keys. I figured that it might be helpful to note them here, especially if I have to troubleshoot it later.

1. You need create a template, like contact.php script, which should include your contact form. You’ll also embed some reCAPTCHA PHP inside this file.

<?php
<form action="/wp-content/themes/<theme name>/contact_code.php" class="contact" method="post">
?>

<?php
  // Include the reCAPTCHA library, this assumes a relative directory.
  require_once('recaptchalib.php');
 
  // Get a key from http://recaptcha.net/api/getkey
  $publickey = "<your public key value goes here>";
 
  // The response from reCAPTCHA
  $resp = null;
  // The error code from reCAPTCHA, if any
  $error = null;
 
  echo recaptcha_get_html($publickey, $error);
?>

2. You need to put the following WordPress template stub at the beginning of your contact.php page.

<?php
  /*
    Template Name: contact
  */
?>

3. After you create and position the contact.php file in the /wp-content/themes/<theme_name>/ within the document root, you should create a new blog page. You don’t put any text in the page, rather you simply choose the contact.php from your list of templates. You also need a title for the page, I used @contact-me (the @ won’t be used in any reference in the URL), which is an alias to the page

4. You need to put the balance of your PHP code in a separate file, like contact_code.php and put the URL re-write and reCAPTCHA calls here.

<?php
  // Include the library for reCAPTCHA at the theme level in the file hierarchy.
  require_once('recaptchalib.php');
 
  // Put any form PHP here.
 
  // Declare your private key.
  $privatekey = "<put your private key here>";
 
  // Check whether there a reCAPTCHA response?
  if ($_POST["recaptcha_response_field"]) {
    $resp = recaptcha_check_answer($privatekey
                                  ,$_SERVER["REMOTE_ADDR"]
                                  ,$_POST["recaptcha_challenge_field"]
                                  ,$_POST["recaptcha_response_field"]);
    // Check for valid response.
    if ($resp->is_valid) {
      if(!$errors) {
        $to = '<email here>@<domain here>';
        $subject = $subject;
        $message = "From: ".$name."\nMessage: ".$message;
        $from = 'From:'.$email;
        mail($to,$subject,$message,$from);
 
        // Set the header to the document root when successful.
        header("Location: /");
      }
    }
    else {
      // Set the header to the virtual path for the contact page.
      header("Location: /contact-me/");
      $error = $resp->error;
    }
  }
?>

That’s about it. If I forgot something, you’ll let me know.

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:

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.

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.

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.

I saw a post in the OTN forum that asked a simple question and had no simple example as an answer, so I thought it would be nice to provide one. Basically, somebody wanted to know how to call into a stored procedure and return a value with more or less one pass-by-value and another pass-by-reference variable.

This defines a simple echo procedure, which takes a message and returns a formatted message:

1
2
3
4
5
6
7

CREATE OR REPLACE PROCEDURE echo
( message IN     VARCHAR2
, reply      OUT VARCHAR2 ) IS
BEGIN
  reply := 'Message ['||message||'] received.';
END;
/

The following PHP calls the procedure and returns the value. It uses the required connection syntax for the Zend Server Community Edition.

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

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", "localhost/xe");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    // Initialize incoming message whether or not parameter sent.
    $msg_in = (isset($_GET['msg'])) ? $_GET['msg'] : "Cat got your keyboard?";
 
    // Set the call statement, like a SQL statement.
    $sql = "BEGIN echo(:a,:b); END;";
 
    // Prepare the statement and bind the two strings.
    $stmt = oci_parse($c,$sql);
 
    // Bind local variables into PHP statement, you need to size OUT only variables.
    oci_bind_by_name($stmt, ":a", $msg_in);
    oci_bind_by_name($stmt, ":b", $msg_out, 80, SQLT_CHR);
 
    // Execute it and print success or failure message.
    if (oci_execute($stmt)) {
      print $msg_out;
    }
    else {
      print "Sorry, I can't do that Dave...";
    }
    // Free resources.
    oci_free_statement($stmt);
    oci_close($c);
  }
?>

You can then test it with or without a parameter, like this example with a parameter:

http://localhost/message_reply.php?msg="Sample message"

I put a link in the forum to this, and I hope it helps a few folks.

I got all the screen shots out last night, then I realized that the configuration instructions were missing. They’ve been added now, and you can check if you’re interested.

In the process, I noticed that Zend Server Community Edition is using connection pooling by default with Oracle. This means that the TNS alias must be qualified by a hostname.

For example, on your local PC with Oracle Database 10g Express Edition, the default TNS alias is XE. You can’t use that anymore when you’re running the Zend Server. You must qualify it as localhost/XE or hostname/XE as the connection string. A natural alternative is to insert a fully qualified TNS network map

Here are three possible connection patterns:

1
2
3
4
5
6
7
8
9
10
11
12

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", "localhost/xe");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    print "Congrats! You've connected to an Oracle database!";
    oci_close($c);
  }
?>

02-JUL-2009 23:32:11 * (CONNECT_DATA=(SERVICE_NAME=xe)(CID=(PROGRAM=C:\Program?Files\Zend\ZendServer\bin\php-cgi.exe)(HOST=MCLAUGHLINXP32)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1405)) * establish * xe * 0

1
2
3
4
5
6
7
8
9
10
11
12

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", "hostname/xe");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    print "Congrats! You've connected to an Oracle database!";
    oci_close($c);
  }
?>

02-JUL-2009 23:29:16 * (CONNECT_DATA=(SERVICE_NAME=xe)(CID=(PROGRAM=C:\Program?Files\Zend\ZendServer\bin\php-cgi.exe)(HOST=MCLAUGHLINXP32)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.153.138)(PORT=1403)) * establish * xe * 0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

<?php
  $tns = "(DESCRIPTION =
              (ADDRESS=(PROTOCOL = TCP)(HOST = mclaughlinxp32)(PORT = 1521))
              (CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = XE)))";
 
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", $tns);
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    print "Congrats! You've connected to an Oracle database!";
    oci_close($c);
  }
?>

02-JUL-2009 23:27:10 * (CONNECT_DATA=(SERVICE_NAME=XE)(CID=(PROGRAM=C:\Program?Files\Zend\ZendServer\bin\php-cgi.exe)(HOST=MCLAUGHLINXP32)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.153.138)(PORT=1401)) * establish * XE * 0

I’m sure it was in the documents but then again, I didn’t read them.

The Zend Core Server replaces the deprecated Zend Core for Oracle. I’ve put a quick installation guide here. It’s much nicer, and the licensed server is now the recommended direction from Oracle.

The community edition also installs MySQL, phpMySQLAdmin, and a brand new console. You should try it out.