Solved

mysql picture import

Posted on 2013-05-21
7
339 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 110

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 110

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
Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

 

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 110

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 110

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

726 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