Script will not create table

[pcw]

Hi, I have been messing around with this all day and am having a bit of trouble.

 

I need this script to create a table in a mysql database but it isnt working.

 

// Make a MySQL Connection
$con = mysql_connect("localhost", "tropicsb_paul", "ozzy2004") or die(mysql_error());
mysql_select_db("tropicsb_membermanager", $con) or die(mysql_error());

$sql = "CREATE TABLE mailbox
(
MailNumber bigint(20) DEFAULT None AUTO_INCREMENT,
Time timestamp(14) DEFAULT NULL,
NewMail tinyint(4) DEFAULT NULL,
From VARCHAR(255) DEFAULT NULL,
To VARCHAR(255) DEFAULT NULL,
Subject VARCHAR(255) DEFAULT NULL,
Body TEXT DEFAULT NULL,
PRIMARY KEY (MailNumber)

)";

mysql_query($sql,$con);

mysql_close($con);

 

Can anybody point me in the right direction please?

[Pikachu2000]

You're attempting to use a couple of MySQL reserved words as field names. They need to either be changed to non-reserved words, or enclosed in `backticks` every time they're referenced in a query string.

[pcw]

Hi Thanks for your speedy reply

 

I now have this, but it doesnt work either:

 

$con = mysql_connect("localhost", "tropicsb_paul", "ozzy2004") or die(mysql_error());
mysql_select_db("tropicsb_membermanager", $con) or die(mysql_error());

$sql = "CREATE TABLE mailbox
(
`MailNumber` bigint(20) DEFAULT None AUTO_INCREMENT,
`Time` timestamp(14) DEFAULT NULL,
`NewMail` tinyint(4) DEFAULT NULL,
`From` VARCHAR(255) DEFAULT NULL,
`To` VARCHAR(255) DEFAULT NULL,
`Subject` VARCHAR(255) DEFAULT NULL,
`Body` TEXT DEFAULT NULL,
PRIMARY KEY (MailNumber)

)";

 

I really cant see where I am going wrong 

[Pikachu2000]

There are other syntactical errors in the SQL statement as well. Are you able to create the table with phpMyAdmin (or similar) and then study the generated SQL statement?

[pcw]

Hi, ok, I done as you said and it worked. Now I have the problem of creating multiple tables.

 

For example, the first table in this script is created, but the next table and any after that are not

 

$con = mysql_connect("localhost", "tropicsb_paul", "ozzy2004") or die(mysql_error());
mysql_select_db("tropicsb_membermanager", $con) or die(mysql_error());


// Mailbox MySQL table

$sql = "CREATE TABLE `mailbox` (
`MailNumber` BIGINT( 20 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`time` DATETIME NULL DEFAULT NULL ,
`NewMail` TINYINT( 4 ) NULL DEFAULT NULL ,
`From` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_general_ci NULL DEFAULT NULL ,
`To` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_danish_ci NULL DEFAULT NULL ,
`Subject` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_danish_ci NULL DEFAULT NULL ,
`Body` TEXT CHARACTER SET latin1 COLLATE latin1_danish_ci NULL DEFAULT NULL
)"; 

mysql_query($sql,$con);

// Sentbox MySQL table

$sql1 = "CREATE TABLE `sentbox` (
`MailNumber` BIGINT( 20 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`time` DATETIME NOT NULL DEFAULT NOT NULL ,
`NewMail` TINYINT( 4 ) NULL DEFAULT NULL ,
`From` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_general_ci NULL DEFAULT NULL ,
`To` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_danish_ci NULL DEFAULT NULL ,
`Subject` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_danish_ci NULL DEFAULT NULL ,
`Body` TEXT CHARACTER SET latin1 COLLATE latin1_danish_ci NULL DEFAULT NULL
)"; 

mysql_query($sql1,$con);

mysql_close($con);

 

as you can see, I named the next $sql - $sql1 so it knew it was a differenct query but it didnt work. I have been searching on google for a solution but cannot find one.

 

Help please!

[Pikachu2000]

It looks like you have an extra NOT NULL on the line for the `time` field . . .

[pcw]

Hi,

 

Thanks for your help, I had added too many NOT NULLS, sorted now. 

 

Many Thanks