Bjorn Behr Bjorn Behr - 7 months ago 25
PHP Question

inner join not working in PHP, but working in phpMyAdmin SQL

I have a MySql Query that is not returning correct values in PHP, but if I run the same MySql Query in phpMyAdmin, it returns a value. If I display the select in a web browser, I get a 'Resource id #27' on the end of it.

PHP Code

$SQL_PhotoQueryList = "SELECT count(*) FROM `invoice_detail`".
" INNER JOIN `photos` ON invoice_detail.photo_id = photos.photo_id".
" INNER JOIN `invoice` ON invoice_detail.invoice_id = invoice.invoice_id".
" WHERE invoice.invoice_active = '$PassStatus' AND photos.user_id = '$SessionUserID'".
$SQL_PhotoResultList = mysql_query($SQL_PhotoQueryList);
$ListPhotoCount = mysql_result($SQL_PhotoResultList,0);
echo "SQL Query = $SQL_PhotoQueryList<br>";
echo "ListCount = $ListPhotoCount<br>";


Screen Output

SQL Query = SELECT count(*) FROM `invoice_detail` INNER JOIN `photos` ON invoice_detail.photo_id = photos.photo_id INNER JOIN `invoice` ON invoice_detail.invoice_id = invoice.invoice_id WHERE invoice.invoice_active = '2' AND photos.user_id = '2'Resource id #27
ListCount = 0


Code calling the routine ($SessionUserID is a $_SESSION Variable)

$PassStatus = "2"; // Active
require("get_invoice.php");
$InfoTotalSales = $ListGalleryCount;

Answer

It looks like you have a typo.

$SQL_PhotoQueryList = "SELECT count(*) FROM `invoice_detail`".
                      " INNER JOIN `photos` ON invoice_detail.photo_id = photos.photo_id".
                      " INNER JOIN `invoice` ON invoice_detail.invoice_id = invoice.invoice_id".
                      " WHERE invoice.invoice_active = '$PassStatus' AND photos.user_id = '$SessionUserID'".
$SQL_PhotoResultList = mysql_query($SQL_PhotoQueryList);    
$ListPhotoCount      = mysql_result($SQL_PhotoResultList,0);
echo "SQL Query = $SQL_PhotoQueryList<br>";
echo "ListCount = $ListPhotoCount<br>";

Note the full stop (actually, concatenation operator) on the last line of the query:

                      " WHERE invoice.invoice_active = '$PassStatus' AND photos.user_id = '$SessionUserID'".

This should be a semicolon. Yep, I've done that too. Sometimes the hardest mistake to find.

The resource ID at the end is from the result of mysql_query().

Comments