Results 1 to 14 of 14

Thread: Database help

  1. #1
    Join Date
    Apr 2008
    Posts
    8

    Unanswered: Database help

    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:

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2008
    Posts
    8
    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);

  4. #4
    Join Date
    Apr 2008
    Posts
    8

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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`)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2008
    Posts
    8
    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

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try removing all the comment lines
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2008
    Posts
    8
    Done so, with the following error:

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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2008
    Posts
    8
    Nope, there was not. Added that, now I get this... (going to copy paste instead)

    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?

  11. #11
    Join Date
    Apr 2008
    Posts
    8
    Anyone any more ideas?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Apr 2008
    Posts
    8
    I dont know where/what the manual is, do you happen to have a link?

    I wrote all this from just looking at other databases.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •