subquery does not work on php

Question

I have table. I need in my query count some field. This is my query:

select COUNT(q1) as 'result'
from (select DISTINCT id_qs as 'q1' FROM main where id_exam=40) as q2

It's query perfect work. But. When I put it in php code:

   function HowManyQuestion($id)
   {
        mysql_connect($this->hostname,$this->username,$this->password) OR DIE("Can't connect");
        mysql_select_db($this->dbName) or die(mysql_error());
        $query = "select COUNT(q1) as 'result' from (select DISTINCT id_qs as 'q1' FROM main where id_exam=40) as q2;";
        $res = mysql_query($query) or die(mysql_error());
        $id=0;
        while ($row=mysql_fetch_array($res)) {
            $id=$row['result'];
            break;
        }
        return $id;

   }

In result I have error:

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 q2' at line 1

So how this fix?

Remove the `;` at the end of the query. -> `as q2";` Also, `mysql_*` functions are depracated. Use mysqli or PDO instead. Read this for more info: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — AmazingDreams, Aug 13 '13 at 13:11

score 5 · Accepted Answer · answered Aug 13 '13 at 13:12

5

Actually you can simplify the query without using subquery, and which I think will remove the error.

SELECT COUNT(DISTINCT id_qs) totalCount
FROM main
WHERE id_exam = 40

answered Aug 13 '13 at 13:12

John Woo

258,903
69
498
492

As long as he keeps the semicolon out of it it probably will :) +1 – AmazingDreams Aug 13 '13 at 13:14

score 0 · Answer 2 · answered Aug 13 '13 at 13:18

0

Alias column name can't be used for further processing

            select COUNT(id_qs) as 'result'
                   from (select DISTINCT id_qs as 'q1' FROM main where id_exam=40) as q2

answered Aug 13 '13 at 13:18

alok.kumar

380
3
11

subquery does not work on php

2 Answers2