Multiple/Bulk inserts into DB

[d0nny]

Hi

I'm using a mySQL DB.
I have a form on my page with effectively 5 rows on it, each with say 3 or 4 fields on it.
So I want to insert all of these 5 rows into the DB at once when the user hits the Save button.

So firstly, how would I name each field on the form?
I assume they can't all be the same name on each row?
So I can't have 'name=date' for a datefield on each row. Does that mean I need to have 'name=date1' and then 'name=date2' etc on the form?

Secondly, once I have all the data and the user hits the Save button, how do I efficiently insert all of that into the database as once?

Thanks ....

 

[vacunita]

For starters, you would need to make your rows into an array. So just name your fields thus:

name="date[red][][/red]"
name="otherfield[red][][/red]"

Notice the empty brackets. Just name them all like that.

PHP sees this and automatically turns the series of fields values into an array.

So the date value of the first row would be in $_POST['date'][0] the second row's $_POST['date'][1] so on and so forth.

for Another field it would be $_POST['otherfield'][0] ...

You could go further and do it like:

name="row[0]['date']";
name="row[0]['otherfield']";

name="row[1]['date']";
name="row[1]['otherfield']";

That way you get a more cohesive structure. Again once PHP sees this it will generate an array.

So you would have $_POST['row'][0]['date'], row[0]['otherfield'] etc...

Then its just a matter of looping through the arrays, building the queries and executing them. You'd still need to execute them one by one, because the PHP mysql functions only support a single query at a time. But a loop would take care of that.

for($i=0;$i<=count($_POST['row']);$i++{
$date=mysql_real_escape_string($_POST['row'][$i]['date']);
$Ofield=mysql_real_escape_string($_POST['row'][$i]['otherfield']);
...
$qry="Insert Into mytable date,field2,field3... values($date,$Ofield,...)";

if(!mysql_query($qry)){
exit ("Error running the following query: " . $qry);
}
}

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/

 

[d0nny]

Mmmm, makes perfect sense and thanks for the explanation.

I'm sure jpadie will tell me he's told me this before!

I'll give it a shot and see what happens.

 

[jpadie]

not only, dear Donald, have I told you before, but i've given you reams of code for it in the shopping cart app.

if this is for what I think, a potentially better solution is to have single set of form data that is uploaded to your server via ajax.

 

[d0nny]

You are quite correct.
After my message and before your response I did go through the code you reference and spotted how this was done.

Consider my wrist slapped!

 

[jpadie]

I shall trust you to sit on the naughty step for a good two minutes. no talking, mind...