[SOLVED] How to select COUNT() in PHP loop?

[webmaster1]

Hi All,

 

I need to pull one variable and it's count into a php page from its database. I can't get the count to work:

 

<?php
//CONNECT TO ADD TO EXPORT THE CATEGORY FIELD
include("dbinfo.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

//BUILD QUERY
$query="SELECT DISTINCT category, count( category ) FROM `masterdata` GROUP BY category";

$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

//DISPLAY THE CATEGORY FIELD
$i=0;
while ($i < $num) {
$category=mysql_result($result,$i,"category");
$categorycount=mysql_result($result,$i,"count(category)");

echo "<a href='recordlist.php?category=$category'>$category</a></br>";
echo "<a href='recordlist.php?category=$categorycount'>$categorycount</a></br>";

$i++;
}
?>

 

Can anyone spot where I've gone wrong?

[ratcateme]

$categorycount=mysql_result($result,$i,"count(category)");

needs to be

$categorycount=mysql_result($result,$i,"count( category )");

a better way is do set a name for count like this

$query="SELECT DISTINCT category, count( category ) AS count FROM `masterdata` GROUP BY category";

and then retrieving it like

$categorycount=mysql_result($result,$i,"count");

 

Scott.

 

[webmaster1]

Brilliant! The 'AS' works much better. Thanks Scott.