如何查询1对多数据库关系并正确显示行？

Question

sorry if my question kind of doesn't make sense but i am total confused onto what im trying to accomplish.

here are my tables.

tbl_products(if the image came out small, right click on it and view picture to see it big.)

tbl_product_images

basically tbl_products has the record for a product and its description. and tbl_product_images has the images for this product. i want to display the product title,description, and its images in a format like this.

problem is the query i wrote which is:

select
tbl_products.dealname,
tbl_products.desc,
tbl_products.price,
tbl_products.discount,
tbl_product_images.thumb,
tbl_product_images.large
from tbl_products
inner join tbl_product_images

on tbl_products.pHash = tbl_product_images.pHash where tbl_products.startdate = '2010-12-09';

displays the query like this.

so when i need to write something like this

while($row = mysql_fetch_assoc($query))
{
   print $row['dealname'];
   print "<img src='".$row['large']."'>";
  //and so on.
}

i will see the product displayed 4 times since i have 4 images for this product. what is the best way to perform what i am trying to accomplish? btw, phash is another way of id. its my way of relating a products identification within another table.

Answer 1

You can use the GROUP_CONCAT() function in MySQL if you want to fetch everything in one query, but you'll have to split up the image information using PHP.

Here's an example using | (pipe) as the delimiter between thumb/large image file names.

Query:

select
tbl_products.dealname,
tbl_products.desc,
tbl_products.price,
tbl_products.discount,
GROUP_CONCAT(tbl_product_images.thumb SEPARATOR '|') AS thumbs,
GROUP_CONCAT(tbl_product_images.large SEPARATOR '|') AS images
from tbl_products
inner join tbl_product_images

on tbl_products.pHash = tbl_product_images.pHash
where tbl_products.startdate = '2010-12-09'
group by tbl_products.pHash;

PHP:

while($row = mysql_fetch_assoc($query))
{
   print $row['dealname'];

   // $thumbs = explode('|', $row['thumbs']);
   $images = explode('|', $row['images']);
   foreach ($images as $image) {
      print "<img src='".$image."'>";
   }
}