Auto Increment Field and POST data

Report · Jan 09, 2013

First off, thanks to everyone for your help here on this forum....it is very valuable.

My question today is about grabbing an Auto Increment field in POST data.

If someone fills out a form, then clicks on the button to submit the form (and open a new form detailed in the Action), I have some questions about the POST data from the first form:

If the table data I am collecting is StudentData, in the StudentData table is an Auto Increment field StudentID. If I am having the person fill in the name and address fields etc, then click submit, how do I get StudentID on the next form through POST?

It seems to me that the table is not updated until they click the button right? So how can I POST a piece of data that isnt available until AFTER they click that button?

Here is what I am asking:

Page1, Form1 = Data Insert into StudentData table using Dreamweaver's Insert>Data Objects > Insert Record > Insert Record Wizard. Here is the form action code:

<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">

In Page1, I am using a recordset called rsStudentData.

Within the StudentData table is an auto increment field "StudentID". In the query that creates rsStudentData, I have not selected the StudentID field at all.

Page2: How do I grab the POST data for the StudentID field? I have not used in the the Recordset of Page1, so how can I grab the field?

I appreciate any help you can provide!

-CL

Report · Jan 09, 2013

Assuming you are using MySQL, use one of these functions as determined by your connection type.

mysql_insert_id(); ----with mysql connection

lastInsertId() ----with a PDO connection

Report · Jan 09, 2013

Thanks once again bregent....

I am trying to figure out where to put that - should I set it up as a variable on page 1 form 1 like this:

$passid = mysql_insert_id();

Where might it go? It looks like it needs to go in Page 1, right after the INSERT INTO statement right?

If I set it up as above, will $passid be passed/available as POST data?

Report · Jan 10, 2013

>Where might it go? It looks like it needs to go in Page 1,

>right after the INSERT INTO statement right?

Yes, you need to execute it right after the insert has executed.

>If I set it up as above, will $passid be passed/available as POST data?

How are you getting from page1 to page2. A redirect after the insert? There is no posted data on a redirect. You either need to put it in the querystring, or set a session variable.

Report · Jan 11, 2013

THanks again bregent. I decided to use a Session variable, and though it is getting errors I know it is close to working, as I am getting Duplicate Entry errors. I think trying to find the exact location for settting the session variable is my problem right now and given the way DW generates its code, it is kinda hard to figure out the right place. So below is the code from above the template, and the code I am trying to place is:

$_Session['Stud_ID']=mysql_insert_id();

I have placed it directly after the long INSERT INTO and before the GETSQLValue statements below, but I still get the Undefined Variable error on the next page where I have a simple <?php echo $_SESSION['Stud_ID']; ?>. Any suggestions???

Here is the code (from ABOVE the template):

<?php require_once('Connections/NetworkSolVPS.php'); ?>
<?php session_start(); ?>
<?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 StudentData (LastName, FirstName, Address1, Address2, City, `State`, Zip, County, HomePhone, OtherPhone, OtherPhoneType, EmailAddress, Center, AgeGroup, EducationLevel, Gender, FamilyIncomeLevel, Race, YearsInChildCare, SocSecurity, BirthDate, RegistryNumber, Notes, UnpaidBalances, Active, ModifiedBy, ModifiedDate) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['LastName'], "text"),
                       GetSQLValueString($_POST['FirstName'], "text"),
                       GetSQLValueString($_POST['Address1'], "text"),
                       GetSQLValueString($_POST['Address2'], "text"),
                       GetSQLValueString($_POST['City'], "text"),
                       GetSQLValueString($_POST['State'], "text"),
                       GetSQLValueString($_POST['Zip'], "text"),
                       GetSQLValueString($_POST['County'], "text"),
                       GetSQLValueString($_POST['HomePhone'], "text"),
                       GetSQLValueString($_POST['OtherPhone'], "text"),
                       GetSQLValueString($_POST['OtherPhoneType'], "text"),
                       GetSQLValueString($_POST['EmailAddress'], "text"),
                       GetSQLValueString($_POST['Center'], "text"),
                       GetSQLValueString($_POST['AgeGroup'], "text"),
                       GetSQLValueString($_POST['EducationLevel'], "text"),
                       GetSQLValueString($_POST['Gender'], "text"),
                       GetSQLValueString($_POST['FamilyIncomeLevel'], "text"),
                       GetSQLValueString($_POST['Race'], "text"),
                       GetSQLValueString($_POST['YearsInChildCare'], "int"),
                       GetSQLValueString($_POST['SocSecurity'], "text"),
                       GetSQLValueString($_POST['BirthDate'], "date"),
                       GetSQLValueString($_POST['RegistryNumber'], "text"),
                       GetSQLValueString($_POST['Notes'], "text"),
                       GetSQLValueString($_POST['UnpaidBalances'], "int"),
                       GetSQLValueString($_POST['Active'], "int"),
                       GetSQLValueString($_POST['ModifiedBy'], "text"),
                       GetSQLValueString($_POST['ModifiedDate'], "date"));

mysql_select_db($database_NetworkSolVPS, $NetworkSolVPS);
$Result1 = mysql_query($insertSQL, $NetworkSolVPS) or die(mysql_error());

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

mysql_select_db($database_NetworkSolVPS, $NetworkSolVPS);
$query_Recordset1 = "SELECT * FROM StudentData";
$Recordset1 = mysql_query($query_Recordset1, $NetworkSolVPS) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

Report · Jan 11, 2013

>I have placed it directly after the long INSERT INTO

>and before the GETSQLValue statements below

No, the value will not be available until after the insert SQL has executed, which occurs here:

$Result1 = mysql_query($insertSQL, $NetworkSolVPS) or die(mysql_error());

so put it after that line and before

$insertGoTo = "workform.php";

Report · Jan 11, 2013

Thanks again. I have done it just as you have suggested, but when the 2nd page loads, I get an undefined index in the line that calls out the Session variable:

<?php echo $_SESSION['Stud_ID']; ?>

I have started the session, the table and field appears to be setup correctly. I even created a new table and tested with that, same results.

Report · Jan 11, 2013

Use an echo statement on the page that is inserting the ID to make sure that mysql_get_id() is returning a value. Comment out the redirect so you can see the results.

What is the datatype of the Autoincrement field you are using?

Report · Jan 11, 2013

The field is set to int. The field is a 5 digit number. I have seen posts referring to errors with smallint. I have tried changing it to bigint.

I will try the echo now.

Thanks again

Report · Jan 15, 2013

Everything is working now. bregent - you rock. I forgot to start the session in the next form and now everything is working (except see my other post from today)....