How to send images into MySQL database?

[moby04]

Hi. I've prepared a database containing one table:

 

create table images (
id int auto_increment primary key,
mime char(15),
image mediumblob not null);

 

Later on I made an html form to send an image and a PHP script to receive and check it. I have already checked if receiving is correct by sending the given file as an image to the browser and it was correct. After receiving the script should send the image to the database and close connection. Unfortunately something goes wrong when I am sending a query. I use the following code:

 

@ $db = new mysqli($db_host, $db_user, $db_pass, $db_name);
if(mysqli_connect_errno())
    echo "Connection failed..<br />\n";
$content = file_get_contents($_FILES['sentfile']['tmp_name']);
$query = 'insert into images (mime, image) values ("'.$_FILES['sentfile']['type'].'", "'.$content.'")';
echo $query;
$outcomes = $db->query($query);
if($outcomes)
    echo $db->affected_rows;
else
    echo $db->error;
$db->close();

 

And it throws the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

. I have also tried with some basic string instead of jpeg image and then it works fine. What should I do?

[marksie1988]

try this

 

CREATE TABLE `images` (

  `id` int(10) unsigned NOT NULL auto_increment,

  `mime` varchar(50) NOT NULL,

  `image` mediumblob NOT NULL,

  PRIMARY KEY  (`id`)

);

[moby04]

marksie1988 --> I surely doubt it would work because (I am pretty sure of that) the problem I have occurs when PHP sends a query to the MySQL. The database itself is correct and in fact you have changed nothing...

[ss32]

the better thing to do would be to merely save a reference of the image to the database, and the actual image is stored in a directory somewhere on the server.  i think you are either using the wrong datatype to store the image or your image is failing to upload, causing a blank string (which mysql doesnt like for some reason).

 

also, use sprintf when writing queries, it makes things a lot easier.

[moby04]

I know that usually the better idea is to store only reference but I need to do it this way in this specific situation. Then, i used a mediumblob which can store up to 16MB of binary safe data so it should be ok... and about the upload, is it possible that it fails at sending data between PHP and MySQL?

 

And one more thing... Error message claims the error is next to '' sign while in fact I don't use such mark in my query. Can I use addslashes() with binary data to solve it?

[ss32]

I have also tried with some basic string instead of jpeg image and then it works fine. What should I do?

that is a pretty important detail.  do you think that your query maybe truncated?  try a very small, 1 by 1 jpeg image and see if it completes.  if not, there is something wrong with your file uploading/retrieval section of the code.

 

if your query looks fine, though, after echoing, then i am afraid the error is beyond me...

 

(i also know that images and any binary data for that matter can contain quotes in it as a result of encoding data... make sure this isnt the case either)