Explore our sites

Stack Exchange
tour help careers 2.0
Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question
add comment

marked as duplicate by Jack, kapa, Aleks G, Sirko, M42 Oct 18 '12 at 9:45

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2 Answers

up vote 5 down vote accepted

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"]
share|improve this answer
 
Thanks! It works perfectly –  rsmith84 Oct 18 '12 at 4:16
add comment
up vote 0 down vote

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>
share|improve this answer
 
I already had all the table stuff built and am familiar with outputting records. I just did not know about the ALIAS part in the accepted answer above. –  rsmith84 Oct 18 '12 at 5:32
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.