PHP echo SQL Count

Question

Okay, so I am having a problem. I seem to be unable to successfully echo an SQL Count in PHP.

SQL:

SELECT TableA.C, COUNT(*) FROM TableA JOIN TableB ON (TableA.C = TableB.D) 
   WHERE TableB.E = 1 GROUP BY TableA.C ORDER BY COUNT(*) DESC

PHP:

$result= mysql_query("SELECT TableA.C, COUNT(*) FROM TableA JOIN TableB ON (TableA.C = TableB.D) 
   WHERE TableB.E = 1 GROUP BY TableA.C ORDER BY COUNT(*) DESC");

while($rows = mysql_fetch_array($result))
{
     echo $rows['Count']."</br>";
}
$rows = mysql_fetch_array($result);
{
     echo $rows['Count'];
}

I've tried two different things I've found online (the above). I even tried one with "mysql_fetch_array($result, MYSQL_ASSOC)" instead of just mysql_fetch_array($result).

Each time, I get the same error messages:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in

/home/semsemx1/public_html/x/xx.php

Additionally, I've tried capitalizing as "$rows['COUNT']", but that doesn't work.

Any help would be appreciated.

echo `mysql_error()` to see what MySQL is returning. Also, look into switching to PDO as `mysql_*` functions are deprecated. — webbiedave, May 20 '13 at 14:21
while using an alias is a good idea, `$rows['count(*)']` should work too. the index of the data is the verbatim result column name (can run the query on the mysql command line to see the names of the result columns) — user428517, May 20 '13 at 14:25

score 6 · Accepted Answer · answered May 20 '13 at 14:18

6

You just need to give your count an ALIAS

SELECT TableA.C, COUNT(*) as total

then you can call it with

echo $rows['total']

Then I would like you to remember that mysql_* functions are deprecated so i would advise you to switch to mysqli or PDO

answered May 20 '13 at 14:18

Fabio

23,183
12
55
64

2

Yes, we cannot stress highly enough you need to move away from mysql_ functions. That and mysqli is much easier to use. – lukeocodes May 20 '13 at 14:27
2

and pdo is even easier to use than mysqli. use it instead. – user428517 May 20 '13 at 14:30

score 4 · Answer 2 · answered May 20 '13 at 14:18

4

Use "As" keyword

SELECT TableA.C, COUNT(*) as count FROM TableA JOIN TableB ON (TableA.C = TableB.D) WHERE TableB.E = 1 GROUP BY TableA.C ORDER BY COUNT(*) DESC


 echo $rows['count '];

answered May 20 '13 at 14:18

Igor S.

3,332
1
25
33

PHP echo SQL Count

2 Answers2

Linked