Solved

mysql picture import

Posted on 2013-05-21
7
329 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

930 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

12 Experts available now in Live!

Get 1:1 Help Now