Page 1 of 1

COUNT()

Posted: Fri Sep 30, 2005 7:05 pm
by Jim_Bo
Hi,

I have used COUNT(photo_id) in the sql query in the code below to count the amount of images within the category .. when I echo out '.$row['photo_id'].' or '.$row[3].' its returning a blank result ...

Whats the correct method?

Code: Select all

$sql = mysql_query("SELECT c.category_id, c.category_name, photo_filename, COUNT(photo_id)
						FROM gallery_category as c
						LEFT JOIN gallery_photos as p ON p.photo_category = c.category_id
						GROUP BY c.category_id LIMIT $from, $max_results");
								
if(mysql_num_rows($sql) == 0) {
	echo '<br><br><font class="txt"><b>No categories have been created yet</b></font>';
	return;
}

?>

<table border="1" bordercolor="#FFFFFF" cellspacing="10" cellpadding="5" align="center">

<?php

    while($row = mysql_fetch_assoc($sql)) {
	
if($row['photo_filename'] == 0) {
	$picture = '<font class="txt">No Images</font><br>';
} else {
	$picture =  '<font class="txt"><a href="../index.php?pages=sh_category&cid='.$row['category_id'].'"><img src="../'.$images_dir.'/tb_'.$row['photo_filename'].'" border="1"></a></font><br>';
}
     
	if($numcolsprinted == $numcols) { 
		echo "<tr></tr>";
			$numcolsprinted = 0; 
}

	echo '<td width="94" height="130" align="center" valign="bottom" bordercolor="#000000">'; 
         
		echo ''.$picture.'<font class="txt"><a href="../index.php?pages=sh_category&cid='.$row['category_id'].'"><br><b>'.$row['category_name'].'</b></a><br><br><font color=\"#000000\">'.$row['photo_id'].' images</font></font>';
                  
	echo '</td>';
Cheers

Posted: Fri Sep 30, 2005 8:15 pm
by Jenk
Give the count an alias, ala:

Code: Select all

<?php
    $sql = mysql_query("SELECT c.category_id, c.category_name, photo_filename, COUNT(photo_id) as count
                        FROM gallery_category as c
                        LEFT JOIN gallery_photos as p ON p.photo_category = c.category_id
                        GROUP BY c.category_id LIMIT $from, $max_results");


$row = mysql_fetch_array($result);

//you can now use:
echo $row['count'];

?>

Posted: Fri Sep 30, 2005 8:58 pm
by Jim_Bo
Hi,

Thanks .. just sused it out .. was comming here to say I had it ..

I did COUNT(photo_id) as photo_id



Cheers