Assigning Images to DB Rows

[DrDankWD]

Okay so here is my mind boggler:

I have a database table that stores a bunch of listings for homes.  Each row has a 6 digit ID field.  Pretty simple...

Now the tricky part is that I have a folder full of images on the server.  Each listing does not have an image, but some have multiple images.  The images are named with the ID for the listing and a _(image number), however the image numbers are not sequential.  For instance:

Listing ID# 12345 has 3 images:  12345_02, 12345_05, 12345_29 

I am trying to write a script that will read the directory of images and insert into the listings database table the names of images for a listing so that the row for listing 12345 would contain the image names: 12345_02, 12345_05, 12345_29

I don't know if any of that made any sense, I hope so!

Any help, pointing in the right direction, or comments are greatly appreciated.

-Martin

[SharkBait]

I dont know code wise but...

You could go through the directory of images with PHP and look at the file names.  Create arrays based on the ID from the filename by somehow parsing out the image numbers.

12345_02, 12345_05, 12345_29, 12344_01, 12344_02, 12344_19, 12344_45

would be placed into an array like:

[code]
<?php

$images = array(12345 => array('02', '05', '29'), 12344 => array('02', '19', '45'));

?>
[/code]

Then you would just have to loop through the array and insert them into the database :)

Hopefully the idea is there to help ya out.

[DrDankWD]

Thanks for your reply SharkBait! I do appreciate it.

I think I am in a bit over my head here. Or maybe sleep deprivation is keeping me from thinking clearly.

The listing IDs and images are going to be changing consistantly every day, so anything hardcoded with IDs or image numbers will not work. 

This is what I've come up with:

1. Query the listings table to get all ID numbers.
2. Read the file folder of images to get a list of all file names.
3. Rename files so that they have consistant image numbers (12345_01, 12345_02, 12345_03 instead of 12345_02, 12345_05, 12345_34)
4. Add the arrays based upon matching the listing ID number and images.

Am I making this more complicated than it really is?

Again, thanks for your reply SharkBait.

I am still open to all thoughts/ideas.

-Martin

[SharkBait]

My eyes are bugging out but let me see if I understand this:

You have a database with listings by IDs.

You have a folder that contains photos that have a prefix of the IDs that are in the database and a suffix of the image number.

Then you want to fix any of the filenames so they they are sequential?

You want your script to go into the photo directory, pull all the filenames, fix them and then insert the fixed filenames into the database corrisponding to the IDs correct?

[DrDankWD]

Yeah thats about the jist of it.

Its making my head hurt.  :)

I guess the filenames don't have to be fixed if there is a way to just insert into the database the original names that correspond with the IDs.

-Martin

[Barand]

Create a second table to hold images

[pre]
listings            images
---------          -------------
listing_id  --+    image_id
location      +---  listing_id
price              image_suffix
[/pre]

Use readdir() to scan the folder and for each image file, insert a record into the images table

You can break down the file names with

[code]
list ($id, $suffix) = split ('[_.]', $filename);
mysql_query ("INSERT INTO images (listing_id, image_suffix) VALUES ('$id', '$suffix)");
[/code]

You might want to do a further check for each filename to see if a listing with that id exists, in case you have images for deleted listings.

[DrDankWD]

Excellent Idea! I am going to play with this now.

I hadn't even thought of using a second table, but I guess thats why you have "Genius" next to your name and I have "n00bie" next to mine!  :)

Thanks a bunch!

-Martin