COUNT()

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

COUNT()

Post 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
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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'];

?>
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post 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
Post Reply