Query that works in SQL but not in PHP

Question

Hello there,

I am having trouble with an SQL query that I have inserted into a piece of PHP code to retrieve some data. The query itself works perfectly within SQL, but when I use it within my PHP script it says "Error in Query" then recites the entire SQL statement. If I copy and paste the SQL statement from the error message directly into MySQL it runs with no errors.

From my research I believe I am missing an apostrophe somewhere, so PHP may be confusing the clauses, but I am not experienced enough to know where to insert them.

The query is using a variable called $userid which is specified earlier in the PHP script.

$sql= <<<END

SELECT sum(final_price)  
FROM (
    SELECT Table_A.rated_user_id, Table_B.seller, Table_B.final_price
    FROM Table_A
       INNER JOIN Table_B ON Table_A.id=Table_B.id
) AS total_bought
WHERE seller != $userid
AND rated_user_id = $userid

    UNION ALL

SELECT sum(final_price)  
FROM (
    SELECT Table_A.rated_user_id, Table_C.seller, Table_C.final_price
    FROM Table_A
        INNER JOIN Table_C ON Table_A.id=Table_C.id
) AS total_bought
WHERE seller != $userid
AND rated_user_id = $userid

END;

After this section the script then goes on to define the output and echo the necessary pieces as per usual. I'm happy with the last part of the code as it works elsewhere, but the problem I am having appears to be within the section above.

Can anyone spot the error?

Edited to add the following additional information:

All of the fields are numerical values, none are text. I have tried putting '$userid' but this only makes the error display the ' ' around this value within the error results. The issue remains the same. Adding parenthasis has also not helped. I had done a bit of trial and erorr before posting my question.

If it helps, the last part of the code bieng used is as follows:

$result = mysql_query($sql);
if (!$res) {
  die('Error: ' . mysql_error() . ' in query ' . $sql);
}
$total_bought = 0;
while ($row = mysql_fetch_array($result)) {
  $total_bought += $row[0];
}
$total_bought = number_format($total_bought, 0);
echo '<b>Your purchases:  '  . $total_bought . '</b>';
echo "<b> gold</b>";

Answer 1

I suppose, you're echo()ing the query somewhere and copy-pasting it from the browser. Could it be that the $userid contains xml tags? They wouldn't be displayed in the browser, you would have to view the page source to spot them.

Answer 2

Should $userid be quoted? ==> '$userid'

Answer 3

you should test with $userid quoted, and parentheses around the two statements.

Answer 4

I'm assuming that rated_user_id is a numeric field, but what type is seller? If it's a character field, then $userid would have to be quoted as streetpc suggests.

Another thing to check is that you have at least one space after the end of your lines for each line of the query. That has tripped me up before. Sometimes when going from your editor/IDE to the database tool those problems are silently taken care of.

Answer 5

You're checking !$res, it should be !$result:

$result = mysql_query($sql);
if (!$result) {
  die('Error: ' . mysql_error() . ' in query ' . $sql);
}