What is wrong with mySQL Syntax?

Question

Hi there,

i am using the following code to count, and to sum the values from the database.

$query = "SELECT
          COUNT(n.*) AS cnt_news,
          COUNT(a.*) AS cnt_adv,
          COUNT(c.*) AS cnt_comm,
          SUM(CASE WHEN c.approve = '1' AND c.spam = '0' THEN 1 ELSE 0 END) AS cnt_approved,
          SUM(CASE WHEN c.approve = '0' AND c.spam = '0' THEN 1 ELSE 0 END) AS cnt_unapproved,
          SUM(CASE WHEN c.spam = '0' THEN 1 ELSE 0 END) AS cnt_spam,
          SUM(a.amount) AS t_amnt,
          SUM(a.cashpaid) AS t_cpaid,
          SUM(a.balance) AS t_bal
          FROM
          news n, advertisements a, comments c";
          $result = mysql_query($query) or die(mysql_error());
          $row = mysql_fetch_array($result);

the following code gives me an error, the error is

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) AS cnt_news, COUNT(a.*) AS cnt_adv, COUNT(c.*) AS cnt_c' at line 2

if i remove the first three lines of the select query, it does not show the error instead it prints the wrong values.

that is wrong with my code. ??

the following code works perfectly fine for me.

$query = "SELECT COUNT(*) as cnt_news FROM news";
$result = mysql_query($query);
$row = mysql_fetch_array($result);


$query = "SELECT COUNT(*) as cnt_adv FROM advertisements";
$result = mysql_query($query);
$row = mysql_fetch_array($result);

$query = "SELECT COUNT(*) as cnt_comm FROM comments";
$result = mysql_query($query);
$row = mysql_fetch_array($result);


$query = "SELECT SUM(CASE WHEN c.approve = '1' AND c.spam = '0' THEN 1 ELSE 0 END) AS cnt_approved,
          SUM(CASE WHEN c.approve = '0' AND c.spam = '0' THEN 1 ELSE 0 END) AS cnt_unapproved,
          SUM(CASE WHEN c.spam = '1' THEN 1 ELSE 0 END) AS cnt_spam
          FROM COMMENTS c";
$result = mysql_query($query);
$row = mysql_fetch_array($result);


$query = "SELECT SUM(a.amount) as t_amnt,
          SUM(a.cashpaid) as t_cpaid,
          SUM(a.balance) as t_bal
          FROM advertisements a";
$result = mysql_query($query);
$row = mysql_fetch_array($result);

where am i going wrong?

Answer 1

It looks like Mysql doesn't like that line. Change COUNT(n.*) to COUNT(n.id) or whatever the name of that table's primary key field is. Do the same for a and c.

Answer 2

You cannot use count(tablename.*), try using count(tablename.columnname)

Answer 3

You can try

SELECT (SELECT COUNT(*) FROM news) AS cnt_news, (SELECT COUNT(*) FROM advertisements) as cnt_adv, ...

Answer 4

Well i dropped the idea of making my queries into a single one, and as suggested by Col.Shrapnel i did a custom function for it, and i found it very easy to maintain the code this way. thank you Col.Sharpnel i am posting the answer suggested by him.

this is the user defined function i created.

function dbgetvar($query) {
             $res = mysql_query($query);
         if( !$res) {
             trigger_error("dbget: ". mysql_error(). " in " .$query);
             return false;
             }
             $row = mysql_fetch_array($res);
             if(!$row) return "";
             return $row;
             }  

and then i called my function using this code.

     $news = dbgetvar("SELECT COUNT(*) as count FROM news");
 $comments = dbgetvar("SELECT SUM(CASE WHEN c.approve = '1' AND c.spam = '0' THEN 1 ELSE 0 END) AS approved,
                       SUM(CASE WHEN c.approve = '0' AND c.spam = '0' THEN 1 ELSE 0 END) AS pending,
                       SUM(CASE WHEN c.spam = '1' THEN 1 ELSE 0 END) AS spam,
                       COUNT(*) AS count
                       FROM COMMENTS c");
$advertise = dbgetvar("SELECT SUM(a.amount) AS amount,
                       SUM(a.cashpaid) AS cashpaid,
                       SUM(a.balance) AS balance,
                       COUNT(*) AS count
                       FROM advertisements a");

the above code is working perfectly fine for me.