LAST_INSERT_ID only returning 0

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
acid coder
Forum Newbie
Posts: 7
Joined: Fri Aug 15, 2008 6:59 am

LAST_INSERT_ID only returning 0

Post 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!
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: LAST_INSERT_ID only returning 0

Post 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?
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: LAST_INSERT_ID only returning 0

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: LAST_INSERT_ID only returning 0

Post by pickle »

Make your life easier & don't run another query - use mysql_insert_id().
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
acid coder
Forum Newbie
Posts: 7
Joined: Fri Aug 15, 2008 6:59 am

Re: LAST_INSERT_ID only returning 0

Post 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
Post Reply