Using PHP to Output MySQL Query Where COUNT(*) is Used [duplicate]

Question

Possible Duplicate:
mysql count into PHP variable

I have the following query that returns successfully when run from MySQL command prompt:

SELECT `from_email`, COUNT(*) 
FROM `user_log` 
GROUP BY `from_email` 
ORDER BY COUNT(*) DESC

This query returns a result set that has the following columns

`from_email` | COUNT(*)

My question is, how do I go about iterating through the result sets and outputting the results. I have my table formatted I just need from_email in one table cell and the associated COUNT in another for each record.

Thanks in advance

Answer 1

add an ALIAS on it

SELECT `from_email`, COUNT(*) AS totalCount 
FROM `user_log` 
GROUP BY `from_email` 
ORDER BY totalCount DESC

and you can now fetch the value

 $row["from_email"]
 $row["totalCount"]

Answer 2

Following is the code for connect to database and retrieve the result and display in table.

<?
$conn = mysqli_connect("localhost", "root","root", "test");
$query="SELECT `from_email`, COUNT(*) AS emailCount FROM `user_log` GROUP BY `from_email` ORDER BY COUNT(*) DESC";
$result = mysqli_query($conn, $query);
if ($result) {
   while ($row = mysqli_fetch_array($result, MYSQLI_BOTH))
   {
      $table[] = $row;
   }
}
?>

<table border="1">
<tr>
   <td width="200">From Email</td>
   <td width="50">Count</td>
</tr>

<?
if($table){
    for($i=0;$i<count($table);$i++){
?>

<tr>
   <td><?=htmlentities($table[$i]["from_email"])?>&nbsp;</td>
   <td><?=htmlentities($table[$i]["emailCount"])?>&nbsp;</td>
</tr>

<?
    }
}
?>

</table>