Solved

MYSQL INSERT LAST_INSERT_ID(); QUERY SLOW

Posted on 2010-09-10
21
1,150 Views
Last Modified: 2012-05-10
I have a form that inserts records to three different tables than updates my relationship table that holds the row number for each new record with the  LAST_INSERT_ID(); function.

The problem that i am facing is that the query is a little slow. My code might not be up to par. is there  a better way to preform this task

This is what i am doing
after the first record is inserted into table one I do this  

$sql1="SELECT LAST_INSERT_ID(); ";
$sql2="INSERT INTO relate SET relate.BOND_ID=LAST_INSERT_ID();";
//$Result2 = mysql_query($sql1, $CLIENT_LIST) or die(mysql_error());
$Result2 = mysql_query($sql2, $CLIENT_LIST) or die(mysql_error());


second record insert  in table 2 than i
update the relationship table   with the second tables row number

$sql4="SELECT LAST_INSERT_ID(); ";


$sql5="UPDATE relate SET relate.ACCOUNTING_ID=LAST_INSERT_ID();";
//$Result4 = mysql_query($sql3, $CLIENT_LIST) or die(mysql_error());
$Result6 = mysql_query($sql5, $CLIENT_LIST) or die(mysql_error());


than i update the relationship table again


 $ResultX = mysql_query($insertSQL, $CLIENT_LIST) or die(mysql_error());
$sql7="SELECT LAST_INSERT_ID(); ";


$sql8="UPDATE relate SET relate.AGENCY_ID=LAST_INSERT_ID();";
$Result7 = mysql_query($sql8, $CLIENT_LIST) or die(mysql_error());

all the records get inserted in the proper tables the the relationship table is populated correctly it just seems like i am missing something or there could be a better way of doing this any suggestions?

<?php require_once('Connections/CLIENT_LIST.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}



if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO bond (BOND_NUMBER, Surety, Bond_Type, PAYING_STATE, BOND_STATE, TERM, CANCELATION_CLAUSE, Bond_Amt, `1st_Issue_Date`, Issue_Date, Exp_Date, RED_STARED, RED_STAR_DATE, PURGE_DATE) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['BOND_NUMBER'], "text"),
                       GetSQLValueString($_POST['Surety'], "text"),
                       GetSQLValueString($_POST['Bond_Type'], "text"),
                       GetSQLValueString($_POST['PAYING_STATE'], "text"),
                       GetSQLValueString($_POST['BOND_STATE'], "text"),
                       GetSQLValueString($_POST['TERM'], "text"),
                       GetSQLValueString($_POST['CANCELATION_CLAUSE'], "text"),
                       GetSQLValueString($_POST['Bond_Amt'], "text"),
                       GetSQLValueString($_POST['st_Issue_Date'], "text"),
                       GetSQLValueString($_POST['Issue_Date'], "text"),
                       GetSQLValueString($_POST['Exp_Date'], "text"),
                       GetSQLValueString($_POST['RED_STARED'], "text"),
                       GetSQLValueString($_POST['RED_STAR_DATE'], "text"),
                       GetSQLValueString($_POST['PURGE_DATE'], "text"));

  mysql_select_db($database_CLIENT_LIST, $CLIENT_LIST);
  $Result1 = mysql_query($insertSQL, $CLIENT_LIST) or die(mysql_error());

//$sql1="SELECT LAST_INSERT_ID();" ;

$sql1="SELECT LAST_INSERT_ID(); ";
$sql2="INSERT INTO relate SET relate.BOND_ID=LAST_INSERT_ID();";
//$Result2 = mysql_query($sql1, $CLIENT_LIST) or die(mysql_error());
$Result2 = mysql_query($sql2, $CLIENT_LIST) or die(mysql_error());

} 
 
 
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO accounting (QUOTED, COMM_FEE, NET_DUE, Surety_Gross, Surety_Net, AGENT_COMMISSION_PERCENTAGE, FL_FEE, NV_FEE, KY_FEE, RE_INSTATMENT_FEE, ADMIN_FEE, BROKER_FEE, CC_FEE, ON_FEE, CLIENT_NUMBER, HOW_MANY_BONDS) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['QUOTED'], "text"),
                       GetSQLValueString($_POST['COMM_FEE'], "text"),
                       GetSQLValueString($_POST['NET_DUE'], "text"),
                       GetSQLValueString($_POST['Surety_Gross'], "text"),
                       GetSQLValueString($_POST['Surety_Net'], "text"),
                       GetSQLValueString($_POST['AGENT_COMMISSION_PERCENTAGE'], "text"),
                       GetSQLValueString($_POST['FL_FEE'], "text"),
                       GetSQLValueString($_POST['NV_FEE'], "text"),
                       GetSQLValueString($_POST['KY_FEE'], "text"),
                       GetSQLValueString($_POST['RE_INSTATMENT_FEE'], "text"),
                       GetSQLValueString($_POST['ADMIN_FEE'], "text"),
                       GetSQLValueString($_POST['BROKER_FEE'], "text"),
                       GetSQLValueString($_POST['CC_FEE'], "text"),
                       GetSQLValueString($_POST['ON_FEE'], "text"),
                       GetSQLValueString($_POST['CLIENT_NUMBER'], "text"),
                       GetSQLValueString($_POST['HOW_MANY_BONDS'], "text"));

  mysql_select_db($database_CLIENT_LIST, $CLIENT_LIST);
  $Result3 = mysql_query($insertSQL, $CLIENT_LIST) or die(mysql_error());
//$sql3="SELECT LAST_INSERT_ID();" ;
$sql4="SELECT LAST_INSERT_ID(); ";


$sql5="UPDATE relate SET relate.ACCOUNTING_ID=LAST_INSERT_ID();";
//$Result4 = mysql_query($sql3, $CLIENT_LIST) or die(mysql_error());
$Result6 = mysql_query($sql5, $CLIENT_LIST) or die(mysql_error());

}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO agent (AGENCY, AGENT_Address, AGENT_City, AGENT_St, AGENT_Zip, AGENT_FIRST, AGENT_MIDDLE, AGENT_LAST, AGENT_SUFF, AGENT_Phone, AGENT_Fax, AGENTS_EMAIL) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['AGENCY'], "text"),
                       GetSQLValueString($_POST['AGENT_Address'], "text"),
                       GetSQLValueString($_POST['AGENT_City'], "text"),
                       GetSQLValueString($_POST['AGENT_St'], "text"),
                       GetSQLValueString($_POST['AGENT_Zip'], "text"),
                       GetSQLValueString($_POST['AGENT_FIRST'], "text"),
                       GetSQLValueString($_POST['AGENT_MIDDLE'], "text"),
                       GetSQLValueString($_POST['AGENT_LAST'], "text"),
                       GetSQLValueString($_POST['AGENT_SUFF'], "text"),
                       GetSQLValueString($_POST['AGENT_Phone'], "text"),
                       GetSQLValueString($_POST['AGENT_Fax'], "text"),
                       GetSQLValueString($_POST['AGENTS_EMAIL'], "text"));

  mysql_select_db($database_CLIENT_LIST, $CLIENT_LIST);
  $ResultX = mysql_query($insertSQL, $CLIENT_LIST) or die(mysql_error());
$sql7="SELECT LAST_INSERT_ID(); ";


$sql8="UPDATE relate SET relate.AGENCY_ID=LAST_INSERT_ID();";
$Result7 = mysql_query($sql8, $CLIENT_LIST) or die(mysql_error());

}


?

Open in new window

0
Comment
Question by:tomjenkins12
  • 10
  • 6
  • 4
  • +1
21 Comments
 
LVL 8

Expert Comment

by:ropenner
ID: 33649012
In the segment below from your code I think your intention looks to be different than what is coded.

$sql1="SELECT LAST_INSERT_ID(); ";
$sql2="INSERT INTO relate SET relate.BOND_ID=LAST_INSERT_ID();";
$Result2 = mysql_query($sql2, $CLIENT_LIST) or die(mysql_error());

I think you mean to do this

$sql1="SELECT LAST_INSERT_ID(); ";
$sql2="INSERT INTO relate SET relate.BOND_ID=$sql1";
$Result2 = mysql_query($sql2, $CLIENT_LIST) or die(mysql_error());

Since you store the last ID in SQL1 and then don't use it I assume you wanted to use it.  This may be related to the slowness of the INSERT.  Each of your queries is done this way and you do the LAST_INSERT_ID() in the query.  I didn't try your code but I suspect that putting LAST_INSERT_ID() in an UPDATE or INSERT may be causing your slowness.
0
 
LVL 8

Expert Comment

by:ropenner
ID: 33649067
I failed to see that you didn't get the result from the first query.  I think your desired code is like this.

$sql1="SELECT LAST_INSERT_ID(); ";
$Result1 = mysql_query($sql1);
list($last_id_variable) = $Result1->fetch_row();

$sql2="INSERT INTO relate SET relate.BOND_ID=$last_id_variable";
$Result2 = mysql_query($sql2, $CLIENT_LIST) or die(mysql_error());
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 33649135
You don't have a WHERE clause on your UPDATE; therefore, you are in essence updating every row with the last inserted ID.  Additionally, the INSERT INTO ... SET syntax is not standard SQL, so I tend to shy away from that; however, it does work in MySQL.

Anyway, here is what I would expect:

// insert sql for `bond` table
// set variable $bond_id = LAST_INSERT_ID()
// insert into relate (bond_id) values ($bond_id)
// insert sql for `accounting` table
// set variable $acct_id = LAST_INSERT_ID()
// update relate set accounting_id = $acct_id where bond_id = $bond_id


Repeat the bolded section for agent table insert.  Technically, if bond_id is the key you can use to filter which record to update in relate table, you can trim down the second, third, etc. calls by skipping the storage to variable of LAST_INSERT_ID() and just do this:

update relate
set accounting_id = last_insert_id()
where bond_id = $bond_id

Hope that helps.

Kevin
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33649163
Ah, I got distracted while typing my response...I see ropenner has already brought up the point that you need to get the results of last_insert_id and store it in a variable and then use it; therefore, sorry for the repeated information ... hopefully though my explanation helps to clarify the overall flow as I think the "slow" response you are seeing is due to the update statements.
0
 

Author Comment

by:tomjenkins12
ID: 33649253
those are all good suggestions i will try them out and let you know  
0
 

Author Comment

by:tomjenkins12
ID: 33649508
Call to a member function fetch_row() on a non-object in list($last_id_variable) = $Result1->fetch_row();

any suggestions
0
 
LVL 8

Expert Comment

by:ropenner
ID: 33649991
can you post your code again in its Revised form.
0
 

Author Comment

by:tomjenkins12
ID: 33650001
i did just like this

$sql1="SELECT LAST_INSERT_ID(); ";
$Result1 = mysql_query($sql1);
list($last_id_variable) = $Result1->fetch_row();

$sql2="INSERT INTO relate SET relate.BOND_ID=$last_id_variable";
$Result2 = mysql_query($sql2, $CLIENT_LIST) or die(mysql_error());
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33650259
You probably want:

$Result1 = mysql_query($sql1, $CLIENT_LIST) or die(mysql_error());

Given the assumption that $CLIENT_LIST is your connection identifier.
0
 

Author Comment

by:tomjenkins12
ID: 33650287
i just tried that and it is still saying

Fatal error: Call to a member function fetch_row() on a non-object in

list($last_id_variable) = $Result1->fetch_row();
0
 
LVL 8

Expert Comment

by:ropenner
ID: 33650386
the mysql manual says
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html

If the previous statement returned an error, the value of LAST_INSERT_ID() is undefined.

If the code just before 'what you sent us' returns an error then it would explain this error.  Can you show us the code just before or verify that the previous insert did work.

$sql1="SELECT LAST_INSERT_ID(); ";
$Result1 = mysql_query($sql1, $CLIENT_LIST);
list($last_id_variable) = $Result1->fetch_row();
0
 
LVL 2

Expert Comment

by:WilliamStam
ID: 33658138
insert into table (table2ID,table3ID) values ((select ID from table2 where *something that was submitted now* ORDER BY ID DESC limit 0,1), (select ID from table3 where *something that was submitted now* ORDER BY ID DESC limit 0,1))


last id only brings back the last id in 1 table... uness i got it wrong... lol not impossible.. ive had issues with it before
0
 

Author Comment

by:tomjenkins12
ID: 33662766
Ok i think i no what is going on. my bond id is updated with the correct number so are my other two tables but the other two tables are updating the rows below them with the new numbers so i need to do something with my where clause. so it doesn't rewrite over the existing data
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33663547
That is what I was explaining here:

http:#a33649135
0
 

Author Comment

by:tomjenkins12
ID: 33663914
i tried using the code that you provided, but when i used the last_insert_id i would get a undefined function error. i changed it to this


$bondid=mysql_insert_id();


$sql2="INSERT INTO relate SET relate.BOND_ID=$bondid";
$Result2 = mysql_query($sql2, $CLIENT_LIST) or die(mysql_error());

}

$acco=mysql_insert_id();


$sql5="UPDATE relate SET relate.ACCOUNTING_ID=$acco WHERE relate.BOND_ID=$bondid";

$Result6 = mysql_query($sql5, $CLIENT_LIST) or die(mysql_error());

}

$agentid=mysql_insert_id();


$sql8="UPDATE relate  SET relate.AGENCY_ID=$agentid WHERE relate.ACCOUNTING_ID=$acco";
$Result7 = mysql_query($sql8, $CLIENT_LIST) or die(mysql_error());

}


 and it works thank you very much. do you know if there is a problem using mysql_insert_last_id()  instead of insert_last_id ()  ?
0
 

Author Comment

by:tomjenkins12
ID: 33663922
sorry that was directed to mwvisa1
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33664041
That is just a PHP function to get at the same information, so you should be fine.  The above looks a little odd in that it doesn't have the actual inserts for the secondary fields, but assuming your real code does.  Note you can use the $bondid throughout for those so on the secondary queries, you should be able to just do this:

Ref.:
http://php.net/manual/en/function.mysql-insert-id.php

// $acco=mysql_insert_id();
$sql5="UPDATE relate SET relate.ACCOUNTING_ID=LAST_INSERT_ID() WHERE relate.BOND_ID=$bondid";

However, if it is working (i.e., it ain't broke)...as they say, no need to fix it. :)
0
 

Author Comment

by:tomjenkins12
ID: 33664210
I did it like this


if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO bond (BOND_NUMBER, Surety, Bond_Type, PAYING_STATE, BOND_STATE, TERM, CANCELATION_CLAUSE, Bond_Amt, `1st_Issue_Date`, Issue_Date, Exp_Date, RED_STARED, RED_STAR_DATE, PURGE_DATE) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['BOND_NUMBER'], "text"),
                       GetSQLValueString($_POST['Surety'], "text"),
                       GetSQLValueString($_POST['Bond_Type'], "text"),
                       GetSQLValueString($_POST['PAYING_STATE'], "text"),
                       GetSQLValueString($_POST['BOND_STATE'], "text"),
                       GetSQLValueString($_POST['TERM'], "text"),
                       GetSQLValueString($_POST['CANCELATION_CLAUSE'], "text"),
                       GetSQLValueString($_POST['Bond_Amt'], "text"),
                       GetSQLValueString($_POST['st_Issue_Date'], "text"),
                       GetSQLValueString($_POST['Issue_Date'], "text"),
                       GetSQLValueString($_POST['Exp_Date'], "text"),
                       GetSQLValueString($_POST['RED_STARED'], "text"),
                       GetSQLValueString($_POST['RED_STAR_DATE'], "text"),
                       GetSQLValueString($_POST['PURGE_DATE'], "text"));

  mysql_select_db($database_CLIENT_LIST, $CLIENT_LIST);
  $Result1 = mysql_query($insertSQL, $CLIENT_LIST) or die(mysql_error());
 
$bondid=mysql_insert_id();


$sql2="INSERT INTO relate SET relate.BOND_ID=$bondid";
//$Result2 = mysql_query($sql1, $CLIENT_LIST) or die(mysql_error());






$Result2 = mysql_query($sql2, $CLIENT_LIST) or die(mysql_error());

}


if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO accounting (QUOTED, COMM_FEE, NET_DUE, Surety_Gross, Surety_Net, AGENT_COMMISSION_PERCENTAGE, FL_FEE, NV_FEE, KY_FEE, RE_INSTATMENT_FEE, ADMIN_FEE, BROKER_FEE, CC_FEE, ON_FEE, CLIENT_NUMBER, HOW_MANY_BONDS) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['QUOTED'], "text"),
                       GetSQLValueString($_POST['COMM_FEE'], "text"),
                       GetSQLValueString($_POST['NET_DUE'], "text"),
                       GetSQLValueString($_POST['Surety_Gross'], "text"),
                       GetSQLValueString($_POST['Surety_Net'], "text"),
                       GetSQLValueString($_POST['AGENT_COMMISSION_PERCENTAGE'], "text"),
                       GetSQLValueString($_POST['FL_FEE'], "text"),
                       GetSQLValueString($_POST['NV_FEE'], "text"),
                       GetSQLValueString($_POST['KY_FEE'], "text"),
                       GetSQLValueString($_POST['RE_INSTATMENT_FEE'], "text"),
                       GetSQLValueString($_POST['ADMIN_FEE'], "text"),
                       GetSQLValueString($_POST['BROKER_FEE'], "text"),
                       GetSQLValueString($_POST['CC_FEE'], "text"),
                       GetSQLValueString($_POST['ON_FEE'], "text"),
                       GetSQLValueString($_POST['CLIENT_NUMBER'], "text"),
                       GetSQLValueString($_POST['HOW_MANY_BONDS'], "text"));

  mysql_select_db($database_CLIENT_LIST, $CLIENT_LIST);
  $Result3 = mysql_query($insertSQL, $CLIENT_LIST) or die(mysql_error());
//$sql3="SELECT LAST_INSERT_ID();" ;
$acco=mysql_insert_id();


$sql5="UPDATE relate SET relate.ACCOUNTING_ID=$acco WHERE BOND_ID=$bondid";
//$Result4 = mysql_query($sql3, $CLIENT_LIST) or die(mysql_error());
$Result6 = mysql_query($sql5, $CLIENT_LIST) or die(mysql_error());

}

so i should have a problem with multiple people inserting data at the same time?
0
 

Author Comment

by:tomjenkins12
ID: 33664243
I meant shouldn't not should sorry
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33664568
I don't believe so as you should get the id for your session, but I might be thinking of MS SQL's scope_identity, but if you do find this to be a problem later you can always look at MySQL transaction and/or moving this logic to a stored procedure and simply calling the procedure from the PHP page.  This way all the actions happen in one SQL server call and the collisions will be controlled by transaction and locking/isolation level associated.
0
 

Author Comment

by:tomjenkins12
ID: 33664622
thank you very much
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question