php/mysql help/question

[MadRhino]

Hey Everyone....

I need some help if anyone is willing....🙂

I am currently working with an inventory list which has images associated with it.
I have already created a table for the inventory, 'inventory'.

Since each item in the inventory table could have more than one image associated with it, I have created another table, 'inventory_images'.

Now, here is where I need help. I already have the images (about 10,000). I need to populated the 'inventory_images' table and associate each file with the inventory item in the 'inventory' table.

Fortunately, the images are named according to the inventory item, e.g.

1234-1.jpg = inventory_id 1234,
1234-2.jpg = inventory_id 1234,

2233-1.jpg = item 2233 and so on.

Here are the first few columns in my inventory table:

id (auto-increment)
inventory_id

Here are the columns in the inventory_images table:
inventory_id
filename

I tried using the dir() function to read the image directory ('site_data') but I was lost after that!

Any thoughts or suggestions would be great and I thank you in advance for taking the time to read my post!!

[bpat1434]

Use dir() or glob() to get the listing of images you want. Loop through that list using making 2 queries to grab the "id" from the inventory table of the proper item, and then insert the current filename into the inventory_images with the retrieved inventory_id.

The two separate queries would look like:

SELECT `id` FROM `inventory` WHERE `inventory_id` = '$id_from_file';
INSERT INTO `inventory_items` (`inventory_id`, `filename`) VALUES ('$id_from_select_above', '$filename');

So something like:

<?php

$img_dir = $_SERVER['DOCUMENT_ROOT'].'/img/site_data/';
chdir($img_dir);
$images = glob('*.jpg');
foreach($images as $image)
{
    $filename = $image;
    preg_match('~([\d]+)\-[\d]\.jpg$~iU', $image, $matches);
    $inventory_id = $matches[1];

$result = mysql_query("SELECT `id` FROM `inventory` WHERE `inventory_id` = '{$inventory_id}'");
$row = mysql_fetch_assoc($result);

$result = mysql_query("INSERT INTO `inventory_items` (`inventory_id`, `filename`) VALUES('{$row['id']}', '{$filename}')");
if(mysql_affected_rows() != 1)
{
    echo 'Failed to add image: '.$filename.'<br />';
}
}

Now, I used the mysql numerical index to link the tables up. This can make searching and joining faster. You should really add an id field to the inventory images table so you can delete records later when those images are removed.

[MadRhino]

bpat! Thanks! I'm going to try it out...

I had initially thought about adding an id field to the inventory_images table. I can't remember why I left it out... but, it does make sense to add it in. Thanks for the suggestion!

[MadRhino]

Hey, bpat1434....

Everything works well except that it doesn't insert the id into inventory_images table.. filename is being populated though!

I have a question. I searched on PHP.net about preg_matches. Pretty neat. However, what does the var $matches in the script?

preg_match('~([\d]+)-[\d].jpg$~iU', $image, $matches);

Again, thanks for your help.. I am getting close!!

G