[SOLVED] Mysql COUNT() function stuck!

[elpaisa]

Hi all!

 

I have a problem with a complex query tha involves COUNT() function.

I am writing a script that calculates the number o records for each word in a table, for an example it may look like this

 

Status

 

bad              168

good            7636

excelent        44842

 

I found some code that helped me solve the problem in part, this is:

 

SELECT sed_statistics.status, COUNT(sed_statistics.id) FROM sed_statistics LEFT JOIN equivalences ON sed_statistics.status = equivalences.equiv GROUP BY sed_statistics.status ORDER BY sed_statistics.status ASC LIMIT 0, 30

 

I don't understand completely how this query works, got it from a web page "http://mysql.adoppt.com/blog/frank/mysql-count-the-number-of-records-139",  this works in phpmyadmin, its  results are:

 

STATUS      COUNT(sed_statistics.id) 

bad              168

good            7636

excelent        44842

 

got this code in my script:

 

$query = "SELECT sed_statistics.status, COUNT(sed_statistics.id) FROM sed_statistics LEFT JOIN equivalences ON sed_statistics.status = equivalences.equiv GROUP BY sed_statistics.status ORDER BY sed_statistics.status ASC LIMIT 0, 30";

 

$result = mysql_query($query) or die(mysql_error());

 

// Print out the contents of each row into a table

while($row = mysql_fetch_array($result)){

echo $row['status'];

echo "<br />";

}

 

 

and its results are:

 

 

bad         

good           

excelent

 

don't know how to show the number of records per each row, can somebody point me in the right direction please, i'm stuck with this code, or if you have any other suggestion it will be welcome, any!.

 

Thanks all!.

 

 

[js_280]

Change your script to:

 

 

$query = "SELECT sed_statistics.status, COUNT(sed_statistics.id) AS my_count FROM sed_statistics LEFT JOIN equivalences ON sed_statistics.status = equivalences.equiv GROUP BY sed_statistics.status ORDER BY sed_statistics.status ASC LIMIT 0, 30";

     

      $result = mysql_query($query) or die(mysql_error());

 

// Print out the contents of each row into a table

      while($row = mysql_fetch_array($result))

    {

      echo $row['status'];

      echo " ";

      echo $row['my_count'];

    }

[elpaisa]

Thanks js_280 works fine, and thanks too much for your quick help!