[Logik22]

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 

[JackOfAllTrades]

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.

[Logik22]

JackOfAllTrades, 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

[JackOfAllTrades]

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.