3 Replies - 4331 Views - Last Post: 01 July 2012 - 01:43 PM Rate Topic: -----

#1 Logik22  Icon User is offline

  • D.I.C Head

Reputation: 8
  • View blog
  • Posts: 153
  • Joined: 12-December 11

#1064 - You have an error in your SQL syntax...at line 1

Posted 01 July 2012 - 11:04 AM

I'm trying to create my first table (mpgdata) within my first db (MPG). I tried looking up the error code but each post the user has a different issue and most times their problem doesn't have any relevant connections (as far as I know) to mine so I can't really apply their solution.


Mysql code:

CREATE TABLE `MPG`.`mpgdata` (

`dbdate` DATE NOT NULL DEFAULT '0',
`dbstation` VARCHAR( 25 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '0',
`dboctane` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '0',
`dbmiles` DOUBLE( 10 ) NOT NULL DEFAULT '0',
`dbgallons` DOUBLE( 10 ) NOT NULL DEFAULT '0',
`dbmpg` DOUBLE( 10 ) NOT NULL DEFAULT '0'
) ENGINE = MYISAM ;




Error:

#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 ') NOT NULL DEFAULT '0', `dbgallons` DOUBLE(10) NOT NULL DEFAULT '0', `dbmpg` DOU' at line 1 


Is This A Good Question/Topic? 0
  • +

Replies To: #1064 - You have an error in your SQL syntax...at line 1

#2 JackOfAllTrades  Icon User is online

  • Saucy!
  • member icon

Reputation: 5951
  • View blog
  • Posts: 23,212
  • Joined: 23-August 08

Re: #1064 - You have an error in your SQL syntax...at line 1

Posted 01 July 2012 - 12:23 PM

I think you want DECIMAL(11,2) rather than DOUBLE. Also, you really need a BIGINT(20) id field for your primary key, which should also be AUTO_INCREMENT.
Was This Post Helpful? 0
  • +
  • -

#3 Logik22  Icon User is offline

  • D.I.C Head

Reputation: 8
  • View blog
  • Posts: 153
  • Joined: 12-December 11

Re: #1064 - You have an error in your SQL syntax...at line 1

Posted 01 July 2012 - 12:34 PM

View PostJackOfAllTrades, on 01 July 2012 - 12:23 PM, said:

I think you want DECIMAL(11,2) rather than DOUBLE. Also, you really need a BIGINT(20) id field for your primary key, which should also be AUTO_INCREMENT.



I made some changes and it works. I'm not sure why and I'm hoping you could give a small explanation as to what it was getting held up on.

What I changed...
-Changed miles, gallons, and MPG from INT to Decimal
-Added a "dbentry" to act as primary key. I was going to use date but I guess that wouldn't work if someone went on a long trip and had to fill up twice the same day
-Did not specify a collation


Working code:
CREATE TABLE `MPG`.`mpgdata` (
`dbentry` BIGINT( 20 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`dbdate` DATE NOT NULL ,
`dbstation` VARCHAR( 25 ) NOT NULL ,
`dboctane` VARCHAR( 8 ) NOT NULL ,
`dbmiles` DECIMAL( 10 ) NOT NULL ,
`dbgallons` DECIMAL( 10 ) NOT NULL ,
`dbmpg` DECIMAL( 10 ) NOT NULL
) ENGINE = MYISAM ;

This post has been edited by Logik22: 01 July 2012 - 12:36 PM

Was This Post Helpful? 0
  • +
  • -

#4 JackOfAllTrades  Icon User is online

  • Saucy!
  • member icon

Reputation: 5951
  • View blog
  • Posts: 23,212
  • Joined: 23-August 08

Re: #1064 - You have an error in your SQL syntax...at line 1

Posted 01 July 2012 - 01:43 PM

According to the MySQL manual, only FLOAT supports a precision argument. But you're better off using DECIMAL as DOUBLE is an approximate value. And id is pretty much the standard column name for a primary key, sometimes prefixed with the table name, like mpgdata_id.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1