mysql_insert_Id returns 0 on live but works on test db

I have code that works in the development environment (mysql 5.0.41) - return the auto incremented Id. But in the live environment it returns a 0. (mysql 5.1.45). The database structure is the same and the code is exactly the same.

How do I resolve this?

if ((isset($_GET["MM_insert"])) && ($_GET["MM_insert"] == "form2")) {
  $insertSQL = sprintf("INSERT INTO tblResource (`Description`,Author,Publisher, Wheretobuy, Link, Mediatype_ID, Comments, ISBN, Superior, Tags) VALUES (%s, %s, %s, %s, %s,%s, %s, %s, %s, %s)",
                       GetSQLValueString($_GET['Description'], "text"),
					   GetSQLValueString($_GET['Author'], "text"),
					   GetSQLValueString($_GET['Publisher'], "text"),
					   GetSQLValueString($_GET['Wheretobuy'], "text"),
					   GetSQLValueString($_GET['Link'], "text"),
					   GetSQLValueString($_GET['MediaType'], "int"),
					   GetSQLValueString($_GET['Comments'], "text"),
					   GetSQLValueString($_GET['ISBN'], "int"),
					   GetSQLValueString($_GET['Superior'], "int"),
					   GetSQLValueString($_GET['Tags'], "text"));

  mysql_select_db($database_connFunda, $connFunda);
  $Result1 = mysql_query($insertSQL, $connFunda) or die(mysql_error());
// creating the URL to call for the next page.
// This first line creates the first part of the URL - i.e. subjectfilters.php

  $insertGoTo = "subjectfilters.php";
  if (isset($_SERVER['QUERY_STRING'])) {
// this next line adds the ? and & to the URL string  

    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
// this next line addes the variable. $_SERVER['querystring'] is a system set variable
    $insertGoTo .= $_SERVER['QUERY_STRING'];

CODE FROM "Subjectfilters.php"
$page_title = "Apply Subject Filters";

echo '<p>&nbsp;</p>';
// echo '<p>&nbsp;</p>';

if ((isset($_GET["MM_insert"])) && ($_GET["MM_insert"] == "form2")) {
  $main = $_GET['main'];
  $description = $_GET['Description'];

// **************************** This is where it picks up the value of the Resource_ID *********************** - ## >
// First pass picks it up from the INSERT on the addresource.php page, second pass picks it from the hidden field on the form
// Also picking up the value of $main again because form has been submitted and GET value from above reset


echo '<p>This is the value of the resource ID from mysql_insert_id:' . $resource_id . '</p>';

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Check the database to see if the primary key is in fact an auto_increment field
Piersvan10Author Commented:
I did. It is auto incrementing in the table
Assuming your database connection resource is named $connFunda, try changing the line to

Piersvan10Author Commented:
The above didn't help. I found a solution. I moved the statement into the same file/page and put it right after the Insert command. Only then did it produce a result. I appended that to the URL for use in the consecutive pages.
I've only just realised that the second file was not included by the first. The mysql_insert_id must be called in the same script execution as the insert statement, so using it across pages won't work. (In fact, if you're using a shared / persistent connection, you must call it immediately after the insert query, otherwise there's a chance that a consecutive thread will issue an insert on the same connection, so the call to mysql_insert_id will return the wrong value.)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.