Re: [PHP-DB] MySQL query executes outside of PHP, but not in PHP

On Fri, 23 Jan 2009, [email protected] 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
[email protected]                                 http://www.angryox.com/
---------------------------------------------------------------------------