Problems inserting data from form.

Slash78

I keep getting my die message ("Couldn't execute query!") I've been tryin to debug for the past 3 days and i'm missing something here. Can someone else catch my problem? By the way, I have register_globals turned off. I'm including 2 php files here. Here's my code for creating the table.......

<?php
//create_tbl.php

$dbhost = 'localhost';
$dbusername = 'root';
$dbuserpassword = '******';
$default_dbname = 'mysql';

$MYSQL_ERRNO = '';
$MYSQL_ERROR = '';

function db_connect() {

global $dbhost, $dbusername, $dbuserpassword, $default_dbname;
global $MYSQL_ERRNO, $MYSQL_ERROR;

$link_id = mysql_connect($dbhost, $dbusername, $dbuserpassword);

if(!link_id)  {

	$MYSQL_ERRNO = 0;
	$MYSQL_ERROR = "Connection failed to the host $dbhost.";
	return 0;
    }

else if(empty($dbname) && !mysql_select_db($default_dbname))  {

	$MYSQL_ERRNO = mysql_errno();
	$MYSQL_ERROR = mysql_error();
	return 0;
}

else return $link_id;

}

function sql_error() {

global $MYSQL_ERRNO, $MYSQL_ERROR;

if(empty($MYSQL_ERROR))  {

	$MYSQL_ERRNO = mysql_errno();
	$MYSQL_ERROR = mysql_error();
    }

return "$MYSQL_ERRNO: $MYSQL_ERROR";

}

$dbname = "db_invi";
$user_tablename = 'user';
$user_table_def = "usernumber MEDIUMINT(10) DEFAULT '0' NOT NULL AUTO_INCREMENT,";
$user_table_def .= "useradmin TINYINT(2) DEFAULT '0' NOT NULL,";
$user_table_def .= "userpassword VARCHAR(20) BINARY NOT NULL,";
$user_table_def .= "userzip MEDIUMINT(10) NOT NULL,";
$user_table_def .= "usercountry VARCHAR(50) DEFAULT 'America' NOT NULL,";
$user_table_def .= "useremail VARCHAR(50) NOT NULL,";
$user_table_def .= "userbday INT(10) DEFAULT '0' NOT NULL,";
$user_table_def .= "registerdate INT(10) DEFAULT '0' NOT NULL,";
$user_table_def .= "lastaccesstime TIMESTAMP(14),";
$user_table_def .= "PRIMARY KEY (usernumber)";

$link_id = db_connect();

if(!link_id) die(sql_error());

if(!mysql_select_db($dbname)) die(sql_error());

if(!mysql_query("CREATE TABLE $user_tablename ($user_table_def)")) die(sql_error());

echo "Successfully created the $user_tablename table.";

And here's my error checking and inserting values into database code.......

<html>
<head></head>
<body>
INVISTATION - REGISTER
 
 

<?php

$HTTP_POST_VARS['date'] = date("Ymd");
$date=$HTTP_POST_VARS['date'];
$fname=$HTTP_POST_VARS['fname'];
$lname=$HTTP_POST_VARS['lname'];
$email=$HTTP_POST_VARS['email'];
$zip=$HTTP_POST_VARS['zip'];
$pass=$HTTP_POST_VARS['pass'];
$pass2=$HTTP_POST_VARS['pass2'];
$month=$HTTP_POST_VARS['month'];
$day=$HTTP_POST_VARS['day'];
$year=$HTTP_POST_VARS['year'];

$warning="";

if (!(eregi("^{([[:alpha:]]|-|')+$",} $fname))) {

$warning[] = "Please enter a valid first name. ";
}

if (!(eregi("^{([[:alpha:]]|-|')+$",} $lname))) {

$warning[] = "Please enter a valid last name. ";
}

if (!(eregi("^{[a-z0-9-]+(.[a-z0-9-]+)*@[a-z0-9-]+(.[a-z0-9-]+)+$",} $email))) {

$warning[] = "Enter a valid email account. ";
}

if (!(eregi("^{[[:digit:]]{5}(-[[:digit:]]{4})?$",} $zip))) {

$warning[] = "Please enter a correct zip code (either xxxxx or xxxxx-xxxx). ";

}

if (!(eregi("^{[[:alnum:]]{6,8}$",} $pass))) {

$warning[] = "Please enter a password between 6-8 characters. ";

}

if (!(eregi("^{[[:alnum:]]{6,8}$",} $pass2))) {

$warning[] = "Please re-enter password. ";

}

if ($month =="--") {

$warning[] = "Please select the month of your birthdate. ";

}

if ($day =="--") {

$warning[] = "Please select the day of your birthdate. ";

}

if (!(eregi("^{[[:digit:]]{4})?$",} $year))) {

$warning[] = "Please enter a valid 4-digit year for your birthdate. ";

}

$password = crypt($pass); // Encrypt the password.

$bday = $year.$month.$day;

$dbhost = "localhost";
$dbusername = "root";
$dbuserpassword = "*****";
$dbname = "db_invi";

$connection = @mysql_connect($dbhost, $dbusername, $dbuserpassword) or die("Couldn't connect.");

$db = @mysql_select_db($dbname) or die("Couldn't select database.");

$result = @("INSERT INTO user(userpassword,userzip,useremail,userbday,registerdate) values ('$password','$zip','$email','$bday','$date')") or die("Couldn't execute query.");

if ($pass!=$pass2) {

$warning[] = "Passwords do not match. ";

}

if (!($warning == "")) {

echo "<form action=\"register2.php\" method =\"post\">";

echo "<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" width=\"75%\">";
while (list($counter) = each($warning)) {
echo "
<tr>
<td align=\"left\" height=\"15\">
$warning[$counter]
</td>
</tr>
";
}

echo "</table> ";

include("register.php");
}

if ($warning == "") {

echo "<h4>Thank you, $fname $lname for registering.  We look forward to serving your needs.</h4> <br>";

include("register.php");

}

?>

</body>
</html>

tomhath

This doesn't look right to me:

$user_table_def = "usernumber MEDIUMINT(10) DEFAULT '0' NOT NULL AUTO_INCREMENT,";

Is it auto_increment, or default 0? 😕

Slash78

Holy DANG!!! LOL. I didn't even notice that. Wow, this may be the issue. I'll work on it and reply back. The only thing that's perplexing is that i didnt get an error creating the table. Hmmm.....

Slash78

This SUCKS. That still didn't fix the problem. I don't know what gives. This is so frustrating.

tomhath

Okay. Just keep peeling layers off the onion.

First, echo the query before trying to execute it

Second, echo the error message mysql gives you from the call to mysql_error;

$query = "INSERT INTO user(userpassword,userzip,useremail,userbday,registerdate) values ('$password','$zip','$email','$bday','$date')";

echo $query;

$result = @mysql_query($query) or die("Couldn't execute query." . mysql_error() );

I'd also suggest you avoid using integer types for birthdate and zipcode because you'll have other problems with leading zeros later on (what date is 2003111? January 11th or Nov 1st?)

Slash78

Ok, i updated my code a little bit. I'll paste it after these few inquiries. The error message I received was this....

Error Message

INSERT INTO user(userpassword,userzip,useremail,userbday,registerdate) VALUES ('$1$tI/.yR/.$aM2LumowkJ06M2IhOtZsk.','33566','punjabee78@hotmail.com','19780919',now())Couldn't execute query.You have an error in your SQL syntax near 'user(userpassword,userzip,useremail,userbday,registerdate) VALUES ('$1$tI/.yR/.$' at line 1

***************************************88

Something about that password value really looks odd to me, I used the crypt function. Ok, here's my updated code.....
By the way, you can see what exactly I'm trying to do, if you go to www.invistation.com/register.php . The whole problem starts there. LOL. I really appreciate you taking your time out to help me as much as you can.

<html>
<head></head>
<body>
INVISTATION - REGISTER
 
 

<?php

$fname=$HTTP_POST_VARS['fname'];
$lname=$HTTP_POST_VARS['lname'];
$email=$HTTP_POST_VARS['email'];
$zip=$HTTP_POST_VARS['zip'];
$pass=$HTTP_POST_VARS['pass'];
$pass2=$HTTP_POST_VARS['pass2'];
$month=$HTTP_POST_VARS['month'];
$day=$HTTP_POST_VARS['day'];
$year=$HTTP_POST_VARS['year'];

$warning="";

if (!(eregi("^{([[:alpha:]]|-|')+$",} $fname))) {

$warning[] = "Please enter a valid first name. ";
}

if (!(eregi("^{([[:alpha:]]|-|')+$",} $lname))) {

$warning[] = "Please enter a valid last name. ";
}

if (!(eregi("^{[a-z0-9-]+(.[a-z0-9-]+)*@[a-z0-9-]+(.[a-z0-9-]+)+$",} $email))) {

  $warning[] = "Enter a valid email account.<br> ";

}

if (!(eregi("^{[[:digit:]]{5}(-[[:digit:]]{4})?$",} $zip))) {

$warning[] = "Please enter a correct zip code (either xxxxx or xxxxx-xxxx). ";

}

if (!(eregi("^{[[:alnum:]]{6,8}$",} $pass))) {

$warning[] = "Please enter a password between 6-8 characters. ";

}

if (!(eregi("^{[[:alnum:]]{6,8}$",} $pass2))) {

$warning[] = "Please re-enter password. ";

}

if ($month =="--") {

$warning[] = "Please select the month of your birthdate. ";

}

if ($day =="--") {

$warning[] = "Please select the day of your birthdate. ";

}

if (!(eregi("^{[[:digit:]]{4})?$",} $year))) {

$warning[] = "Please enter a valid 4-digit year for your birthdate. ";

}

$password = crypt($pass); // Encrypt the password.

$bday = $year.$month.$day;

$dbhost = "localhost";
$dbusername = "root";
$dbuserpassword = "*******";
$dbname = "db_invi";

$connection = mysql_connect($dbhost, $dbusername, $dbuserpassword) or die("Couldn't connect.");

$db = mysql_select_db($dbname) or die("Couldn't select database.");

$sql = "INSERT INTO user(userpassword,userzip,useremail,userbday,registerdate) VALUES ('$password','$zip','$email','$bday',now())";

echo $sql;

$result = @($sql) or die("Couldn't execute query." . mysql_error() );

if (mysql_affected_rows() != 1) { echo "Something went wrong."; }

else { echo "It affected 1 row."; }

mysql_close($connection);

if ($pass!=$pass2) {

$warning[] = "Passwords do not match. ";

}

if (!($warning == "")) {

echo "<form action=\"register2.php\" method =\"post\">";

echo "</table> ";

include("register.php");
}

if ($warning == "") {

echo "<h4>Thank you, $fname $lname for registering.  We look forward to serving your needs.</h4> <br>";

include("register.php");

}

?>

</body>
</html>

Slash78

It took me 5 friggin days to figure out my problem. I was using a reserved word as my table name. Mysql treats a table name of 'user' as a reserved word!!! Arghhhhhh. I used back ticks and now my form is entering values into my database perfectly. Well almost. As you stated, my birthdat field is wacked out. I have to fix that. And i need to lengthen the field by one more digit. I don't know if that makes any sense, but if you have any more input on the date category, i'd appreciate the input. Thanks for your help!

dombey

Use the date datatype for dates and the datetime datatype for times. The queries are faster and the conversions are simpel. Also there are a lot of useful mysql functions that deal with columns of the date datatype.