multi-row insert

Anon

Hi all:

Very newbie question:

I'd like to insert multiple rows into mysql with a single submit, as in a product order form. I imagine a for loop would do, is anyone willing to give an example of what such a loop would look like? How would the values array be defined?

Thank you,
John

Anon

OK, let's say you have 3 fields in the table (keep it simple) and you have a form that generates the three fields in arrays $field1, $field2 and $field3 and you don't know how many entries there will be. You can do something like this (assuming validation has been done to insure all fields have valid data):

$values="";
for ($i=0;$i<count($field1);$i++)
{
$values.="('$field1[$i]','$field2[$i]','$field3[$i]'),";
}
$values=substr($values,0,-1); // to remove last comma
$query="insert into table name values $values";
$result=mysql_query($query);

of course depending on your needs you may need to add the field names to your query, insert some null values for IDs, dates, ...

Anon

Rod:

Just what I needed, it's very much appreciated. Thank you.

John

Anon

Hi Mate,

Are you aware that depending on what version of mysql you are using you can do multiple inserts using one query?

From a php perspective it still means running thru a loop but it will greatly reduce the usage on your mysql server as it will only be dealing with one sql statement, rather than many. All you need to do is to put a comma in between each record.

INSERT INTO <table> VALUES (<field1>,<field2>),(<field1>,<field2>).....

Cheers

Shaitan

Anon

Uh, Ray, you are aware that's what I showed him how to do....

Anon

Hi Rod,

Yup..I did not read your code thoroughly enough 🙂

Cheers

Ray

Anon

Ray:

Thanks for the reply. I just became aware of this feature. Sure makes things neater. Really appreciate your help.

Thank you,

John

Anon

This worked great for me, except I needed to place a semi-colon in the insert string.

$temp_sql .= "(".$temp['id']."),";

$temp_sql=substr($temp_sql,0,-1);

$db->query("INSERT IGNORE INTO TEMP VALUES".$temp_sql.";");