Count number of subitems in each main item?

[dannyb785]

CRAP, just realized this goes on MYSQL area, not php.. sorry! please move it, mods!

 

Hi all,

 

In my database, I have a table called "Header", which just holds the id and the name of the header. The header in my site is any variation of multiple images that are faded through using javascript(like a slideshow). The images for each given header is located in 'Header_Image' table. The columns for each are:

 

Header

h_id - h_name

 

Header_Images

hi_id - hi_dest - hi_type(this links to h_id)

 

where hi_dest is the image's location and hi_type identifies which header it belongs to.

 

Now, in my main area where I'm looking at all the different headers I've created, I wish to display them all(easy enough, with a SELECT * FROM Header query), but within each header outputted, I wish to display the number of images assigned to each header WITHOUT doing a new query within each query(I this can be done easily that way, but I don't want unncecessary extra queries). I know there's a way to use the count() function but every time I use it, it doesn't include headers that don't have any images. It'll only display those that have atleast one image.

 

Can anyone help me? I'm almost positive I should be using the count() function but I'm probably using it wrong.

[Rowno]

From what I've used of the count() function, it counts the number of matches and returns a one row resource with the number and nothing else. It would probably just be easier to a second query since i don't think it's possible otherwise

[GingerRobot]

You'll need a left join:

 

$sql = "SELECT h_id,h_name,COUNT(hi_type) as count FROM header LEFT JOIN header_images ON header.h_id=header_images.hi_type GROUP BY hi_type ORDER BY count DESC";
$result = mysql_query($sql) or trigger_error(mysql_error());
while($row = mysql_fetch_assoc($result)){
    echo 'ID: '.$row['id'].' Count: '.$row['count'].'<br />';
}