Display count of unique values in MySQL db table using PHP

Question

So I have a table named pins that has 3 columns that need to be taken into consideration. These columns are plan and order_id.

I need to get a count for all of the pins that have an order_id=0 and plan=9.

This is what I have so far:

$qT="SELECT plan, COUNT(*) as cnt FROM pins WHERE order_id=0 and plan=9";
$res=mysql_query($qT);<br/>
mysql_free_result($res);

while($row = mysql_fetch_array($res)) {<br/>
echo $row['plan'];<br/>
}

Any help in displaying the results would be a great help.

You shouldn't be using mysql_* it is deprecated. You can look into PDO or mysqli_* which will help you out, please see http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — BRBT, Apr 25 '15 at 02:23
The columns are 3, you post only plan and order_id. The other column is? — Adrian Cid Almaguer, Apr 25 '15 at 02:32

A l w a y s S u n n y · Accepted Answer · 2015-04-25T14:18:10.757

1

Try to group your pin using GROUP BY, So

$result = mysql_query("SELECT plan, COUNT(pin) as cnt FROM pins WHERE (order_id=0 and plan=9) GROUP BY plan");

echo "<table border='1' style='border-collapse: collapse'>";  
echo "<th>Plan</th><th>Count</th>";  
while ($row = mysql_fetch_array($result))   
{  
   echo "<tr><td>".$row['plan']."</td><td>".$row['cnt']."</td></tr>";
}

echo "</table>";        
mysql_free_result($result);
?>

edited Apr 25 '15 at 14:18

answered Apr 25 '15 at 02:29

A l w a y s S u n n y

36,497
8
60
103

@user3241520, answer edited as per your `pin` column, let me know it works for you or not? – A l w a y s S u n n y Apr 25 '15 at 03:06
Almost there! This is what was displayed: Plan: 9 Count: 1Plan: 9 Count: 1Plan: 9 Count: 1 – Michael S Apr 25 '15 at 03:32
figured it out... Just changed the GROUP by pin to GROUP by plan. I now get Plan: 9 Count: 3 Thank you! – Michael S Apr 25 '15 at 03:35

Display count of unique values in MySQL db table using PHP

1 Answers1