Solved

mysql picture import

Posted on 2013-05-21
7
331 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
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.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mySql Syntax 7 53
[MYSQL]: Delete is very slow 4 69
Could you point a way to form a view's combo based on Codeigniter's results? 4 29
mysql between clause 2 21
A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
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.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

785 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