Problem with Drop / Create table

[scialom]

Hi,

I try to this:
$Newsql = "DROP TABLE IF EXISTS tbl_items";
$Newsql .= " CREATE TABLE tbl_items (";
$Newsql .= " items_id int(11) NOT NULL auto_increment,";
$Newsql .= " item_type smallint(6) NOT NULL default '0',";
$Newsql .= " item_serial varchar(255) NOT NULL default '',";
$Newsql .= " item_man smallint(6) NOT NULL default '0',";
$Newsql .= " item_model varchar(255) NOT NULL default '',";
$Newsql .= " user2item smallint(6) NOT NULL default '0',";
$Newsql .= " comment varchar(255) NOT NULL default '',";
$Newsql .= " status tinyint(4) NOT NULL default '0',";
$Newsql .= " archive_reason varchar(255) NOT NULL default '',";
$Newsql .= " PRIMARY KEY (items_id)";
$Newsql .= " ) TYPE=MyISAM AUTO_INCREMENT=25;";

But I get this 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 'CREATE TABLE tbl_items ( items_id int(11) NOT NULL auto_inc

why?

Assaf

[Ravenous]

I'm not positive on this, but I don't think you can DROP a table and CREATE a table in the same query.

[stolzyboy]

very true..... do two seperate queries

[jamesm87]

If i am not mistaken can you just insert a ; so that it looks like this:

$Newsql = "DROP TABLE IF EXISTS `tbl_items`[B];[/B] "; 

so all together it looks like this:

$Newsql = "DROP TABLE IF EXISTS `tbl_items`;"; 
$Newsql .= " CREATE TABLE `tbl_items` ("; 
$Newsql .= " `items_id` int(11) NOT NULL auto_increment,"; 
$Newsql .= " `item_type` smallint(6) NOT NULL default '0',"; 
$Newsql .= " `item_serial` varchar(255) NOT NULL default '',"; 
$Newsql .= " `item_man` smallint(6) NOT NULL default '0',"; 
$Newsql .= " `item_model` varchar(255) NOT NULL default '',"; 
$Newsql .= " `user2item` smallint(6) NOT NULL default '0',"; 
$Newsql .= " `comment` varchar(255) NOT NULL default '',"; 
$Newsql .= " `status` tinyint(4) NOT NULL default '0',"; 
$Newsql .= " `archive_reason` varchar(255) NOT NULL default '',"; 
$Newsql .= " PRIMARY KEY (`items_id`)"; 
$Newsql .= " ) TYPE=MyISAM AUTO_INCREMENT=25;"; 

I might be wrong but just thought have a ";" means a new SQL query. 🙂

[scialom]

Thanks it is working now...

[jamesm87]

Its ok