Using PHP to INSERT INTO table with AUTO INCREMEMENT field.

[phoenixshade]

I'm having a problem with a simple php statement to INSERT INTO an existing database where the first field is an AUTO INCREMENT NOT NULL field. How do I get my script to properly insert records? Right now, it fails.

Here's the create table statement:

CREATE TABLE members (
id int NOT NULL auto_increment,
username varchar(20) NOT NULL default '',
password char(32) binary NOT NULL default '',
email varchar(50) NOT NULL default '',
onlist tinyint(1) NOT NULL default 0,
cookie char(32) binary NOT NULL default '',
PRIMARY KEY (id),
UNIQUE KEY (username)

And here's the addUser function:

function addUser($email,$username,$password,$notify) {
global $con;
$md5pass = md5($password);
$cookie = md5($username . $password);
mysql_select_db('lnusser_chesslive',$con);
$sql = "INSERT INTO members (username,password,email,onlist,cookie) ";
$sql .= "VALUES ($username,$md5pass,$email,$notify,$cookie)";
if (!mysql_query($sql)) {
die('Insert Row Failed!');
}
}

Execution dies with 'Insert Row Failed!' whenever the addUser function is called. I have already made sure there are no NULL values being passed to the function. The only thing I can imagine is causing this is that the auto-increment field is not being populated, which defeats the purpose of the 'auto' if you ask me.

Thanks for your help!

[CFMaBiSmAd]

Change your die(...) statement to get mysql to tell you why it failed, anything else would just be a guess. Try this -

die('Insert Row Failed!' . mysql_error());

[phoenixshade]

OK, I added a semicolon to the end of my sql query statement, and added the mysql_error() to my die. Here's the error result:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@hotmail.com,1,ef2ff17634f5909c775068475bb3efcd)' at line 1
I don't see anything wrong with it. What gives? Does @ mean something to mysql that I don't know about???

Or do I have to do something in php to format $notify as a tinyint(1)? (Its value is already ALWAYS 0 or 1.)

[CFMaBiSmAd]

I am not sure why the syntax error was listed starting at that point, but the problem is that your VALUES(....) need quotes if they are character/string types -
"VALUES ('$username','$md5pass','$email',$notify,'$cookie')";

[phoenixshade]

THANK YOU SO MUCH!!!!!!!

Worked like a charm! I've been pulling out my hair over this for like four hours, trying all kinds of things with backticks and the like. Finally, my log-in script is working!

[CFMaBiSmAd]

I probably could have spent more time examining your code to find the problem, but after reading as many lines of code that I have, I would rather have PHP/mysql... narrow down where to start looking by making use of error checking and error reporting. Hopefully, you have learned to use the mysql_error() function to help you find future query problems and you won't need to wait around in a forum for an answer.

[guelphdad]

now that problem is fixed change your hashing algorithm from MD5 to SHA1, MD5 is broken and no longer considered secure.