Database help

RabR89 · #1 (**permalink**) 04-10-08, 06:53

Alright. First off, I'm not good with databases, so go pretty easy

Also sorry if this is the wrong section, not really sure on the technical terms (yet!)

So what we are doing - Its a project for uni, we are creating a website with an online shop (all hosted through one.com). We wish to record the sales etc on a database. Now where the database will be hosted is through phpMyAdmin. There is already a database in there called "cruisestirling_" (name of the project). This has tables in it that were created automatically when we made 2 PHPBB2 forums. So anyway, we got to work making the code (using notepad). I went to upload to phpMyAdmin what we had done and the first like said permission denied (CREATE DATABASE sales; USE sales) So got rid of that, and instead of making a new database, decided just to use the current database, and just add our tables in with the forum ones. Upon trying to upload that I got a few syntax errors, so I exported the tables that were in already, and altered what I had so it was somewhat more like the ones phpMyAdmin was already using.

So this is what our code looks like so far -

Code:

-- phpMyAdmin SQL Dump
-- version 2.9.2-Debian-1.one.com1
-- http://www.phpmyadmin.net
-- 
-- Host: MySQL Server
-- Generation Time: Apr 10, 2008 at 09:51 AM
-- Server version: 5.0.32
-- PHP Version: 5.2.0-8+etch10
-- 
-- Database: `cruisestirling_`
-- 


USE `cruisestirling_`


-- --------------------------------------------------------

-- 
-- Table structure for table `members`
-- 

CREATE TABLE `members` (	
	`member_number` mediumint (4) unsigned NOT NULL auto_increment,
	`first_name` varchar (20) unsigned NOT NULL,
	`last_name` varchar (20) unsigned NOT NULL,
	`password` varchar(20) unsigned NOT NULL,
	`date_of_birth` DATE (11) unsigned NOT NULL,
	`date_joined` DATE (11) unsigned NOT NULL,
	`email_address` varchar(50) unsigned NOT NULL),
	PRIMARY KEY (`member_number')
	) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;


-- --------------------------------------------------------

-- 
-- Table structure for table `transactions`
-- 
	
CREATE TABLE `transactions` (				
	`member_number` mediumint (4) unsigned NOT NULL,
	`item_number` mediumint (4) unsigned NOT NULL,
	`date_of_trans` DATE (11) unsigned NOT NULL,
	`quantity` INT (5) unsigned NOT NULL,
	`transaction_number` mediumint (255) unsigned NOT NULL auto_increment,		
	`total_cost` DOUBLE (255) unsigned NOT NULL),
	PRIMARY KEY (`transaction_number'),
	KEY (`member_number`),
	KEY (`item_number`)
	) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;


-- --------------------------------------------------------

-- 
-- Table structure for table `items`
-- 

CREATE TABLE `items` (							
	`item_number` mediumint (4) unsigned NOT NULL auto_increment,			
	`item_name` varchar (20) unsigned NOT NULL,
	`item_description` varchar (255) unsigned NOT NULL,
	`unit_price` DOUBLE (255) unsigned NOT NULL),
	PRIMARY KEY (`item_number')
	) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

-- 
-- Dumping data for table `phpbb_2acl_options`
-- 

INSERT INTO `items` VALUES (1, 'Keyring', 'Cruise:Stirling Keyring with logo. Black.', 4.99); 
INSERT INTO `items` VALUES (2, 'Sticker', 'Cruise:Stirling Window Sticker with logo. Black or White.', 7.99); 
INSERT INTO `items` VALUES (3, 'Cap', 'Cruise:Stirling Baseball Cap with logo. Blue.', 9.99); 
INSERT INTO `items` VALUES (4, 'TShirt', 'Cruise:Stirling T-Shirt with logo. Black or Blue in all sizes.', 14.99); 
INSERT INTO `items` VALUES (5, 'Calander', 'Cruise:Stirling Calander with various feature cars - 2009.', 14.99); 

-- --------------------------------------------------------

So when I uploaded that I get the message:

Quote:

MySQL said: Documentation
#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 'CREATE TABLE `members` (
`member_number` mediumint (4) unsigned NOT NULL auto' at line 6

Now Im sure thats not going to be the only problem. Im just pretty stumped as to what to do next, since my knowlege of this sort of stuff is minimal.

Any one have any ideas / suggestions to how I can fix this?

Thanks in advance for any replies.

r937 · #2 (**permalink**) 04-10-08, 07:44

no idea why it's barfing on the CREATE keyword

did you notice that you have single quotes behind your primary keys, all three of them?

e.g. PRIMARY KEY (`member_number')

RabR89 · #3 (**permalink**) 04-10-08, 07:48

Yes, because thats how the current DB I exported from phpMyAdmin did it, so I kept it the same.

Example (taken from a table in the current working DB, guessing its a table to do witha PHPBB forum)

Code:

CREATE TABLE `phpbb_2acl_groups` (
  `group_id` mediumint(8) unsigned NOT NULL default '0',
  `forum_id` mediumint(8) unsigned NOT NULL default '0',
  `auth_option_id` mediumint(8) unsigned NOT NULL default '0',
  `auth_role_id` mediumint(8) unsigned NOT NULL default '0',
  `auth_setting` tinyint(2) NOT NULL default '0',
  KEY `group_id` (`group_id`),
  KEY `auth_opt_id` (`auth_option_id`),
  KEY `auth_role_id` (`auth_role_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- 
-- Dumping data for table `phpbb_2acl_groups`
-- 

INSERT INTO `phpbb_2acl_groups` VALUES (1, 0, 85, 0, 1);
INSERT INTO `phpbb_2acl_groups` VALUES (1, 0, 93, 0, 1);
INSERT INTO `phpbb_2acl_groups` VALUES (1, 0, 110, 0, 1);

RabR89 · #4 (**permalink**) 04-10-08, 07:52

Here is a screenshot:

http://i6.photobucket.com/albums/y23.../Problem-1.jpg

r937 · #5 (**permalink**) 04-10-08, 08:31

okay, quotes and backticks in screenshot are not legible, but i was able to see the PRIMARY KEY clause outside the CREATE TABLE statement, with a big red X beside it

i am willing to bet that phpmyadmin does not use a single quote instead of a backtick

this is what you had --

Code:

PRIMARY KEY (`member_number')

this is what it should be--

Code:

PRIMARY KEY (`member_number`)

RabR89 · #6 (**permalink**) 04-10-08, 09:08

Well spotted

Changed all the PRIMARY KEY (`member_number`)'s but the same error still shows, seems like its hitting another error before it picked up on the ' ` one.

Thanks for the replys btw, really appreciate it

r937 · #7 (**permalink**) 04-10-08, 09:51

try removing all the comment lines

RabR89 · #8 (**permalink**) 04-10-08, 10:36

Done so, with the following error:

http://i6.photobucket.com/albums/y23...0/Problem2.jpg

r937 · #9 (**permalink**) 04-10-08, 11:01

still can't really read the image too well (you should try copy/paste the actual text)

is there a semi-colon between the USE statement and the CREATE TABLE statement?

RabR89 · #10 (**permalink**) 04-10-08, 11:07

Nope, there was not. Added that, now I get this... (going to copy paste instead)

Quote:

Error

SQL query:

CREATE TABLE `members` (
`member_number` mediumint( 4 ) unsigned NOT NULL AUTO_INCREMENT ,
`first_name` varchar( 20 ) unsigned NOT NULL ,
`last_name` varchar( 20 ) unsigned NOT NULL ,
`password` varchar( 20 ) unsigned NOT NULL ,
`date_of_birth` DATE( 11 ) unsigned NOT NULL ,
`date_joined` DATE( 11 ) unsigned NOT NULL ,
`email_address` varchar( 50 ) unsigned NOT NULL
),
PRIMARY KEY ( `member_number` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8 COLLATE = utf8_bin AUTO_INCREMENT =1;

MySQL said: Documentation
#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 'unsigned NOT NULL,
`last_name` varchar (20) unsigned NOT NULL,
`password` va' at line 3

EDIT: I also see an error on its way, where I have stored DATE, you can see from the screenshots that DATE appears in red, and the other variable types are orange. Does Date need to be a text field?

RabR89 · #11 (**permalink**) 04-13-08, 08:23

Anyone any more ideas?

r937 · #12 (**permalink**) 04-13-08, 08:32

your best bet would be to check the documentation

if you did, you would realize that VARCHAR cannot be UNSIGNED (stop and think about it -- when was the last time you saw a VARCHAR with a sign?)

also, DATE(11) is invalid because DATE doesn't take a length

you can be your own best friend, if you read the fine manual