mysql php insert multiple rows with comma delimited values

Question

I have an html page where I collect an array of values from checkboxes to insert in a database. The html page posts to a PHP page that collects the data and then stores in the database.

For each value, there are a few other fields I would like to include that are the same for all the values such as time entered. I can easily convert the captured array into a comma delimited list using implode. I use such a comma delimited list of ids to update and delete records. However, when I want to insert them, MYSQL does not seem to allow you to use a comma delimited list. My question is, what is the easiest way to insert records, one for each value in the c comma delimited list, without using a loop.

html page

<input type="checkbox" name="var[]" value=1>
<input type="checkbox" name="var{}" value=2>

PHP page

$vars = $_POST['var'];

This gives me an array that I can convert to a comma delimited list using implode.

To delete, I can go

$sql = "DELETE * from table WHERE id in '$vars'";

To update I can go

$sql = "UPDATE table, WHERE id in '$vars'";

But there does not seem to be an equivalent for Insert.

Following would work:

$sql = "INSERT into table (var, timeentered) values (1,now()) (2,now())";

However, that's not how I have my data. what I would like to do is something like

$sql = "INSERT into table (var,timeentered) values($vars), now()" but of course that doesn't work.

Do I have to convert my nice comma delimited list that works so well for update and delete into something that looks like (1,now) (2, now()) for inserting or is there an alternative?

Thanks for any suggestions.

@Lucas That is absolutely incorrect.

Mike Brant
– Mike Brant

2013-01-31 00:23:58 +00:00
Commented Jan 31, 2013 at 0:23 — Mike Brant
– Mike Brant, Commented Jan 31, 2013 at 0:23

J33nn · Accepted Answer · 2013-01-31 00:26:18Z

1

Unfortunately you have to build whole query by yourself:

$sql ="insert into table (var, timeentered) values ";
$sql .= "(".implode(", now()), (", $vars).")";

answered Jan 31, 2013 at 0:26

J33nn

3,2446 gold badges35 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1904273 Over a year ago

so implode("now(),.",".$vars.") inserts a now after every element in var? This looks like an elegant way to do it. However, that does not seem like ordinary implode syntax or am I missing something?

J33nn Over a year ago

It is an ordinary implode syntax but in order to append starting "(" and ending ")" sign you need to concat it manually.

user1904273 Over a year ago

Doesn't implode insert delimiter between array values but not at end? So I have to manually append final delimiter (in this case extra ,now()

J33nn Over a year ago

My bad, of course it should be ", now())" concatenated at the end insetead of ")" :)

Mike Brant · Accepted Answer · 2013-01-31 00:27:12Z

You need to loop through your data set and create the multi-line insert query manually. There is no other alternative if you want to insert multiple rows with a single query. That is outside of using certain DB frameworks which might present a better interface for doing this. Of course at the end of the day, such a DB framework would in essence be building the multi-item insert query manually at some point.

The question might come done to one of how many items are you going to insert. If you are only going to be inserting a few records at a time, then you might want to consider just using prepared statements with individual inserts. However if you are going to be inserting hundreds of records at a time, that would probably not be a good idea.

nathan hayfield · Accepted Answer · 2013-01-31 00:28:22Z

0

In your mysql database you can set the default for the column "time_created" to be a TIMESTAMP default CURRENT_TIMESTAMP. This way you don't have to worry about it. Just use the regular insert and it will automatically set the "time_created" column.

For your other issue of multi-line inserts you can create an $update array and use a foreach loop to issue a sql insert command on every row of data.

answered Jan 31, 2013 at 0:28

nathan hayfield

2,7051 gold badge20 silver badges29 bronze badges

Comments

score 0 · Accepted Answer · 2013-01-31 00:37:33Z

0

Two options I can think of.

Build a dynamic insert query like you suggest. However do not call now() each time but just insert a single date ie

$time = gmdate();
$sql = "INSERT into table (var, timeentered) values (1,$time) (2,$time)";

Or use a prepared statement of the single insert below, turn off autocommit, start a transaction and execute the prepared statement in a for loop for the number of inserts needed, then commit the transaction.

$sql = "INSERT into table (var, timeentered) values (?,?)"

edited Jan 31, 2013 at 0:37

answered Jan 31, 2013 at 0:24

user1914530

Comments

user1433150 · Accepted Answer · 2013-01-31 00:58:04Z

0

Mostly you will have to build your query using some type of looping structure. Convention and best practice aside if you just want to know how to make your array acceptable for a multiple insert statement then why not just do this:

$values = '('.implode('),(', $array).')';

or if already CSV then:

$values = '('.implode('),(', explode(',' $csv)).')';

then you can just use $values in your query using double quotes.

answered Jan 31, 2013 at 0:58

user1433150

3193 silver badges8 bronze badges

Collectives™ on Stack Overflow

mysql php insert multiple rows with comma delimited values

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related