Solved

MYSQL INSERT LAST_INSERT_ID(); QUERY SLOW

Posted on 2010-09-10
21
1,121 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
those are all good suggestions i will try them out and let you know  
0
 

Author Comment

by:tomjenkins12
Comment Utility
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
Comment Utility
can you post your code again in its Revised form.
0
 

Author Comment

by:tomjenkins12
Comment Utility
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
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 8

Expert Comment

by:ropenner
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
That is what I was explaining here:

http:#a33649135
0
 

Author Comment

by:tomjenkins12
Comment Utility
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
Comment Utility
sorry that was directed to mwvisa1
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
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
Comment Utility
I meant shouldn't not should sorry
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
thank you very much
0

Featured Post

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.

Join & Write a Comment

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
The viewer will learn how to dynamically set the form action using jQuery.

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now