SQL Error after change

[mdj]

Hi All,

I had to change one of the fields in one of my tables from username to musername to avoid conflicts when updating.

However,

Now the following piece of script which validates the new user (this part works) and creates a table with the name as their username produces and SQL error.

The error i get is.

You have an error in your SQL syntax near 'grant ( id int(11) DEFAULT '0' NOT NULL auto_increment, first_name varchar' at line 1

The script that creates the database is below.

I cant see that making the change described above causes the error, but maybe im wrong.

$username = $HTTP_POST_VARS['username'];
$code = $HTTP_POST_VARS['code'];

$updateSQL = ("UPDATE customers SET activated='1' WHERE musername='$username' AND randc='$code'");

mysql_select_db($database_smslist, $smslist);
$sql = mysql_query($updateSQL, $smslist) or die(mysql_error());

$sql_doublecheck = mysql_query("SELECT * FROM customers WHERE musername='$username' AND randc='$code' AND activated='1'");
$doublecheck = mysql_num_rows($sql_doublecheck);

if($doublecheck == 0){
	echo "<strong><font color=red>Your account could not be activated!</font></strong>";
} elseif ($doublecheck > 0) {

$createtb = "CREATE TABLE $username (
   id int(11) DEFAULT '0' NOT NULL auto_increment,
   first_name varchar(50) NOT NULL,
   surname varchar(50) NOT NULL,
   m_prefix varchar(100),
   m_tel varchar(100),
   email varchar(100) NOT NULL,
   signup_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   randc int(11),
   activated tinyint(4) DEFAULT '0' NOT NULL,
   PRIMARY KEY (id)
)
";

mysql_select_db($database_smslist, $smslist);
$sql2 = mysql_query($createtb, $smslist) or die(mysql_error());

echo

The connections are all there, ive just trimmed this down.

All ideas welcome please.

[LoneTraveler]

But I wonder just from scanning through if you actually changed your table field "username" to "musername"? If not, that would certainly seem to me to be a problem. If you did, excuse me for being dense... I just had to wonder, because if you only actually changed it in the script, well, it seems like something I might do...

[xblue]

so, from the error message it seems that you try to create a table called 'grant' which may not work because it is a reserved word.

I'd recommend to rename it, or if you create tables based on user input or something and therefore can't control what the variable is, prepend something to the table name, e.g. table$username or user$username.