• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

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

  • 2
  • 2
1 Solution
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.)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now