SQL Help :S

[MrCraig]

Ok, i got this error whilst running the following code:



$dump = @file_get_contents("SQL.txt");
mysql_query($dump) or print(mysql_error());
The error produced is:

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 `usr_badge` ( `id` int(9) NOT NULL auto_increment, `user` var' at line 1And the SQL.txt is:



DROP TABLE IF EXISTS `usr_badge`;
CREATE TABLE `usr_badge` (
`id` int(9) NOT NULL auto_increment,
`user` varchar(20) NOT NULL default '',
`reason` varchar(100) NOT NULL default '',
`badge` varchar(100) NOT NULL default '',
`display` char(1) NOT NULL default '1',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;



DROP TABLE IF EXISTS `usr_badge_recent`;
CREATE TABLE `usr_badge_recent` (
`url` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



DROP TABLE IF EXISTS `usr_cms`;
CREATE TABLE `usr_cms` (
`page` varchar(5) NOT NULL default '',
`content` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



INSERT INTO `usr_cms` (`page`, `content`) VALUES
('home', '• Welcome! •\r\n\r\n\r\nWelcome to UserSystem v2 - THE Habbo-Related UserSystem!

\r\n\r\nBefore you turn the system online (which can be done through the AdminCP > System Configuration, it is advised that

you get to know how to use the software.\r\n\r\nYou can find many help topics on the website, <a href="www.habbosoft.com"

target="_blank">HabboSoft.com</a> and learn how to efficiently administrate your panel.\r\n\r\nIt is advisable that you do

not make anyone an administrator without knowing them completely as HabboSoft are in no way responsible for any damage done

to your server or the system caused by someone you have administered. HabboSoft are also not responsible for anything other

than technical issues. It is your responsibility to use your system in a legal manner and not to host any copyrighted

material.\r\n\r\nYour licence for this system can be revoked at any time for any reason. \r\n\r\nThis is NOT free software

and cannot be redistributed. Any attempt to remove the licencing code or the "Powered By HabboSoft" will result in immediate

licence teminating.\r\n\r\nUserSystem v2 was developed by Cj555 of Habbo UK and all layouts were done by C-Storm of Habbo

UK.\r\n\r\nOther than that, Have fun managing your automated habbo usersystem.'),
('cred', '&bull; How to Get Credits &bull;\r\n\r\nYour content goes here.'),
('furni', '• How to Get Furni •\r\n\r\nYour content goes here.'),
('terms', 'The terms and Conditions of Use of this system are as follows:\r\n\r\nYou May:\r\n- Enjoy the System\r\n-

Trade Furni, Credits etc\r\n- Use it to pm your friends\r\n- Play the Games\r\n- Be an active member on the forum\r\n\r\n

You may NOT\r\n- Attempt to hack/exploit any systems\r\n- Flame other users\r\n- Post Spam on the Forum\r\n- Find ways of

giving yourself an un-fair advantage on the games\r\n\r\nTo Site Owners..\r\n\r\nYou are permitted to use this

tool as long as you comply with the terms and conditions you first agreed to during installation. You are not permitted to

remove copyright in any way or claim this work as your own.\r\n\r\nThis page CANNOT be edited or you will be in violation of

our terms of service.\r\n\r\n<em>UserSystem v2 was coded for HabboSoft.com for commercial purposes.<br />Software developed

by CJ555 and C-Storm</em>');



DROP TABLE IF EXISTS `usr_config`;
CREATE TABLE `usr_config` (
`aspect` varchar(50) NOT NULL default '',
`preference` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `usr_config` (`aspect`, `preference`) VALUES
('SITE_NAME', 'YourSite.com'),
('ALLOW_PM', '1'),
('ALLOW_FORUM', '1'),
('ALLOW_GAME', '1'),
('ALLOW_PRESET', '1'),
('ALLOW_REG', '1'),
('BOARD_OFFLINE', '1'),
('BOARD_MESSAGE', '<font size="2" color="#990000">Sorry, An admin has turned the system offline.<br /><br />\r\nPlease check

back later<br />\r\n<em>The Management</em>\r\n</font>'),
('ADM_MSG', 'Welcome to UserSystem v2!'),
('ONLINE_TIME', '500'),
('ENABLE_LOG', ''),
('LOGIN_REDIR', '1'),
('EMAIL_VERT', '1'),
('SYS_EMAIL', '[email protected]');



DROP TABLE IF EXISTS `usr_furni`;
CREATE TABLE `usr_furni` (
`buy` int(20) NOT NULL auto_increment,
`id` varchar(20) NOT NULL default '',
`userid` varchar(9) NOT NULL default '',
PRIMARY KEY (`buy`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;



DROP TABLE IF EXISTS `usr_furnicat`;
CREATE TABLE `usr_furnicat` (
`id` int(9) NOT NULL auto_increment,
`catname` varchar(50) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


DROP TABLE IF EXISTS `usr_furnidb`;
CREATE TABLE `usr_furnidb` (
`id` int(20) NOT NULL auto_increment,
`furni` varchar(100) NOT NULL default '',
`price` varchar(10) NOT NULL default '',
`cat` varchar(9) NOT NULL default '',
`url` varchar(100) NOT NULL default '',
`enable` char(1) NOT NULL default '1',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


DROP TABLE IF EXISTS `usr_game`;
CREATE TABLE `usr_game` (
`id` int(3) NOT NULL auto_increment,
`name` varchar(70) NOT NULL default '',
`url` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;


INSERT INTO `usr_game` (`id`, `name`, `url`) VALUES
(1, 'Higher or Lower', 'game_hl.php'),
(2, 'Roulette', 'game_roul.php'),
(3, 'Lotto', 'game_lotto.php');


DROP TABLE IF EXISTS `usr_game_lotto`;
CREATE TABLE `usr_game_lotto` (
`id` int(5) NOT NULL auto_increment,
`username` varchar(16) NOT NULL default '',
`ticket` varchar(18) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;


DROP TABLE IF EXISTS `usr_game_lotto_adm`;
CREATE TABLE `usr_game_lotto_adm` (
`cost` char(3) NOT NULL default '',
`daten` varchar(30) NOT NULL default '',
`nump` varchar(18) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `usr_level`;
CREATE TABLE `usr_level` (
`name` varchar(20) NOT NULL default '',
`level` int(2) NOT NULL auto_increment,
`allow_html` char(1) NOT NULL default '',
`name_col` varchar(15) NOT NULL default '',
`adm_access` char(1) NOT NULL default '',
`mod_access` char(1) NOT NULL default '',
`forum_start` char(1) NOT NULL default '',
`forum_post` char(1) NOT NULL default '',
`game` char(1) NOT NULL default '',
`allow_pm` char(1) NOT NULL default '',
`name_change` char(1) NOT NULL default '',
PRIMARY KEY (`level`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;


DROP TABLE IF EXISTS `usr_logs`;
CREATE TABLE `usr_logs` (
`id` int(10) NOT NULL auto_increment,
`user` varchar(20) NOT NULL default '',
`ip` varchar(30) NOT NULL default '',
`time` time NOT NULL default '00:00:00',
`date` date NOT NULL default '0000-00-00',
`success` varchar(7) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


DROP TABLE IF EXISTS `usr_online`;
CREATE TABLE `usr_online` (
`ip` varchar(40) NOT NULL default '',
`username` varchar(36) NOT NULL default '',
`time` varchar(20) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `usr_pm`;
CREATE TABLE `usr_pm` (
`id` int(9) NOT NULL auto_increment,
`subject` varchar(20) NOT NULL default '',
`sendto` varchar(20) NOT NULL default '',
`sentfrom` varchar(20) NOT NULL default '',
`privmsg` text NOT NULL,
`msgread` char(1) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


DROP TABLE IF EXISTS `usr_users`;
CREATE TABLE `usr_users` (
`username` varchar(20) NOT NULL default '',
`password` varchar(60) NOT NULL default '',
`level` varchar(3) NOT NULL default '2',
`ban` char(1) NOT NULL default '',
`id` int(9) NOT NULL auto_increment,
`sec` varchar(4) NOT NULL default '',
`word` varchar(40) NOT NULL default '',
`email` varchar(40) NOT NULL default '',
`credits` varchar(10) NOT NULL default '0',
`avator` varchar(100) NOT NULL default '',
`vipen` char(1) NOT NULL default '0',
`sig` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;


DROP TABLE IF EXISTS `usr_vip`;
CREATE TABLE `usr_vip` (
`id` int(9) NOT NULL auto_increment,
`userid` varchar(9) NOT NULL default '',
`vipid` varchar(9) NOT NULL default '',
`joined` varchar(50) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


DROP TABLE IF EXISTS `usr_vipdb`;
CREATE TABLE `usr_vipdb` (
`id` int(9) NOT NULL auto_increment,
`url` varchar(100) NOT NULL default '',
`price` varchar(4) NOT NULL default '',
`name` varchar(30) NOT NULL default '',
`duration` varchar(9) NOT NULL default '',
`vip` char(2) NOT NULL default '',
`enable` varchar(1) NOT NULL default '1',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


any help on whats going wrong? :S

[Calon]

int maximum = 3..

[MrCraig]

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 `usr_badge` ( `id` int(3) NOT NULL auto_increment, `user` var' at line 1

[Calon]

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 `usr_badge` ( `id` int(3) NOT NULL auto_increment, `user` var' at line 1

Have you ensured it's the table key?

Hmm, that's strange, I tried your code in my phpMyAdmin and it worked fine..

[Jackboy]

Did you get SQL from ur db? And also is there any error returned from file get contents

[MrCraig]

Have you ensured it's the table key?

Hmm, that's strange, I tried your code in my phpMyAdmin and it worked fine..

Yeah its primary key


Did you get SQL from ur db? And also is there any error returned from file get contents

Yeah i exported SQL and no error from f_g_c

[Calon]

Yeah its primary key



Yeah i exported SQL and no error from f_g_c
You have a very temperamental MySQL server, try fiddling with the settings?

[MrCraig]

well its going to be an installer, so gah lool. Il try having a look at other installers and see if anythings diff.

EDIT: the other one ive looked at it, the guy thats made has done them all seperately. but i dont see how that makes a difference.

[iUnknown]

Try int(11) rather.

Also, if the MySQL server that it was exported from was more advanced or possibly an older version than the MySQL server you are trying to run it on, they can cause unexpected errors like this.

[Calon]

Try int(11) rather.

Also, if the MySQL server that it was exported from was more advanced or possibly an older version than the MySQL server you are trying to run it on, they can cause unexpected errors like this.
INT (http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html) 4 -2147483648 2147483647
If not key, int must not be bigger than 4.

[Jackboy]

echo $dump

Does it show the sql ?

[iUnknown]

I always use int 11 and it always works.

[MrCraig]

yeah the f_g_c's working.

[iUnknown]

Try:

CREATE TABLE IF NOT EXISTS `usr_badge` (

Instead of:

DROP TABLE IF EXISTS `usr_badge`;
CREATE TABLE `usr_badge` (

etcetera...

[MrCraig]

Thanks guy, got it working now :D xxxx :P

Thanks for all the help guys :)

[Calon]

Try:

CREATE TABLE IF NOT EXISTS `usr_badge` (

Instead of:

DROP TABLE IF EXISTS `usr_badge`;
CREATE TABLE `usr_badge` (

etcetera...
Dump the SQL and try to re-add..