Split array into separate columns in mysql php

Question

I am really new at php/mysql, so I hope you will bear with me! This code is part of a larger script, but I think it is the crucial parts needed to help me further.

$order_id = $_POST['order_ids'];
$order_ids = implode(",",$order_id);

<input type="text" name="order_ids[]" value="123">
<input type="text" name="order_ids[]" value="456">

$query = "INSERT INTO order_list (id,order_id) VALUES (LAST_INSERT_ID(),'$order_ids')";

I would like to get:

id|order_id
10|123
10|456

Instead of what I get now

id|order_id
10|123, 456

UPDATE

The code from @Ido seems to work out of the box, I have one more field input I would like to add as well to the column which in the table is called "amount" which is similar to the order_id field input.

$order_amount = $_POST['order_amounts_field'];
$order_amounts = implode(",",$order_amount);

I tried copying this and changing with the other one, but soon realized I have to execute both inputs in the same query so as to get them in the same row:

$order_ids = array();

foreach($order_id as $id)
$order_ids[] = "(LAST_INSERT_ID(), '". $id ."')";

$order_ids = implode(", ", $order_ids);

$query = "INSERT INTO order_list (id,order_id) VALUES $order_ids";

Why do you need the `LAST_INSERT_ID()`, isn't `id` auto-incrementing? — user3783243, Apr 03 '15 at 22:34
It needs to be the same ID as I use in a previous query. It might not make sense though? — Mark, Apr 03 '15 at 22:36
AFAIK The column has to be auto-incrementing for that to work so you can't have the same ID in there twice. — user3783243, Apr 03 '15 at 22:44
Well that part seems to work fine for me, the inserted ID matches the ID I want it to match. Would be surprised if my method was recommended practice though. — Mark, Apr 03 '15 at 22:56

score 0 · Answer 1 · answered Apr 03 '15 at 22:31

0

You're explicitly combining the IDs into a string and inserting one row so the results make sense.

You need to loop through each ID submitted and attach them to a dynamically built INSERT query:

$query = "INSERT INTO order_list (id,order_id) VALUES ";
foreach ($_POST['order_ids'] as $order_id) {
    $order_id = (int) $order_id; // sanitize numerical value
    $query .= "(LAST_INSERT_ID(), $order_id),";
}
$query = rtrim($sql, ',');

This just illustrates a concept. There are multiple ways to do this. Just be sure you sanitize your data as your sample code is very insecure as it is wide open to SQL injection.

answered Apr 03 '15 at 22:31

John Conde

217,595
99
455
496

Will try to implement this code once my head becomes clear again :) For now I'm working locally, with quite a few other queries and statements already working properly, although also insecure. I know you don't have access to my other lines of code, but as a general rule of thumb, how vast are the differences? I guess I'm asking since I'd like to continue writing functionality and get it all working properly, then focus on security, but if it will require a complete rewrite to sanitize into mysqli or similar then I might as well start now I suppose. – Mark Apr 03 '15 at 22:43
It's a broad topic but this [question and its answers](http://stackoverflow.com/questions/60174) cover it well. – John Conde Apr 03 '15 at 22:45

Ido · Accepted Answer · 2015-04-03T22:38:58.900

0

$order_ids = array();

foreach($order_id as $id)
    $order_ids[] = "(LAST_INSERT_ID(), '". $id ."')";

$order_ids = implode(", ", $order_ids);

$query = "INSERT INTO order_list (id,order_id) VALUES $order_ids";

edited Apr 03 '15 at 22:38

answered Apr 03 '15 at 22:35

Ido

2,034
1
17
16

That isn't valid SQL. Also, always use curly braces in loops. It's good practice and prevents future errors. You also should mention that their code is very insecure and recommend best practices. – John Conde Apr 03 '15 at 22:36
Put that query in a SQL editor and see. And that's a bad practice that you should try to get out of. – John Conde Apr 03 '15 at 22:37
The OP is used string value there .. so I just left it like that .. even thought I also think its should be int. I first used `sprinft()` but didn't wanted to make things complicated for him. – Ido Apr 03 '15 at 22:40
That's not the error. And to really do it right, and be complex, this should be done with PDO and call_user_func_array(). – John Conde Apr 03 '15 at 22:40
Space after `,` ? it is allowed .. if its not the error either then what is the error ? – Ido Apr 03 '15 at 22:42
I think you need to strip the last comma. – user3783243 Apr 03 '15 at 22:45
Thanks, will try to work with this as well as John Conde's suggestion as soon as I get back to the desktop – Mark Apr 03 '15 at 22:59
WOW, really @John Conde ? `implode()` is only between element .. no comma at the end .. – Ido Apr 03 '15 at 23:24
Seemed to work great, have updated my original question with a new one, asking how to add a second input field similar to the order_id input. Thanks for all your help so far, even if it made me realize how many hours I gotta study for now :) – Mark Apr 03 '15 at 23:56

score 0 · Answer 3 · answered Apr 03 '15 at 23:03

$query = "INSERT INTO order_list (id,order_id) VALUES (LAST_INSERT_ID(),'$order_ids')";

In SQL if you want to insert multiple rows you have to do :

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

See details here

So, in your case :

INSERT INTO order_list (order_id) VALUES (123), (456)

We can avoid increment manually "id" if it's an integer autoincrement and the primary key for this table in your database.

I hope that I have made this clear. Regards

Split array into separate columns in mysql php

UPDATE

3 Answers3