mysqli multiple queries - set variable produces boolean error/how to skip this?

Question

Got the following simple query which works fine through phpmyadmin but when I add it to my php website no results are returned and no error/warning messages either. If I remove "SET @N=-1;" then it works fine.

<?php 
$db_connect = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD, true);
mysql_select_db(DB_NAME, $db_connect);

$test_query = mysql_query("SET @N=-1;SELECT `id`, (@N:=@N+1) AS `mycount` FROM `mydb`;");

for ($i = 0; $i <= mysql_num_rows($test_query)-1; $i++) {
   echo mysql_result($db_directorymap, $i, 0) . " " . mysql_result($db_directorymap, $i, 1) . "<br />";
}
?>

UPDATE: I just moved to mysqli but of course I'm still having a problem with the mysql statement and mysqli_multi_query. It seems when it runs the first part of the query the results returned are empty thus a boolean error is given. I'm guessing I have to skip the first set of results but I don't know how to do that?

Answer 1

Multiple queries via mysql_query aren't supported, so I'd guess that it's only executing the SET command, and not the subsequent SELECT command.

Answer 2

It's because the mysql_query function will only accept one query, but you've given it two, separated by a semicolon. Try either:

1. Running each query separately (don't know if this will work):

   mysql_query( "SET @N=-1" );
   mysql_query( "SELECT `id`, (@N:=@N+1) AS `mycount` FROM `mydb`" );
   

2. Using mysqli with the multi_query function (or a PDO equivalent if there is one).

To answer your updated question: check the PHP manual page for multi_query. I think you'll want to use mysqli::next_result. Something like this, using procedural style:

mysqli_multi_query($link, $query);
mysqli_next_result($link);

if ($result = mysqli_store_result($link)) {
    while ($row = mysqli_fetch_row($result)) {
        printf("%s\n", $row[0]);
    }
    mysqli_free_result($result);
}