Mrarrowhead.com

Home   How to write files php   How to Install Apache   How to Install PHP   HTML Forms   PHP Arrays   Passing Vairables In PHP   Post PHP   Contact  
 
   

Sign up for FREE PHP Newsletter

Learn basic syntax, experiment with loops, and build user areas.

Name:

Email:
 
 

How to get images out of mysql database with php and use them on my webpages

Author: D.Shaun Morgan

Versions and Skill Level
  • PHP Version - PHP 5x
  • Mysql Version - 5
  • Reader skill level - Intermediate
Tutorial Outline:
  1. Creating a Test Mysql Database
  2. Php Script to Load Images in Mysql Database
  3. Php Script to retrieve images from mysql database
  4. Example of calling php script with img HTML tag
  5. Explanation of Image Script
See Also:
  1. How to store images in MySql database

This tutorial expands on another database/image how-to How to Store Images in Mysql Database. In the "Images In" tutorial I showed you the basic steps needed to open a file (your image file) and read it into a variable ($img), convert that file using php's base64_encode() function, and stick that file into a Mysql database. Now, you will learn how to bring those images back out of your Mysql database, and output them to a web browser.

Retrieving Images From Mysql Database With php

To begin, do the following:
  1. Create a mysql database with a table matching (Database Example)
  2. Create a directory on your server to place files in (test)
  3. Create a file named "test01.php" and place example 1 in it (below)
  4. Create a file named "test02.php" and place example 2 php script in it (below)
  5. Create a file named "test03.php" and place example 3 HTML in it (below)
  6. Copy the following test images and save them in your test folder


Be certain that you have changed the database username, password et cetera so the test scripts can access your database server.

>>NOTE>> If you were looking for a tutorial on how to use php to store and retrieve bmp images, please read on... YOU WILL BE LOOKING FOR A LONG TIME! Bmp images are so large that they find little use in web development. Thus, there is very little support for it in php. You will do well to just gear your website toward jpg, gif or png. IF you are insisting on using bmp, you will do better to store them in a folder on your web server with unique names, and record their locations in your database. Storing the location of your pics in mysql database is covered here at mrarrowhead.com. Use the search function to find it.

Database example

id pics ext gender
1 retergfghfghterhgcfnt
hy6e5ghjwtjthj 		jpg female
  1. Database name is "test_imgs"
  2. Table name is pictures
  3. "id" is primary, key, not null, and auto_increment
  4. "pics" blob not null
  5. "ext" is varchar(4) not null
  6. "gender" is varchar(7) not null
  7. I will assume English mysql defaults for everything else.
  8. Practice script "test.php"

Example 1

<?php
$host = '';

$user = '';

//$pw = '';

//$db = '';

$testpic = 'testpic.jpg';
$ext = 'jpg';

//$testpic = 'testpic.gif';
//$ext = 'gif';

//$testpic = 'testpic.png';
//$ext = 'png';



mysql_connect($host,$user,$pw);

mysql_select_db($db);

$handle = fopen($testpic, "rb");
$img = fread($handle, filesize($testpic));
fclose($handle);


$img = base64_encode($img);

$sql = "insert into pictures values(null,'$img','$ext','female');";

mysql_query($sql) or die('Bad Query at 12'.mysql_error());

echo "Success! You have inserted your picture!";
?>

< href="test02.php">Next</a>


Example 2

<?php
$host = '';
$user = '';
$pw = '';
$db = '';


mysql_connect($host,$user,$pw);

mysql_select_db($db);

$sql = "select pic, ext from pictures where id='1'";

$result = mysql_query($sql) or die('Bad query at 12!'.mysql_error());

while($row = mysql_fetch_array($result,MYSQL_ASSOC)){

$db_img = $row['pic'];
$type = $row['ext'];


}

$db_img = base64_decode($db_img); //print_r($db_img );

$db_img = imagecreatefromstring($db_img);
if ($db_img !== false) {
switch ($type) {
case "jpg":
header("Content-Type: image/jpeg");
imagejpeg($db_img);
break;
case "gif":
header("Content-Type: image/gif");
imagegif($db_img);
break;
case "png":
header("Content-Type: image/png");
imagepng($db_img);
break;
}


}
imagedestroy($db_img);
?>


Example 3

<img src="test02.php"/>

Tutorial Conclusion and Explanation

After building the database, and uploading all the example files and images to your server, simply navigate to the test01.php script on your server. By default the test script should open testpic.jpg and send it to your mysql server with an id of "1". Click the next link and go to test_02.php to view your image. Then type test03.php into your browser's nav bar.

Notice that on the test03.php page, I inserted test_02.php in the img tag.

<img src="test02.php" alt=""/>

This works great for creating on the fly images with your html. Using the img tag allows me to call a php script without using an include, or iframe. It also sets up the right header for my image to display. If you notice on test02.php, I had to send an header before I used the image function to output my image to browser. The php header function is not necessary if you use the test03.php method. Comment the header functions out of test02.php, then run both test02.php and test03.php. test03.php will give an image, and the former will give a mess.

Repeat the steps above for the .gif and .png images. I suggest you practice with some loops, if statements, and a form so that your php script can handle all three image situations based on a user's choice.

NOTE:
php also supports wbmp for wireless. You can find the appropriate functions in the php manual.