mysql_insert_id(); not returning value after successful row insert

Question

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

Karleen

I don't see the code that actually calls mysql_insert_id() or newRelationship. Without that, we can't diagnose the problem. – Gambeson

also... new to this site, so still trying to get the hang of the controls here... but it seems like the second half of my first block of code was cut off. $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"; } – Karleen

@JeromeGarrison: you don't use <code>, just indent the blocks and they'll be code automatically. – Gambeson

Two things - I didn't see any call to mysql_insert_id() in your code; and poured should be pored - unless you meant you poured yourself glasses of your favorite beverage while pondering the issue. – Melanson

Consider adding the output of SHOW CREATE TABLE notes; – Mohave

@BurhanKhalid HA! A little bit of both! Whoops – Karleen

@Deepak If there was anything wrong with the connection it would return false. – Mohave

@Deepak I tried this after browsing this site with the same results. – Karleen

@Gambeson Thank you, I fixed the code in the post. – Karleen

@Jack Per your suggestion, I have added the output of SHOW CREATE TABLE notes; to the bottom of my original post. – Karleen

Answer 1

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);

Seldom

I updated my sample code in my original post. It now includes the section that requests mysql_insert_id(). – Karleen

As far as i can see, no other queries are executing between my insert and mysql_insert_id() – Karleen

oh my gosh... that worked! I swear I tried that already! ::banging head on desk:: must of been some other error that all this debugging had me fix. Thank you for your help and patience!! :) – Karleen