9focuspoints 9focuspoints - 7 months ago 13
SQL Question

Count with database values php

I've got a database with products like so:

+----+--------+-------+
| ID | Name | Price |
+----+--------+-------+
| 1 | Burger | 3,96 |
+----+--------+-------+
| 2 | Fries | 1,49 |
+----+--------+-------+


I get an array of id's from JS into the PHP script and now I want to get the total price in a single variable.

I use to use the PDO method to fetch data, but I've only sent them through to my JS. How do I actually use that data in PHP instead?

$get = $db->prepare("SELECT price FROM products WHERE id IN ('$idString')");
$get->execute();
$rows = $get->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($rows);

Answer

If you just need the total (without the rest of the data), you can do that in your SQL:

$get = $db->prepare("SELECT SUM(price) as total FROM products WHERE id IN ($idString)");
$get->execute();
$row = $get->fetch(PDO::FETCH_ASSOC);
echo $row['total'];

Alternatively, you can iterate your rows and produce a total:

$get = $db->prepare("SELECT price FROM products WHERE id IN ($idString)");
$get->execute();
$rows = $get->fetchAll(PDO::FETCH_ASSOC);
$total = 0;
foreach ($rows as $row) {
    $total += $row['price'];
}
echo $total;
Comments