phpmyadmin returns query results but php returns 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.

And if you're using MySQLi, why aren't you using a prepared statement with bind variables? — Mark Baker
– Mark Baker, Commented Jan 22, 2014 at 0:37
@MarkBaker I'm not sure what the point of using prepared statements is if you're not accepting user input. Are you worried about not sanitizing your own code — Ohgodwhy
– Ohgodwhy, Commented Jan 22, 2014 at 0:38
An unrelated problem: you want mysqli_fetch_assoc or mysqli_fetch_array($result, MYSQLI_ASSOC) in order to get those echoes to work. — miyasudokoro
– miyasudokoro, Commented Jan 22, 2014 at 0:47
What are the ampersands for in echo "oid$line=$row[orderID]&"; etc. ? — Funk Forty Niner
– Funk Forty Niner, Commented Jan 22, 2014 at 0:49

Mike Brant · Accepted Answer · 2014-01-22 00:42:00Z

2

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.

edited Jan 22, 2014 at 0:42

answered Jan 22, 2014 at 0:36

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1864610 Over a year ago

Good advice, but neither comment is an answer to the OPs problem

Mike Brant Over a year ago

@MikeW Well yes and no. The fact that he is using multi query without using proper use/store result function to work with the result set is a problem. I added that here. My main intention was to suggest to the poster to just use mysqli_query() in this case.

user1864610 Over a year ago

OP has tried both mysqli_query() and mysqli_multi_query() - he says so in his post.

Mike Brant Over a year ago

@MikeW Ah didn't see that at first. Well I guess we won't know what the problem is until error handling is added...

Collectives™ on Stack Overflow

phpmyadmin returns query results but php returns 0

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related