Problem with LIMIT and JOIN

[cmarlo]

I'm having difficulty getting the right number of results with LIMIT and JOIN in my PHP script.

 

Table one "productline" contains basic information about my products. Table two "productline_images" lists all the images associated with each product.

 

Here is my problem. I basically want to return 10 products from table 1 with all it's associated images from table 2. However if a product has 10 images, it's only going to return 1 record from table one.

 

I have a nice loop that breaks my HTML table accordingly, so everything is horizontal, regardless of how many columns is needed for images. I've included the code. Hope this makes sense. My thanks to anyone who can help.

 

<?php

$display = 10;

$start = 1;

 

$query = "SELECT p.style_id, p.style, p.description, i.thumbnail, i.style

FROM productline p, productline_images i

where p.style_id = i.style_id LIMIT $start, $display";

 

 

$result = mysql_query($query);

 

$rows = mysql_num_rows($result);

 

$displayhtmltableintro = '1';

 

for ($i=0; $i < $rows; $i++) {

// Get individual record

$Products = mysql_fetch_array($result);

$style=$Products['style'];

$thumbnail=$Products['thumbnail'];

 

if ($checkstyle != $style) {

echo '</tr></table>';

        $displayhtmltableintro = 1;

}

 

if ($displayhtmltableintro == 1) {

        echo '<table><tr>';

        $displayhtmltableintro = '0';

        }

       

        echo '<td width="150" align="left" bgcolor="#ffffff"><p><b>Style_ID:'.$style.' <br />'.$thumbnail.'</p></td>';

       

        $checkstyle = $style;

       

        }

?>

[teng84]

try the distinct function or explain your q by showing your tables relationship 

[mezise]

Hi,

it is impossible to achieve what you want using only one query. One product record may be related to none or many product images. You cannot select combined product and product images and simultaneously apply appropriate LIMIT paging.

 

One way (11 SQL queries for 10 products) is to select only product information and for each product select its images.

Other way (always 2 SQL queries) is to traverse selected products, gather its style_id values to use it in one statement selecting images for all products on a page (... productline_images.style_id IN (...)). Then while displaying each product you traverse through the images result and get images with the same style_id.

 

Michal