Solved

mysql picture import

Posted on 2013-05-21
7
333 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 109

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 109

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 109

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 109

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 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