MySQL table create. How are these different.

[DeanWhitehouse]

i am creating three tables using PHP

$sql_user = "CREATE TABLE $user(
user_id int(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
user_name varchar(100) NOT NULL,
user_password varchar(100) NOT NULL,
user_email varchar(100) NOT NULL,
user_ip varchar(20) NOT NULL,
user_ban varchar(3) NOT NULL,
ban_reason varchar(100) NOT NULL,
ban_length varchar(25) NOT NULL,
userlevel tinyint(1) unsigned NOT NULL,
random_key varchar(32) NOT NULL,
user_activated varchar(25) NOT NULL
)";


$sql_forumques = "CREATE TABLE $forum_quest(
id int(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
topic varchar(255) NOT NULL default '',
detail longtext NOT NULL,
name varchar(65) NOT NULL default '',
email varchar(65) NOT NULL default '',
datetime varchar(25) NOT NULL default '',
view int(4) NOT NULL default '0',
reply int(4) NOT NULL default '0'
)AUTO_INCREMENT=1";


$sql_forumansw = "CREATE TABLE $forum_answ(
question_id int(4) NOT NULL default '0',
a_id int(4) NOT NULL default '0',
a_name varchar(65) NOT NULL default '',
a_email varchar(65) NOT NULL default '',
a_answer longtext NOT NULL,
a_datetime varchar(25) NOT NULL default '',
KEY a_id (`a_id`)
)";

 

i have a code to check if they already exist, but for the bottom two tables, it always says table created.

this is my checking code

$make_user= mysql_query($sql_user);
$make_forum = mysql_query($sql_forum1);
$make_forum1 = mysql_query($sql_forum2);
$checkifexist_user = mysql_query ("SELECT * FROM '$user' LIMIT 0,1");
$checkifexist_quest = mysql_query ("SELECT * FROM '$forum_quest' LIMIT 0,1");
$checkifexist_answ = mysql_query ("SELECT * FROM '$forum_answ' LIMIT 0,1");

if (!$make_user)
{
echo("'$user' Table already exists.<br>'$user' table could not be created.<br>");
}
else
{
echo("'$user' Table Created.<br>");
}
if (!make_forum)
{
echo("'$forum_quest' Table already exists.<br>'$forum_quest' table could not be created.<br>");
}
else
{
echo("'$forum_quest' Table Created.<br>");
}
if (!make_forum1)
{
echo("'$forum_quest' Table already exists.<br>'$forum_quest' table could not be created.<br>");
}
else
{
echo("'$forum_answ' Table Created.<br>");
}

[DeanWhitehouse]

does anyone have any idea?

[rhodesa]

what are the values of $sql_forum1 and $sql_forum2?

[DeanWhitehouse]

$sql_forum1 = "CREATE TABLE $forum_quest(
id int(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
topic varchar(255) NOT NULL default '',
detail longtext NOT NULL,
name varchar(65) NOT NULL default '',
email varchar(65) NOT NULL default '',
datetime varchar(25) NOT NULL default '',
view int(4) NOT NULL default '0',
reply int(4) NOT NULL default '0'
)AUTO_INCREMENT=1";


$sql_forum2 = "CREATE TABLE $forum_answ(
question_id int(4) NOT NULL default '0',
a_id int(4) NOT NULL default '0',
a_name varchar(65) NOT NULL default '',
a_email varchar(65) NOT NULL default '',
a_answer longtext NOT NULL,
a_datetime varchar(25) NOT NULL default '',
KEY a_id (`a_id`)
)";

soz i updated it a bit, bust still the same problem

[DeanWhitehouse]

Am i to assume no one knows???

[rhodesa]

Everything looks right. I don't know why mysql_query would return true if the table already exists.

[DeanWhitehouse]

ok, thanks , how do i overwrite the tables?? is it something like

IF TABLE table_name EXISTS DELETE

[rhodesa]

Well, if you always want to replace the table, just run a:

DROP TABLE IF EXISTS table_name

before the CREATE TABLE