Saving images in MySQL with PHP

July 18, 2007 at 11:33 pm · Filed under MySQL, PHP

People have different opinions on saving images in database. Some says, "Why bother database if we can handle this by saving images to disk". I am agree with this;).

Most of the requirements can be fulfilled by saving the images to disk. This reduces the unnecessary load on MySQL.

Here is the small code to save the image in MySQL with the help of PHP.

Images are saved in MySQL as BINARY data. BINARY data can not be saved in varchar or char data types, for this purpose we need a data type which can handle binary data. BLOB columns are treated as binary strings (byte strings). The following table is fulfilling our requirements for a simple test with a BLOB field.

CREATE TABLE `images` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `image` BLOB NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

Now we have a MySQL table ready to store the image. Next steps are easy i.e.

  1. Read the image
  2. Encode the image data
  3. Save binary data in DB

These 3 steps are performed with the following PHP code

$image = chunk_split(base64_encode(file_get_contents("image.jpg")));
$query = "INSERT INTO images (image) VALUES('$image')";
mysql_query($query) or die(mysql_error());
echo "Image id is ".mysql_insert_id();

Now we have saved the image in database successfully. The next step is to display the image.

// showimage.php
header('Content-type: image/jpeg');
$query = "SELECT image from images where id=1";
$rs = mysql_fetch_array(mysql_query($query));
echo base64_decode($rs["image"]);

Ohhh!!! You wanted to display the image in HTML page. No problem, call this file as a image in <img> .e.g.

<html>
<head>
<title>Image Test</title>
</head>
 
<body>
<h1>Displaying image from database</h1>
 
<img src="showimage.php" />
</body>
 
</html>

Let me know if you need some more info on this topic.

del.icio.us:Saving images in MySQL with PHP digg:Saving images in MySQL with PHP spurl:Saving images in MySQL with PHP newsvine:Saving images in MySQL with PHP blinklist:Saving images in MySQL with PHP furl:Saving images in MySQL with PHP reddit:Saving images in MySQL with PHP blogmarks:Saving images in MySQL with PHP Y!:Saving images in MySQL with PHP

Related Posts

7 Comments »

  1. Matthew said,

    March 9, 2008 @ 6:14 am

    Hi I was just reading you little tutorial and was wondering if you would be kind enough maby to explain how you could work this as a form to insert one or several images to the database. Im working on a gallery done in css and php although im not very good a php yet but id like to try your idea and see if it works out for me…

  2. Wasif said,

    March 10, 2008 @ 11:52 pm

    You need to make a form to upload files and read the uploaded files with $_FILES.

    Here is a sample form for single file upload.
    <form action=”file_upload.php” enctype=”multipart/form-data” method=”post”>
    Provide Image: <input type=”file” name=”image_file” />
    <input type=”submit” title=”Upload” />
    </form>

    And the PHP code to upload file
    < ?
    // file_upload.php
    // Check if the file uploaded successfully
    if(is_uploaded_file($_FILES["image_file"]['tmp_name']))
    {
    $image_type = "";
    //for security check the image type
    switch($_FILES["image_file"]["type"])
    {
    case "image/pjpeg":
    case "image/jpeg":
    case "image/gif":
    $image_type = $_FILES["image_file"]["type"];
    default:
    echo "Invalid image type";
    exit;
    break;
    }

    $image = chunk_split(base64_encode(file_get_contents($_FILES["image_file"]['tmp_name'])));
    // here you can save the image and image type in DB
    // You will output image type in header() when displaying image
    }
    ?>

    For multiple files upload, visit the above link or ask again.

  3. Margaret said,

    May 3, 2008 @ 10:41 pm

    I am new to php. I am working on a website where clients would search my website for listings. I have no idea how to design a database that upon request the listings would display information about particular club together with an image. I also wanted to have links displayed for example, “send to a friend”, “rate this” etc. I’ve consulted many books in this matter and still have no idea how to start with this. I need help!!!

  4. PHP-Ausgabe incl. Bilder auf Server speichern - PHP @ tutorials.de: Forum, Tutorial, Anleitung, Schulung & Hilfe said,

    May 14, 2008 @ 1:55 am

    [...] und im Dateisystem ablegen? Wie Bilder in einer Datebank gespeichert werden, erklärt z. B. dieses Tutorial. Ein großer Nachteil des Ganzen ist natürlich, das Bilder groß sein können. Das heißt gerade wenn [...]

  5. Chavez said,

    June 24, 2008 @ 3:06 am

    I need multiple images upload, please help me

  6. Wasif said,

    June 24, 2008 @ 9:44 pm

    For multiple file upload you need to use ‘[]‘ with the HTML file element name. e.g. <input type=â€file†name=â€image_file[]†/>. You can find the example @ http://www.php.net/manual/en/features.file-upload.multiple.php.

  7. gaurav said,

    June 30, 2008 @ 1:18 am

    this helps a lot and provide rapid help to me.. thnx alot :)

RSS feed for comments on this post

Leave a Comment