Caio Ferrari Caio Ferrari - 5 months ago 9
SQL Question

When concatenating values, SQL returns the array word instead of values

I have a table

orders
where there is a list where users select and them stored in the column
list


TABLE orders
userid | theorder | price
-------------------------------
20 | Cigar | 5.00
20 | Beer | 6.00
20 | Whiskey | 20.00
20 | Bacon | 10.00
21 | Beer | 10.00
21 | Bacon | 10.00
22 | Cigar | 10.00


There is also a new table named
confirmation
where I want to concatenate all the users choice! The problem is that the column is outputting the many 'array' words!

Here is how I want:

TABLE confirmation
userid | list | price
-------------------------------------------------
20 | Cigar, Beer, Whiskey, Bacon | 41.00
21 | Beer, Bacon | 20.00
22 | Cigar | 10.00


Here is the actual problem:

TABLE confirmation
userid | list | price
-------------------------------------------------
20 | Array, Array, Array, Array | 41.00
21 | Array, Array | 20.00
22 | Array | 10.00


Here is the code I'm using

$sql = "SELECT theorder FROM orders WHERE userid='$userID'";
$result = $conn->query($sql);
$getOrder = $result->fetchAll(PDO::FETCH_ASSOC);

$sql = "INSERT INTO confirmation (list, userid) SELECT GROUP_CONCAT('$getOrder' SEPARATOR ', '), '$userID' FROM orders";
$result = $conn->query($sql);






Note: I've tried using
foreach
function, but it outputs many rows with the same "array" word value!

Answer

You can use one query to do that;)

INSERT INTO confirmation (list, userid, price)
SELECT
    GROUP_CONCAT(theorder SEPARATOR ', '),
    userid,
    sum(price)
FROM orders
WHERE userid='$userID'
GROUP BY userid