PHP Question

[soundser]

Hi,

Does anyone know why PHP issues this Warning:

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /home/mydir/public_html/myprog/weekly.php on line 103

Here is line 103:

$result = mysqli_query($dbcnx, $sql2); 
    while($data = mysqli_fetch_assoc($result)) { 

$message  = 'Invalid query: ' . mysqli_error($dbcnx) . "\n";
$message .= 'Whole query: ' . $sql2 . "</p>";

        $results[] = $data;  
  }
} 

Here is the sql:

(SELECT id, REF_NO, KASI_NO, TYPE, CITY, FINISH_SQ_FT, FOREMAN, INVOICE, REGIONTBL, FIN_DATE FROM Atlantic_CapeMay_Done WHERE status = 'DONE' AND FIN_DATE > '2006-01-15
' AND FIN_DATE < '2006-01-22
' AND INVOICE != 'BACK') UNION (SELECT id, REF_NO, KASI_NO, TYPE, CITY, FINISH_SQ_FT, FOREMAN, INVOICE, REGIONTBL, FIN_DATE FROM Audobon_Done WHERE status = 'DONE' AND FIN_DATE > '2006-01-15
' AND FIN_DATE < '2006-01-22
' AND INVOICE != 'BACK') UNION (SELECT id, REF_NO, KASI_NO, TYPE, CITY, FINISH_SQ_FT, FOREMAN, INVOICE, REGIONTBL, FIN_DATE FROM Monmouth_Done WHERE status = 'DONE' AND FIN_DATE > '2006-01-15
' AND FIN_DATE < '2006-01-22
' AND INVOICE != 'BACK') UNION (SELECT id, REF_NO, KASI_NO, TYPE, CITY, FINISH_SQ_FT, FOREMAN, INVOICE, REGIONTBL, FIN_DATE FROM Ocean_Done WHERE status = 'DONE' AND FIN_DATE > '2006-01-15
' AND FIN_DATE < '2006-01-22
' AND INVOICE != 'BACK') UNION (SELECT id, REF_NO, KASI_NO, TYPE, CITY, FINISH_SQ_FT, FOREMAN, INVOICE, REGIONTBL, FIN_DATE FROM West_Done WHERE status = 'DONE' AND FIN_DATE > '2006-01-15
' AND FIN_DATE < '2006-01-22
' AND INVOICE != 'BACK') ORDER BY FOREMAN, FIN_DATE

This started right after I changed the sql to a UNION statement. Thanks!

[Smo]

Are you on the php5 server?
mysqli_fetch_assoc is a php5 function after all.

[Ron]

...and UNION is a MySQL 4.0 addition, too. I don't know what server you're on, but check your version of MySQL.

[jason]

That error will occur if the mysql(i) query on the previous line fails. In that case mysqli_query() returns the boolean value "false" which is not the mysqli_result set that mysqli_fetch_assoc() expects.

Try this:

    $result = mysqli_query($dbcnx, $sql2);
    if($result === false) {
        die(mysqli_error());
    }
    while($data = mysqli_fetch_assoc($result)) {
        $results[] = $data;  
  }
} 

In that example, if the query fails, PHP will print an error message to the browser and then exit. You were mixing the code for error processing and processing the actual results (if the query hadn't failed) in the block of code intended for processing the result. Therefore you error code wasn't being run and you were getting the generic PHP error instead.

--Jason

[soundser]

Are you on the php5 server?
mysqli_fetch_assoc is a php5 function after all.

yes

...and UNION is a MySQL 4.0 addition, too. I don't know what server you're on, but check your version of MySQL.

4.1.14-standard

Jason,

After changing to your code, the following is returned:

Warning: mysqli_error() expects exactly 1 parameter, 0 given in /home/mysite/public_html/myprog/weekly.php on line 100

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /home/mysite/public_html/myprog/weekly.php on line 102

I changed die(mysqli_error()); to print(mysqli_error()); so the script would continue, which it does, and it actually returns the correct info. I'm just puzzled about the warnings.

[jason]

Sorry, it should have been mysqli_error($dbcnx).

Also try this in place of your current while statement:

while(false !== ($data = mysqli_fetch_assoc($result))) {
...
} 

What I think is happening is that mysqli_fetch_assoc is returning each result as expected. It then gets to the end and returns false as it should, but since the assignment to $result is successful, the expression returns true and the while loop doesn't get its siganl to stop. The new code that I just provided should check to see if $data has been assigned a value of false and, if so, the loop should end.

Let me know if it doesn't work.

--Jason

[soundser]

Sorry, it should have been mysqli_error($dbcnx).

Also try this in place of your current while statement:

while(false !== ($data = mysqli_fetch_assoc($result))) {
...
} 

What I think is happening is that mysqli_fetch_assoc is returning each result as expected. It then gets to the end and returns false as it should, but since the assignment to $result is successful, the expression returns true and the while loop doesn't get its siganl to stop. The new code that I just provided should check to see if $data has been assigned a value of false and, if so, the loop should end.

Let me know if it doesn't work.

--Jason

Nope. It displays the same warning in an endless loop.