get posted AI value to use in next page

Question

johnbo100 0 Newbie Poster

15 Years Ago

Hi All,

I have a page with a PHP insert form that posts relevant info to the SQL database. At the same time the key value "reg_id" is auto incremented by the DB.

How do I get new auto incremented value that has been posted so that I can use it to pass to the next page?

I have posted the code below if this helps

Many thanks

John

PS. I'm using Adobe DW CS4

<?php require_once('../Connections/venture.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO registration (First_Name, middle_name1, middle_name2, Last_Name, DOB, Gender, Ethnic_Background, Religion, `Language`, registration_date, prefered_name, Main_address, Main_post_code, Main_tel_no, Main_mobile_no, Main_work_no, Other_address, Other_post_code, Other_tel_no, Other_mobile_no, Other_work_no, surgery_Id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['First_Name'], "text"),
                       GetSQLValueString($_POST['middle_name1'], "text"),
                       GetSQLValueString($_POST['middle_name2'], "text"),
                       GetSQLValueString($_POST['Last_Name'], "text"),
                       GetSQLValueString($_POST['DOB'], "date"),
                       GetSQLValueString($_POST['Gender'], "text"),
                       GetSQLValueString($_POST['Ethnic_Background'], "text"),
                       GetSQLValueString($_POST['Religion'], "text"),
                       GetSQLValueString($_POST['Language'], "text"),
                       GetSQLValueString($_POST['registration_date'], "date"),
                       GetSQLValueString($_POST['prefered_name'], "text"),
                       GetSQLValueString($_POST['Main_address'], "text"),
                       GetSQLValueString($_POST['Main_post_code'], "text"),
                       GetSQLValueString($_POST['Main_tel_no'], "text"),
                       GetSQLValueString($_POST['Main_mobile_no'], "text"),
                       GetSQLValueString($_POST['Main_work_no'], "text"),
                       GetSQLValueString($_POST['Other_address'], "text"),
                       GetSQLValueString($_POST['Other_post_code'], "text"),
                       GetSQLValueString($_POST['Other_tel_no'], "text"),
                       GetSQLValueString($_POST['Other_mobile_no'], "text"),
                       GetSQLValueString($_POST['Other_work_no'], "text"),
                       GetSQLValueString($_POST['surgery_Id'], "int"));

  mysql_select_db($database_venture, $venture);
  $Result1 = mysql_query($insertSQL, $venture) or die(mysql_error());

  $insertGoTo = "NewRegRecordParent.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

mysql_select_db($database_venture, $venture);
$query_Recordset1 = "SELECT * FROM registration";
$Recordset1 = mysql_query($query_Recordset1, $venture) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

mysql_free_result($Recordset1);
?>

<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
  <table align="center">
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">First_Name:</td>
      <td><input type="text" name="First_Name" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Middle_name1:</td>
      <td><input type="text" name="middle_name1" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Middle_name2:</td>
      <td><input type="text" name="middle_name2" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Last_Name:</td>
      <td><input type="text" name="Last_Name" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">DOB:</td>
      <td><input type="text" name="DOB" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Gender:</td>
      <td><input type="text" name="Gender" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Ethnic_Background:</td>
      <td><input type="text" name="Ethnic_Background" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Religion:</td>
      <td><input type="text" name="Religion" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Language:</td>
      <td><input type="text" name="Language" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Registration_date:</td>
      <td><input type="text" name="registration_date" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Prefered_name:</td>
      <td><input type="text" name="prefered_name" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Main_address:</td>
      <td><input type="text" name="Main_address" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Main_post_code:</td>
      <td><input type="text" name="Main_post_code" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Main_tel_no:</td>
      <td><input type="text" name="Main_tel_no" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Main_mobile_no:</td>
      <td><input type="text" name="Main_mobile_no" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Main_work_no:</td>
      <td><input type="text" name="Main_work_no" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Other_address:</td>
      <td><input type="text" name="Other_address" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Other_post_code:</td>
      <td><input type="text" name="Other_post_code" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Other_tel_no:</td>
      <td><input type="text" name="Other_tel_no" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Other_mobile_no:</td>
      <td><input type="text" name="Other_mobile_no" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Other_work_no:</td>
      <td><input type="text" name="Other_work_no" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Surgery_Id:</td>
      <td><input type="text" name="surgery_Id" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">&nbsp;</td>
      <td><input type="submit" value="Insert record" /></td>
    </tr>
  </table>
  <input type="hidden" name="MM_insert" value="form1" />
</form>
<p>&nbsp;</p>

mysql php

1 Contributor
1 Reply
213 Views
7 Hours Discussion Span
Latest Post 15 Years Ago Latest Post by johnbo100

Dani AI

Generated 7 Months Ago

As discovered, the correct pattern is: perform the INSERT, capture the database API's “last insert id” immediately (before any other DB calls on that connection), then pass that id to the next page (session, query string, or hidden field). The posted code uses the old mysql_* style; modern, safer code should use mysqli or PDO with prepared statements and explicit casting/validation for the id.

Example (mysqli, object style):

$mysqli = new mysqli($host, $user, $pass, $db);
$stmt = $mysqli->prepare("INSERT INTO registration (First_Name, Last_Name, DOB) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $first, $last, $dob);
$stmt->execute();
$newId = $mysqli->insert_id;
session_start();
$_SESSION['reg_id'] = (int)$newId;
header("Location: NewRegRecordParent.php?reg_id={$newId}");
exit;

Example (PDO):

$pdo = new PDO($dsn, $user, $pass, $options);
$stmt = $pdo->prepare("INSERT INTO registration (First_Name, Last_Name, DOB) VALUES (:first, :last, :dob)");
$stmt->execute([':first' => $first, ':last' => $last, ':dob' => $dob]);
$newId = (int)$pdo->lastInsertId();
session_start();
$_SESSION['reg_id'] = $newId;
header("Location: NewRegRecordParent.php?reg_id={$newId}");
exit;

Practical notes and gotchas: call session_start() before any output. Always retrieve the insert id right after the insert on the same connection; running other queries first can change the value. Use prepared statements to avoid SQL injection. If passing the id in the URL, cast and validate it on the next page (for example with (int) or filter_input). For multi-step inserts that must be atomic, wrap them in a transaction so parent and child rows remain consistent. Finally, migrate away from the deprecated mysql extension to mysqli or PDO to ensure compatibility with current PHP versions and improved security.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

johnbo100 0 Newbie Poster · Answer 1 · 2011-06-29T22:43:06+00:00