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

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.

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."'>";
   }
}

Create links to a page where you want to view a product's detail by linking to it and passing a qstring parameter along the lines of ?phash=$phash.

On the details page:

Use a SQL query like this one:

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.pHash = $detailsProductPHash



$i = 0;

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

From what I understand, you want to only display information about the product once, and display the images multiple times. The above will accomplish what you want.

The easiest and most straightforward way to do this is to make two queries: one for the product, which will get you one row, and another for the photos, which will get you multiple rows.

Trying to do this in one query means you have to de-duplicate the data that you've asked the database to repeat. That's silly.