MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL Text/Blob Processing

with 9 comments

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.

  1. Create a LOB (Large Object) directory for the PHP files inside the htdocs directory.

oraclelobsetup1

    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 = getcwd()."\\temp\\".$_FILES['userfile']['name'];
    else
      $upload_file = getcwd()."/temp/".$_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 = getcwd()."\\temp\\".$_FILES['userfile']['name'];
    else
      $upload_file = getcwd()."/temp/".$_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));
  }
?>
  1. 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.

oraclelobsetup2

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

oraclelobsetup3

  1. Inside the Upload directory, you should create the following two directories:

oraclelobsetup4

    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

  1. 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 the student 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
  1. 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 the student user, or whichever account you’re using. You should confirm that you have a item_desc column of TEXT data type, and an item_blob column of MEDIUMBLOB type in the item 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.

  1. Enter the http://localhost/lob/UploadItemDescMySQLForm.htm URL, and complete the form by choosing a valid item_id column value and text file from your C:\Data\Upload\TextFiles directory. Then, click the Upload File button (you can see a larger version of the image by clicking on it).

mysql_lob1

  1. This page displays after you successfully upload the text file to the database.

mysql_lob2

  1. Enter the http://localhost/lob/UploadItemBlobFormMySQL.htm URL, and complete the form by choosing a valid item_id column value and image file from your C:\Data\Upload\ImageFiles directory. Then, click the Upload File button (you can see a larger version of the image by clicking on it).

mysql_lob3

  1. This page displays after you successfully upload the image file to the database.

mysql_lob4

Troubleshooting the Configuration ↓

This section shows you how to check why something isn’t working.

  1. The first thing to check are the credentials. They’re in the MySQLCredentials.inc file. They’re posted with a localhost machine name, student username, student password, and sampledb database.
  1. 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.
  1. 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 the student schema.
  1. Check whether the TEXT and MEDIUMBLOB are loaded into the database. You use the LENGTH 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;
  1. Check if the item_id value is found in the list of values.
  1. 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.

Written by maclochlainn

July 9th, 2009 at 2:35 am

Posted in Uncategorized

9 Responses to 'MySQL Text/Blob Processing'

Subscribe to comments with RSS or TrackBack to 'MySQL Text/Blob Processing'.

  1. I found an error in the select statement troubleshooting area, it’s missing commas, and you probably want item_desc where you have item_clob.

    Michael

    9 Jul 09 at 1:51 pm

  2. Thanks, I’ve updated the blog page with the corrections. 🙂

    maclochlainn

    9 Jul 09 at 2:27 pm

  3. For those revisiting the blog page, the code zip file is updated and you should use it rather than the originally posted code.

    maclochlainn

    10 Jul 09 at 12:22 am

  4. Somebody was nervous about my zip files. I completely understand that. So, I put the full code in a nested folding section.

    maclochlainn

    25 Jul 09 at 12:08 am

  5. I’m having a problem getting the data back out of the database. I do the same bind and fetch but when I do a var_dump of the variable that I bound it into I only get the first 256 characters of the blob out.

    Nevins

    17 Sep 09 at 12:46 pm

  6. I upgraded to the newest dev snapshot of php and if fixed this problem.

    Nevins

    18 Sep 09 at 8:09 am

  7. Steve’s re-posting content from Andrey at the URL below.

    If you select LOBs use the following order of execution or you risk mysqli allocating more memory that actually used

    1. prepare()
    2. execute()
    3. store_result()
    4. bind_result()

    If you skip #3, or exchange #3 with #4 then mysqli will allocate memory for the maximal length of the column. That’s 255 for TINYBLOB, 64K for BLOB (still OK), 16MB for MEDIUMBLOB – quite a lot, and 4G for LONGBLOB (good if you have so much memory). Queries which use the correct order are a bit slower when there is a LOB but this is the price of not having memory exhaustion in seconds.

    http://php.net/manual/en/mysqli-stmt.bind-result.php

    Steve Williamson

    4 Dec 09 at 3:05 pm

  8. Two quick items:

    Steve’s right but I’ve opted to leave the code for smaller memory footprints.

    The code examples are now completely in text display are compatible with PHP 5.3, but the zip files aren’t compatible with PHP 5.3 yet. I’ll update this when I fix the zip files.

    maclochlainn

    27 Feb 10 at 1:36 pm

  9. […] generic PHP/MySQL solution […]

    MySQL Image Architecture

    23 Feb 14 at 2:03 am

Leave a Reply