CODEWITHSUNDEEP
mysqlsqlarraysrow
Caio Ferrari
Caio Ferrari

Reputation: 319

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!

Upvotes: 0

Views: 27

Answers (1)

Blank
Blank

Reputation: 12378

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

Upvotes: 2

Related Questions