By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,417 Members | 1,856 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,417 IT Pros & Developers. It's quick & easy.

insert multi rows with a single mysql function call?

P: n/a
Dear folks,
pardon me if this is a stupid question.

For this table
---------------------------------------
| id | foo | bar |
--------------------------------------

I understand that we can insert a row to the table per call, like this
sql_query = "INSERT INTO `table` (`foo`, `bar`) VALUES ('$string_1',
'$string_2')";
mysql_query($sql_query, $db_handle);

if I want to insert multi rows(the following example inserts two rows)
at the same time, the following example works, but can I merge this
two insertion into a single mysql operation (to achieve efficiency)?

sql_query = "INSERT INTO `table` (`foo`, `bar`) VALUES ('$string_1',
'$string_2')";
mysql_query($sql_query, $db_handle);
sql_query = "INSERT INTO `table` (`foo`, `bar`) VALUES ('$string_1',
'$string_2')";
mysql_query($sql_query, $db_handle);

Thanks,

Jul 21 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
>For this table
>---------------------------------------
| id | foo | bar |
--------------------------------------

I understand that we can insert a row to the table per call, like this
sql_query = "INSERT INTO `table` (`foo`, `bar`) VALUES ('$string_1',
'$string_2')";
mysql_query($sql_query, $db_handle);

if I want to insert multi rows(the following example inserts two rows)
at the same time, the following example works, but can I merge this
two insertion into a single mysql operation (to achieve efficiency)?

sql_query = "INSERT INTO `table` (`foo`, `bar`) VALUES ('$string_1',
'$string_2')";
mysql_query($sql_query, $db_handle);
sql_query = "INSERT INTO `table` (`foo`, `bar`) VALUES ('$string_1',
'$string_2')";
mysql_query($sql_query, $db_handle);
INSERT INTO `table` (`foo`, `bar`) VALUES
('$string_1', '$string_2'),
('$string_3', '$string_4'),
('$string_5', '$string_6'),
('$string_7', '$string_8');

Warning: you are limited in the length of the query you can have by
some mysql parameters, so depending on the length of the strings, you
may need to limit this to a few thousand records at a time.

Jul 21 '07 #2

P: n/a
..oO(Gordon Burditt)
>INSERT INTO `table` (`foo`, `bar`) VALUES
('$string_1', '$string_2'),
('$string_3', '$string_4'),
('$string_5', '$string_6'),
('$string_7', '$string_8');

Warning: you are limited in the length of the query you can have by
some mysql parameters, so depending on the length of the strings, you
may need to limit this to a few thousand records at a time.
Yep, I've run into this before ...

A better solution (IMHO) would be to use PDO and a prepared statement.
The most time-consuming part is to analyze and optimize the query. With
a prepared statement this just has to be done once. After that you just
execute the statement in a loop with all the values you want to insert.

Micha
Jul 21 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.