MySQL Email Datatype

[ervisboy007]

Hi,

 

$tableSQL = "CREATE TABLE Members
		(
			UserName varchar(15),
			PassWord varchar(15),
			EMail varchar(15),
			JoinDate varchar(15)
		)"

 

$sqlquery = "INSERT INTO Members (UserName, PassWord, EMail, JoinDate)
					VALUES($username, $password, $email, $joindate)";

 

When I use my form to register an account, I get this:

"An error occured: 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, 03-Mar-2010)' at line 2"

 

Here is the site if you want to take a look at:

http://cegames.megabyet.net/Sancum/register.html

 

 

Thanks,

Ervisboy007

 

EDIT:

MySQL version  5.0.87-community-log

PHP version 5.2.11

[mapleleaf]

varchar(15) won't be enough for email

[ervisboy007]

Hi,

 

 

I change it to varchar(45) but I still think that that's not the problem.

 

An error occured: 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, 03-Mar-2010)' at line 2

 

does MySQL have a special datatype for emails? maybe the '@' is causing the problem? could it be that my table is setup wrong?

 

Thanks,

Ervisboy007

[mapleleaf]

No @ is allowed in varchar so not that

echo out your $email variable and see what you get

[ervisboy007]

Hi,

 

this is what I get:

 

username, password, email, joindate

tester
test
test@hotmail.com
04-Mar-2010
An error occured: 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, 04-Mar-2010)' at line 2

[ervisboy007]

SOLVED:

 

$sqlquery = "INSERT INTO Members (UserName, PassWord, EMail, JoinDate)
					VALUES('$username', '$password', '$email', '$joindate')";

 

yes, the problem were the single quotations -- i forget them

 

 

[fenway]

Actually, not solved -- you're storing dates as VARCHAR -- not DATE -- use a proper format.

[ervisboy007]

Hi,

 

thanks for the warning, but now, I have another error

 

Error: 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 'CURDATE(), PRIMARY KEY (MemberID) )' at line 7

 

$tableSQL = "CREATE TABLE Members
		(
		 	MemberID int(11) unsigned NOT NULL auto_increment,
			UserName varchar(30) NOT NULL default '',
			PassWord varchar(32) NOT NULL default '',
			EMail varchar(50),
			JoinDate date NOT NULL DEFAULT CURDATE(),
			PRIMARY KEY (MemberID)
		)";

 

$sqlquery = "INSERT INTO Members (UserName, PassWord, EMail)
	    VALUES('$username', '$password', '$email')";

 

Should I use unsigned for the JoinDate too?

 

Thanks,

Ervisboy007

[xcoderx]

i  guess whenever there is a text field quotation is must :-)

[xcoderx]

yes u have to assign joindate too if im not wrong.

[fenway]

And you can't use an expression as a default.