Inserting and displaying images in MySQL using PHP

Well working with images is quite easy task in MySQL  using php code. Some years back managing images in relational database is quite complex task as at those times relational databases are able to store only textual data so file path to the images are stored in database and images are stored and retrieved externally. Also special  file functions are necessary for retrieving  images this way and this approach is system dependent (because of path names used). Nowadays, almost all major DBMS support storing of images directly in database by storing images in the form of binary data. Here, I am explaining the method of storing and retrieving images in MySQL database using PHP code.

Inserting images in mysql-:

MySQL has a blob data type which can used to store binary data. A blob is a collection of binary data stored as a single entity in a database management system. Blobs are typically images, audio or other multimedia blob objects. MySQL has four BLOB types:

  • TINYBLOB
  • BLOB
  • MEDIUMBLOB
  • LONGBLOB

All these types differ only in their sizes.

For my demonstration, lets us create a test table named test_image in MySQL having 3 columns show below-:

  • Id (INT) -Act as primary key for table.
  • Name (VARCHAR) – Used to store image name.
  • Image (BLOB) – Used to store actual image data.

You can use phpMyAdmin tool to create the above table else use the following MySQL query-:

create table test_image (
id              int(10)  not null AUTO_INCREMENT PRIMARY KEY,
name            varchar(25) not null default '',
image           blob        not null
 );

PHP code to upload image and store in database-:

To upload the image file from client to server and then store image in MySQL database on server, I am posting here the PHP code for our test/sample table (test_image).

Please change the values of variables in file_constants.php file according to your system. Save the following scripts with names as shown in your web directory.

file_constants.php

<?php
$host="your_hostname";
$user="your_databaseuser";
$pass="your_database_password";
$db="database_name_to_use";
?>

file_insert.php

<html>
<head><title>File Insert</title></head>
<body>
<h3>Please Choose a File and click Submit</h3>

<form enctype="multipart/form-data" action=
"<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<input type="hidden" name="MAX_FILE_SIZE" value="10000000" />
<input name="userfile" type="file" />
<input type="submit" value="Submit" />
</form>

<?php

// check if a file was submitted
if(!isset($_FILES['userfile']))
{
    echo '<p>Please select a file</p>';
}
else
{
    try {
    $msg= upload();  //this will upload your image
    echo $msg;  //Message showing success or failure.
    }
    catch(Exception $e) {
    echo $e->getMessage();
    echo 'Sorry, could not upload file';
    }
}

// the upload function

function upload() {
    include "file_constants.php";
    $maxsize = 10000000; //set to approx 10 MB

    //check associated error code
    if($_FILES['userfile']['error']==UPLOAD_ERR_OK) {

        //check whether file is uploaded with HTTP POST
        if(is_uploaded_file($_FILES['userfile']['tmp_name'])) {    

            //checks size of uploaded image on server side
            if( $_FILES['userfile']['size'] < $maxsize) {  
  
               //checks whether uploaded file is of image type
              //if(strpos(mime_content_type($_FILES['userfile']['tmp_name']),"image")===0) {
                 $finfo = finfo_open(FILEINFO_MIME_TYPE);
                if(strpos(finfo_file($finfo, $_FILES['userfile']['tmp_name']),"image")===0) {    

                    // prepare the image for insertion
                    $imgData =addslashes (file_get_contents($_FILES['userfile']['tmp_name']));

                    // put the image in the db...
                    // database connection
                    mysql_connect($host, $user, $pass) OR DIE (mysql_error());

                    // select the db
                    mysql_select_db ($db) OR DIE ("Unable to select db".mysql_error());

                    // our sql query
                    $sql = "INSERT INTO test_image
                    (image, name)
                    VALUES
                    ('{$imgData}', '{$_FILES['userfile']['name']}');";

                    // insert the image
                    mysql_query($sql) or die("Error in Query: " . mysql_error());
                    $msg='<p>Image successfully saved in database with id ='. mysql_insert_id().' </p>';
                }
                else
                    $msg="<p>Uploaded file is not an image.</p>";
            }
             else {
                // if the file is not less than the maximum allowed, print an error
                $msg='<div>File exceeds the Maximum File limit</div>
                <div>Maximum File limit is '.$maxsize.' bytes</div>
                <div>File '.$_FILES['userfile']['name'].' is '.$_FILES['userfile']['size'].
                ' bytes</div><hr />';
                }
        }
        else
            $msg="File not uploaded successfully.";

    }
    else {
        $msg= file_upload_error_message($_FILES['userfile']['error']);
    }
    return $msg;
}

// Function to return error message based on error code

function file_upload_error_message($error_code) {
    switch ($error_code) {
        case UPLOAD_ERR_INI_SIZE:
            return 'The uploaded file exceeds the upload_max_filesize directive in php.ini';
        case UPLOAD_ERR_FORM_SIZE:
            return 'The uploaded file exceeds the MAX_FILE_SIZE directive that was specified in the HTML form';
        case UPLOAD_ERR_PARTIAL:
            return 'The uploaded file was only partially uploaded';
        case UPLOAD_ERR_NO_FILE:
            return 'No file was uploaded';
        case UPLOAD_ERR_NO_TMP_DIR:
            return 'Missing a temporary folder';
        case UPLOAD_ERR_CANT_WRITE:
            return 'Failed to write file to disk';
        case UPLOAD_ERR_EXTENSION:
            return 'File upload stopped by extension';
        default:
            return 'Unknown upload error';
    }
}
?>
</body>
</html>

Below is screenshot of above web page when executed by browser-:

With this you will be able to upload and store images in MySQL database. Also check for the presence of file in the database using phpMyAdmin or any other tool.

Displaying images stored in MySQL-:

Now we are in a position to write PHP code to see images stored by the above script. For that firstly save the script below with name file_display.php in your web directory.

file_display.php

<?php
 include "file_constants.php";
 // just so we know it is broken
 error_reporting(E_ALL);
 // some basic sanity checks
 if(isset($_GET['id']) && is_numeric($_GET['id'])) {
     //connect to the db
     $link = mysql_connect("$host", "$user", "$pass")
     or die("Could not connect: " . mysql_error());

     // select our database
     mysql_select_db("$db") or die(mysql_error());

     // get the image from the db
     $sql = "SELECT image FROM test_image WHERE id=" .$_GET['id'] . ";";

     // the result of the query
     $result = mysql_query("$sql") or die("Invalid query: " . mysql_error());

     // set the header for the image
     header("Content-type: image/jpeg");
     echo mysql_result($result, 0);

     // close the db link
     mysql_close($link);
 }
 else {
     echo 'Please use a real id number';
 }
?>

Now you can see the images stored in the database using the following query string-:

http://{path_to_your_web_directory}/file_display.php?id=1

You will see image which is stored  in database corresponding to id having value  “1” . You can use different numbers to see different images. Below is a sample screenshot-:

Advertisements

Migrating from MS SQL to PostgreSQL or from MySQL to PostgreSQL

PostgreSQL  is another strong widely used companion in addition to MySQL in the field of free and open source softwares (FOSS). PostgreSQL also has extension names PostGIS which is specially designed to handle Map data efficiently. If you have your existing data in MS SQL or MySQL and want to migrate it to PostgreSQL then follow the following steps-:

  • Firstly, if you are migrating from MS SQL , then migrate your database to MySQL . You can follow my previous post for details. Here is the link of the post Migrating from MS SQL to MySQL.
  • After migrating to MySQL download and install PostgreSQL if not already in your pc from the PostgreSQL website. Ready to install (.bin) files for Linux and .exe files for Windows are available to download.
  • When installation of PostgreSQL is completed it will ask you for adding additional components. Continue on this window by clicking on Next button. If you already have PostgreSQL installed on your pc then start PostgreSQL Application Stack Builder from Start Menu in case of Windows or from Applications Menu in case of Linux. You can also use following command under Linux

#{PostgreSQL installation path}/scripts/launchstackbuilder.sh

Select your server on first Screen and click on Next.

  • Now select EnterpriseDB MySQL migration wizard under Registration required add-ons as shown in following screenshot-:
  • You have to register on PostgreSQL website for installing the above add-on . Then follow on screen options to complete installation.
  • Now run the Migration Wizard from  Linux (Applications–>PostgreSQL Plus Add-ons –>Migration Wizard) or from Windows (Start Menu –>PostgreSQL Plus Add-ons –> Migration Wizard). Below is Screenshot of Migration Wizard-:
  • Now fill in the various fields according to your configuration and then follow on screen instructions to complete the migration process
  • After migrating data you can use phpPgAdmin or pgAdmin graphical tools to log in to your database and verify the transfer process.

Migrating from MS SQL to MySQL Server

I am here discussing about the ways, reasons, problems that can occur while migrating from MS SQL Server to MySQL.

First of all for what purpose these servers are used???

I know this is a stupid question…….but it may be possible that you don’t know about these servers (I also don’t know about MS SQL Server some days back). So don’t worry !!

Ok…These two are database management systems (DBMS) available in client or server packages  used to manage large data in better and organised way than filesystem..

Reasons for migration

As today is a world of open source and everyone is rushing towards free and open source softwares then why not I and you??

Here I am giving some of common reasons –

  • MySQL is free
  • MySQL is fast.
  • MySQL is Cross-platform
  • MySQL supports large Applications Without Database Abstraction
  • MySQL is easy to use

You can find details regarding these facts by searching internet.

Lets Move towards Migration process. I am here explaining the migration process taking example of MS SQL 2005 Express Edition. This is free version of MS SQL but has limited features as it is obvious that Microsoft doesn’t provide you full features without money.

Migration tools -:

  • MySQL Migration Toolkit

This is the most powerful and easy to use tool for migrating your database. This tool is provided by MySQL community and is free but this tool is designed currently for windows (it may be available for Linux in near future)..but as we are migrating from MS SQL which is present on windows so be happy with this fact….

Below is screenshot of this tool

Here is the link of tutorial about using this tool on MySQL website -:

http://dev.mysql.com/doc/migration-toolkit/en/index.html

Note:

If you are experiencing problems while connecting to MS SQL check that whether tcp service of MS SQL is enables on correct port and also check that MS SQL Server authentication should be enabled in addition to Windows authentication.

Steps to enable tcp service and set port number –

Go to

SQL Server Configuration Manager >
SQL Server 2005 Network configuration >
Protocols for SQLEXPRESS >
TCP/IP >
Properties >
IP Addresses>
TCP Port

If this is blank or anything else  change it to 1433 (default port) and restarted the service.

Steps to enable Server authentication login:

To change security authentication mode

  1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
  2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
  3. In the SQL Server Management Studio dialog box, click OK, to acknowledge the need to restart SQL Server.

To restart SQL Server from SQL Server  Management     Studio

  1. In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

To enable the sa login by using Transact-SQL

  1. Execute the following statements to enable the sa password and assign a password.

Copy

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = ‘<password>’ ;
GO

To enable the sa login by using Management Studio

  1. In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
  2. On the General page, you may have to create and confirm a password for the sa login.
  3. On the Status page, in the Login section, click Enabled, and then click OK.

Tell me if you are experiencing other problems while migrating using this tool.

  • Migration using Microsoft DTS service

Microsoft DTS ( Data Transformation Services ) is a tool available in MS SQL which provides a lot of features including Import and Export Wizard which I am using here. This tool is mainly used to backup MS SQL data in some other form.

Using DTS is fairly straightforward, you choose a ODBC data source to read data from, and then select a ODBC data source to convert the data to. You are then given a list of tables to convert, with an option of renaming the destination table and even performing basic transformations on the data before it is inserted into the target database. These transformations are performed using Visual Basic scripting. In addition, you are given control over the table creation statements to be used, allowing you to fine-tune the MySQL table definitions to add parameters such as table handler (InnoDB, BDB, etc) to the script that will be executed.

Installing DTS in MS SQL 2005 Express

DTS is available under  Microsoft SQL Server 2005 Express Edition Toolkit. You can download it from here

http://go.microsoft.com/fwlink/?linkid=65111

Using Import Export Wizard -:

Run  “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe” and follow instructions given on-screen.

  • Migration with the help of third-party softwares

Here are names of some third-party softwares which are shareware and are available for windows or Linux (or both). You can use Import/Export features or Data Transformation/Migration Wizard present in these tools.

  • SQLyog
  • Navicat
  • MySQL Front End
  • MS Access import export
  • DB Tools Manager