MYSQL INSERT LAST_INSERT_ID(); QUERY SLOW

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

tomjenkins12Asked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
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
 
ropennerCommented:
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
 
ropennerCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Kevin CrossChief Technology OfficerCommented:
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
 
tomjenkins12Author Commented:
those are all good suggestions i will try them out and let you know  
0
 
tomjenkins12Author Commented:
Call to a member function fetch_row() on a non-object in list($last_id_variable) = $Result1->fetch_row();

any suggestions
0
 
ropennerCommented:
can you post your code again in its Revised form.
0
 
tomjenkins12Author Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
You probably want:

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

Given the assumption that $CLIENT_LIST is your connection identifier.
0
 
tomjenkins12Author Commented:
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
 
ropennerCommented:
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
 
WilliamStamCommented:
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
 
tomjenkins12Author Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
That is what I was explaining here:

http:#a33649135
0
 
tomjenkins12Author Commented:
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
 
tomjenkins12Author Commented:
sorry that was directed to mwvisa1
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
tomjenkins12Author Commented:
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
 
tomjenkins12Author Commented:
I meant shouldn't not should sorry
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
tomjenkins12Author Commented:
thank you very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.