SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot southernlady's Avatar
    Join Date
    Mar 2005
    Location
    TN
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Error in MySql File, can't find it, help!

    I am in the process of transferring a database from one part of my server to another. It is not a straight transfer due to a modification on the new database. Both databases run Zen Cart, the first one is a little over 6 months old. The second one is almost a fresh install with a specific modification for my designers so only my designers are added, no products.

    As a result I actually have three copies of a database to work with in creating a fourth.

    1) My old database, named gblcreations/store
    2) My new database, named secretcity-treasures/store, fresh install (as a check to make sure of the correct code)
    3) My new database, named secretcity-treasures/store, with designers added.
    4) My new workingsecretcitydatabase that is the mesh of the above three.

    Where I get hung up is in the products section. I have an error in my syntax and I can not find it. I am using the table data from #2 and the dump data, altered from #1.

    It accepted the designer data when I put in the table data from #2 and the dump data from #2.

    Anyway, here is the code:
    Code:
    -- Table structure for table `products`
    --
    
    DROP TABLE IF EXISTS `products`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `products` (
      `products_id` int(11) NOT NULL auto_increment,
      `products_type` int(11) NOT NULL default '1',
      `products_quantity` float NOT NULL default '0',
      `products_model` varchar(32) default NULL,
      `products_image` varchar(64) default NULL,
      `products_price` decimal(15,4) NOT NULL default '0.0000',
      `products_virtual` tinyint(1) NOT NULL default '0',
      `products_date_added` datetime NOT NULL default '0001-01-01 00:00:00',
      `products_last_modified` datetime default NULL,
      `products_date_available` datetime default NULL,
      `products_weight` float NOT NULL default '0',
      `products_status` tinyint(1) NOT NULL default '0',
      `products_tax_class_id` int(11) NOT NULL default '0',
      `manufacturers_id` int(11) default NULL,
      `products_ordered` float NOT NULL default '0',
      `products_quantity_order_min` float NOT NULL default '1',
      `products_quantity_order_units` float NOT NULL default '1',
      `products_priced_by_attribute` tinyint(1) NOT NULL default '0',
      `product_is_free` tinyint(1) NOT NULL default '0',
      `product_is_call` tinyint(1) NOT NULL default '0',
      `products_quantity_mixed` tinyint(1) NOT NULL default '0',
      `product_is_always_free_shipping` tinyint(1) NOT NULL default '0',
      `products_qty_box_status` tinyint(1) NOT NULL default '1',
      `products_quantity_order_max` float NOT NULL default '0',
      `products_sort_order` int(11) NOT NULL default '0',
      `products_discount_type` tinyint(1) NOT NULL default '0',
      `products_discount_type_from` tinyint(1) NOT NULL default '0',
      `products_price_sorter` decimal(15,4) NOT NULL default '0.0000',
      `master_categories_id` int(11) NOT NULL default '0',
      `products_mixed_discount_quantity` tinyint(1) NOT NULL default '1',
      `metatags_title_status` tinyint(1) NOT NULL default '0',
      `metatags_products_name_status` tinyint(1) NOT NULL default '0',
      `metatags_model_status` tinyint(1) NOT NULL default '0',
      `metatags_price_status` tinyint(1) NOT NULL default '0',
      `metatags_title_tagline_status` tinyint(1) NOT NULL default '0',
      `original_cat` int(11) NOT NULL default '0',
      `moved_to_cat` int(11) NOT NULL default '0',
      `product_on_queue` int(11) NOT NULL default '0',
      PRIMARY KEY  (`products_id`),
      KEY `idx_products_date_added_zen` (`products_date_added`),
      KEY `idx_products_status_zen` (`products_status`),
      KEY `idx_products_date_available_zen` (`products_date_available`),
      KEY `idx_products_ordered_zen` (`products_ordered`),
      KEY `idx_products_model_zen` (`products_model`),
      KEY `idx_products_price_sorter_zen` (`products_price_sorter`),
      KEY `idx_master_categories_id_zen` (`master_categories_id`),
      KEY `idx_products_sort_order_zen` (`products_sort_order`),
      KEY `idx_manufacturers_id_zen` (`manufacturers_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Dumping data for table `products`
    --
    
    INSERT INTO `products` (`products_id`, `products_type`, `products_quantity`, `products_model`, `products_image`, `products_price`, `products_virtual`, `products_date_added`, 
    `products_last_modified`, `products_date_available`, `products_weight`, `products_status`, `products_tax_class_id`, `manufacturers_id`, `products_ordered`, 
    `products_quantity_order_min`, `products_quantity_order_units`, `products_priced_by_attribute`, `product_is_free`, `product_is_call`, `products_quantity_mixed`, 
    `product_is_always_free_shipping`, `products_qty_box_status`, `products_quantity_order_max`, `products_sort_order`, `products_discount_type`, `products_discount_type_from`, 
    `products_price_sorter`, `master_categories_id`, `products_mixed_discount_quantity`, `metatags_title_status`, `metatags_products_name_status`, `metatags_model_status`, 
    `metatags_price_status`, `metatags_title_tagline_status`, `original_cat`, `moved_to_cat`, `product_on_queue`) VALUES 
    (1, 1, 100, 'GBL10014', 'GraphicsByLiz_EasterTyme.gif', '0.0000', 0, '2009-07-11 21:10:15', '2010-02-03 00:42:05', NULL, 0, 0, 0, 8, 0, 1, 1, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 
    '0.0000', 8, 1, 0, 0, 0, 0, 0, 0, 0, 0), 
    (2, 1, 100, 'gbp10053', 'graphicsbypokadot_coffeetime.gif', '4.0000', 0, '2009-07-12 00:19:04', '2010-02-03 12:00:55', NULL, 0, 1, 0, 9, 0, 1, 1, 0, 0, 0, 1, 0, 1, 0, 0, 0, 0, 
    '4.0000', , 9, 0, 0, 0, 0, 0, 0, 0, 0), 
    (3, 1, 100, 'ASD10012', 'AScrappersDream_ForeverYoursPREV.jpg', '2.0000', 0, '2009-07-12 02:16:52', '2010-02-03 01:13:05', NULL, 0, 1, 0, 3, 0, 1, 1, 0, 0, 0, 1, 0, 1, 0, 0, 0,
     0, '2.0000', 3, 1, 0, 0, 0, 0, 0, 0, 0, 0),
    Here is the error message that I get tossed back at me:


    I also have one other question, I am in need of a sql editor...right now using a trial version of RazorSQL and while it is in my price range, I haven't figured it out completely. It may do everything I need. I would like one that would allow me to look at a sql file as if it were a text box or excel file and not a sql file, so that I can match up the columns.

    Also I need a way to do find/replace in batch in a better way. The integer that corresponds with `manufacturers_id` may need to be replaced with NULL to make this work.

    Any help would be much appreciated.

    Liz

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sorry, cannot see all of your post because that stupid image is way too wide

    when i maximize my browser window, many of your post's text lines run right off the screen

    i blame the convoluted html/css that drives this forum

    anyhow, getting back to your problem...

    in the second line of VALUES, there are two consecutive commas --

    (2, 1, 100, 'gbp10053', 'graphicsbypokadot_coffeetime.gif', '4.0000', 0, '2009-07-12 00:19:04', '2010-02-03 12:00:55', NULL, 0, 1, 0, 9, 0, 1, 1, 0, 0, 0, 1, 0, 1, 0, 0, 0, 0,
    '4.0000', , 9, 0, 0, 0, 0, 0, 0, 0, 0),

    can you see it? that's your error

    as for your question about the sql editor, i couldn't really read what you said but i recommend you try HeidiSQL, it's awesome
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot southernlady's Avatar
    Join Date
    Mar 2005
    Location
    TN
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now I get this error message
    #1136 - Column count doesn't match value count at row 2
    And yes, I did see the extra comma, THANK YOU!!!

    Liz

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    and what do you suppose "Column count doesn't match value count" means?

    can you find this one yourself?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot southernlady's Avatar
    Join Date
    Mar 2005
    Location
    TN
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, found that one, have a new one and now that I know what to look for I may be able to figure this one out...if not, I'll post the code. Liz

  6. #6
    SitePoint Zealot southernlady's Avatar
    Join Date
    Mar 2005
    Location
    TN
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I managed to get the files sorted. I tried a different method...not by working with the sql file altho working with it was a lesson not lost on me, thanks for the very valuable help.

    My big issue with the sql editor you recommended was that I could not get it connected. There was no way to work offline with files. I suspect the not connecting was my fault...not holding my tongue right or something because I know my partner can do it with no trouble.

    Thanks again for your help.

    Liz


Tags for this Thread

Bookmarks

Posting Permissions

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