Jump to content

Check out our Community Blogs

Register and join over 40,000 other developers!

Recent Status Updates

View All Updates

- - - - -

Tutorial: Storing Images in MySQL with PHP

max_file_size mysql images php

  • This topic is locked This topic is locked
47 replies to this topic

#1 Guest_Jordan_*

  • Guest

Posted 12 March 2008 - 04:40 PM

In this tutorial I will show you how to store binary images (or any binary file) in MySQL using PHP.

Why store binary files in MySQL using PHP?
You may want to allow users to upload files to your PHP script or store banner images from advertisers inside of your SQL database. Instead of creating and using a messy directory structure it may be more suitable to store them directly in your SQL database along with the advertiser or user information.

Reasons to store your images (binary files) in a database:

  • Storing in a database allows better security for your files and images.
  • Eliminates messy directory and file structures (this is especially true when users are allow to contribute files).
  • Files/Images can be stored directly linking to user or advertiser information.

You may want to reconsider storing your binary files in the database. Here are some reason you may not want to:

  • You can't directly access your files using standard applications such as FTP.
  • If you database becomes corrupt, so do your files.
  • Migrating to a new database is made more difficult
  • Pulling images from a database and displaying them is slower than using file access.

Note: all of the banners and user attachments that CodeCall has are stored directly in MySQL.

Creating the Database and Table
Using the console we will access MySQL and create a database and table for our binary upload PHP script. Using a tool such as PHPMyAdmin can make this task a bit easier. If your webhost has cpanel or Plesk then you probably have access to PHPMyAdmin. If your host does not you might want to consider hosting from ToastedPenguin.com.

First login to MySQL:

# mysql -u root -p
Enter Password: ******

Replace the user root with your username for MySQL. Since I'm running this on my local machine I will access the database using the root account via the console and PHP Script. This is not recommended and you should not access your database in a public environment, such as the internet, using the root user, ever!

Now create the database:

mysql> CREATE DATABASE binary;

Press Enter, you should see results similar to:

Query OK, 1 row affected (0.00 sec)

Lets create the table now:

mysql> CREATE TABLE tbl_images (
> id tinyint(3) unsigned NOT NULL auto_increment,
> image blob NOT NULL,
> );

What is a BLOB?
Above we created two tables, one the primary ID (of the row/entry) and the binary BLOB. A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. These differ only in the maximum length of the values they can hold. The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to the four BLOB types and have the same maximum lengths and storage requirements.


You've now created your database and are ready to upload binary files to it. In this case, images. Next we will create a simple upload script.

Creating the HTML
Creating the user interface where you or your visitors will upload files is very simple. Using basic HTML and relying on the browser to do most of the heavy lifting you can easily create an upload form. Here is the one we will use:

<form enctype="multipart/form-data" action="insert.php" method="post" name="changer">
<input name="MAX_FILE_SIZE" value="102400" type="hidden">
<input name="image" accept="image/jpeg" type="file">
<input value="Submit" type="submit">

Copy this code into a file and save it as add.html

The code above allows the uses to browse for a file and select any image/jpeg file type. Once selected and the submit button is pressed the image contents are forwarded to PHP script, insert.php.


Inserting the Image into MySQL
Create a new file named insert.php. If you change the name of this file you will also need to change the action="" part in your HTML file above. Open the file in your favorite text editor or PHP IDE.

Write or copy/paste this code into your file:

// Create MySQL login values and
// set them to your login information.
$username = "YourUserName";
$password = "YourPassword";
$host = "localhost";
$database = "binary";

// Make the connect to MySQL or die
// and display an error.
$link = mysql_connect($host, $username, $password);
if (!$link) {
die('Could not connect: ' . mysql_error());

// Select your database
mysql_select_db ($database);
The above code sets your connection variables, creates a connection to MySQL and selects your database (binary if you followed my instructions above). Next we need to read the form data and insert it into the database.

// Make sure the user actually
// selected and uploaded a file
if (isset($_FILES['image']) && $_FILES['image']['size'] > 0) {

// Temporary file name stored on the server
$tmpName = $_FILES['image']['tmp_name'];

// Read the file
$fp = fopen($tmpName, 'r');
$data = fread($fp, filesize($tmpName));
$data = addslashes($data);

// Create the query and insert
// into our database.
$query = "INSERT INTO tbl_images ";
$query .= "(image) VALUES ('$data')";
$results = mysql_query($query, $link);

// Print results
print "Thank you, your file has been uploaded.";

else {
print "No image selected/uploaded";

// Close our MySQL Link
The PHP code above takes the user selected image, reads it with fopen and stores the data in a variable named $data. The binary data is then inserted into our database for retrieval at a later time.

Save the file above and access add.html via your browser. Select an image file and upload it.


Questions and Attachments
If you have any questions please ask them here. I'll try to answer them when I can. I've attached working copies of the add.html and insert.php scripts to this thread. If the tutorial was useful please post a simple "thank you".

In the next PHP Tutorial I'll show you how to retrieve these binary images from MySQL and display them to your visitors.

Part II: By Jaan


Edited by Roger, 29 September 2014 - 04:40 PM.

  • 0

#2 schumid


    CC Lurker

  • Just Joined
  • Pip
  • 2 posts

Posted 23 March 2008 - 06:16 AM

thank you that really works
please post the code how to retrieve the images from the database
  • 0

#3 Guest_Jordan_*

  • Guest

Posted 23 March 2008 - 02:09 PM

I'll probably add the tutorial for showing images from MySQL this week. Keep an eye out.
  • 0

#4 schumid


    CC Lurker

  • Just Joined
  • Pip
  • 2 posts

Posted 29 March 2008 - 03:03 PM

I'll probably add the tutorial for showing images from MySQL this week. Keep an eye out.

hey jordan sorry for asking again, can you post the tutorial for showing images from mysql as soon as possible ?
  • 0

#5 Whitey


    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 259 posts

Posted 30 March 2008 - 09:05 PM

Yea please :D
I would really like to know as well.
  • 0

#6 xiou


    CC Lurker

  • Just Joined
  • Pip
  • 4 posts

Posted 20 April 2008 - 04:03 PM

Thanks so much for this tutorial. I am looking forward to the tutorial on retrieving the images from the database.
  • 0

#7 Guest_Jordan_*

  • Guest

Posted 29 April 2008 - 05:39 AM

Jaan wrote the Part II of this tutorial. You can find it here: http://forum.codecal...our-images.html
  • 0

#8 quickfox902


    CC Lurker

  • Just Joined
  • Pip
  • 2 posts

Posted 22 June 2008 - 04:46 AM

need help with creating sql tables in sql 5.0


SQL query:

mysql > CREATE TABLE tbl_images(
> id tinyint( 3 ) unsigned NOT NULL AUTO_INCREMENT ,
> image blob NOT NULL ,
> PRIMARY KEY ( id ) >

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql> CREATE TABLE tbl_images (
> id tinyint(3) unsigned NOT NULL auto_in' at line 1

can anyone help me?

Edited by quickfox902, 22 June 2008 - 05:00 AM.

  • 0

#9 Guest_Jordan_*

  • Guest

Posted 22 June 2008 - 05:20 AM

Don't type "mysql>" into MySQL as part of your query.
  • 0

#10 quickfox902


    CC Lurker

  • Just Joined
  • Pip
  • 2 posts

Posted 22 June 2008 - 02:24 PM

thank you
  • 0

#11 Guest_Jordan_*

  • Guest

Posted 22 June 2008 - 04:02 PM

No problem.
  • 0

#12 markus7258


    CC Lurker

  • Just Joined
  • Pip
  • 1 posts

Posted 17 August 2008 - 07:10 PM

Its a great script... would it be possible to upload multiple images using one submit button only? I tried but it did not work...can you help me... show me how to do it ... thanks so much
  • 0

Also tagged with one or more of these keywords: max_file_size, mysql, images, php

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download