In this tutorial I will show you how to store images in a mysql database. I strive to make this tutorial as simple as possible. The example scripts that I provide here are intended to show you how to simply get a single image into your database. It will be up to you to write other scripts using php loops -- and functions like:
Creating a Test Mysql Database
Since it is impossible for me know if you already have a mysql database set up, I will include a fictitious database and table for this tutorial. The database will be called test_imgs. It will contain a single table called pictures.
| id
|
pics
|
ext
|
gender
|
| 1
|
retergfghfghterhgcfnt
hy6e5ghjwtjthj
|
jpg
|
female
|
- "id" is primary, key, not null, and auto_increment
- "pics" blob not null
- "ext" is varchar(4) not null
- "gender" is varchar(7) not null
- I will assume English mysql defaults for everything else.
- I am calling the directory "/test/."
- Practice script "test.php"
Example Php Database Function
Before I can put anything in my database, I will need to connect to it. I have decided to write a function for this task.
function database_connect($db_host, $db_user, $db_pw, $db_name, &$db_selected, &$connection){
$connection = mysql_connect($db_host, $db_user, $db_pw);
if (!$connection){
die("Could not Connect to $db_host");
}
$db_selected = mysql_select_db($db_name, $connection);
if (!$db_selected){
die( "Could not select database $db_name");
}
}
Explanation of Database Function
I named my function "database connect" for obvious reasons, then passed the following arguments:
- $db_host -- Database Host
- $db_user -- Database User Name
- $db_pw -- Database Password
- $db_name -- Database Name
- &$db_selected -- Passed by reference
- &$connection -- Passed by reference
Arguments 1 through 4 need no explanation. Five and six were passed by reference so that, if I need to, I can used them outside the function without declaring them "global," or returning them. "$db_selected" will be of type "bool," which provides a great way to see if the database you are trying to connect to was selected. I suggest that you comment out the "die()" functions when you are not troubleshooting your code.
Just to make note of it; php does not require that the programmer declare a type for functions or variables. In a programming language like C, the coder would have to declare the above function "VOID" because it does not return anything.
Php Script to Load Images in Mysql Database
Now lets get down to the task of storing images in a mysql database.
$handle = fopen("testpic.jpg", "rb");
$img = fread($handle, filesize('testpic.jpg'));
fclose($handle);
//die($img);
$img = base64_encode($img);
database_connect
('localhost', 'root', 'admin', 'img_test', &$db_selected, &$connection);
$sql = "insert into pictures values(null,'$img','jpg','female')";
mysql_query($sql) or die('Bad Query at 12');
echo "Success! You have inserted your picture!";
?>
Explanation of Image Script
Notice that I used "fopen(), fread(), and fclose()" to get the contents of "testpic.php" and store it in a variable -- $img.
Next, I take $img and run it through "base64_encode()."( If you need an explanation of base64_encode and images, I suggest you Google it. Just know that it works when storing images -- 'That's enough for right now, I think.)
Lastly, after a little SQL magic, I stick the encoded data from my test picture into the database. Viola! Now I have a jpeg image stored in mysql. Make note that I made a point to record the file extension along with the picture itself. I will be using that information when I am ready to get images out of my sql database.
Putting images in mysql |
Getting images out mysql |
Storing image locations mysql
Copyright 2007 Storing images in mysql Database