Description:
I'm not sure if I selected the correct Category - please change as neccessary.
My website is hosted by iPowerWeb (www.ipowerweb.com)
They supply the following environment:
Operating System Linux
Perl Version 5.008
Perl Path /usr/bin/perl
Installed Perl Modules Click to View
PHP Version 4.3.2
Mysql Version 4.0.15-standard
The crux of the issue is that table columns defined as NOT NULL are inserting NULL values.
The web form and PHP processing page are correctly capturing user entered non-null values from the form. The values make it into the variables in the SQL statement - however they don't make it into the database. A row is created in the table with only the auto-generated id value and a value in one other column. Two columns defined as NOT NULL accept, without complaint, NULL values.
I don't know if this is repeatable in another environment so I'll supply all code used but if you can't recreate I'll be happy to let you into my database the see what is happening. directly... I have experienced this issue in th epast - but it was a random incident and until now I could never recreate it.
All related code and SQL is below - PLEASE contact me for any additional info you need as this is a serious problem for me and is holding up a project.
How to repeat:
HTML FORM CODE: (Note: I removed unecessary code to make it easier for you...)
_______________________________
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>User Registeration</TITLE>
<script language="JavaScript" type="text/javascript" src="jsfile.js"></script>
</HEAD>
<BODY>
<FORM name="userRegForm" action="CertRegExe.php" method="post" onsubmit="return validateFields(un, pw, pwcf, Fname, Lname)">
<TABLE CLASS="contact" cellSpacing=0 cellPadding=0 border=0 align="left" width=550>
<TR bgcolor="#DDDDDD">
<TD COLSPAN=2>
<CENTER><H2 CLASS="normal">SAI Database Experts Registration Form</H2></CENTER>
</TD>
</TR>
<TR>
<TD CLASS="label"><SPAN CLASS="red">*</SPAN> User Name:</TD>
<TD><INPUT TYPE="text" NAME="un" MAXLENGTH="15"></TD>
</TR>
<TR>
<TD CLASS="label"><SPAN CLASS="red">*</SPAN> Password:</TD>
<TD><INPUT TYPE="password" NAME="pw" MAXLENGTH="8"></TD>
</TR>
<TR>
<TD CLASS="label"><SPAN CLASS="red">*</SPAN> Confirm Password:</TD>
<TD><INPUT TYPE="password" NAME="pwcf" MAXLENGTH="8"></TD>
</TR>
<BR><BR>
<TR>
<TD CLASS="label"><SPAN CLASS="red">*</SPAN> First Name:</TD>
<TD><INPUT TYPE="text" NAME="Fname" MAXLENGTH="20"></TD>
</TR>
<TR>
<TD CLASS="label"><SPAN CLASS="red">*</SPAN> Last Name:</TD>
<TD><INPUT TYPE="text" NAME="Lname" MAXLENGTH="20"></TD>
</TR>
<TR>
<TD CLASS="label"><SPAN CLASS="red">*</SPAN> Email Address:</TD>
<TD><INPUT TYPE="text" SIZE="40" NAME="EmailAddress"></TD>
</TR>
<TR>
<TD CLASS="label"><SPAN CLASS="red">*</SPAN> Home Phone:</TD>
<TD><INPUT TYPE="text" NAME="HomePhone" MAXLENGTH="20"></TD>
</TR>
<TR>
<TD CLASS="label">Work Phone:</TD>
<TD><INPUT TYPE="text" NAME="WorkPhone" MAXLENGTH="20"></TD>
</TR>
<TR>
<TD CLASS="label"> Cell Phone:</TD>
<TD><INPUT TYPE="text" NAME="CellPhone" MAXLENGTH="20"></TD>
</TR>
<BR><BR>
<TR align="right">
<TD width=550>
<SCRIPT LANGUAGE="JavaScript" TYPE="text/javascript">
function disableButton(obj) {
obj.disabled = true;
}
</SCRIPT>
<INPUT type="submit" value="Sign In >" name="submit">
</FORM>
</TD>
</TR>
</TABLE>
<BR>
</BODY></HTML>
_______________________________
PHP processing page code:
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<HTML>
<HEAD>
<TITLE>SAI Database Experts Add Certification Registration to Database</TITLE>
</HEAD>
<BODY bgcolor=white lang=EN-US>
<?php
include("RegFc.php");
include("RegVar.php");
$un = $_POST['un'];
$pw = $_POST['pw'];
$pwcf = $_POST['pwcf'];
$Fname = $_POST['Fname'];
$Lname = $_POST['Lname'];
/* the following code is to print the values in the variables passed from the form */
echo $un;
echo "<BR>";
echo $pw;
echo "<BR>";
echo $Fname;
echo "<BR>";
echo $Lname;
echo "<BR>";
echo $Company;
echo "<BR>";
echo $HomeAddrs1;
echo "<BR>";
echo $HomeAddrs2;
echo "<BR>";
echo $HomeCountry;
echo "<BR>";
echo $State;
echo "<BR>";
echo $HomeCity;
echo "<BR>";
echo $HomeZipcode;
echo "<BR>";
echo $Birthdate;
echo "<BR>";
echo $Gender;
echo "<BR>";
echo $ResumeText;
echo "<BR>";
echo $Referrer;
echo "<BR>";
echo $EmailAddress;
echo "<BR>";
echo $HomePhone;
echo "<BR>";
echo $WorkPhone;
echo "<BR>";
echo $CellPhone;
echo "<BR>";
echo "<BR>";
if(($un=="")||($pw=="")){
printf ("<FONT SIZE=4><B>SAI Database Experts</B></FONT><BR><BR>");
printf ("<FONT SIZE=2 Color=red><B>Invalid Entry: Please be sure you entered a password & username</B></FONT><BR><BR>");
printf ("<BR><BR><a href='userReg.htm'>Back</a>");
exit;
}
if($pwcf !== $pw){
printf ("<FONT SIZE=4><B>SAI Database Experts</B></FONT><BR><BR>");
printf ("<FONT SIZE=2 Color=red><B>Invalid Entry: Your password confirmation failed. Please try again.</B></FONT><BR><BR>");
printf ("<BR><BR><a href='userReg.htm'>Back</a>");
exit;
}
if (empty($Fname))
{
printf ("<FONT SIZE=4><B>SAI Database Experts</B></FONT><BR><BR>");
printf ("<FONT SIZE=2 Color=red><B>Registration Error</B></FONT><BR><BR>");
printf ("You must enter a first name. Please use your browser's BACK button to return to the registration form and enter your first name.<BR><BR><BR><BR>");
exit;
}
if (empty($Lname))
{
printf ("<FONT SIZE=4><B>SAI Database Experts</B></FONT><BR><BR>");
printf ("<FONT SIZE=2 Color=red><B>Registration Error</B></FONT><BR><BR>");
printf ("You must enter a last name. Please use your browser's BACK button to return to the registration form and enter your last name.<BR><BR>");
exit;
}
function funcCheckEmail($sEmailAddress)
{
// Regex of valid characters
$sChars = "^[A-Za-z0-9\._-]+@([A-Za-z][A-Za-z0-9-]{1,62})(\.[A-Za-z][A-Za-z0-9-]{1,62})+$";
// Check to make sure it is valid
$bIsValid = true;
if(!ereg("$sChars",$sEmailAddress))
{
$bIsValid = false;
}
return $bIsValid;
}
if (empty($EmailAddress))
{
printf ("<FONT SIZE=4><B>SAI Database Experts</B></FONT><BR><BR>");
printf ("<FONT SIZE=2 Color=red><B>Registration Error</B></FONT><BR><BR>");
printf ("You must enter an email address. Please use your browser's BACK button to return to the registration form and enter an email address.<BR><BR>");
exit;
}
if (!funcCheckEmail($EmailAddress))
{
printf ("<FONT SIZE=4><B>SAI Database Experts</B></FONT><BR><BR>");
printf ("<FONT SIZE=2 Color=red><B>Registration Error</B></FONT><BR><BR>");
printf ("You did not enter a valid email address. Please use your browser's BACK button to return to the registration form and enter a valid email address.<BR><BR>");
exit;
}
if (empty($HomePhone) && empty($WorkPhone) && empty($CellPhone))
{
printf ("<FONT SIZE=4><B>SAI Database Experts</B></FONT><BR><BR>");
printf ("<FONT SIZE=2 Color=red><B>Registration Error</B></FONT><BR><BR>");
printf ("You must provide at least one phone number. Please use your browser's BACK button to return to the registration form and enter a phone number.<BR><BR>");
exit;
}
$dateTime = date("Y-m-d G:i:s"); //added on 4/2/04
//To record date
$year = date("Y");
$mo = date("m");
$day = date("d");
$hr = date("H"); //Added on 11/4/03
$min = date("i"); //Added on 11/4/03
$sec = date("s"); //Added on 11/4/03
//$dbDate = "$year-$mo-$day"; //Used before
$dbDate = "$year-$mo-$day $hr:$min:$sec"; //date_created field must be DATETIME
dbConnect();
$table = $perTable;
$sVal = "Student";
$varArray1 = array($sVal, $Fname, $Lname, $Company, $HomeAddrs1, $HomeAddrs2, $HomeCountry, $State, $HomeCity, $HomeZipcode, $Birthdate, $Gender, $Comments, $ResumeText, $Referrer, $dateTime);
$dbArray1 = array("person_type", "f_name", "l_name", "company", "home_addrs_1", "home_addrs_2", "country", "home_state", "home_city", "home_zipcode", "birthdate", "gender", "comments", "referrer", "date_created");
/* the following code is to print the values passed to the SQL statement */
var_dump($varArray1);
echo "<BR>";
echo "<BR>";
InsertRowInTable($table, $varArray, $dbArray);
$PersonID = mysql_insert_id();
$table = $emailTable;
$varArray2 = array($PersonID, $EmailAddress, $dateTime);
$dbArray2 = array("person_id", "email_addrs", "date_created");
InsertRowInTable($table, $varArray, $dbArray);
if (!empty($HomePhone))
$table = $phoneTable;
$varArray3 = array($PersonID, "Home", $HomePhone, $dateTime);
$dbArray3 = array("person_id", "phone_type", "phone_num", "date_created");
InsertRowInTable($table, $varArray, $dbArray);
if (!empty($WorkPhone))
$table = $phoneTable;
$varArray4 = array($PersonID, "Work", $WorkPhone, $dateTime);
$dbArray4 = array("person_id", "phone_type", "phone_num", "date_created");
InsertRowInTable($table, $varArray, $dbArray);
if (!empty($CellPhone))
$table = $phoneTable;
$varArray5 = array($PersonID, "Cell", $CellPhone, $dateTime);
$dbArray5 = array("person_id", "phone_type", "phone_num", "date_created");
InsertRowInTable($table, $varArray, $dbArray);
$table = $userTable;
$field = "username";
$fieldData = $un;
$more = "pw = '$pw'";
$varArray = array($PersonID, $un, $pw, $dbDate);
$dbArray = array("person_id", "username", "pw", "date_created");
$id = recordAndGetIdByVal($table, $varArray, $dbArray);
echo $id;
echo "<BR>";
echo "<BR>";
?>
</BODY></HTML>
______________________________
Code for the functions that are called:
function InsertRowInTable($table, $varArray, $dbArray){
$dbStr = "";
$varStr = "";
$num = count($dbArray);
$numVal = count($varArray);
if($num!=$numVal){
//die("recordAndGetIdByVal Function Error: Number of Args doesn't match");
errorClose("Database Error - 301"); //changed on 11/11/03
}
for($i=0; $i<$num; $i++){
$field = $dbArray[$i];
$value = $varArray[$i];
if($i==0){
$dbStr = "$field";
$varStr = "'$value'";
}
else{
$dbStr .= ", $field";
$varStr .= ", '$value'";
}
}
$dbQuery = "INSERT INTO $table ($dbStr)";
$dbQuery .= " VALUES ($varStr)";
mysql_query($dbQuery) or errorClose("Database Error - 302"); //changed on 11/11/03
//die("Couldn't insert data into a table!<br>".mysql_error());
}
function recordAndGetIdByVal($table, $varArray, $dbArray){
$dbStr = "";
$varStr = "";
$num = count($dbArray);
$numVal = count($varArray);
if($num!=$numVal){
//die("recordAndGetIdByVal Function Error: Number of Args doesn't match");
errorClose("Database Error - 301"); //changed on 11/11/03
}
for($i=0; $i<$num; $i++){
$field = $dbArray[$i];
$value = $varArray[$i];
if($i==0){
$dbStr = "$field";
$varStr = "'$value'";
}
else{
$dbStr .= ", $field";
$varStr .= ", '$value'";
}
}
$dbQuery = "INSERT INTO $table ($dbStr)";
$dbQuery .= " VALUES ($varStr)";
mysql_query($dbQuery) or errorClose("Database Error - 302"); //changed on 11/11/03
//die("Couldn't insert data into a table!<br>".mysql_error());
$id = mysql_insert_id();
return $id;
}
TABLE DEFS: with affected data exported
# phpMyAdmin MySQL-Dump
# version 2.4.0
# http://www.phpmyadmin.net/ (download page)
#
# Host: localhost
# Generation Time: Jun 06, 2004 at 01:55 AM
# Server version: 4.0.15
# PHP Version: 4.2.3
# Database : `saidata_StudentDB`
# --------------------------------------------------------
#
# Table structure for table `PERSONS`
#
CREATE TABLE PERSONS (
person_id int(10) unsigned NOT NULL auto_increment,
person_type varchar(50) default 'Student',
f_name varchar(25) NOT NULL default '',
l_name varchar(25) NOT NULL default '',
company varchar(100) default NULL,
company_id int(10) default NULL,
home_addrs_1 varchar(150) NOT NULL default '',
home_addrs_2 varchar(100) default NULL,
country varchar(15) NOT NULL default 'USA',
home_state varchar(50) NOT NULL default '',
home_city varchar(100) NOT NULL default '',
home_zipcode varchar(15) NOT NULL default '',
birthdate datetime default NULL,
gender char(1) NOT NULL default '',
comments varchar(255) default NULL,
resume_text longtext,
referrer varchar(100) default NULL,
date_created datetime default NULL,
last_date_edited datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (person_id),
KEY l_name (l_name)
) TYPE=MyISAM COMMENT='collect data about persons';
#
# Dumping data for table `PERSONS`
#
Affected rows of data entered through testing
INSERT INTO PERSONS VALUES (118, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (119, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (120, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (121, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (122, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (123, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (124, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (125, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (126, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (127, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (128, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (129, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (130, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (131, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (132, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (133, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
NSERT INTO PERSONS VALUES (136, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (137, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (138, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
Suggested fix:
not sure - this is a pretty serious issue.