4 Replies - 1288 Views - Last Post: 11 June 2012 - 06:01 AM

#1 Duckington   User is offline

  • D.I.C Addict

Reputation: 172
  • View blog
  • Posts: 615
  • Joined: 12-October 09

SQL Syntax error (when run in php)

Posted 11 June 2012 - 04:26 AM

Confuzzled again..

I have this query, which works perfectly fine in MySQL Workbench, in phpmyamdin and in MySQL console, but when I try and run it in php, i get a syntax error...

SET @i := 0;

SELECT *, @i := @i +1 AS id FROM
(
     SELECT 
        DISTINCT (c.name), NULL AS tid, c.name AS cname
     FROM 
        mdl_tracking_qual_units qu
     INNER JOIN 
        mdl_tracking_criteria c ON c.trackingunitid = qu.trackingunitsid
     WHERE 
        qu.trackingqualificationid = 439
     AND 
        c.type = 'theory'
     ORDER BY c.name ASC
) tbl1

 UNION

 SELECT *, @i := @i +1 AS id FROM
 (
     SELECT 
        DISTINCT CONCAT_WS('/', c.name, t.name) as name, t.id AS tid, NULL AS cname
     FROM
        mdl_tracking_qual_units qu
     INNER JOIN
        mdl_tracking_criteria c ON c.trackingunitid = qu.trackingunitsid
     INNER JOIN
        mdl_tracking_task t ON t.trackingunitid = qu.trackingunitsid
     INNER JOIN
        mdl_tracking_task_criteria tc ON (tc.trackingcriteriaid = c.id AND tc.trackingtaskid = t.id)
     WHERE
        qu.trackingqualificationid = 439
     AND
        c.type = 'practical'
     ORDER BY t.id ASC, c.id ASC
 ) tbl2

 ORDER BY IFNULL(cname, 'ZZZZ'), cname ASC, tid ASC



For instance if I break it right down to just a simple mysql_query or die, this is the error output:

Quote

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 'SELECT *, @i := @i +1 AS id FROM ( SELECT DI' at line 4


So it's clearly not liking the variable i'm using... but it works fine everywhere else.. :/ I would have thought if it really was a syntax error, it wouldn't work in mysql console, etc...

Any ideas?

Thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Syntax error (when run in php)

#2 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4240
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: SQL Syntax error (when run in php)

Posted 11 June 2012 - 05:10 AM

The mysql_query() function doesn't support multiple statements in one call.

php.net/mysql_query said:

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database...


If you want to do that, I'd suggest that you to use PDO instead. With PDO, you can do stuff like this:
$dbLink = new PDO("mysql:host=localhost;dbname=test", "uname", "pwd");

$sql = "SET @add = 3;
        SHOW TABLES; 
        SELECT COUNT(*) + @add AS comb FROM `member`;";
$res = $dbLink->query($sql);

do {
    $set = $res->fetchAll(PDO::FETCH_ASSOC);
    var_dump($set);
}
while ($res->nextRowset());


And the result would be three separate result sets, one for each statement. - And yes, that includes an empty set for the SET @add = 3; statement.

One piece of advice though. If you are executing long blocks of statements like that, you may do better to create a procedure, so you won't have to send the whole query string each time it's needed.
Was This Post Helpful? 1
  • +
  • -

#3 Duckington   User is offline

  • D.I.C Addict

Reputation: 172
  • View blog
  • Posts: 615
  • Joined: 12-October 09

Re: SQL Syntax error (when run in php)

Posted 11 June 2012 - 05:15 AM

Ah, i'll have to do it some other way then. I have to use the core moodle functions and they don't use PDO in the version we're using.

This post has been edited by Duckington: 11 June 2012 - 05:15 AM

Was This Post Helpful? 0
  • +
  • -

#4 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4240
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: SQL Syntax error (when run in php)

Posted 11 June 2012 - 05:46 AM

Do you have access to MySQLi? If not, your kind of out of luck. The old MySQL extension doesn't support multiple statements, or procedure calls. (It was made for MySQL 3 and 4, not 5.)
Was This Post Helpful? 0
  • +
  • -

#5 Duckington   User is offline

  • D.I.C Addict

Reputation: 172
  • View blog
  • Posts: 615
  • Joined: 12-October 09

Re: SQL Syntax error (when run in php)

Posted 11 June 2012 - 06:01 AM

Nah, but it seems to work fine if I just do one query to set the variable and then another query to do the select, so i'll just do it that way.

Cheers.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1