upload image to mysql using php

jmz · Jul 19, 2005

hello can anyone help me? ..i try to upload image to mysql using php...but only type of the image get in the field like jpeg/gif... i'm using blob for the image type..but it still not working... i dont know why...
can anyone give me any suggestion?

planetsim · Jul 19, 2005

What code have you got, whats the error? If you want only a specific image type use the [man]getimagesize[/man] function.

chrisranjana · Jul 19, 2005

jmz wrote:
hello can anyone help me? ..i try to upload image to mysql using php...but only type of the image get in the field like jpeg/gif... i'm using blob for the image type..but it still not working... i dont know why...
can anyone give me any suggestion?

Is php compiled with GD properly to accept jpg, gif, png file types?

A post of your phpinfo() would help.

Weedpacket · Jul 19, 2005

GD Lib is unnecessary unless actual manipulation of the image is required.

First of all, storing images in BLOBs is usually more trouble than it's worth: simpler and easier is to store them as files and then store the file path in the database.

Secondly, without any code it's pretty much impossible to tell why you're managing to store the image type in the database instead of the image data - except to say that you're storing the wrong thing (but you already know this).

jmz · Jul 21, 2005

here is the coding that i got from internet...but i have some problem to run it

can anyone help me?

insert.php

<?php
include 'db.php';

if (empty($short) || empty($userfile))
{
?>
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Upload an Image File</title>
</head>
<body bgcolor="white">
<form method="post" action="insert.php" enctype="multipart/form-data">
<h1>Upload an Image File</h1>
<h3>Please fill in the details below to upload your file.
Fields shown in <font color="red">red</font> are mandatory.</h3>
<table width="453">
<col span="1" align="right">

<tr>
   <td width="133"><font color="red">Short description:</font></td>
   <td width="308"><input type="text" name="short" size=50></td>
</tr>

<tr>    
   <td><font color="red">File:</font></td>
   <td><input name="userfile" type="file"></td>
</tr>
<tr>
   <td><input type="submit" value="Submit"></td>
</tr>
</table>
<input type="hidden" name="MAX_FILE_SIZE" value="300000">
</form>
<h3>Click <a href="index.php">here</a> to browse the images instead.</h3>
</body>
</html>

<?php

}
else
{
$short = clean($short, 50);
$userfile = clean($userfile, 50);

 if (!($connection = @ mysql_pconnect($hostName, $username, $password)))
    showerror();

 if (!mysql_select_db("files", $connection))
    showerror();

 // Was a file uploaded?
 if (is_uploaded_file($userfile))
 {

   switch ($userfile_type)
   {
      case "image/gif";       
         $mimeName = "GIF Image";
         break;
      case "image/jpeg";          
         $mimeName = "JPEG Image";
         break;
      case "image/png";       
         $mimeName = "PNG Image";
         break;
      case "image/x-MS-bmp";       
         $mimeName = "Windows Bitmap";
         break;
      default: 
         $mimeName = "Unknown image type";
   }

   // Open the uploaded file
   $file = fopen($userfile, "r");

   // Read in the uploaded file
   $fileContents = fread($file, filesize($userfile)); 

   // Escape special characters in the file
   $fileContents = AddSlashes($fileContents);
 }  
 else
   $fileContents = NULL;

 $insertQuery = "INSERT INTO files VALUES ( NULL, \"{$short}\",
     \"{$userfile_type}\", \"{$mimeName}\", \"{$fileContents}\")";

 if ((@ mysql_query ($insertQuery, $connection)) 
     && @ mysql_affected_rows() == 1)
   header("Location: receipt.php?status=T&file=". mysql_insert_id($connection));
 else
   header("Location: receipt.php?status=F&file=". mysql_insert_id($connection));

} // if else empty()

?>

index.php

<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Browse Upload Files</title>
</head>
<body bgcolor="white">

<?php
include 'db.php';

$query = "SELECT id, shortName, mimeName FROM files";

if (!($connection = @ mysql_pconnect($hostName, $username,$password))) showerror();

if (!mysql_select_db("praktikal", $connection))
showerror();

if (!($result = @ mysql_query ($query, $connection)))
showerror();
?>
<h1>Image database</h1>

<h3>Click <a href="insert.php">here</a> to upload an image.</h3>

<?php
require 'disclaimer';

if ($row = @ mysql_fetch_array($result))
{
?>

<table>
<col span="1" align="right">
<tr>
   <th>Short description</th>
   <th>File type</th>
   <th>Image</th>

</tr>

<?php
do
{
?>
<tr>
<td><?php echo "{$row["shortName"]}";?></td>

<td><?php echo "{$row["mimeName"]}";?></td>
<td><?php echo "<img src=\"view.php?file={$row["id"]}\">";?></td>
</tr>
<?php
} while ($row = @ mysql_fetch_array($result));
?>
</table>
<?php
} // if mysql_fetch_array()
else
echo "<h3>There are no images to display</h3>\n";
?>
</body>

</html>

receipt.php

<!DOCTYPE HTML PUBLIC 
           "-//W3C//DTD HTML 4.0 Transitional//EN"
           "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <title>File Insert Receipt</title>
</head>
<body bgcolor="white">

<body bgcolor="white">
<?php
include 'db.php';

$status = clean($status, 1);
$file = clean($file, 5);

// did the insert operation succeed?
switch ($status)
{
case "T":
// Yes, insert operation succeeded.
// Show details of the new file.

 $query = "SELECT shortName, mimeName FROM files WHERE id = $file";

 if (!($connection = @ mysql_pconnect($hostName, $username, $password)))
    showerror();

 if (!mysql_select_db("files", $connection))
    showerror();

 // Run the query on the DBMS
 if (!($result = @ mysql_query ($query, $connection)))
    showerror();

 if ($row = @ mysql_fetch_array($result))
 {

?>
<h1>File Insert Receipt</h1>
<h3>The following file was successfully uploaded:
<table>
<col span="1" align="right">
<tr>
<td><font color="red">Short description:</font></td>
<td><?php echo "{$row["shortName"]}";?></td>
</tr>

<tr>
   <td><font color="red">File type:</font></td>
   <td><?php echo "{$row["mimeName"]}";?></td>
</tr>

<tr>
   <td><font color="red">File:</font></td>
   <td><?php echo "<img src=\"view.php?file={$file}\">";?></td>
</tr>
</table>

<?php
} // if mysql_fetch_array()

 break;

default:
//case "F":
// No, insert operation failed
// Show an error message
echo "The file insert operation failed.";
echo "<br>Contact the system administrator.";

 //break;

//default:
// User did not provide a status parameter
// echo "You arrived unexpectedly at this page.";

} // end of switch
?>
<h3>Click <a href="insert.php">here</a> to upload another image.</h3>
<h3>Click <a href="index.php">here</a> to browse the uploaded images.</h3>
</body>

</html>

view.php

<?php
include 'db.php';

$file = clean($file, 4);

if (empty($file))
exit;

if (!($connection = @ mysql_pconnect($hostName, $username, $password)))
showerror();

if (!mysql_select_db("files", $connection))
showerror();

$query = "SELECT mimeType, fileContents FROM files
WHERE id = $file";

if (!($result = @ mysql_query ($query,$connection)))
showerror();

$data = @ mysql_fetch_array($result);

if (!empty($data["fileContents"]))
{
// Output the MIME header
header("Content-Type: {$data["mimeType"]}");
// Output the image
echo $data["fileContents"];
}

?>

db.php

<?php

// These are the DBMS credentials
$hostName = "localhost";
$username = "huzaimi";
$password = "huzaimi";

// Show an error and stop the script
function showerror()
{
if (mysql_error())
die("Error " . mysql_errno() . " : " . mysql_error());
else
die("Could not connect to the DBMS");
}

// Secure the user data by escaping characters
// and shortening the input string
function clean($input, $maxlength)
{
$input = substr($input, 0, $maxlength);
$input = EscapeShellCmd($input);
return ($input);
}

?>

:p

LoganK · Jul 21, 2005

Please put the code within [ PHP ] [/ PHP ] tags!!!! It makes it much easier for us to read and understand.

jchapa · Jan 19, 2006

Binary Data + MySQL + PHP
How to Store Images Directly in the Sql Database
Florian Dittmer
If you want to store binary data like images and html files directly in your MySQL database, this column is for you!
I will show how you can store the data via the HTML forms "File" feature in your database and how you can access and use this data in your webproject.
If you have read the article "PHP, MySQL and images" by William Samplonius here on phpbuilder.com, this might be interesting for you as William stores the binary data somewhere on your harddisk (using a shell command), instead of storing the image directly in the Sql-Database.
Overview:
• Create a new database on your SQL Server
• A sample php3 script you can use to store data in your database
• A sample php3 script with which you can access the stored data
Create a new database on your SQL Server
First of all, you have to create a new database on your SQL server in which your script will store the binary data.
For my example I use the following structure. To create this database, you have to do the following steps:
• login to the mysql monitor
• enter the command "create database binary_data;"
• enter the command "use binary_data;"
• copy and paste the following intructions (the table structure) to the monitor
• the database+table should be created

CREATE TABLE binary_data (
id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
description CHAR(50),
bin_data LONGBLOB,
filename CHAR(50),
filesize CHAR(50),
filetype CHAR(50)
);
Binary Data + MySQL + PHP
How to Store Images Directly in the Sql Database
A sample php3 script you can use to store data in your database
With the php script store.php3 you can transfer files via a html form interface into the created database.
store.php3

<?php

// store.php3 - by Florian Dittmer <dittmer@gmx.net>
// Example php script to demonstrate the storing of binary files into
// an sql database. More information can be found at http://www.phpbuilder.com/
?>

<html>
<head><title>Store binary data into SQL Database</title></head>
<body>

<?php
// code that will be executed if the form has been submitted:

if ($submit) {

// connect to the database
// (you may have to adjust the hostname,username or password)

MYSQL_CONNECT("localhost","root","password");
mysql_select_db("binary_data");

$data = addslashes(fread(fopen($form_data, "r"), filesize($form_data)));

$result=MYSQL_QUERY("INSERT INTO binary_data (description,bin_data,filename,filesize,filetype) ".
    "VALUES ('$form_description','$data','$form_data_name','$form_data_size','$form_data_type')");

$id= mysql_insert_id();
print "<p>This file has the following Database ID: <b>$id</b>";

MYSQL_CLOSE();

} else {

// else show the form to submit new data:

?>

<form method="post" action="<?php echo $PHP_SELF; ?>" enctype="multipart/form-data">
File Description:<br>
<input type="text" name="form_description"  size="40">
<input type="hidden" name="MAX_FILE_SIZE" value="1000000">
<br>File to upload/store in database:<br>
<input type="file" name="form_data"  size="40">
<p><input type="submit" name="submit" value="submit">
</form>

<?php

}

?>

</body>
</html>
So if you execute this script, you will see a simple html form. Use the "browse" button to select a file (for example: an image) and press the "submit" button.
Notes: Addslashes usage:
SQL syntex for upload imag file to MySQL

Hope this will help
$userfile = addslashes (fread(fopen ($FILES["userfile"]["tmp_name"], "r"), filesize ($FILES["userfile"]["tmp_name"])));

$file_type = $FILES["userfile"]["type"];
$file_name = $FILES["userfile"]["name"];

$save_query = mysql_query("update tablename set bImg = '".$userfile."',ImgType = '".$file_type."',ImgName = '".$file_name."'");
Binary Data + MySQL + PHP
How to Store Images Directly in the Sql Database
After the file has been uploaded to the webserver, the script will tell you which database ID the uploaded file has. You need to know this ID to access this data (with the following script).
A Sample php3 Script With Which You Can Access the Stored Data
The script getdata.php3 is an example script that fetches the binary data from the database and passes it directly to the user.

<?php

// getdata.php3 - by Florian Dittmer <dittmer@gmx.net>
// Example php script to demonstrate the direct passing of binary data
// to the user. More infos at http://www.phpbuilder.com
// Syntax: getdata.php3?id=<id>

if($id) {

// you may have to modify login information for your database server:
@MYSQL_CONNECT("localhost","root","password");

@mysql_select_db("binary_data");

$query = "select bin_data,filetype from binary_data where id=$id";
$result = @MYSQL_QUERY($query);

$data = @MYSQL_RESULT($result,0,"bin_data");
$type = @MYSQL_RESULT($result,0,"filetype");

Header( "Content-type: $type");
echo $data;

};
?>
As the script needs to "know" which file is requested, you have to add the ID as a parameter.
Example: A file has been stored with ID 2 in the database. To get this file, you have to call:
getdata.php3?id=2
If you have images saved in the database, you can use the getdata script as <img src> in your webpage.
Example: You saved an Image as ID 3 in the database and want to show it on your webpage. Use the following code:
<img src="getdata.php3?id=3">
Binary Data + MySQL + PHP
How to Store Images Directly in the Sql Database
How to Handle Files Larger Than 1 MB:
If you want to upload and store files bigger than 1 MB, you have make several changes to the scripts and your php/sql setup, as it is caused by default limitations of the programs. Do the following to be able to store files as large as 24 Megabyte:
1. Edit the store.php3 script. Change the MAX_FILE_SIZE value (in the form) to 24000000.
2. Remove the filesize limitation from your php installation. This is set either in your php.ini or in your apache config files. By default, php3 only allowes files smaller than 2 MB. You have to change the max_filesize variable to: max_filesize = 24000000.
3. Remove the mysql packet size limitation. By default, mysql only accepts packets that are smaller than 1 MB.
4. You have to restart your database with some parameters, which will remove the limitations.
Mark Leidy wrote in that the following set up worked for him (and it worked for me, too):
/usr/local/bin/safe_mysqld -O key_buffer=16M -O table_cache=128 -O sort_buffer=4M -O record_buffer=1M -O max_allowed_packet=24M
If you are using Unix, check out your init-tree and change the corresponding startup file.
I hope this works fine for all of you. I also want to thank those of you, who wrote in improvements and fixes which helped me to complete this article.
If You Still Get Errors:
This could be a timeout problem. If you upload large files via a slow connection, php's default timeout of 30 seconds might kill your process. Change the max_execution:time variable in your php.ini to:
max_execution_time=-1
Some Last Words...
Yes, I know that this is a really short column and not a very detailed description, but even if you are new to php (like me), it should be possible for you to understand the provided scripts.
phpbuilder.com-reader Mark Leidy rewrote the scripts. You might want to check out his versions.
If you have any questions or hints for me to improve the scripts, I would be glad to talk to you through email.

LoganK · Jan 19, 2006

Nice bump Almost six months old!

jchapa · Jan 19, 2006

This code will read the image files from a directory and place the filename in a varchar field and image in a blob field. Works great tested several times before releasing. If you have problems with the formatting I will shoot you an email with the file attached.

<?php
//Replace localhost and port(3306) with your values port not required, and username.
$conn = mysql_connect("localhost:3306","root");
//Replace database name (sou) with your database name.
$db = mysql_select_db("sou");
//Replace with your image directory.
$dir = "/apache/htdocs/img";
//If statement that reads the image directory
if (is_dir($dir)) {
if ($dh = opendir($dir)) {
while (($file = readdir($dh)) !== false) {
//The If statement below bypasses the two files that exist in Winddows/Dos directories.
//and eliminates "permission denied error" that would otherwise be produced if left out.
if ($file != "." && $file != ".."){
writeToBlob($file, $dir);
}
}
closedir($dh);
}
}
function writeToBlob($file, $dir) {
//Replace localhost and port(3306) with your values port not required, and username.
$conn = mysql_connect("localhost:3306","root");
//Replace database name (sou) with your database name.
$db = mysql_select_db("sou");
// Reads the filename and prepares data to be placed in a blob field
$newHandle = addslashes(fread(fopen($dir."/".$file, "rb"), filesize($dir."/".$file)));

// Write it to the DB INSERT INTO TABLENAME (FIELD1,FIELD2) VALUES ('$String1','String2')
mysql_query("INSERT INTO img (filename,pic) VALUES ('$file','$newHandle')");
mysql_close();

}
?>