phpmyadmin返回查询结果但php返回0

Question

I've spent the past couple hours combing this site and the web for a solution to my problem. I'm certain it is an issue of syntax, and that it's a simple fix. When running the following query in phpmyadmin, I get the desired results. I'm joining two tables and displaying sums from each. I used phpmyadmin to generate php code but return 0 lines. Changing the SELECT query in php to a simple line which gathers only the sum and records from one table outputs multiple rows. It is only when I use the multiple SELECTS from the more advanced query that I get no results.

SELECT
od.orderID,
od.owed,
od.freight,
op.paid
FROM
(
    SELECT
        acct,
        orderID,
        SUM(price * qty) AS owed,
    freight
    FROM
        old_details
    GROUP BY
        orderID
) AS od
INNER JOIN (
    SELECT
        orderID,
        SUM(payment) AS paid
    FROM
        old_payment
    GROUP BY
        orderID
) AS op ON (op.orderID = od.orderID)
WHERE od.acct='AS3576E'

The PHP script is...

<?php

include_once "connect.php";

$cID = 'AS3576E';


$query = "SELECT od.orderID, od.owed, od.freight, op.paid
FROM ( SELECT acct, orderID, SUM(price * qty) AS owed, freight
FROM old_details
GROUP BY orderID ) AS od
INNER JOIN (
SELECT orderID, SUM(payment) AS paid
FROM old_payment
GROUP BY orderID ) AS op ON (op.orderID = od.orderID)
WHERE od.acct='$cID'";

$line=0;

$results = mysqli_multi_query($link, $query);

while($row=mysqli_fetch_array($results)){
echo "oid$line=$row[orderID]&";
echo "odate$line=$row[invDate]&";
echo "oowed$line=$row[owed]&";
echo "opaid$line=$row[paid]&";
echo "ofreight$line=$row[freight]&";

$line++;
}


echo "line=$line";


mysqli_close($link);

?> 

I have tried mysqli_multi_query and mysqli_query. I have tried many different tweaks. There isn't a problem connecting to the database, as a change in the php query outputs rows of data. Any help would be greatly appreciated.

Answer 1

You don't need to use mysqli_multi_query here. Even though you use subselects, this is still only a single query with a single result set usuitable for use with mysqli_query(). If one were to use mysqli_multi_query, you would need to use mysqil_use_result() or mysqli_store_result() to work with the result set(s).

Also, you should get in the habit of actually checking for and logging errors when working with the database. Here you start trying to work with $results before you even bother to check whether it contains a valid resource for the result set.