[SOLVED] help with query from two related mysql tables...

[mac007]

hello, all:

I need help retrieving records from a catalog-like format I am building. Should be soemthing simple i think, but just cant get it...

Here is the thing: I have a mysql "Products" table with each product's info in it, and another "images" table that has all the image/paths for all products in general (images share the same "products" id to tie them together).  So that, one product could have several images associated with it, whose paths reside in the "images" table. My problem is: how do I display back the product record along with all the images that go with it, since they are in different tables. A JOIN, or a UNION, or GROUP BY doesnt do it...

 

Please help.

 

see here basic tables structure:

 

Products table name: item

 

item_id, description, title, quantity, size, price, shipping

12, painting, Horse and Barn, 1, 18 X 24, 200, 10

13, painting, Mother and Child, 1, 12 X 12, 150, 10

14, painting, Bunny Rabbit, 1, 12 X 16, 100, 10

 

Images table name: item_images

item_id, image_id, image_file

12, 100, horses1.jpg

12, 101, horses2.jpg

12, 102, horses3.jpg

13, 103, mother1.jpg

13, 104, mother2.jpg

14, 105, rabbit.jpg

 

as you can see images and products table are linked by the item_id field. So how do I display record #12 (Horse and Barn) with all it's respective images???

 

thanks!!

 

 

[rhodesa]

You would do 2 separate queries. First to get your product info:

SELECT * FROM item WHERE item_id = 12

 

Then your images:

SELECT * FROM item_images WHERE item_id = 12

 

Since it's a One-to-Many relationship, a JOIN won't work

[atl_andy]

rhodesa,

Just curious on why a join won't work on a one-to-many relationship?

 

 

SELECT t1.item_id, t1.image_id, t1.image_file, t2.description, t2.title, t2.quantity, t2.size, t2.price, t2.shipping FROM item_images AS t1 LEFT OUTER JOIN item as t2 ON t1.item_id = t2.item_id;

 

disclaimer: I'm no expert, just looking to learn something new.

[rhodesa]

Technically, a JOIN would work...but you would end up with 3 rows, and the item info would be repeated several times.

 

1) It's not proper SQL (in my opinion)

2) The big flaw is if there are no images, you don't get any results back even though there is product info in there

[mac007]

that's right, Rhodesa... the problem I was having was that I was getting three rows, with item's info being repeated three times.

You say to run 2 queries, do I just put the second SELECT right after the first SELECT and that will then display the one record with the associated images??.. or do I have to place them like a subquery (like in parentheses within first SELECT statement, or something liek that)???

[rhodesa]

You would run the first query, and then inside the WHILE loop, you would run the second query....like so:

 

<?php
  $items = mysql_query("SELECT * FROM item");
  while($item = mysql_fetch_assoc($items)){
    print "Title: {$item['title']}<br>";
    print "Description: {$item['description']}<br>";
    //etc

    //Now the images
    $images = mysql_query("SELECT * FROM item_images WHERE item_id = '{$item['item_id']}'");
    while($image = mysql_fetch_assoc($images)){
      print '<img src="images/'.$image['image_file'].'" />';
    }
  }
?>

 

Does that make more sense?

[mac007]

Rhodesa: you are awesome!!  gonna try this later today, but it makes sense now... I did think I had to do some kind of a loop or something, but as you can see I am beginner...

 

thanks for your very specific code; cant wait to test it... been driving me up the wall!

[mac007]

Rhodesa: your script worked perfectly!

Thanks a lot, much appreaciated!