I want create two mysql tables (register, messages) with one php script.

The first table is created:

mysql_query('CREATE TABLE register (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(25) NOT NULL,
password VARCHAR(15) NOT NULL,
email VARCHAR(50) NOT NULL,
validate VARCHAR(50) NOT NULL,
forgetpwd VARCHAR(50) NOT NULL,
date_entered DATETIME NOT NULL)');


Now i want to create another table called messages.
What need to do ?

Thanks !

2 ways

mysql_query('CREATE TABLE register (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(25) NOT NULL,
password VARCHAR(15) NOT NULL,
email VARCHAR(50) NOT NULL,
validate VARCHAR(50) NOT NULL,
forgetpwd VARCHAR(50) NOT NULL,
date_entered DATETIME NOT NULL)');
echo 'created first table';

mysql_query('CREATE TABLE register (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(25) NOT NULL,
password VARCHAR(15) NOT NULL,
email VARCHAR(50) NOT NULL,
validate VARCHAR(50) NOT NULL,
forgetpwd VARCHAR(50) NOT NULL,
date_entered DATETIME NOT NULL)');
echo 'created secound table';
mysql_close();

2.secound

$var1="CREATE TABLE register (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(25) NOT NULL,
password VARCHAR(15) NOT NULL,
email VARCHAR(50) NOT NULL,
validate VARCHAR(50) NOT NULL,
forgetpwd VARCHAR(50) NOT NULL,
date_entered DATETIME NOT NULL)";

$var2="CREATE TABLE register2 (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(25) NOT NULL,
password VARCHAR(15) NOT NULL,
email VARCHAR(50) NOT NULL,
validate VARCHAR(50) NOT NULL,
forgetpwd VARCHAR(50) NOT NULL,
date_entered DATETIME NOT NULL)";
mysql_query('$var1');
mysql_query('$var2');
echo 'created';
mysql_close();

I want to create this table, but I get the following 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 '(id) ) TYPE=INNODB' at line 2

Any idea why?

I use mySQL 4.x


$create_register = 'CREATE TABLE register (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY (id)
) TYPE=INNODB;';

There's a comma missing after AUTO_INCREMENT, and column "id" doesn't exist. I guess you mean user_id?



CREATE TABLE register (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (user_id)
) TYPE=INNODB;

I have some suggestions to simplify your Code:
You can leave out "NOT NULL", Primary Keys are implicitly NOT NULL.
And do you really need UNSIGNED? (If you increment automatically, starting with 1 (which is the default), there will never be a negative value).

Cheers!