vb.org Archive - SQL synax problem

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - SQL synax problem (https://vborg.vbsupport.ru/showthread.php?t=239954)

killa seven

04-06-2010 09:55 PM

SQL synax problem

PHP Code:


		
			
$vbulletin->db->query_write("CREATE TABLE IF NOT EXISTS ".TABLE_PREFIX."`dbh_victims` (
`id` INT( 4 ) NOT NULL AUTO_INCREMENT ,
`username` VARCHAR( 45 ) NOT NULL ,
`beat` VARCHAR( 45 ) NOT NULL ,
`cscore` INT( 1 ) NOT NULL DEFAULT '0' ,
`oscore` INT( 1 ) NOT NULL DEFAULT '0'
UNIQUE (
`id`
");

MySQL 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'' at line 7
Error Number : 1064

im thinking that theres an extra comma some where near id

Lynne

04-07-2010 12:03 AM

look at your last three lines.... now tell me where the ending ") is for the first line.

killa seven

04-07-2010 12:29 AM

what do you mean ending? i put an ending parentheses at the end of ") but still no go

Lynne

04-07-2010 12:35 AM

Yeah, so where is the ending paren to the UNIQUE ( that you have?

killa seven

04-07-2010 12:38 AM

oh ok

PHP Code:


		
			
$vbulletin->db->query_write("CREATE TABLE IF NOT EXISTS ".TABLE_PREFIX."`dbhVictims` (
`id` INT( 4 ) NOT NULL AUTO_INCREMENT ,
`username` VARCHAR( 45 ) NOT NULL ,
`beat` VARCHAR( 45 ) NOT NULL ,
`cscore` INT( 1 ) NOT NULL DEFAULT '0' ,
`oscore` INT( 1 ) NOT NULL DEFAULT '0'
UNIQUE (`id`)
");

i put the paren after id no syntax errors but it isn't created in the database

TigerC10

04-07-2010 01:27 AM

You're still missing a closing parenthesis (from right after the dbhVictims), and you may also want to get rid of the ` symbol. In Perl the `operator performs a system command and PHP is supposed to be Perl embedded in HTML (but I'm unclear as to the status of the ` operator in PHP).

Try this instead...

PHP Code:


		
			
$vbulletin->db->query_write(

    "CREATE TABLE IF NOT EXISTS ".TABLE_PREFIX."dbhVictims (

        id       INT( 4 )      NOT NULL AUTO_INCREMENT,

        username VARCHAR( 45 ) NOT NULL,

        beat     VARCHAR( 45 ) NOT NULL,

        cscore   INT( 1 )      NOT NULL DEFAULT 0,

        oscore   INT( 1 )      NOT NULL DEFAULT 0,

        PRIMARY KEY(id)

    )"

);

Doing it this way (with the staggered indents) makes errors more obvious to see in the future.

killa seven

04-07-2010 01:28 AM

yeagh i thought those tick marks looked weird...

edits..

its not going into the database for some reason

TigerC10

04-07-2010 01:33 AM

There's nothing wrong with the backticks in a MySQL command. I know of many .SQL files that use them. Using the ` operator can be confusing in Perl though. It's not required, so why put them in?

killa seven

04-07-2010 01:36 AM

yeagh... its still not going into the database tho

there is actually no syntax errors now its just not going in when i upload the product

TigerC10

04-07-2010 01:46 AM

Well it might have to do with the IF NOT EXISTS that you gave it. If you ran this code with the wrong syntax then it may have created an empty table. Then when you ran it a second time the empty table was there and it didn't issue the create statement again.

Whatever method you're using to view your database, you may be missing the table. Sometimes viewers sort based on the capital letters and the lower case letters. If it's a web-based viewer then you may need to close it down and open it again (browser cache could mess you up).

killa seven

04-07-2010 01:53 AM

its phpmyadmin

i also have workbench but in phpmyadmin right now

TigerC10

04-07-2010 02:30 AM

Well, close down the tab or the browser and then bring it up again after a few minutes (just in case).

killa seven

04-07-2010 05:55 AM

its just not going in.. closed it down and retried a couple times

Marco van Herwaarden

04-09-2010 09:09 AM

Are you upgrading the product, or uninstalling/installing?

killa seven

04-09-2010 11:37 AM

uninstalling/installing

building a poll system for a battle hack i am making

finally got it to work after hours of banging my head on the desk lol

heres the code i got away with... i feel stupid because i don't see the difference haha

PHP Code:


		
			
$vbulletin->db->query_write("CREATE TABLE IF NOT EXISTS ".TABLE_PREFIX."`dbh_victims` (
`id` INT( 10 ) NOT NULL AUTO_INCREMENT ,
`username` VARCHAR( 50 ) NOT NULL ,
`beat` VARCHAR( 50 ) NOT NULL ,
`cscore` INT( 10 ) NOT NULL ,
`oscore` INT( 10 ) NOT NULL,
UNIQUE (`id`)
) ");

All times are GMT. The time now is 08:23 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.02188 seconds Memory Usage 1,747KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (4)bbcode_php_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (15)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: