Solved

mysql picture import

Posted on 2013-05-21
7
328 Views
Last Modified: 2013-06-02
Hello,

My customer gave a lot of pictures (about 300) so as I should load them in a MySql datbase table (in a blob field).

Which one is the fastest method to do this? Perhaps a free software?

The nicest would be a software in which I would set the folder of the pictures, then the database, table and blob field name and click on Start button. :-)

Thank you
0
Comment
Question by:starhu
  • 4
  • 3
7 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39185626
I would recommend against using the BLOB data type to hold pictures.  There are many reasons for this related to backup and performance.  Instead, put the pictures in a directory of the server and put the URL or file path of the pictures in the data base.  The exact way to do this would depend on use cases that aren't part of the question, but the overall picture is still the same: No Pictures in the Data Base.
0
 

Author Comment

by:starhu
ID: 39185680
I understand and thank you for the advice.

However there is already this system which uses blob fields for pictures and I need to upload the given pictures to the database.

So the question remains the same.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39185972
Well, the correct thing to do would be to convert the existing system to use the server file system and get the pictures out of the data base.  This is sort of like driving on the wrong side of the road.  You can get away with doing it a lot, but when something goes wrong (like someone runs a SELECT * query without a LIMIT clause) the results are going to be extremely suboptimal.

I do not have any teaching example showing how to put image files into a data base (mostly because anybody who has ever done that would never do it again).  But I am willing to try to help you with your code.  If you want to post the code you've started and tell me where you're stuck, I will be glad to help as much as I can.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:starhu
ID: 39197037
This is not a "just finished" software, but a long time "ready to use" one.
Quite a few customers use it already and it would take me at least a week to rewrite it as you said.
The customer needs this import on Monday so rewriting the software is not an issue now.

When I asked the question I already new the pros and cons of storing the files in the database.

All I need is an importing software because I have 2 days left to import the files.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39197157
Can you show me the basics of the data base table, please?  Column names and definitions, etc.  It's not hard to write such a thing.

What would you do about duplicate file names?  Keep the first, keep the last, etc.,
0
 

Author Comment

by:starhu
ID: 39200842
It's a mysql table:

  `tk_autoinc` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `tk_product_autoinc` int(11) unsigned DEFAULT NULL, -- this connects to the product
  `tk_picture` mediumblob,

This is connected to the product table:
  `product_autoinc` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `product_name` varchar(80) NOT NULL,
...

There are no duplicate names for sure.

The database can handle many picture per product but this import has only one picture per product.
The file name contains the autoinc of the product e.g. 12545665333.jpg belongs to the product with product_autoinc=12545665333.

The application is Delphi based so the example can be in Delphi code as well.

Thank you
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39201740
Please see http://www.laprbass.com/RAY_temp_starhu.php

A little discussion is in order.  I do not have any matching data with file names that are numbers, so I did not do any sanity checks on this part of the data, and did not check for duplicate names at all.  In fact, the `products` table did not come into play in any way and it could have been omitted from this exercise.

With large images, the packet size might matter, since I experienced a query failure on an image file that is 7MB.

I am not sure if the base64_encode() is necessary.  It may be necessary if you transport the images over a binary-sensitive mechanism.  Line 136.

<?php // RAY_mysqli_example.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// THIS SCRIPT DEMONSTRATES IMAGE IMPORT USING MySQLi


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE pictures
( `tk_autoinc`         INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, `tk_product_autoinc` INT NOT NULL DEFAULT 0 -- this connects to the product
, `tk_picture`         MediumBLOB
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW THE RESULTS OF THE QUERY
var_dump($res);

// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE products
( `product_autoinc` INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, `product_name`    VARCHAR(80) NOT NULL DEFAULT ''
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW THE RESULTS OF THE QUERY
var_dump($res);


// FINDING THE IMAGE FILES IN THE DIRECTORY
$directory = 'RAY_junk';
$suffixs = array( 'png', 'jpg', 'gif' );
$arr = scandir($directory);
foreach ($arr as $key => $filename)
{
    $suffix = end(explode('.', $filename));
    if (!in_array(strtolower($suffix), $suffixs)) unset($arr[$key]);
}
var_dump($arr);

// LOADING THE IMAGE FILES INTO THE DATA BASE
foreach ($arr as $filename)
{
    $image = file_get_contents($directory . DIRECTORY_SEPARATOR . $filename);
    $name  = current(explode('.', $filename));

    // ESCAPE THE DATA FOR SAFE USE IN A QUERY
    $safe_fn  = $mysqli->real_escape_string($name);
    $safe_im  = $mysqli->real_escape_string(base64_encode($image));

    // CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
    $sql = "INSERT INTO pictures ( tk_product_autoinc, tk_picture ) VALUES ( '$safe_fn', '$safe_im' )";

    // RUN THE QUERY TO INSERT THE ROW
    $res = $mysqli->query($sql);

    // IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
    {
        $err
        = "QUERY FAIL: "
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql;
        ;
        trigger_error($err);
    }

    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_fn</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;


// CHECK THE WORK
$sql = "SELECT * FROM pictures ORDER BY tk_product_autoinc";
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql;
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// AND SO WE CAN NOW USE $res IN OTHER MYSQLI FUNCTIONS


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;


// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo PHP_EOL . 'USING MySQLi_Result::Fetch_<i>Object</i>(): ';
echo PHP_EOL;
while ($row = $res->fetch_object())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;

Open in new window

HTH, ~Ray
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now