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...
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???