Caio Ferrari Caio Ferrari - 1 year ago 64
SQL Question

Insert many rows from a table into one unique row in another table

I have a table named

where there is set a column name

TABLE list
id | list | price | date
1 | Cigar | 5.00 | 2016-06-30
2 | Beer | 6.00 | 2016-06-30
3 | Whiskey | 20.00 | 2016-06-30
4 | Bacon | 10.00 | 2016-06-30

I'd like to insert the
into another table named
in a way that all of them could be in a same row! However, it doesn't work and is inserting in many rows!

The way I want

TABLE confirmation
id | theorder
1 | Cigar, Beer, Whiskey, Bacon

The way is showing

TABLE confirmation
id | theorder
1 | Cigar,
2 | Beer,
3 | Whiskey,
4 | Bacon,

Here is the code: I'm working with foreach!

$sql = "SELECT list FROM order";
$result = $conn->query($sql);
$getList = $result->fetchAll(PDO::FETCH_ASSOC);

foreach($getOrder as $order) {
$products = $order['theorder'] . ', ';
$sql = "INSERT INTO confirmation (theorder) VALUES ('$products')";
$result = $conn->query($sql);

Answer Source

Every time you perform an INSERT query it creates a new row. Since you're doing this in a loop, it creates a new row for each product.

If you want to combine items, use GROUP_CONCAT:

INSERT INTO confirmation (theorder)
FROM `order`

Notice that you need to quote the table name order with backticks because it's a reserved word. It's generally best to avoid using reserved words as table and column names, since if you forget to quote it you'll get a confusing syntax error. See Syntax error due to using a reserved word as a table or column name in MySQL

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download