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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.