Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2005
    Posts
    6

    Question Unanswered: COLLATE error on CREATE TABLE (was "SQL Syntax issues?")

    Hello, I'm trying to run a query in phpMyAdmin to build my database and am getting the following error:

    -- phpMyAdmin SQL Dump
    -- version 2.6.0-pl3
    -- http://www.phpmyadmin.net
    --
    -- Host: localhost
    -- Generation Time: Mar 10, 2005 at 01:23 PM
    -- Server version: 4.1.8
    -- PHP Version: 5.0.3
    --
    -- Database: `ssk_zencart`
    --
    -- --------------------------------------------------------
    --
    -- Table structure for table `admin`
    --
    CREATE TABLE `admin` (

    `admin_id` int( 11 ) NOT NULL AUTO_INCREMENT ,
    `admin_name` varchar( 32 ) COLLATE latin1_general_ci NOT NULL default '',
    `admin_email` varchar( 96 ) COLLATE latin1_general_ci NOT NULL default '',
    `admin_pass` varchar( 40 ) COLLATE latin1_general_ci NOT NULL default '',
    `admin_level` tinyint( 1 ) NOT NULL default '1',
    PRIMARY KEY ( `admin_id` )
    ) ENGINE = MYISAM DEFAULT CHARSET = latin1 COLLATE = latin1_general_ci AUTO_INCREMENT =4


    #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 'collate latin1_general_ci NOT NULL default '',
    `admin_email`


    I built this shopping cart on my local machine, working with XAMPP to get it all set up and now I can't get the database to work on the remote server... Any solutions?

    Thanks in advance.
    Last edited by kitelife; 03-10-05 at 17:38. Reason: layout, ease of reading

  2. #2
    Join Date
    Mar 2005
    Posts
    6
    This might be contributing to the issue...

    The server I'm trying to run this script on is using PHP version 4.3.10, I built the database in 5.0.3 locally... Does this make a difference?

  3. #3
    Join Date
    Oct 2004
    Posts
    6
    "COLLATE latin1_general_ci NOT NULL default"
    I am not sure why are you using "default" here. Default is generally used to set a default value for the column and you are not doing. Remove the default.

  4. #4
    Join Date
    Mar 2005
    Posts
    6
    I removed default from the query, and now I get this:

    #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 ''0',
    entry_gender char(1) NOT NULL '',
    entry_company varc

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    strip all the collate stuff out and see what happens

    the version of php doesn't matter, the version of mysql does
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2005
    Posts
    6
    After dropping all instances of collate(space), I'm still getting this error message:

    CREATE TABLE address_book(

    address_book_id int( 11 ) NOT NULL AUTO_INCREMENT ,
    customers_id int( 11 ) NOT NULL default '0',
    entry_gender char( 1 ) latin1_general_ci NOT NULL default '',
    entry_company varchar( 32 ) latin1_general_ci default NULL ,
    entry_firstname varchar( 32 ) latin1_general_ci NOT NULL default '',
    entry_lastname varchar( 32 ) latin1_general_ci NOT NULL default '',
    entry_street_address varchar( 64 ) latin1_general_ci NOT NULL default '',
    entry_suburb varchar( 32 ) latin1_general_ci default NULL ,
    entry_postcode varchar( 10 ) latin1_general_ci NOT NULL default '',
    entry_city varchar( 32 ) latin1_general_ci NOT NULL default '',
    entry_state varchar( 32 ) latin1_general_ci default NULL ,
    entry_country_id int( 11 ) NOT NULL default '0',
    entry_zone_id int( 11 ) NOT NULL default '0',
    PRIMARY KEY ( address_book_id ) ,
    KEY idx_address_book_customers_id( customers_id )
    ) ENGINE = MYISAM DEFAULT CHARSET = latin1 COLLATE = latin1_general_ci
    MySQL said:

    #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 'latin1_general_ci NOT NULL default '',
    entry_company varchar

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "strip all the collate stuff" was meant to include the actual collation name latin1_general_ci as well
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2005
    Posts
    6
    Based on my understanding thus far, I tried removing the following:

    COLLATE latin1_general_ci
    COLLATE=latin1_general_ci


    And I came up with this:

    #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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=2' at line 17

    I'm not sure where to go from here, and can certainly make the whole export available to you if it helps.

Posting Permissions

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