SELECT LAST_INSERT_ID()

[DaveC426913]

Is it possible to SELECT LAST_INSERT_ID() directly in SQL, so I can tell if it's my PHP code failing or otherwise?

database: emu
table: events
primary key auto-incr: event_code


How do I tell it what table to look in?

This does not work:
SELECT LAST_INSERT_ID(events.event_code)

(It says "unknown table events")

 

[sleipnir214]

First, what SQL-speaking database backend are we talking about?

If we're talking about MySQL, then a single value for last_insert_id() is held by the server for each connection. It's not held on a per-table basis, so "SELECT last_insert_id()" would be correct. See:

http://dev.mysql.com/doc/refman/4.1/en/odbc-and-last-insert-id.html

If we're talking about a non-MySQL database, the last_insert_id() may not exist.


Want the best answers? Ask the best questions! TANSTAAFL!

 

[DaveC426913]

Oops. Yes, MySQL.

This is a spin-off of another question [

http://www.tek-tips.com/viewthread.cfm?qid=1212361&page=1

] I'm in the middle of looking for an answer to. The PHP is returning a non-sensical number - a number that's about 150 less than my auto-increment value - and I don't know why.

"...then a single value for last_insert_id() is held by the server for each connection..."

I was afraid of this.

I'm still no closer to understanding this value.

 

[sleipnir214]

I think you're overthinking this whole thing. The last_insert_id() is pretty simple.

Each user has a connection. (Each instantiation of a script is a separate user.) When a user inserts a record into any table, and that insert should cause MySQL to generate an auto_increment ID, MySQL remembers the value generated. And MySQL keeps separate track of the last insert ID each user generated.

Since each user's last auto_increment ID is remembered separately, if the order of events is:[ol][li]User 1 inserts a record and that insert causes MySQL to generate an auto_increment ID[/li][li]User 2 inserts a record and that insert causes MySQL to generate an auto_increment ID[/li][li]User 1 issues a "SELECT last_insert_id()" query[/li][li]User 2 issues a "SELECT last_insert_id()" query[/li][/ol]then MySQL will return to each user the auto_increment ID that each user, himself, caused MySQL to generate. This is by design.


Want the best answers? Ask the best questions! TANSTAAFL!

 

[DaveC426913]

I'm not overthinking it, I'm trying to resolve a bug in existing code.

The event_code (the primary key) it is retrieving (which it thinks is the last event created) is 1551, whereas the actual event_code just created was 1724.

If I create a new event, I see that it appears in the db as event_code 1725 (which is correct), if I then immediately turn around and ask (i.e. within the very same PHP function - next line in fact) what the LAST_INSERT_ID() was, I get 1552 (what???)

Note how it _is_ incrementing - adding a record at 1726 gives me 1553, adding a record at 1727 gives me 1554, etc..

 

[sleipnir214]

I don't know what to say. I have never known MySQL to return an incorrect value, either using mysql_insert_id() or an explicit "SELECT last_insert_id()" query.

I have seen bad script code to garble a value.


What do you mean when you say: "then immediately turn around and ask (i.e. within the very same PHP function - next line in fact)" You're not trying to issue two queries on one invocation of mysql_query() are you?


Want the best answers? Ask the best questions! TANSTAAFL!

 

[DaveC426913]

i.e.:

$query1 = "INSERT INTO events (event_code,client_dbase,event_id,modified,beta_date,event_date,setup_date,
practice_date,derig_date,expiry_date,zone_type,event_type,audience_type,
viewers,quote_id,invoice_id,sales,pm,est_value,notes,ehi_status)
VALUES (NULL,'$clientDB','$eventID',NOW(),'$betaDate','$eventDate','$setupDate',
'$practDate','$derigDate','$expirDate','$zoneType','$eventType','$audncType',
'$viewers','$quoteID','$invoiceID','$sales','$pm','$value','foo!','100')";

$result1 = $Data->PostData($mainDB, $query1);

echo "last insert id is :" . mysql_result(mysql_query ("SELECT LAST_INSERT_ID()"),0,0)."<br>";

 

[sleipnir214]

Insufficient data for a meaningful answer.


That code invokes classes not available to me.


Want the best answers? Ask the best questions! TANSTAAFL!

 

[DaveC426913]

This is beginning to parallel my other thread which wasn't my intention, but as long as I'm getting traction here...

Here is relevant the bits of code:

$Data = new Data($clientDB,$eventID,'db');

$result1 = $Data->PostData($mainDB, $query1);



class Data 
{
    // constructor
    function Data ($clientDB,$eventID,$src = "db")
    {
        $this->clientDB = $clientDB; // the client db name, also directory, etc.
        $this->eventID  = $eventID;
        $this->source   = $src;
    }

    function PostData ($db,$query)
    {
        $result = $this->_dbQuery($db,$query);
        if ($result)
            $liveUpdate = $this->_livedbQuery($db,$query);
        // ToDo: need to process SQL result so not to return a SQL object
        if ($liveUpdate)
            return $result;
        else
            return false;
        
    }


    function _livedbQuery ($db,$query)
    {
        $dbHost = "<server>";
        $dbUser = "<u>";
        $dbPass = "<p>";
        mysql_connect($dbHost,$dbUser,$dbPass) or die("connection error4 ... [" . mysql_error()."]");
        mysql_select_db($db) or die("db select error for \"".$db."\"... " . mysql_error());
        $result = mysql_query($query) or die("error occurred within Data class ... ".mysql_error()." running query: " . $query);
    }

}

 

[sleipnir214]

Does that class actually attempt to open a new connection on every query? Although PHP is supposed to return the same connection handle in subsequent calls to myssql_connect() when the function is handed the same parameter values, I'd double check that. In any regard, I disagree strongly with that design -- the connection invocation should either be in the class constructor or in a separate method.

Also, I've had squirrely behavior using mysql_* functions from within a class unless I explicitly carry around the various handles created by mysql_connect(), mysql_query(), etc.

To be honest, I'm still unsure as to what this class is supposed to do. Your example construction of the class:

$Data = new Data($clientDB,$eventID,'db');

passes the constructor values I don't know what are done with.


Want the best answers? Ask the best questions! TANSTAAFL!