Reputation: 11
Many servers now a days have the latest version of MySQL - which is giving an error when running the following piece of code:
$drop_table = mysql_query("DROP TABLE IF EXISTS `$t_ads`");
$sqls[$t_ads] = "CREATE TABLE `$t_ads` (
`adid` int unsigned NOT NULL auto_increment,
`adtitle` varchar(100) NOT NULL default '',
`addesc` longtext NOT NULL,
`area` varchar(50) NOT NULL default '',
`email` varchar(50) NOT NULL default '',
`showemail` enum('0','1','2') NOT NULL default '0',
`password` varchar(50) NOT NULL default '',
`code` varchar(35) NOT NULL default '',
`cityid` smallint unsigned NOT NULL default '0',
`subcatid` smallint unsigned NOT NULL default '0',
`price` DECIMAL( 10, 2 ) NOT NULL default '0',
`othercontactok` enum('0','1') NOT NULL default '0',
`hits` int unsigned NOT NULL default '0',
`ip` varchar(15) NOT NULL default '',
`verified` enum('0','1') NOT NULL default '0',
`abused` int unsigned NOT NULL default '0',
`enabled` enum('0','1') NOT NULL default '0',
`createdon` datetime NOT NULL default '0000-00-00 00:00:00',
`expireson` datetime NOT NULL default '0000-00-00 00:00:00',
`timestamp` timestamp(14) NOT NULL,
`paid` enum('0','1','2') NOT NULL default '2',
PRIMARY KEY (`adid`),
KEY `subcatid` (`subcatid`),
KEY `cityid` (`cityid`),
KEY `verified` (`verified`),
KEY `enabled` (`enabled`),
KEY `paid` (`paid`)
) TYPE=MyISAM;";
Servers with an older version of mysql seem to accept this, but the new version is not accepting it at all and giving an error. I have many errors related to it since the PHP database setup is little bigger. An example of the error is below:
#1064
- 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 '(14) NOT NULL,paid
enum, NOT NULL default '2', PRIMARY KEY (adid
), K' at line 21
Upvotes: 1
Views: 297
Reputation: 16304
The plain SQL part of your code looks ok (just tested it with MySQL 5.6), though I'd generally advise not to use keywords like timestamp
as a column name. The table will be created in MySQL 5.6, but you'll encounter two warnings:
TIMESTAMP(14)
is deprecated and will be removed in
MySQL 6.0. Please use TIMESTAMP
instead TYPE=storage_engine
is deprecated and will be removed in MySQL
6.0. Please use ENGINE=storage_engine
insteadIf this doesn't help, please post the actual MySQL version you're working with.
Upvotes: 1
Reputation: 47482
Change your column name it should not be mysql keyword 'timestamp' and
chnage
`timestamp` timestamp(14) NOT NULL,
to
`<SOME OTHERNAME>` timestamp NOT NULL,
Upvotes: 0