|
|
|
|
Thread title: MySQLi: Multiple insert into's and last_insert_id |
|
|
|
|
|
Thread tools
Search this thread
Display Modes
|
|
12-31-2012, 02:17 AM
|
#1
|
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
Posts: 3,164
|
MySQLi: Multiple insert into's and last_insert_id
I'm attempting one large query rather than several smaller ones. Firstly I'm inserting into one table and grabbing it's row ID to insert into another, for multiple rows. Is there anyway to do this?
Is there a better method of multiple inserts than I'm currently doing? Writing the same insert into several times feels like overkill.
Code:
INSERT INTO `users notes` (`text`)
VALUES ('blah blah some text here');
INSERT INTO `users exercises` (`userid`, `exerciseid`, `time`, `weight`, `distance`, `reps`, `intensity`, `notes`)
(1, 188, 1, NULL, NULL, NULL, NULL, last_insert_id()),
(1, 188, 1, NULL, NULL, NULL, NULL, last_insert_id());
INSERT INTO `users notes` (`text`)
VALUES ('blah blah some text here');
INSERT INTO `users exercises` (`userid`, `exerciseid`, `time`, `weight`, `distance`, `reps`, `intensity`, `notes`)
(1, 188, 1, NULL, NULL, NULL, NULL, last_insert_id()),
(1, 188, 1, NULL, NULL, NULL, NULL, last_insert_id());
etc.
|
|
12-31-2012, 04:41 AM
|
#2
|
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
Posts: 6,894
|
The beauty of prepared statements is that you can use them more than once with different parameters. They are called prepared statements because the database engine partially processes the command itself (without the parameter values) before you actually set the values and run the query. This provides a massive speed benefit.
In your case the first two queries seem necessary but the next two are the exact same thing. Prepare a statement with what you need to be run over and over, use some control structure to continually set the parameters to their next value then run the query.
A more generic example of what I'm talking about
PHP Code:
$arr=array('a','b','c','d','e','f'); $DBLink= new mysqli("localhost", "user", "password", "database");
//Bad way $DBlink->query(" INSERT INTO table VALUES ($arr[0]); INSERT INTO table VALUES ($arr[1]); INSERT INTO table VALUES ($arr[2]); INSERT INTO table VALUES ($arr[3]); INSERT INTO table VALUES ($arr[4]); INSERT INTO table VALUES ($arr[5]); INSERT INTO table VALUES ($arr[6]); ");
//Better code, but the same thing as above. foreach($arr as $v{ $DBlink->query("INSERT INTO table VALUES ($v)"); }
//Best way $insertLetters= $DBlink->prepare("INSERT INTO table VALUES (?)"); foreach($arr as $v{ $insertLetters->bind_param('s',$v); $insertLetters->execute(); }
|
|
|
|
|
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
|