MySQL and PHP
This page demonstrates how to upload, store, and manage Text (Character Large Data Streams) and BLOBs (Binary Large Objects). It provides MySQL equivalent instructions to those for manaing LOBs in an Oracle database. As covered in Chapter 8 in my Oracle Database 11g PL/SQL Programming book.
Before you begin these steps, you should have already installed Zend Server Community Edition. If you haven’t done so, please click here for instructions.
Create directories or folders, and position code ↓
This section provides you with instructions on how to position the code components in Windows, at least for the newbie. If you’re on Linux, you probably know how to do most if not all of this already. Likewise, if you already know how to put things in the right place, please choose your own locations.
- Create a
LOB
(Large Object) directory for the PHP files inside thehtdocs
directory.
- You can down the MySQL PHP Upload LOB Web Code zip file and unzip it into the directory you just created. It can co-exist with the Oracle equivalent if you’ve done that already.
Load a TEXT (like an Oracle CLOB) column to the MySQL database ↓
This is a copy of the three files required to load a large string to a MySQL database into a mediumtext
data type. The code is in clear text because somebody asked for it. They’re nervous about zip files. Click the title above to expand all the code text.
MySQLCredentials.inc
1 2 3 4 5 6 7 | <?php // Connection variables. define('HOSTNAME',"localhost"); define('USERNAME',"student"); define('PASSWORD',"student"); define('DATABASE',"sampledb"); ?> |
UploadItemDescMySQLForm.htm
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 | <html> <head> <title> UploadItemDescMySQLForm.htm </title> </head> <body> <form id="uploadForm" action="UploadItemDescMySQL.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>Item Title</td> <td> <input id="title" name="title" 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> |
UploadItemDescMySQL.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 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 | <?php // Set database credentials. include_once("MySQLCredentials.inc"); // Displayed moved file in web page. $item_desc = process_uploaded_file(); // 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 mysqli_error() and format double and single quotes. print mysqli_error(); // Kill the resource. die(); } else { // Declare input variables. $id = (isset($_POST['id'])) ? (int) $_POST['id'] : $id = 21; $title = (isset($_POST['title'])) ? $_POST['title'] : $title = "Harry #1"; // Initialize a statement in the scope of the connection. $stmt = mysqli_stmt_init($c); // Declare a PL/SQL execution command. $sql = "Update item set item_desc = ? where item_id = ?"; // Prepate statement and link it to a connection. if (mysqli_stmt_prepare($stmt,$sql)) { mysqli_stmt_bind_param($stmt,"si",$item_desc,$id); // Execute it and print success or failure message. if (mysqli_stmt_execute($stmt)) { query_insert($id,$title); } else { print "You're target row doesn't exist."; } } // Disconnect from database. mysqli_close($c); } // Query results afret an insert. function query_insert($id,$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_desc FROM item WHERE item_id = ?"; // 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)) { // Bind result to local variable. mysqli_stmt_bind_result($stmt, $desc); // Read result. mysqli_stmt_fetch($stmt); // Format HTML table to display biography. $out = '<table border="1" cellpadding="3" cellspacing="0">'; $out .= '<tr>'; $out .= '<td align="center" class="e">'.$title.'</td>'; $out .= '</tr>'; $out .= '<tr>'; $out .= '<td class="v">'.$desc.'</td>'; $out .= '</tr>'; $out .= '</table>'; // Print the HTML table. print $out; } } // Disconnect from database. mysqli_close($c); } } // Manage file upload and return file as string. function process_uploaded_file() { // Declare a variable for file contents. $contents = ""; // Define the upload file name for Windows or Linux. if (preg_match(".Win32.",$_SERVER["SERVER_SOFTWARE"])) $upload_file = "C:\\temp\\".$_FILES['userfile']['name']; else $upload_file = "/tmp/".$_FILES['userfile']['name']; // Check for and move uploaded file. if (is_uploaded_file($_FILES['userfile']['tmp_name'])) move_uploaded_file($_FILES['userfile']['tmp_name'],$upload_file); // Open a file handle and suppress an error for a missing file. if ($fp = @fopen($upload_file,"r")) { // Read until the end-of-file marker. while (!feof($fp)) $contents .= fgetc($fp); // Close an open file handle. fclose($fp); } // Return file content as string. return $contents; } ?> |
Load a BLOB column to the MySQL database ↓
This is a copy of the four files required to load a large image to a MySQL database into a MEDIUMBLOB
data type. The fourth file reads the binary image and translates it into an HTML header and image that can be read through a call to the src
attribute of an img
tag. You can find the call to the forth file in the UploadItemBlobMySQL.php
.
The code is in clear text because somebody asked for it. They’re nervous about zip files. Click the title above to expand all the code text.
MySQLCredentials.inc
1 2 3 4 5 6 7 | <?php // Connection variables. define('HOSTNAME',"localhost"); define('USERNAME',"student"); define('PASSWORD',"student"); define('DATABASE',"sampledb"); ?> |
UploadItemBlobMySQLForm.htm
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 | <html> <head> <title> UploadItemBlobMySQLForm.htm </title> </head> <body> <form id="uploadForm" action="UploadItemBlobMySQL.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>Item Title</td> <td> <input id="title" name="title" 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> |
UploadItemBlobMySQL.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 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 | <?php // Set database credentials. include_once("MySQLCredentials.inc"); // Displayed moved file in web page. $item_blob = process_uploaded_file(); // 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 mysqli_error() error and format double and single quotes. print mysqli_error(); // Kill the resource. die(); } else { // Declare input variables. $id = (isset($_POST['id'])) ? (int) $_POST['id'] : 1021; $title = (isset($_POST['title'])) ? $_POST['title'] : "Harry #1"; // Initialize a statement in the scope of the connection. $stmt = mysqli_stmt_init($c); // 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_stmt_send_long_data($stmt,0,substr($item_blob,$start,$chunk)); $start += $chunk; } // Execute the PL/SQL statement. if (mysqli_stmt_execute($stmt)) { query_insert($id,$title); } else { print "Your target row doesn't exist."; } } else { print "mysqli_stmt_prepare() failed."; } // Disconnect from database. mysqli_close($c); } // Query results afret an insert. function query_insert($id,$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_desc FROM item WHERE item_id = ?"; // Prepare statement and link it to a connection. if (mysqli_stmt_prepare($stmt,$sql)) { mysqli_stmt_bind_param($stmt,"i",$id); // Execute the PL/SQL statement. if (mysqli_stmt_execute($stmt)) { // Bind result to local variable. mysqli_stmt_bind_result($stmt, $data); // Read result. mysqli_stmt_fetch($stmt); // Format HTML table to display BLOB photo and CLOB description. $out = '<table border="1" cellpadding="5" cellspacing="0">'; $out .= '<tr>'; $out .= '<td align="center" class="e">'.$title.'</td>'; $out .= '</tr>'; $out .= '<tr><td class="v">'; $out .= '<div>'; $out .= '<div style="margin-right:5px;float:left">'; $out .= '<img src="ConvertMySQLBlobToImage.php?id='.$id.'">'; $out .= '</div>'; $out .= '<div style="position=relative;">'.$data.'</div>'; $out .= '</div>'; $out .= '</td></tr>'; $out .= '</table>'; // Print the HTML table. print $out; } else { print "You're target row doesn't exist."; } } // Disconnect from database. mysqli_close($c); } } // Manage file upload and return file as string. function process_uploaded_file() { // Declare a variable for file contents. $contents = ""; // Define the upload file name for Windows or Linux. if (preg_match(".Win32.",$_SERVER["SERVER_SOFTWARE"])) $upload_file = "C:\\TEMP\\".$_FILES['userfile']['name']; else $upload_file = "/tmp/".$_FILES['userfile']['name']; // Check for and move uploaded file. if (is_uploaded_file($_FILES['userfile']['tmp_name'])) move_uploaded_file($_FILES['userfile']['tmp_name'],$upload_file); // Open a file handle and suppress an error for a missing file. if ($fp = @fopen($upload_file,"r")) { // Read until the end-of-file marker. while (!feof($fp)) $contents .= fgetc($fp); // Close an open file handle. fclose($fp); } // Return file content as string. return $contents; } ?> |
ConvertMySQLBlobToImage.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 | <?php // Database credentials must be set manually because an include_once() function // call puts something ahead of the header, which causes a failure when rendering // an image. // Return successful attempt to connect to the database. if (!$c = @mysqli_connect("localhost","student","student","sampledb")) { // 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. $id = (isset($_GET['id'])) ? (int) $_GET['id'] : 1023; // Initialize a statement in the scope of the connection. $stmt = mysqli_stmt_init($c); // Declare a SQL SELECT statement returning a MediumBLOB. $sql = "SELECT item_blob FROM item WHERE item_id = ?"; // Prepare statement and link it to a connection. if (mysqli_stmt_prepare($stmt,$sql)) { mysqli_stmt_bind_param($stmt,"i",$id); // Execute the PL/SQL statement. if (mysqli_stmt_execute($stmt)) { // Bind result to local variable. mysqli_stmt_bind_result($stmt, $image); // Read result. mysqli_stmt_fetch($stmt); } } // Disconnect from database. mysqli_close($c); // Print the header first. header('Content-type: image/x-png'); imagepng(imagecreatefromstring($image)); } ?> |
- Create a
temp
directory for the upload target location, as qualified in the PHP code. The PHP code works regardless of whether you’re on Windows or Linux, but it does depend on the creation of this directory.
- Create a directory or folder for the large file source directories. This directory is probably on your test machine (laptop) but it mimics a client laptop and would work if your server was on a different machine.
- Inside the Upload directory, you should create the following two directories:
- You should download the CLOB Text File zip file and unzip it into the
textfiles
directory; then download the BLOB Image File zip file and unzip it into the imagefiles
directory.Assuming you’ve downloaded the zip files and extracted them into the correct locations, this section is done.
Prepare the MySQL database ↓
This section provides you with instructions on how to ensure everything will work once the PHP programs call the database. Even if you have one of my sample Video Store databases, you should verify and add appropriate columns. This post assumes you’ve downloaded the one of my basic Video Store models
- Navigate to the directory that you created for SQL scripts, which should be
C:\Data\SQL
. In that directory at the command prompt, connect as thestudent
user, which should be student. You connect to the MySQL database, with the following syntax as student (if you need more help, check this blog post on configuring MySQL).
C:\> mysql -ustudent -pstudent |
Once connected to the database, you run the files to create the database, like:
mysql> SOURCE C:\DATA\SQL\create_mysql_store.sql mysql> SOURCE C:\DATA\SQL\seed_mysql_store.sql |
- Navigate to the directory that you created for SQL scripts, which should be
C:\Data\SQL
. In that directory at the command prompt, connect as thestudent
user, or whichever account you’re using. You should confirm that you have aitem_desc
column ofTEXT
data type, and anitem_blob
column ofMEDIUMBLOB
type in theitem
table. If you don’t have those columns, you can add them with the following statement:
ALTER TABLE item ADD (item_desc TEXT, item_blob MEDIUMBLOB); |
After ensuring that you have those two columns, you’ve completed this section.
Test the Configuration ↓
This section shows you how to test all that you’ve done. It works provided you created the directories and extracted the zip file contents to their respective directories. The virtual URL actually maps to the C:\Program Files\Zend\Apache2\htdocs\lob
directory.
- Enter the
http://localhost/lob/UploadItemDescMySQLForm.htm
URL, and complete the form by choosing a validitem_id
column value and text file from yourC:\Data\Upload\TextFiles
directory. Then, click the Upload File button (you can see a larger version of the image by clicking on it).
- This page displays after you successfully upload the text file to the database.
- Enter the
http://localhost/lob/UploadItemBlobFormMySQL.htm
URL, and complete the form by choosing a validitem_id
column value and image file from yourC:\Data\Upload\ImageFiles
directory. Then, click the Upload File button (you can see a larger version of the image by clicking on it).
- This page displays after you successfully upload the image file to the database.
Troubleshooting the Configuration ↓
This section shows you how to check why something isn’t working.
- The first thing to check are the credentials. They’re in the
MySQLCredentials.inc
file. They’re posted with alocalhost
machine name,student
username,student
password, andsampledb
database.
- Not to be funny, but the second thing to check are credentials. Specifically, you need to check the credentials in the
ConvertBlobToImage.php
file. They’re individually entered in the connect string of this file because otherwise they put something in front of the header, which is disallowed to render the image.
- Check to see if the text or image file made it to the
C:\Program Files\Zend\Apache2\htdocs\lob\temp
directory. If they made it that far but no further, check to see if you have valid procedures in thestudent
schema.
- Check whether the
TEXT
andMEDIUMBLOB
are loaded into the database. You use theLENGTH
function, like this:
SELECT i.item_id , LENGTH(i.item_desc) , LENGTH(i.item_blob) FROM item i WHERE i.item_desc IS NOT NULL OR i.item_blob IS NOT NULL; |
- Check if the
item_id
value is found in the list of values.
- If you’re stumped, add a comment and explain what’s up.
If you find any problems, please let me know. I’ll be happy to fix them.
[…] the mysqli_stmt_store_results($stmt); function (line number 76 in the program snippet below from source) between the execute and bind result […]
PHP 5.29 to 5.30 Changes | MacLochlainns Weblog
7 Dec 09 at 7:15 pm
A few of the downloads return a 404 and don’t work
Aaron Haggen
29 Mar 10 at 2:50 pm
The ConvertBlobToImage needs to be renamed to ConvertMySQLBlobToImage or else the code won’t load the right file. It will try to use the Oracle version.
Daren J.
29 Mar 10 at 8:24 pm
There are two files, not one. They’re both named correctly in the zip files.
michaelmclaughlin
29 Mar 10 at 9:45 pm
I believe these are all resovled now.
michaelmclaughlin
29 Mar 10 at 9:53 pm
The video scripts linked here don’t work for half of what they are trying to do
Andrew T.
30 Mar 10 at 9:52 am
What video scripts? There are only screen shots.
michaelmclaughlin
30 Mar 10 at 12:07 pm
You have mislabeled the required name for the Blob Conversion php script. The htm form is calling ConvertMySQLBlobToImage, but you labeled it ConvertBlobToImage.
Skyler
30 Mar 10 at 1:06 pm
Skyler, I believe it’s fixed now. If I missed it somewhere else, please let me know.
michaelmclaughlin
1 Apr 10 at 12:17 pm
for the create script i had to add a “SET FOREIGN_KEY_CHECKS = 0;” to get it to work. otherwise it had problems with foreign key conflicts. is this the correct approach, or is there a better way to handle it?
jason
9 Dec 10 at 1:26 pm
The item table does not show Harry Potter 3 in it, just the first one. I don’t know if this is intentional or if we’re using a different seed file than you did when you created this.
Mike Westover
26 Mar 11 at 6:31 pm
You just add the row.
michaelmclaughlin
2 May 11 at 10:58 am
I’ve run into an interesting couple of problems. When trying to upload the picture, I get the following two errors.
Error 1:
I was able to fix this line by adding the following line to the UploadItemBlobMySQL.php file:
Here was the second error:
I haven’t been able to figure this error out yet…
Cody Conder
4 Apr 13 at 12:16 am
Cody, The solution is on this post on my technical blog. As to the
mysqli_send_long_data
it was deprecated in PHP 5.3 and eliminated in PHP 5.4. I’ll need to write a new code module for those releases – great catch!michaelmclaughlin
18 Mar 14 at 11:21 am
Jason, That’s covered in the Lesson #2: Creating Tables web page under the Re-runnable Referential Integrity Scripts segment.
michaelmclaughlin
22 Oct 14 at 2:03 am
Hurrah! After all I got a blog from where I can genuinely take valuable facts regarding my study and knowledge.
ve may bay
11 Mar 15 at 4:17 am
UploadItemBlobMySQL.php
Line 61 Changed from
TO
Tyler Nelson
6 Apr 15 at 3:48 pm
Change in the code, but using procedural methods.
michaelmclaughlin
6 Apr 15 at 4:15 pm