nikhil nikhil - 5 months ago 40
MySQL Question

Insert Blobs in MySql databases with php

I am trying to store an image in the DataBase, for some reason it doesn't seem to work. Here's the structure of my table.

mysql> describe ImageStore;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| ImageId | int(11) | NO | PRI | NULL | |
| Image | longblob | NO | | NULL | |
+---------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)


And here is my query which inserts the image or at least thats what it should:

//Store the binary image into the database
$tmp_img = $this->image['tmp_name'];
$sql = "INSERT INTO ImageStore(ImageId,Image)
VALUES('$this->image_id','file_get_contents($tmp_image)')";
mysql_query($sql);


If I print the value of file_get_contents($tmp_image), then there is a tons of data on the screen. But this value doesn't get stored in the database and that is the issue that I'm facing.

Answer

Problem

$sql = "INSERT INTO ImageStore(ImageId,Image)
        VALUES('$this->image_id','file_get_contents($tmp_image)')";

This creates a string in PHP named $sql. Forget about MySQL for a minute, because you're not executing any query yet. You're just building a string.

The magic of PHP means that you can write a variable name — say, $this->image_idinside the double quotes and the variable still gets magically expanded.

This functionality, known as "variable interpolation", does not occur for function calls. So, all you're doing here is writing the string "file_get_contents($tmp_image)" into the database.


Solution (1)

So, to concatenate the result of calling file_get_contents($tmp_image), you have to jump out of the string and do things explicitly:

$sql = "INSERT INTO ImageStore(ImageId,Image)
        VALUES('$this->image_id','" . file_get_contents($tmp_image) . "')";

(You can see even just from the syntax highlighting how this has worked.)


Solution (2)

Now the problem you have is that if the binary data contains any ', your query is not valid. So you should run it through mysql_escape_string to sanitize it for the query operation:

$sql = "INSERT INTO ImageStore(ImageId,Image)
        VALUES('$this->image_id','" . mysql_escape_string(file_get_contents($tmp_image)) . "')";

Solution (3)

Now you have a really big string, and your database is getting bulky.

Prefer not storing images in databases, where you can help it.