Today's Posts Follow Us On Twitter! TFL Members on Twitter  
Forum search: Advanced Search  
Navigation
Marketplace
  Members Login:
Lost password?
  Forum Statistics:
Forum Members: 24,256
Total Threads: 81,169
Total Posts: 566,839
There are 50 users currently browsing (tf).
 
  Our Partners:
 
  TalkFreelance     Design and Development     Programming     PHP and MySQL :

MySQLi: Multiple insert into's and last_insert_id

Thread title: MySQLi: Multiple insert into's and last_insert_id
Reply    
    Thread tools Search this thread Display Modes  
12-31-2012, 02:17 AM
#1
Dan is offline Dan
Dan's Avatar
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
 
Posts: 3,164
iTrader: 15 / 86%
 

Dan is an unknown quantity at this point

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

Reply With Quote
12-31-2012, 04:41 AM
#2
Village Genius is offline Village Genius
Village Genius's Avatar
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
 
Posts: 6,892
iTrader: 18 / 100%
 

Village Genius will become famous soon enough

  Old

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();

Reply    


Thread Tools
Display Modes

  Posting Rules  
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump:
 
  Contains New Posts Forum Contains New Posts   Contains No New Posts Forum Contains No New Posts   A Closed Forum Forum is Closed