1 to Many & GROUP BY help

[rossh]

Hi i have the following mySql tables:-

property
---------
property_id (PK)
...

image
---------
image_id (PK)
ID_property (FK)
...

$sql = "SELECT * FROM property LEFT JOIN image ON image.ID_property = property.property_id WHERE property_id = ".$_GET['property_id']." GROUP BY property_id;";


I have the sql statement which displays 1 record which is great, but i also want to display the > 1 images that are in the database, at the moment it just displays 1 image?

Thanks

Ross

[bibby]

[b]You said:[/b]
[QUOTE]
$sql = "
SELECT * FROM property
LEFT JOIN image
ON image.ID_property = property.property_id
WHERE property_id = ".$_GET['property_id']."
GROUP BY property_id;";
[/QUOTE]

Ok. The thing with left join is that it's going to return the whole of the other table no matter what.
What you are doing is duplicating a lot of data and eliminating most of it. What the query is asking for is [b]property.*[/b], which for all I know is 25 columns. So, if there were 10 images found, you get 225 more cells than you bargained for.

You should probably make that process two separate queries, one for the property row, and one the get imageIDs.
At best, you wouldn't gain much by making it one query, as compared to the hit to the resource it could take if there were more cells.

something like this
[code]<?
if ($_GET['property_id'])
{
$id=$_GET['property_id'];

$getProperty="
select * from property
where property_id = '$id'; ";

if($query=mysql_query($getProperty))
{
if( ($n=mysql_num_rows($query)) > 0 )
{
//property array
$property=mysql_fetch_assoc($query);
print_r($property);

$getImages="select image_id from image where 1 and ID_property=";

if($query=mysql_query($getImages))
{
//image array
while($image=mysql_fetch_row($query))
print_r($image);
}
}
else echo "no property rows returned";
}
else echo "getProperty did not run. <!-- ".$getProperty." -->";
}
else echo "give me a property_id";
?>[/code]