HELP, I can’t seem to make this work!
I have a form that inserts information into a database after field validation, and returns error messages via “self processing” while retaining the field value. The error messages appear in the label tag next to the missing field, then submits data after verifying “count($missing)==0”. The validations all work great. However, I have CustomerID set as Primary in the database and associated form field is “AcctNum”. When a duplicate number is entered mysql returns the message “Duplicate entry '1' for key 1”. Can someone tell me how to check for duplicate CustomerID and return another message next to the same label I use for the “missing error”, instead have it return the “duplicate error”, and if no duplicate, continue insert.
(I have left out several of the form fields below because of the script length)
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$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;
}
}
mysql_select_db($database_conn_db248088246, $conn_db248088246);
$query_Recordset1 = "SELECT CustomerID FROM customers";
$Recordset1 = mysql_query($query_Recordset1, $conn_db248088246) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
//list expected fields
$expected = array('AcctNum', 'AcctName', 'CustFName', 'CustLName', 'CustTitle', 'SalesTerr', 'CustAddress_1', 'CustAddress_2', 'CustCity', 'CustState', 'CustZip', 'CustPhone', 'CustWebsite', 'CustEmail', 'CustNotes');
//set required fields
$required = array('AcctNum', 'AcctName', 'CustFName', 'CustLName', 'CustTitle', 'SalesTerr', 'CustAddress_1', 'CustCity', 'CustState', 'CustZip', 'CustPhone', 'CustEmail');
//create empty array for any missing fields
$missing = array();
// process the $_POST variables
foreach ($_POST as $key => $value) {
// assign to temporary variable and strip whitespace if not an array
$temp = is_array($value) ? $value : trim($value);
// if empty and required, add to $missing array
if (empty($temp) && in_array($key, $required)) {
array_push($missing, $key);
}
// otherwise, assign to a variable of the same name as $key
elseif (in_array($key, $expected)) {
${$key} = $temp;
}
}
if ( count($missing)==0 && (isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
header('Location: customer_add_success.php');
$insertSQL = sprintf("INSERT INTO customers (CustomerID, CustomerAccountName, CustomerFirstName, CustomerLastName, CustomerTitle, SalesTerr, CustomerAddress1, CustomerAddress2, CustomerCity, CustomerState, CustomerZip, CustomerPhone, CustomerWebsite, CustomerEmail, CustomerNotes) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
GetSQLValueString($_POST['AcctNum'], "int"),
GetSQLValueString($_POST['AcctName'], "text"),
GetSQLValueString($_POST['CustFName'], "text"),
GetSQLValueString($_POST['CustLName'], "text"),
GetSQLValueString($_POST['CustTitle'], "text"),
GetSQLValueString($_POST['SalesTerr'], "text"),
GetSQLValueString($_POST['CustAddress_1'], "text"),
GetSQLValueString($_POST['CustAddress_2'], "text"),
GetSQLValueString($_POST['CustCity'], "text"),
GetSQLValueString($_POST['CustState'], "text"),
GetSQLValueString($_POST['CustZip'], "text"),
GetSQLValueString($_POST['CustPhone'], "text"),
GetSQLValueString($_POST['CustWebsite'], "text"),
GetSQLValueString($_POST['CustEmail'], "text"),
GetSQLValueString($_POST['CustNotes'], "text"));
mysql_select_db($database_conn_db248088246, $conn_db248088246);
$Result1 = mysql_query($insertSQL, $conn_db248088246) or die(mysql_error());
}
?>
<!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">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Customer Information</title>
<style type="text/css">
<!--
-->
</style>
<style type="text/css">
<!--
.style1 {font-family: Verdana, Arial, Helvetica, sans-serif}
.style2 {font-family: Verdana, Arial, Helvetica, sans-serif; font-weight: bold; }
.style3 {
font-size: 9px;
color: #FF0000;
}
.style5 {
font-size: 10px;
font-weight: bold;
}
-->
</style>
</head>
<body>
<table width="632" height="498" border="0">
<tr>
<td width="615" height="36"><div align="center" class="style2">Add Customer Account <span class="style3">*Required</span></div></td>
<td width="10"> </td>
</tr>
<tr>
<td height="456"><form action="<?php echo $editFormAction; ?>" method="POST" name="form1" id="form1">
<table width="585" align="center">
<tr valign="baseline">
<td width="577" class="style1"><label>
<div align="center" class="style3">
<div align="right">
<?php if ($missing) { // Show if recordset empty ?>
<span class="style5">Please Complete *Required</span>
<?php } // Show if recordset empty ?>
</div>
</div>
<div align="right"><span class="style3">*</span>Sales Territory<span class="style3">
<?php
if (isset($missing) && in_array('SalesTerr', $missing)) { ?>
(Please Enter Territory id)
<?php } ?>
</span>
<input name="SalesTerr" type="text" id="SalesTerr" size="32"
<?php if (isset($missing)) {
echo 'value="'.htmlentities($_POST['SalesTerr']).'"';
} ?>
/>
</div>
</label></td>
</tr>
<tr valign="baseline">
<td class="style1"><label>
<div align="right"><span class="style3">*</span>Account Number<span class="style3">
<?php
if (isset($missing) && in_array('AcctNum', $missing)) { ?>
(Please Enter Account Number)
<?php } ?>
</span>
<input name="AcctNum" type="text" id="AcctNum" size="32"
<?php if (isset($missing)) {
echo 'value="'.htmlentities($_POST['AcctNum']).'"';
} ?>/>
</div>
</label></td>
</tr>
</table>
<p> </p>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>
