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.
- My old database, named gblcreations/store
- My new database, named secretcity-treasures/store, fresh install (as a check to make sure of the correct code)
- My new database, named secretcity-treasures/store, with designers added.
- 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:
-- 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