Was: Grab the last inserted id - mysql Now: Where should we call the last insert id ?

Question

Here's the thing, I don't have access to code that inserts data into a given table. However, I need to add related additional data into another table. So, I was thinking about grabbing the last inserted ID and from there... insert the related data into that other table.

Since I don't have access to the statement, I believe that mysql last insert id function will be of no use here.

All the PDO::lastInsertId examples that I see, are also attached to some "insert query" before it, so no use as well.

How can I grab the last inserted ID on the cases were we DON'T have access to the original insert statement ?

Data flow: It starts here: signup.tpl Where we have:

onclick="checkoutvalidate();return false"

On the js we have:

function checkoutvalidate() {
    $.post("order/index.php", 'a=validatecheckout&'+$("#orderfrm").serialize(),
    function(data){
        if (data) {
            ...
        } else {
            document.orderfrm.submit();
        }
    });

So, now, let's look for "validatecheckout" into index.php And we found it:

We can't read along this lines, anything concerning the insertion. The immediately after that I can get is, after the conditional statement - right ?

if ($a=="validatecheckout") {
   $errormessage = '';
   $productinfo = getProductInfo($pid);
            if ($productinfo['type']=='server') {
                if (!$hostname) $errormessage .= "<li>".$_LANG['ordererrorservernohostname'];
                else {
                    $result = select_query("tblhosting","COUNT(*)",array("domain"=>$hostname.'.'.$domain,"domainstatus"=>array("sqltype"=>"NEQ","value"=>"Cancelled"),"domainstatus"=>array("sqltype"=>"NEQ","value"=>"Terminated"),"domainstatus"=>array("sqltype"=>"NEQ","value"=>"Fraud")));
                    $data = mysql_fetch_array($result);
                    $existingcount = $data[0];
                    if ($existingcount) $errormessage .= "<li>".$_LANG['ordererrorserverhostnameinuse'];
                }
                if ((!$ns1prefix)OR(!$ns2prefix)) $errormessage .= "<li>".$_LANG['ordererrorservernonameservers'];
                if (!$rootpw) $errormessage .= "<li>".$_LANG['ordererrorservernorootpw'];

            }
            if (is_array($configoption)) {
                foreach ($configoption AS $opid=>$opid2) {
                    $result = select_query("tblproductconfigoptions","",array("id"=>$opid));
                    $data = mysql_fetch_array($result);
                    $optionname = $data["optionname"];
                    $optiontype = $data["optiontype"];
                    $qtyminimum = $data["qtyminimum"];
                    $qtymaximum = $data["qtymaximum"];
                    if ($optiontype==4) {
                        $opid2 = (int)$opid2;
                        if ($opid2<0) $opid2=0;
                        if ((($qtyminimum)OR($qtymaximum))AND(($opid2<$qtyminimum)OR($opid2>$qtymaximum))) {
                            $errormessage .= "<li>".sprintf($_LANG['configoptionqtyminmax'],$optionname,$qtyminimum,$qtymaximum);
                            $opid2=0;
                        }
                    }
                }
            }
            $errormessage .= checkCustomFields($customfield);
            if (!$_SESSION['uid']) {
                if ($_REQUEST['signuptype']=="new") {
                    $firstname = $_REQUEST['firstname'];
                    $lastname = $_REQUEST['lastname'];
                    $companyname = $_REQUEST['companyname'];
                    $email = $_REQUEST['email'];
                    $address1 = $_REQUEST['address1'];
                    $address2 = $_REQUEST['address2'];
                    $city = $_REQUEST['city'];
                    $state = $_REQUEST['state'];
                    $postcode = $_REQUEST['postcode'];
                    $country = $_REQUEST['country'];
                    $phonenumber = $_REQUEST['phonenumber'];
                    $password1 = $_REQUEST['password1'];
                    $password2 = $_REQUEST['password2'];
                    $temperrormsg = $errormessage;
                    $errormessage = $temperrormsg.checkDetailsareValid($firstname,$lastname,$email,$address1,$city,$state,$postcode,$phonenumber,$password1,$password2);
                    $errormessage .= checkPasswordStrength($password1);
                } else {
                    $username = $_REQUEST['username'];
                    $password = $_REQUEST['password'];
                    if (!validateClientLogin($username,$password)) $errormessage .= "<li>".$_LANG['loginincorrect'];
                }
            }
            if (($CONFIG['EnableTOSAccept'])AND(!$_REQUEST['accepttos'])) $errormessage .= "<li>".$_LANG['ordererrortermsofservice'];
            $_SESSION['cart']['paymentmethod'] = $_REQUEST['paymentmethod'];
            if ($errormessage) echo $_LANG['ordererrorsoccurred']."<br /><ul>".$errormessage."</ul>";
            else {
                if ($_REQUEST['signuptype']=="new") {
                    $userid = addClient($firstname,$lastname,$companyname,$email,$address1,$address2,$city,$state,$postcode,$country,$phonenumber,$password1);
                }
            }

            //DO THE DO INSERT_LAST_ID() here ?
        }

Thanks in advance, MEM

Answer 1

After the insert statement you can fire another query:

SELECT LAST_INSERT_ID();

and this will return one row with one column containing the id.

Docs: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

mysql> SELECT LAST_INSERT_ID();
        -> 195

This works per connection so there is no problem if another thread writes into the table. But your SELECT needs to be executed 'RIGHT AFTER'/'As the next query' after the insert query ran

Edit

An example:

$dbConnection = MyMagic::getMeTheDatabase("please");

$oSomeFunkyCode->createThatOneRowInTheDatabase($dbConnection);

$result = $dbConnection->query("SELECT LAST_INSERT_ID();");
// ... fetch that one value and you are good to go

Answer 2

If the column is a simple auto_incrementing integer, you could use SELECT MAX(MyAutoincrementingColumn) FROM MyTable. You might risk selecting a row that has been inserted by another user in the meantime, if your users are not using transactions.

Answer 3

If you don't have access to the last INSERT line, you can make a subquery to find the last inserted id:

select max(id) from <table>