Forums

How-to: Returning ID when using INSERT

Permalink 1 user found helpful
While working on a project, I had a problem getting the auto-incremented value. My solution is to edit the database.php file (located in: concrete/libraries/database.php) with the following:
public function InsertWithReturn($sql) {
      $this->db->Execute($sql);
      return $this->db->GetOne("SELECT LAST_INSERT_ID()");
   }

I have tested this on MySQL and it works. It returns the last Auto-increment value that has been generated. This can be a problem if a new value is inserted before you run the LAST_INSERT_ID(). This is why I put it in a new database function that will automatically execute it after your insert query.

How to use:
$db = Loader::db();
$r = $db->InsertWithReturn($query);


Return value:
String. The returned value isn't an array. This is because the function uses adodb's GetOne function.

Possible errors:
It is possible that something get's inserted before concrete5 runs 
SELECT LAST_INSERT_ID()


Limitations:
Doesn't work well on pages that use INSERT a lot.

Optimal solution:
Combine the two query's so that they are executed simultaneously.
sebmel
 
jordanlev replied on at Permalink Best Answer Reply
jordanlev
Concrete5 uses the ADODB library for database access, which provides a function for this already:
$this->db-Execute($sql); //Assumes there's an INSERT statement in the SQL.
$new_id = $this->db->Insert_ID();


Seehttp://phplens.com/lens/adodb/docs-adodb.htm#inserted_id... for details
sebmel replied on at Permalink Reply
sebmel
Nice. Didn't even cross my mind to check from adodb... noob mistake :D

But thanks, this makes my life much easier.