PDA

View Full Version : how to insert an image into mysql using php


imryn
29th March 2006, 05:46 AM
Hello Everyone,

I am trying to insert images into mysql database using php. However, my code isn't working. Does anyone, have a link or clue how I can do this. I am using html to create a table to upload the image, then calling php to insert it. now it does insert the name and description, but not the image its self. Where did I go wrong?

Thank you,
imryn :rolleyes:

code_astronomer
29th March 2006, 09:14 AM
It could be the type of field you are using. If I remember correctly, the field should be a binary type, but I could be wrong.

It would help if you posted your sql and field types.

code_astronomer
29th March 2006, 09:17 AM
Just found this ( might be helpful to you): http://www.stardeveloper.com/articles/display.html?article=2001033101&page=1
http://php.linux.hr/manual/en/function.mssql-query.php - there is a bit about inserting binary data in the comments.

sej7278
29th March 2006, 09:43 AM
very bad idea storing images in a database, it's sql 101.

you should store the image on the hard disk and the path to the image as a varchar in the database.

BandC
29th March 2006, 08:56 PM
I agree with sej7278. If you still wanna go ahead and do it, the MySQL data type you're looking for is BLOB (or its variants). A google search returns sample code:

http://www.google.com/search?hl=en&q=storing+image+php+mysql&btnG=Google+Search

imryn
30th March 2006, 02:41 AM
My code looks like this this is first my html code, then my PHP code.

<form enctype="multipart/form-data" action="upload.php" method="post">
<table border="0">
<tr>
<td><input type="hidden" name="MAX_FILE_SIZE" value="700000000></td>
<td><input type="file" name="userfile"></td>
</tr>
<tr>
<td>Picture name:<td>
<td><input type="text" name="pname" size="20"></td>
</tr>
<tr>
<td>Picture Description:</td>
<td><input type="text" name="pdesc" size="60"></td>
</tr>
<tr><br>
<td><input type="submit" value="Send File"></td>
</tr><br>

Separate page PHP

<?php
$filename = $_FILES['userfile'];
$pname = $_POST['pname'];
$pdesc = $_POST['pdesc'];

//Connecting to database.
$link = mysql_pconnect($dbhost,$dbuser,$dbpswd)
or die("Could not connect ".mysql_error());

//Selectong the database to use.
mysql_select_db("pictures") or die("Could not select database");

//Performing INSERT into database.
$sqlinsert="INSERT INTO pic(pic_name,pic_desc,pic) VALUES";
$sqlinsert .="('$pname','$pdesc','$filename')";

//Performs insert statement.
$insertResult = mysql_query($sqlinsert) or die("Insert Failed :".mysql_error());

//Close database connection
mysql_close($link)
?>

Now it says that the insert was successful and I checked the database but in the pic column it says ARRAY..... and when I try to do a while statement to fetch the pictures it won't load anything, so I am not sure what is really in the database. I do have the pic listed as a BLOB too.

imryn

Shakes
30th March 2006, 05:56 PM
I don't use MySQL much but I am guessing that it is storing the data as a byte array which is why it says array. A lot of tools for viewing in the database don't display much for binary data because they don't know how to display those columns.

I would guess it's the retrieve code thats wrong. Maybe if you post that someone can help with that.

BandC
30th March 2006, 09:55 PM
Maybe you are storing it right. Obviously, you won't see a picture in the column. To see a picture, it need to be converted from how it's stored (binary). To prove that you are storing it right, you need to retrieve data and reconvert it and display it as an image. I believe Mysql-Query-Browser has a view feature for binary columns. So you can verify it. Go to mysql.com to get it.

imryn
13th April 2006, 01:32 AM
Well, I am still tring to upload an image to a mysql database. Has anyone every done this and have it work successfully? I can upload all the info I need and what except for the image its self? That column in blank. Here is the Newest version I have written

Code:
<?php
error_reporting(E_ALL);
include "open_db.inc";

if($_POST['action'] == "upload")
{
$binName = $_FILES['binFile']['name'];
$binTemp = $_FILES['binFile']['tmp_name'];
$binType = $_FILES['binFile']['type'];
$binSize = $_FILES['binFile']['size'];
$binErr = $_FILES['binFile']['error'];

if(isset($_FILES['binFile']['name'] && $_FILES['binFile']['name'] != "")
{
$data1 = fopen($binName,'rb');
$Content = fread($data1,$binSize);
$Content = addslashes($Content);
$binName = addslashes($binName);

$strDescription = addslashes($_POST['txtDescription']);

$sql = "INSERT INTO binary_data";
$sql .="(description,bin_data,filename,filesize,filetype)";
$sql .="VALUES('$strDescription','$Content','$binName','$b inSize','$binType')";

$result = mysql_query($sql,$db);

echo "Thank you. The new image was successfully added to our database.<br><br>";
echo "<a href='main.php>Continue</a>

echo "<br><br><img src=".$Content."\n>";
}
else
{
print "Either isset is false or binFile is empty";
print $binErr;
}
mysql_close();
}
else
{
?>

<html>
<body>
<form method="post" action="add2.php" enctype="multipart/form-data">
<input type="hidden" name="MAX_FILE_SIZE" value="90000000">
<input type="hidden" name="action" value="upload">
<table border="1";
<tr>
<td>Description:</td>
<td><textarea name="txtDescription" rows="10" cols="50"></textaarea></td>
</tr>
<tr>
<td>File: </td>
<td><input type="file" name="binFile"></td>
</tr>
<tr>
<td colspan="2"><input type="submit" value="Upload"></td>
</tr>
</table>
</form>
</body>
</html>

<?php
}
?>

it act says it's uploaded, and it shows all of the data EXCEPT for the bin_data column is blank in the DB. How do you get the image there or what steps must be taken to get it there?

imryn

jtang613
13th April 2006, 03:06 AM
This sounds like an issue I once ran into creating a webcam image repository. The problem is you are attempting to insert binary data, when the connector is expecting a character string. You need to use the mysql_real_escape_string() function to 'escape' the non-printable characters ('0x00' becomes '\0' and so on).

Read: http://www.php.net/manual/en/function.mysql-real-escape-string.php

hth,
Jason

ps: BandC and sej7278 make a good point.

jtang613
13th April 2006, 03:18 AM
... and yes, the image should successfully upload and download from the db intact. And if I remember correctly, you should even see an image thumbnail in phpmyadmin (if you set the blob type field to image).

J