MySQL help!

August 27, 2006 10:07 PM

Please help me create a MySQL database from a collection of images. There is of course

I have a folder which contains some subfolders. Each of these subfolders contains 100-200 images. These images are all numbered in the format #########.jpg, and each photo has a unique name. In other words, there is no photo in folder X which has the same filename as a photo in folder Y, for all X and Y.

What I want to do is add these photos information to a MySQL database on my local machine (I have a WAMP setup). I want to enter the following information.

ID# - this is the filename of the image but without the .jpg tag. In other words, image 000001307.jpg should have an ID of 000001307

description - the description needs to be the same as the name of the folder is in. So if a file is in folder "School" it needs to have a description of "School".

filename - simply foldername/filename for the image.

How can I set up some sort of loop to automatically create all the entries needed in a MySQL table? There is only ~10 folders of images, so even if I had to set up a new script for each folder, that wouldnt be too bad.

Please help save me the time of manually enterring 1300 entries into a DB!

posted by LoopyG to Computers & Internet (10 answers total)

Assuming the 'P' in WAMP stands for PHP, you can try this script. Change the $dir and $tableName variables. The script iterates over the $dir directory, iterates over all sub directories and adds any file it finds to an array. It iterates over this array and produces mysql insert queries. To run this script, type "php scriptname.php > insertions.sql" on the command line and then import the data into your mysql database.
posted by null terminated at 10:34 PM on August 27, 2006

You have PHP installed, use it.

look at php.net/mysql

and heres an easy function to get a recursive directory listing into an array

http://www.bigbold.com/snippets/posts/show/155
posted by mphuie at 10:35 PM on August 27, 2006

using perl:

use File::Find;find( \&wanted, "<yourdirectory>" );sub wanted {    if ( $File::Find::name =~ /\.jpg$/ ) {       ( $dir = $File::Find::dir ) =~ s|^.*/||;        print "insert into mytable values ('$dir', '$_');\n";    }}

now you've got a list of SQL insert statements you can use.

The SQL insert syntax is probably wrong but you get the idea.
posted by AmbroseChapel at 10:48 PM on August 27, 2006

Correction -- forgot you didn't want the filename, just the number:

use File::Find;find( \&wanted, "<yourdir>"
sub wanted {    if ( $File::Find::name =~ /\.jpg$/ ) {
        ( $dir = $File::Find::dir ) =~ s|^.*/||;        ( $id = $_ )                =~ s|\.jpg||; #remove the extension        print "insert into mytable values ('$dir', '$id');\n"; }}

posted by AmbroseChapel at 10:59 PM on August 27, 2006

ID# - this is the filename of the image but without the .jpg tag. In other words, image 000001307.jpg should have an ID of 000001307

This seems a phenomenally bad practice. You probably don't want IDs overwritten when the camera decides to give the same digits to different pictures.

Instead, use an AUTO_INCREMENTed field so that your INSERT generates auto-incremented ID tags. You can use an INDEX on your filename if you need to search on the camera picture ID.
posted by Blazecock Pileon at 11:34 PM on August 27, 2006

Response by poster: Blazecock, I intend to do just that. Those images have been numbered by using auto-increment. New pictures are added to the database through a form, and the form assigns an ID tag and filename, ignoring whatever crazy number scheme the camera has given it.

As for everyone else, thanks for the help! I am new to PHP/MySQL, and dont have access to my files while here at work, so a quick question. Can I use such PHP-scripts while in the MySQL console (going to Start->Run, and loading cmd, then logging into my SQL DB), or do I have to make a little .php file to do this, and then access that PHP file to run the script?
posted by LoopyG at 9:40 AM on August 28, 2006

New pictures are added to the database through a form, and the form assigns an ID tag and filename, ignoring whatever crazy number scheme the camera has given it.

But what Blazecock meant was that MySQL, not the form, should handle the ids. That way they are guaranteed unique. What does the form do if two people submit at the same time?
posted by mendel at 1:18 PM on August 28, 2006

Response by poster: Ah, I see what you are saying. I believe that this is already the case. This script I am trying to make will be a one-time thing to start the database. From there on out IDs/filenames will be assigned by MySQL, by incrementing the ID value by 1.

This script is only to be used by me, once only, to submit 1000+ images which have already been named, and are guranteed unique. In the future, uniqueness will be maintained by the database.
posted by LoopyG at 2:45 PM on August 28, 2006

LoopyG: You'll need to create a .php file. If you save the file I linked to above as a .php file and then run it, it should give you what you want.
posted by null terminated at 4:58 PM on August 28, 2006

>Can I use such PHP-scripts while in the MySQL console (going to Start->Run, and loading cmd, then logging into my SQL DB), or do I have to make a little .php file to do this, and then access that PHP file to run the script?

No, you can't use the PHP (or Perl!) scripts in mysql.

What we're proposing is that the PHP (or Perl!) script create a 1300-line file, or just screen output you cut and paste, with one SQL statement on each line.

Then you can run all the statements using something like

mysql -u <your username> -p<your password> < file.sql

But if you're at this level, can I suggest that it will be a lot easier for you to use PHPMyAdmin for stuff like this?
posted by AmbroseChapel at 4:59 PM on August 28, 2006