mysql_insert_id() returns 0

Talk about writing modules and plugins for CMS Made Simple, or about specific core functionality. This board is for PHP programmers that are contributing to CMSMS not for site developers
Locked
nervino
Power Poster
Power Poster
Posts: 448
Joined: Sun Dec 28, 2008 12:15 pm
Location: Roma, Italy

mysql_insert_id() returns 0

Post by nervino »

Hi, I'm trying to use the mysql_insert_id() php function to have the last inserted id after an INSERT STATEMENT, but the function always returns 0 (zero).

- I have a table, "MyTable", with AUTO INCREMENT ID

- I make:
$query = 'INSERT INTO '.cms_db_prefix().'MyTable (a,b) VALUES ('a-value,'b-value')';
$dbr = $db->Execute($query)

hence:
$new_generated_id = mysql_insert_id();


The new record is correctly added to "MyTable" with the new id, but $new_generated_id is always 0.

Any suggestion?

thank you
nervino
Power Poster
Power Poster
Posts: 448
Joined: Sun Dec 28, 2008 12:15 pm
Location: Roma, Italy

Re: mysql_insert_id() returns 0

Post by nervino »

Well, I tried this way and it works (but there is probably a better way...):

just after the execution of the INSERT QUERY:

Code: Select all

$dbr = $db->Execute($query)

		$q = "SELECT LAST_INSERT_ID()";
		$q_r = $db->Execute($q);

    while ($q_r && $rowq_r = $q_r->FetchRow())
      {
		$new_generated_id = ($rowq_r['LAST_INSERT_ID()']);
		}
Jos
Support Guru
Support Guru
Posts: 4017
Joined: Wed Sep 05, 2007 8:03 pm
Location: The Netherlands

Re: mysql_insert_id() returns 0

Post by Jos »

You should do it like this

Code: Select all

$query = "INSERT INTO ".cms_db_prefix()."MyTable (a,b) VALUES (?,?)";
$dbr = $db->Execute($query, array($a-value,$b-value));

$insertid = $db->Insert_ID();
nervino
Power Poster
Power Poster
Posts: 448
Joined: Sun Dec 28, 2008 12:15 pm
Location: Roma, Italy

Re: mysql_insert_id() returns 0

Post by nervino »

Thank you!
Locked

Return to “Developers Discussion”