Wednesday Nov 18, 2009

PHP's MySQLi extension: Storing and retrieving blobs

There are a lot of tutorial out there describing how to use PHP's classic MySQL extension to store and retrieve blobs. There are also many tutorials how to use PHP's MySQLi extension to use prepared statements to fight SQL injections in your web application. But there are no tutorials about using MySQLi with any blob data at all.

Until today... ;)

Preparing the database

Okay, first I need a table to store my blobs. In this example I'll store images in my database because images usually look better in a tutorial than some random raw data.

mysql> CREATE TABLE images (
       id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
       image MEDIUMBLOB NOT NULL,
       PRIMARY KEY (id)
       );
Query OK, 0 rows affected (0.02 sec)

In general you don't want to store images in a relational database. But that's another discussion for another day.

Storing the blob

To make a long story short, here's the code to store a blob using MySQLi:

<?php
	$mysqli=mysqli_connect('localhost','user','password','db');

	if (!$mysqli)
		die("Can't connect to MySQL: ".mysqli_connect_error());

	$stmt = $mysqli->prepare("INSERT INTO images (image) VALUES(?)");
	$null = NULL;
	$stmt->bind_param("b", $null);

	$stmt->send_long_data(0, file_get_contents("osaka.jpg"));

	$stmt->execute();
?>

If you already used MySQLi, most of the above should look familiar to you. I highlighted two pieces of code, which I think are worth looking at:

  1. The $null variable is needed, because bind_param() always wants a variable reference for a given parameters. In this case the "b" (as in blob) parameter. So $null is just a dummy, to make the syntax work.

  2. In the next step I need to "fill" my blob parameter with the actual data. This is done by send_long_data(). The first parameter of this method indicates which parameter to associate the data with. Parameters are numbered beginning with 0. The second parameter of send_long_data() contains the actual data to be stored.

While using send_long_data(), please make sure that the blob isn't bigger than MySQL's max_allowed_packet:

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16776192 | 
+--------------------+----------+
1 row in set (0.00 sec)

If your data exceeds max_allowed_packet, you probably don't get any errors returned from send_long_data() or execute(). The saved blob is just corrupt!

Simply raise the value max_allowed_packet to whatever you'll need. If you're not able to change MySQL's configuration, you'll need to send the data in smaller chunks:

	$fp = fopen("osaka.jpg", "r");
	while (!feof($fp)) 
	{
 	   $stmt->send_long_data(0, fread($fp, 16776192));
	}

Usually the default value of 16M should be a good start.

Retrieving the blob

Getting the blob data out of the database is quite simple and follows the usual way of MySQLi:

<?php
	$mysqli=mysqli_connect('localhost','user','password','db');

	if (!$mysqli)
		die("Can't connect to MySQL: ".mysqli_connect_error());

	$id=1;  
	$stmt = $mysqli->prepare("SELECT image FROM images WHERE id=?"); 
	$stmt->bind_param("i", $id);

	$stmt->execute();
	$stmt->store_result();

	$stmt->bind_result($image);
	$stmt->fetch();

	header("Content-Type: image/jpeg");
	echo $image; 
?>

Connect to the database, prepare the SQL statement, bind the parameter(s), execute the statement, bind the result to a variable, and fetch the actual data from the database. In this case there is no need to worry about max_allowed_packet. MySQLi will do all the work:

3925128491.jpg

By the way...

If you want to insert a blob from the command line using MySQL monitor, you can use LOAD_FILE() to fetch the data from a file:

mysql> INSERT INTO images (image) VALUES( LOAD_FILE("/home/oswald/osaka.jpg") );

Be aware that also in this case max_allowed_packet limits the amount of data you're able to send to the database:

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_allowed_packet | 7168  | 
+--------------------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO images (image) VALUES( LOAD_FILE("/home/oswald/osaka.jpg") );
ERROR 1048 (23000): Column 'image' cannot be null
mysql> SET @@max_allowed_packet=16777216;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 | 
+--------------------+----------+
1 row in set (0.00 sec)

mysql> INSERT INTO images (image) VALUES( LOAD_FILE("/home/oswald/osaka.jpg") );
Query OK, 1 row affected (0.03 sec)

        
    

Monday Sep 21, 2009

What's going on inside your MySQL server?

Your MySQL server is under heavy load or refuses any new connections because MySQL runs out of available threads. Ever wondered why?

For me the easiest way to get a first and very helpful real-time insight in what is happening in your database server is to use the show processlist statement:

mysql> show processlist;
+--------+---------+-----------+-------------+----------------+------+--------------------+------------------------------------------------------------------+
| Id     | User    | Host      | db          | Command        | Time | State              | Info                                                             |
+--------+---------+-----------+-------------+----------------+------+--------------------+------------------------------------------------------------------+
| 426144 | DELAYED | localhost | statistics  | Delayed insert |  170 | Waiting for INSERT |                                                                  |
| 431669 | root    | localhost | NULL        | Query          |    0 | NULL               | show processlist                                                 |
| 431677 | mantis  | localhost | mantis      | Query          |    0 | Sending data       | SELECT \* FROM mantis_custom_field_table WHERE id='3'             |
+--------+---------+-----------+-------------+----------------+------+--------------------+------------------------------------------------------------------+
3 rows in set (0.00 sec)

Only the first 100 characters of a statement are shown in the Info column. If you really need more information simply call show full processlist to get the whole complete statement information.

Wednesday Sep 09, 2009

MySQL and UTF-8 recipe

If you're leaving the secure world of ASCII characters you're usually enter a dangerous land of confusion. It's actually a very complex topic and from a technical point of view there is no one to blame.

But if you don't care about the basics, don't care why or how, and just want a reliable and working system, simply follow these three advices to attain happiness and inner peace in your developer's life.

Advice 1

Define the character set for every column where you plan to store international character data:

CREATE TABLE t1 ( col1 VARCHAR(42) CHARACTER SET utf8 );

Or at least for every table:

CREATE TABLE t1 ( col1 VARCHAR(42) ) CHARACTER SET utf8;

The later example saves you some typing, but usually you don't need UTF-8 in every string column.

If you omit this parameter, MySQL assumes a default value which may be different from system to system.

Advice 2

After connecting to MySQL always do a SET NAMES 'utf8' before doing anything else.

For example in PHP:
$mysqli=mysqli_connect('localhost','demo','demo','demo');
$mysqli->query("SET NAMES 'utf8'");

If you omit this parameter, MySQL assumes a default value which may be different from system to system.

Advice 3

Before sending any text to the browser set the charset in the Content-Type header of your HTTP response.

For example in PHP:

header("Content-Type: text/html; charset=utf-8");

If you omit this parameter, your browser assumes a default value which may be different from system to system.

A final word...

This is a very simple recipe made for people who don't want to be bothered with technical details, but it's very important to follow all these advices, and not just one or two.

Tuesday Sep 08, 2009

SELECT LAST_INSERT_ID() FROM LEGEND

I don't know why or who was the first who introduced this legend, but if you review source code from time to time, you see one weird MYSQL SQL statement showing up regularly: SELECT LAST_INSERT_ID() FROM some table.

If you google for »SELECT LAST_INSERT_ID() FROM« you'll get 98,100 hits. And if you use Google Code you actually find all the public available sources using this phrase: 216 times in some PHP code, 120 times in Perl code, 102 times in Java code, 21 times in Python code, and finally 1 time in Ruby code.

But what's so bad about this statement? Let's go into the lab:

mysql> SELECT \* FROM pages;
+----+---------+-----------------------------------------+
| id | name    | content                                 |
+----+---------+-----------------------------------------+
|  1 | welcome | Dear Traveler, welcome to my AMP world! | 
|  2 | about   | This is about AMP!                      | 
|  3 | team    | Apache, MySQL, and PHP.                 | 
+----+---------+-----------------------------------------+
3 rows in set (0.00 sec)

A simple table I used in an earlier blog entry. Now let's add some data:

mysql> INSERT INTO pages (name,content) VALUES('faq','Typical mistakes');
Query OK, 1 row affected (0.00 sec)
And now we want to know which id our new data set got:
mysql> SELECT LAST_INSERT_ID() FROM pages;
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 | 
|                4 | 
|                4 | 
|                4 | 
+------------------+
4 rows in set (0.00 sec)

4 times a »4«? Sure, because LAST_INSERT_ID() got calculated for every row in that table. And since we have 4 rows in that table, we got the LAST_INSERT_ID() for 4 times. In this case it's not a big problem, but in case of a really large table this can create some serious issues. Especially if you know that this whole list of last IDs is also transferred from the server to the client.

The statement is syntactically correct, but it's definitely not what you want.

To get what you want, simply omit the FROM and the table name:

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 | 
+------------------+
1 row in set (0.00 sec)

Good.

Monday Aug 31, 2009

The Manga Guide to Databases

You don't know anything about databases and want to get rid of this social stigma? But you don't want to read a 500 pages book about theoretical aspects of relational database management system written by an aged computer science professor who thinks "real life" must be the name of a theater next to the campus?

In this case, this book is just what the nurse ordered: The Manga Guide to Databases.

The story starts like this:

Princess Ruruna and Cain have a problem: Their fruit-selling is a tangle of conflicting and duplicated data, and sorting the melons from the apples and strawberries is causing real difficulties. But what can they do?

Yes, of course they start setting up a database.

manga_db_sample.png

This book is fun, but it's also serious: lots of examples and exercises, and an appendix of frequently used SQL statements gives the tools you need to create and maintain the first databases of your life.

About

Kai 'Oswald' Seidler writes about his life as co-founder of Apache Friends, creator of XAMPP, and technology evangelist for web tier products at Sun Microsystems.

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
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
   
       
Today