Re: MySQL query executes outside of PHP, but not in PHP

On Fri, 23 Jan 2009, Dave.McGovern@sungard.com wrote:

Hi, I am running: PHP 5.2.8, Apache 2.2.11, MySQL 5.1.30 on Win32/XP. I have a number of queries on my page which are very similar in structure, and they all work except for the following one. $mysql['process'] = $client2->real_escape_string($clean['process']); $sql = "SELECT f.name, f.description

       FROM files f, file_mapping m, processes p
       WHERE m.file_id = f.id
       AND p.name = '{$mysql['process']}'
       AND m.process_id = p.id
       AND m.io_flag = 'I'";

if ($client2->multi_query($sql)) { echo '<h3 class="H3-OTMS">Main Input Files</h3>'; do { if ($result = $client2->use_result()) {

        while ($input = $result->fetch_row()) {
              $filename = $input[0];
              $descr = $input[1];
              echo '<p><span class=FileName>'.$filename.'</span>'.'

'.$descr.'</p>';

            }
           $result->close();
           }

} while ($client2->next_result()); } If I echo the $sql, and then run it in MySQL directly, it works fine. I have tried replacing the variable in the WHERE clause with a hardcoded value and and have tried replacing this query with a very basic query with no variable, but nothing has worked. No error message is returned. Any suggestions as to what I might check? Here's an example of an echo of the following $sql that runs OK in MySQL Query Browser: SELECT f.name, f.description FROM files f, file_mapping m, processes p WHERE m.file_id = f.id AND p.name = 'BCOM1AC' AND m.process_id = p.id AND m.io_flag = 'I'

error_log("Hey, the SQL is: $sql"); Then look in your php error log (you do have error logging enabled, right?) If that SQL in the error log is fine, then your problem is $client2->multi_query($sql) -- what does THAT return? What SHOULD it return? What are you expecting it to return? Does it return what you thought it did? When you do "if ($var)" it can sometimes have unexpected results. If $var is an empty string, it's still true, and executes. I don't know that multi_query SHOULD return, or how to determine if it throws an error, but that's one place to start. Next, if multi_query worked, then this line is suspect:

if ($result = $client2->use_result()) {

This will always result in TRUE, as the assignment will always succeed. Change it to:

if (($result = $client2->use_result())) {

(added parenthesis) What DB library are you using? ---------------------------------------------------------------------------

Peter Beckman                                                  Internet Guy
beckman@angryox.com                                 http://www.angryox.com/

---------------------------------------------------------------------------