构造一个sqlite查询

Question

I don't know sql (or PHP) and probably have no business coding, but here we are...

I have a database table with buyer, price, qty.

I want to query the database and return subtotals grouped by distinct buyers.

I've tried

$result = $db->query('SELECT buyer, SUM(price * qty) AS subtotal FROM transactions GROUP BY buyer ASC')
foreach($result as $row)
{
print $row['buyer'];
print $row['subtotal']."<br />";
}

Not working.

What am I doing wrong?

Answer 1

You have to fetch the data before you can loop through it -

$result = $db->query('SELECT buyer, SUM(price * qty) AS subtotal FROM transactions GROUP BY buyer ASC');
$results = $result->fetchAll(PDO::FETCH_ASSOC);

foreach($results as $row)
{
print $row['buyer'];
print $row['subtotal']."<br />";
}