Page 1 of 1

LAST_INSERT_ID only returning 0

Posted: Fri Aug 15, 2008 7:15 am
by acid coder
Hi all,

I had a search through the forum for my problem and have checked all the obvious stuff. We recently changed one of our apps from MySQLi to MySQL. We were using mysqli_insert_id to return the last inserted id, but due to the change we moved everything about and the new code only returns 0.

Code: Select all

public function addBlankItem()
        {
            $sql = "INSERT INTO paxProfile () VALUES ()";
            $this->connection->directExecute( $sql );
            $this->lastID = $this->connection->getLastID();
        }
 
public function getLastID()
        {
            $sql = "SELECT LAST_INSERT_ID() AS id";
            $RS = mysql_query($sql, $this->dataConnection);
            $row = mysql_fetch_assoc($RS);
            return $row;
        }
 
Above are the two methods in question. The connection class holds all the information of the connection, and the directExecute method of this class simply runs what ever query it is set using this connection. Right after the query has been executed I call the getLastId method that should return the last inserted id. However this only returns 0.

The tables key field contains an auto increment number, so that part is ok, and no other queries are run after the INSERT so that should be ok. Does any one have any idea why I keep getting 0 returned??

Thanks All!

Re: LAST_INSERT_ID only returning 0

Posted: Fri Aug 15, 2008 8:35 am
by onion2k
Your code seems a little odd to me because in the first method you're using $this->connection and in the second you're using $this->dataConnection ... Can we assume that $this->connection is an abstraction object while $this->dataConnection is a copy of the raw connection? If that's the case then check whether $this->dataConnection is a copy or if it's a reference. If it's a copy then it's essentially a separate connection to the database, and consequently won't be able to access the last insert id (because they're connection dependent).

Why aren't you fetching the last insert id via $this->connection? Perhaps with mysql_insert_id() just as you used mysqli_insert_id() before?

Re: LAST_INSERT_ID only returning 0

Posted: Fri Aug 15, 2008 8:42 am
by Bill H
In order to return the row number itself, you also need

Code: Select all

$row = mysql_fetch_assoc($RS);
return $row['id'];
So that you are returning an integer rather than an an array.

Re: LAST_INSERT_ID only returning 0

Posted: Fri Aug 15, 2008 9:55 am
by pickle
Make your life easier & don't run another query - use mysql_insert_id().

Re: LAST_INSERT_ID only returning 0

Posted: Wed Aug 20, 2008 5:17 am
by acid coder
Cheers All,

Yeah, connection was a copy of dataConnection so nothing returned. Dumped everything from getLastID() and used mysql_insert_id($this->dataConnection); and all fine now. Just a case of over-doing a solution.

Thanks