mysql_insert_id(); not returning value after successful row insert
Per the documentation:
The ID generated for an AUTO_INCREMENT column by the previous query on success, 0 if the previous query does not generate an AUTO_INCREMENT value, or FALSE if no MySQL connection was established.
The documentation states that it can only return 0
if the query last executed does not generate an AUTO_INCREMENT
value, which should mean that your PRIMARY KEY column in notes
is not properly setup with auto_increment
. I would recommend double-checking that the PRIMARY KEY column in notes
is in-fact setup with auto_increment
(never hurts to check again!).
Viewing your sample code, you do call mysql_insert_id()
immediately after insertion, so there shouldn't be any conflicting queries in between to skew the result.
The only thing I see that may cause an issue is that you're passing the MySQL resource to mysql_query()
, but not to mysql_insert_id()
:
if (!mysql_query($note_sql,$con))
...
$note_id = mysql_insert_id();
There may be a conflict in the resources due to this. Try updating your call to:
$note_id = mysql_insert_id($con);
Admin
Updated on June 17, 2022Comments
-
Admin almost 2 years
I swear I have poured and poured over every other similar question on this site and others... but I think I'm just missing something. Hopefully someone can point out a silly mistake that my brain is hiding from me. :)
My script inserts values from a form into a table called "notes"
At that point it creates two entries in a table called "relationships" through a function called newRelationship.
The value of the variable "$note_id" is populated via my mysql_insert_id(); and passed into the above function.
When the code is executed, the entry is successfully added to "notes" and the "id" column is given a proper value with auto_increment.
Next, two entries are added to the "relationship" table (as they should).
HOWEVER, my mysql_insert_id() keeps kicking out a value of "0" instead of the new row id.
For the life of me I cant figure out what I'm doing wrong. I even tried creating a new table from scratch with the same results. The kicker is that I use pretty much identical code in other files of my project with no problems. Anyone see what I'm doing wrong?
the code in question
if ($user->is_loaded()) { if($_POST['project_id']) { $project_id = $_post['project_id']; $long_note = $_POST['long_note']; $created_by = $_POST['created_by']; $note_sql="INSERT INTO notes (`long_note`, `added`, `created_by`) VALUES ('$long_note', '$timenow', '$created_by')"; if (!mysql_query($note_sql,$con)) { die('Error: ' . mysql_error($note_sql)); } else { echo "note created Creating relationship "; $note_id = mysql_insert_id(); echo $note_id; newRelationship($project_id, "project", $note_id, "note"); newRelationship($client_id, "client", $note_id, "note"); echo "note added successfuly"; }
And my function
function newRelationship($parent_id, $parent_type, $child_id, $child_type) { global $sql, $con, $timenow; $relationship_sql="INSERT INTO `relationships` (`parent_id`, `parent_type`, `child_id`, `child_type`) VALUES ('$parent_id', '$parent_type', '$child_id', '$child_type');"; if (!mysql_query($relationship_sql,$con)) { die('Error: ' . mysql_error($relationship_sql)); } else { echo $parent_type." ".$parent_id." realationship with ".$child_type." ".$child_id." successful "; } }
Per @jack's suggestion here is the sql of my notes table
CREATE TABLE `notes` ( `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT, `contact_id` int(10) NOT NULL, `client_id` int(10) NOT NULL, `status` text NOT NULL, `long_note` text NOT NULL, `added` int(11) NOT NULL, `modified` int(11) NOT NULL, `edit_by` int(10) NOT NULL, `short_note` text NOT NULL, `created_by` int(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=295 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC