insert multi rows with a single mysql function call?

[newbie]

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($sq l_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($sq l_query, $db_handle);
sql_query = "INSERT INTO `table` (`foo`, `bar`) VALUES ('$string_1',
'$string_2')";
mysql_query($sq l_query, $db_handle);

Thanks,

[Gordon Burditt]

Re: insert multi rows with a single mysql function call?

>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($s ql_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($s ql_query, $db_handle);
>sql_query = "INSERT INTO `table` (`foo`, `bar`) VALUES ('$string_1',
>'$string_2') ";
>mysql_query($s ql_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.

[Michael Fesser]

Re: insert multi rows with a single mysql function call?

..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