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

what do you call "form"? HTML forms do not add anything to databases. What is exact data flow and what is position of your wannabe code in it? — Your Common Sense, Sep 27 '10 at 10:33
@Col. Shrapnel - this system has a js function called checkout() that does: document.orderfrm.submit(); However, I don't see on any php available to me, the call of that checkout(). I presume it's on some encrypted files. — MEM, Sep 27 '10 at 10:54
what you gonna do with this id? send it back to js or use for some other insert operation? — Your Common Sense, Sep 27 '10 at 12:09
The only thing i see in that code that seems like it does some sort of insert is the `addClient` function and that functions seems to return the newly created id. Are we talking about the user table ? — edorian, Sep 27 '10 at 12:55
no. :) domains table. So, with me, you notice that we don't have any "after insert" place here. So, my best guess will be after the conditional. I will try there and see if I get any ID. If not, I believe the MAX will be my only option hm... ? — MEM, Sep 27 '10 at 13:10
I didn't retrieve anything after the conditional. I got 0. :( So, I will try immediately after the document.orderfrm.submit(); If that doesn't work either, then MAX will be my only option? — MEM, Sep 27 '10 at 16:54

edorian · Accepted Answer · 2010-09-27T10:51:10.353

3

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

edited Sep 27 '10 at 10:51

answered Sep 27 '10 at 10:26

edorian

38,542
15
125
143

But I don't have access to the "after insert statement momentum". – MEM Sep 27 '10 at 10:31
@MEM but make yourself such access! How and when your code is supposed to be called? a half-year after this insert statement or what? – Your Common Sense Sep 27 '10 at 10:35
@Col. Shrapnel : No. After the insert. But I can't see exactly were. This is a mess with ajax calls and a lot of procedural code that I find hard to grasp due to my lack of knowledge. :s I guess I have to FIRST found out where can I call this and, then, use LAST_INSERT_ID() as suggested since MAX as the inconvenience of not being accurate... I can't see the insert statement but I can see the js function that submits the form... but I cannot see the PHP that process it, if any. :s – MEM Sep 27 '10 at 10:44
@MEM i provided an example, as long as you can get anywhere in between "insert query" and "next query" you are good to go. If not you probably will have to make some function somewhere return the id or pass it around another way but without an example i'm just guessing in the dark :) – edorian Sep 27 '10 at 10:53
@edorian: Thanks a lot. Please have patience. I will try to understand the workflow and edit my question accordingly. – MEM Sep 27 '10 at 11:00
@edorian - once you have the opportunity, please have a quick look into the additional information above. – MEM Sep 27 '10 at 11:21

score 0 · Answer 2 · answered Sep 27 '10 at 10:29

0

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.

answered Sep 27 '10 at 10:29

Hammerite

21,755
6
70
91

It was not me. I'm still struggling here, and perhaps, MAX is my only option here btw... because I really can't get near insert, and the near I can get it's perhaps, if I do another $.post AFTER document.orderfrm.submit(); hopping that I can get a last insert id from there? :/ – MEM Sep 27 '10 at 16:18
I have added a vote up, because in this case, and to me, it seems pertinent. – MEM Sep 27 '10 at 16:52

score -1 · Answer 3 · answered Sep 27 '10 at 10:29

-1

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>

answered Sep 27 '10 at 10:29

CristiC

22,068
12
57
89

2

What happens when 2 users insert a row at the same time over different connections? You could potentially end up with the wrong ID – Neil Aitken Sep 27 '10 at 10:37

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

3 Answers3

Edit