The money is in the eyes

Welcome to TMO

Home
Talk
Rants
Life
Music
Web
Media
Society
Sex
Announce
Games

How do I get a tag ?

Read the FAQ !



email us
TMO Talk Post New Topic  Post A Reply
my profile login | search | faq | forum home

  next oldest topic   next newest topic
» TMO Talk » Web » mySQL gods - I plea to you!

   
Author Topic: mySQL gods - I plea to you!
sabian

 - posted      Profile for sabian           Edit/Delete Post   Reply With Quote 
Right, I have this guy and when I say guy I mean a fucking pain in the ass, who I've been working on a site for +/- 3 years now. He's an absolute nightmare so I moved him from a static site to a Zencart install so he can do his own bloody updating and leave me alone.

Well, he's back. Sigh.

This time he's deleted a category from Zencart but didn't move the items first so there are 'ghost' items in the database that show up in the customer front end as it displays products based on productID, however in the admin panel, these products can not be edited because it depends on the categoryID. Needless to say, I'm not a happy camper to go through 300+ products and cross reference every one with a valid category so I'm wondering if there's a "cheat".

Basically, I like a lovely query that would run through and spit out any products that do not have a valid category assigned to them. For example, here is the schema for 3 products, the first item is correct and editable via the admin panel, but the following two aren't because there master categoryID doesn't exist:
code:
-- Table structure for table `zen_products`
--

CREATE TABLE IF NOT EXISTS `zen_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',
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 AUTO_INCREMENT=1563 ;

--
-- Dumping data for table `zen_products`
--

INSERT INTO `zen_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`) VALUES
(476, 1, 0, '150340', 'Chaise Chair 150.jpg', 218.0000, 0, '2007-01-01 00:00:00', '2008-08-18 11:07:00', NULL, 0, 1, 1, 11, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 218.0000, 291, 1, 0, 0, 0, 0, 0),
(1015, 1, 0, 'A601000', 'Chaise-150-x.jpg', 392.0000, 0, '2007-01-01 00:00:00', '2008-08-18 12:18:25', NULL, 0, 1, 1, 8, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 392.0000, 306, 1, 0, 0, 0, 0, 0),
(1447, 1, 0, '150440', 'Chaise dining chair 150.jpg', 250.0000, 0, '2007-01-01 00:00:00', '2008-08-18 10:33:40', NULL, 0, 1, 1, 11, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 250.0000, 296, 1, 0, 0, 0, 0, 0);

And here is the schema for the category table for your reference:
code:
-- phpMyAdmin SQL Dump
-- version 2.11.6
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Sep 03, 2008 at 01:13 PM
-- Server version: 4.1.22
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `gregk_shop`
--

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

--
-- Table structure for table `zen_categories`
--

CREATE TABLE IF NOT EXISTS `zen_categories` (
`categories_id` int(11) NOT NULL auto_increment,
`categories_image` varchar(64) default NULL,
`parent_id` int(11) NOT NULL default '0',
`sort_order` int(3) default NULL,
`date_added` datetime default NULL,
`last_modified` datetime default NULL,
`categories_status` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`categories_id`),
KEY `idx_parent_id_cat_id_zen` (`parent_id`,`categories_id`),
KEY `idx_status_zen` (`categories_status`),
KEY `idx_sort_order_zen` (`sort_order`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=445 ;

--
-- Dumping data for table `zen_categories`
--

INSERT INTO `zen_categories` (`categories_id`, `categories_image`, `parent_id`, `sort_order`, `date_added`, `last_modified`, `categories_status`) VALUES
(10, NULL, 9999, 0, '2008-02-21 10:13:40', NULL, 1),
(12, NULL, 9999, 0, '2008-02-21 10:13:40', NULL, 1),
(13, NULL, 9999, 0, '2008-02-21 10:13:40', NULL, 1),
(211, NULL, 383, 0, '2008-02-21 10:13:40', '2008-08-07 15:55:14', 1),
(424, NULL, 334, 66, '2008-08-07 16:33:54', '2008-08-08 11:51:16', 1),
(296, NULL, 9999, 0, '2008-02-21 10:13:40', NULL, 1),
(239, NULL, 334, 40, '2008-02-21 10:13:40', '2008-08-07 13:34:14', 1),
(437, NULL, 341, 0, '2008-08-22 15:07:23', NULL, 1),
(429, NULL, 333, 0, '2008-08-08 11:02:06', NULL, 1),
(268, NULL, 379, 0, '2008-02-21 10:13:40', '2008-08-07 15:41:57', 1),
(278, NULL, 250, 0, '2008-02-21 10:13:40', NULL, 1),
(283, NULL, 337, 0, '2008-02-21 10:13:40', '2008-08-07 15:54:01', 1),
(286, NULL, 334, 20, '2008-02-21 10:13:40', '2008-09-02 11:34:43', 1),
(288, NULL, 335, 0, '2008-02-21 10:13:40', '2008-08-08 10:32:08', 1),
(428, NULL, 429, 0, '2008-08-08 11:01:54', '2008-08-08 11:02:39', 1),
(293, NULL, 334, 60, '2008-02-21 10:13:40', '2008-08-07 14:56:23', 1),
(425, NULL, 378, 0, '2008-08-08 10:35:19', NULL, 1),
(438, NULL, 341, 0, '2008-08-22 15:16:53', NULL, 1),
(440, NULL, 341, 0, '2008-08-22 15:17:23', NULL, 1),
(441, NULL, 341, 0, '2008-08-26 10:20:56', NULL, 1),
(442, NULL, 0, 15, '2008-08-26 11:26:27', '2008-09-02 11:23:09', 1),
(431, NULL, 333, 0, '2008-08-08 11:30:20', NULL, 1),
(427, NULL, 378, 0, '2008-08-08 10:36:53', NULL, 1),
(315, NULL, 415, 0, '2008-02-21 10:13:40', '2008-08-07 14:54:03', 1),
(316, NULL, 334, 30, '2008-02-21 10:13:40', '2008-08-07 13:33:59', 1),
(323, NULL, 333, 0, '2008-02-21 10:13:40', '2008-08-08 11:31:35', 1),
(434, NULL, 333, 0, '2008-08-08 11:56:16', NULL, 1),
(333, NULL, 0, 80, '2008-07-28 12:04:56', '2008-08-08 09:51:45', 1),
(334, NULL, 0, 10, '2008-07-28 15:10:09', '2008-08-08 19:01:15', 1),
(335, NULL, 0, 40, '2008-07-28 15:10:24', '2008-08-05 13:17:29', 1),
(381, NULL, 0, 120, '2008-08-05 12:59:38', '2008-08-05 13:15:09', 1),
(337, NULL, 0, 70, '2008-07-28 15:11:18', '2008-08-05 13:17:15', 1),
(339, NULL, 0, 60, '2008-07-28 15:12:06', '2008-08-05 13:17:03', 1),
(384, NULL, 0, 20, '2008-08-05 13:05:21', '2008-08-05 13:18:11', 1),
(341, NULL, 0, 110, '2008-07-28 15:14:12', '2008-08-05 13:17:56', 1),
(342, NULL, 0, 130, '2008-07-28 15:14:29', '2008-08-05 13:17:43', 1),
(343, NULL, 337, 0, '2008-07-28 15:18:51', NULL, 1),
(382, NULL, 0, 100, '2008-08-05 13:00:03', '2008-08-05 13:16:04', 1),
(398, NULL, 384, 0, '2008-08-06 16:39:40', '2008-08-26 11:30:33', 1),
(426, NULL, 378, 0, '2008-08-08 10:36:30', NULL, 1),
(352, NULL, 335, 0, '2008-07-28 15:22:55', NULL, 1),
(353, NULL, 335, 0, '2008-07-28 15:23:10', NULL, 1),
(380, NULL, 0, 140, '2008-08-05 12:59:17', '2008-08-05 13:14:56', 1),
(378, NULL, 0, 50, '2008-07-28 16:29:01', '2008-08-05 13:15:50', 1),
(357, NULL, 339, 0, '2008-07-28 15:24:40', NULL, 1),
(358, NULL, 339, 0, '2008-07-28 15:24:50', NULL, 1),
(359, NULL, 339, 0, '2008-07-28 15:25:10', NULL, 1),
(432, NULL, 429, 0, '2008-08-08 11:40:11', '2008-08-08 11:43:11', 1),
(444, NULL, 383, 0, '2008-08-27 15:52:29', NULL, 1),
(362, NULL, 429, 0, '2008-07-28 15:26:51', '2008-08-21 10:45:54', 1),
(363, NULL, 333, 0, '2008-07-28 15:40:46', NULL, 1),
(364, NULL, 333, 0, '2008-07-28 15:41:14', NULL, 1),
(399, NULL, 384, 0, '2008-08-06 16:40:04', '2008-08-26 11:30:50', 1),
(379, NULL, 0, 30, '2008-07-28 16:30:32', '2008-08-05 13:16:19', 1),
(383, NULL, 0, 90, '2008-08-05 13:04:35', '2008-08-05 13:16:37', 1),
(372, NULL, 339, 0, '2008-07-28 15:44:28', NULL, 1),
(373, NULL, 339, 0, '2008-07-28 15:44:55', NULL, 1),
(374, NULL, 337, 0, '2008-07-28 15:45:29', NULL, 1),
(375, NULL, 337, 0, '2008-07-28 15:45:40', NULL, 1),
(376, NULL, 335, 0, '2008-07-28 15:46:02', NULL, 1),
(377, NULL, 335, 0, '2008-07-28 15:46:16', NULL, 1),
(400, NULL, 398, 0, '2008-08-06 16:40:29', NULL, 1),
(401, NULL, 398, 0, '2008-08-06 16:40:37', NULL, 1),
(402, NULL, 399, 0, '2008-08-06 16:40:56', NULL, 1),
(403, NULL, 399, 0, '2008-08-06 16:41:04', NULL, 1),
(404, NULL, 379, 0, '2008-08-06 16:57:09', NULL, 1),
(423, NULL, 404, 0, '2008-08-07 15:45:31', NULL, 1),
(406, NULL, 379, 0, '2008-08-06 16:57:37', NULL, 1),
(407, NULL, 379, 0, '2008-08-06 16:57:55', NULL, 1),
(408, NULL, 379, 0, '2008-08-06 16:58:15', NULL, 1),
(409, NULL, 404, 0, '2008-08-06 16:58:33', NULL, 1),
(410, NULL, 404, 0, '2008-08-06 16:58:48', NULL, 1),
(412, NULL, 334, 10, '2008-08-07 13:30:08', '2008-08-08 18:58:44', 1),
(414, NULL, 334, 70, '2008-08-07 13:31:09', '2008-08-07 13:34:54', 1),
(415, NULL, 334, 50, '2008-08-07 13:31:27', '2008-08-07 13:34:29', 1),
(417, NULL, 334, 110, '2008-08-07 13:32:04', '2008-08-07 13:36:22', 1),
(418, NULL, 334, 100, '2008-08-07 13:32:18', '2008-08-07 13:36:09', 1),
(439, NULL, 341, 0, '2008-08-22 15:17:02', NULL, 1),
(421, NULL, 334, 80, '2008-08-07 13:35:24', NULL, 1),
(422, NULL, 334, 90, '2008-08-07 13:35:51', NULL, 1);

So, to sum up, I basically want a query that does the following.

Searches product table and references the category table, if categoryID of any product doesn't exist, echo results.

If that makes sense.

Thank you and I shall tithe at the end of the year.

--------------------
Evil isn't what you've done, it's feeling bad about it afterwards... Yield to temptation. It may not pass your way again.

Posts: 3793  |  IP: Logged
ralph

 - posted      Profile for ralph           Edit/Delete Post   Reply With Quote 
code:
SELECT * 
FROM zen_products zp, zen_categories zc
WHERE zp.products_id = "WHATEVER PRODUCT YOU ARE LOOKING FOR"
AND zp.master_categories_id = zc.categories_id

this query will either return the product info (if it's tied to an existing category) or nothing if there is no corresponding category.

Is this what you mean?

Posts: 7436  |  IP: Logged
sabian

 - posted      Profile for sabian           Edit/Delete Post   Reply With Quote 
Yes and no...

I want it to return products that AREN'T tied to an existing category so that I have a list of them that I can edit manually and put them into the right category.

Basically, I just don't want to go through 300+ files and check each one if there's a category or not and want to have it spit out a list of all the ones I have to edit.

--------------------
Evil isn't what you've done, it's feeling bad about it afterwards... Yield to temptation. It may not pass your way again.

Posts: 3793  |  IP: Logged
ralph

 - posted      Profile for ralph           Edit/Delete Post   Reply With Quote 
simple:

code:
SELECT * 
FROM zen_products
WHERE master_categories_id NOT IN
(SELECT categories_id FROM zen_categories)


Posts: 7436  |  IP: Logged
sabian

 - posted      Profile for sabian           Edit/Delete Post   Reply With Quote 
You're a diamond!

One last question.

Here is the output your query made:
code:
-- Table structure for table `zen_categories`
--

CREATE TABLE IF NOT EXISTS `zen_categories` (
`categories_id` int(11) NOT NULL auto_increment,
`categories_image` varchar(64) default NULL,
`parent_id` int(11) NOT NULL default '0',
`sort_order` int(3) default NULL,
`date_added` datetime default NULL,
`last_modified` datetime default NULL,
`categories_status` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`categories_id`),
KEY `idx_parent_id_cat_id_zen` (`parent_id`,`categories_id`),
KEY `idx_status_zen` (`categories_status`),
KEY `idx_sort_order_zen` (`sort_order`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=445 ;

--
-- Dumping data for table `zen_categories`
--

INSERT INTO `zen_categories` (`categories_id`, `categories_image`, `parent_id`, `sort_order`, `date_added`, `last_modified`, `categories_status`) VALUES
(10, NULL, 9999, 0, '2008-02-21 10:13:40', NULL, 1),
(12, NULL, 9999, 0, '2008-02-21 10:13:40', NULL, 1),
(13, NULL, 9999, 0, '2008-02-21 10:13:40', NULL, 1),
(211, NULL, 383, 0, '2008-02-21 10:13:40', '2008-08-07 15:55:14', 1),
(424, NULL, 334, 66, '2008-08-07 16:33:54', '2008-08-08 11:51:16', 1),
(296, NULL, 9999, 0, '2008-02-21 10:13:40', NULL, 1),
(239, NULL, 334, 40, '2008-02-21 10:13:40', '2008-08-07 13:34:14', 1),
(437, NULL, 341, 0, '2008-08-22 15:07:23', NULL, 1),
(429, NULL, 333, 0, '2008-08-08 11:02:06', NULL, 1),
(268, NULL, 379, 0, '2008-02-21 10:13:40', '2008-08-07 15:41:57', 1),
(278, NULL, 250, 0, '2008-02-21 10:13:40', NULL, 1),
(283, NULL, 337, 0, '2008-02-21 10:13:40', '2008-08-07 15:54:01', 1),
(286, NULL, 334, 20, '2008-02-21 10:13:40', '2008-09-02 11:34:43', 1),
(288, NULL, 335, 0, '2008-02-21 10:13:40', '2008-08-08 10:32:08', 1),
(428, NULL, 429, 0, '2008-08-08 11:01:54', '2008-08-08 11:02:39', 1),
(293, NULL, 334, 60, '2008-02-21 10:13:40', '2008-08-07 14:56:23', 1),
(425, NULL, 378, 0, '2008-08-08 10:35:19', NULL, 1),
(438, NULL, 341, 0, '2008-08-22 15:16:53', NULL, 1),
(440, NULL, 341, 0, '2008-08-22 15:17:23', NULL, 1),
(441, NULL, 341, 0, '2008-08-26 10:20:56', NULL, 1),
(442, NULL, 0, 15, '2008-08-26 11:26:27', '2008-09-02 11:23:09', 1),
(431, NULL, 333, 0, '2008-08-08 11:30:20', NULL, 1),
(427, NULL, 378, 0, '2008-08-08 10:36:53', NULL, 1),
(315, NULL, 415, 0, '2008-02-21 10:13:40', '2008-08-07 14:54:03', 1),
(316, NULL, 334, 30, '2008-02-21 10:13:40', '2008-08-07 13:33:59', 1),
(323, NULL, 333, 0, '2008-02-21 10:13:40', '2008-08-08 11:31:35', 1),
(434, NULL, 333, 0, '2008-08-08 11:56:16', NULL, 1),
(333, NULL, 0, 80, '2008-07-28 12:04:56', '2008-08-08 09:51:45', 1),
(334, NULL, 0, 10, '2008-07-28 15:10:09', '2008-08-08 19:01:15', 1),
(335, NULL, 0, 40, '2008-07-28 15:10:24', '2008-08-05 13:17:29', 1),
(381, NULL, 0, 120, '2008-08-05 12:59:38', '2008-08-05 13:15:09', 1),
(337, NULL, 0, 70, '2008-07-28 15:11:18', '2008-08-05 13:17:15', 1),
(339, NULL, 0, 60, '2008-07-28 15:12:06', '2008-08-05 13:17:03', 1),
(384, NULL, 0, 20, '2008-08-05 13:05:21', '2008-08-05 13:18:11', 1),
(341, NULL, 0, 110, '2008-07-28 15:14:12', '2008-08-05 13:17:56', 1),
(342, NULL, 0, 130, '2008-07-28 15:14:29', '2008-08-05 13:17:43', 1),
(343, NULL, 337, 0, '2008-07-28 15:18:51', NULL, 1),
(382, NULL, 0, 100, '2008-08-05 13:00:03', '2008-08-05 13:16:04', 1),
(398, NULL, 384, 0, '2008-08-06 16:39:40', '2008-08-26 11:30:33', 1),
(426, NULL, 378, 0, '2008-08-08 10:36:30', NULL, 1),
(352, NULL, 335, 0, '2008-07-28 15:22:55', NULL, 1),
(353, NULL, 335, 0, '2008-07-28 15:23:10', NULL, 1),
(380, NULL, 0, 140, '2008-08-05 12:59:17', '2008-08-05 13:14:56', 1),
(378, NULL, 0, 50, '2008-07-28 16:29:01', '2008-08-05 13:15:50', 1),
(357, NULL, 339, 0, '2008-07-28 15:24:40', NULL, 1),
(358, NULL, 339, 0, '2008-07-28 15:24:50', NULL, 1),
(359, NULL, 339, 0, '2008-07-28 15:25:10', NULL, 1),
(432, NULL, 429, 0, '2008-08-08 11:40:11', '2008-08-08 11:43:11', 1),
(444, NULL, 383, 0, '2008-08-27 15:52:29', NULL, 1),
(362, NULL, 429, 0, '2008-07-28 15:26:51', '2008-08-21 10:45:54', 1),
(363, NULL, 333, 0, '2008-07-28 15:40:46', NULL, 1),
(364, NULL, 333, 0, '2008-07-28 15:41:14', NULL, 1),
(399, NULL, 384, 0, '2008-08-06 16:40:04', '2008-08-26 11:30:50', 1),
(379, NULL, 0, 30, '2008-07-28 16:30:32', '2008-08-05 13:16:19', 1),
(383, NULL, 0, 90, '2008-08-05 13:04:35', '2008-08-05 13:16:37', 1),
(372, NULL, 339, 0, '2008-07-28 15:44:28', NULL, 1),
(373, NULL, 339, 0, '2008-07-28 15:44:55', NULL, 1),
(374, NULL, 337, 0, '2008-07-28 15:45:29', NULL, 1),
(375, NULL, 337, 0, '2008-07-28 15:45:40', NULL, 1),
(376, NULL, 335, 0, '2008-07-28 15:46:02', NULL, 1),
(377, NULL, 335, 0, '2008-07-28 15:46:16', NULL, 1),
(400, NULL, 398, 0, '2008-08-06 16:40:29', NULL, 1),
(401, NULL, 398, 0, '2008-08-06 16:40:37', NULL, 1),
(402, NULL, 399, 0, '2008-08-06 16:40:56', NULL, 1),
(403, NULL, 399, 0, '2008-08-06 16:41:04', NULL, 1),
(404, NULL, 379, 0, '2008-08-06 16:57:09', NULL, 1),
(423, NULL, 404, 0, '2008-08-07 15:45:31', NULL, 1),
(406, NULL, 379, 0, '2008-08-06 16:57:37', NULL, 1),
(407, NULL, 379, 0, '2008-08-06 16:57:55', NULL, 1),
(408, NULL, 379, 0, '2008-08-06 16:58:15', NULL, 1),
(409, NULL, 404, 0, '2008-08-06 16:58:33', NULL, 1),
(410, NULL, 404, 0, '2008-08-06 16:58:48', NULL, 1),
(412, NULL, 334, 10, '2008-08-07 13:30:08', '2008-08-08 18:58:44', 1),
(414, NULL, 334, 70, '2008-08-07 13:31:09', '2008-08-07 13:34:54', 1),
(415, NULL, 334, 50, '2008-08-07 13:31:27', '2008-08-07 13:34:29', 1),
(417, NULL, 334, 110, '2008-08-07 13:32:04', '2008-08-07 13:36:22', 1),
(418, NULL, 334, 100, '2008-08-07 13:32:18', '2008-08-07 13:36:09', 1),
(439, NULL, 341, 0, '2008-08-22 15:17:02', NULL, 1),
(421, NULL, 334, 80, '2008-08-07 13:35:24', NULL, 1),
(422, NULL, 334, 90, '2008-08-07 13:35:51', NULL, 1);

How do I change all the categoryID's to one number? I want to put these all into an 'unsorted' category that is administrable (there's a word and a half!) from the admin panel so he can move the products into the right category.

The unsorted category I've made is 6666.

Thanks again ralphy boy, no matter what they say about ya, you're golden!

[ 03.09.2008, 09:37: Message edited by: sabian ]

--------------------
Evil isn't what you've done, it's feeling bad about it afterwards... Yield to temptation. It may not pass your way again.

Posts: 3793  |  IP: Logged
ralph

 - posted      Profile for ralph           Edit/Delete Post   Reply With Quote 
It's nice to have a chance to repay you for pointing me to AnyDVD so I could *cough* backup dvd's.

Anyway, this update query should work:

code:
UPDATE zen_products
SET master_categories_id = 6666
WHERE master_categories_id NOT IN(SELECT categories_id FROM zen_categories)


Posts: 7436  |  IP: Logged
sabian

 - posted      Profile for sabian           Edit/Delete Post   Reply With Quote 
getting kids from school, will try when I get back, thanks a lot!

--------------------
Evil isn't what you've done, it's feeling bad about it afterwards... Yield to temptation. It may not pass your way again.

Posts: 3793  |  IP: Logged
ralph

 - posted      Profile for ralph           Edit/Delete Post   Reply With Quote 
you're most welcome.
Posts: 7436  |  IP: Logged
sabian

 - posted      Profile for sabian           Edit/Delete Post   Reply With Quote 
Nope, that didn't work... It said that 144 rows effected but there was no products in the unsorted category as well as fucking up the DB proper. Thankfully I had a 3 hour old backup (hope no work was done in the mean time! *snigger*)...

It's mysql 4 if that makes a difference! [Smile]

--------------------
Evil isn't what you've done, it's feeling bad about it afterwards... Yield to temptation. It may not pass your way again.

Posts: 3793  |  IP: Logged
ralph

 - posted      Profile for ralph           Edit/Delete Post   Reply With Quote 
heh. I probably should have mentioned that I've never used mySql before. The syntax I gave you was MS SQL -- the logic should work fine but you may have to figure out the syntax on your own.

I assumed you would backup the data before running the query. Bad, sabian!

Posts: 7436  |  IP: Logged
ralph

 - posted      Profile for ralph           Edit/Delete Post   Reply With Quote 
Give this a whirl...

UPDATE zen_products LEFT JOIN zen_categories ON zen_products.master_categories_id = zen_categories.categories_id
SET zen_products.master_categories_id = 6666 WHERE zen_categories.categories_id IS NULL

Posts: 7436  |  IP: Logged
sabian

 - posted      Profile for sabian           Edit/Delete Post   Reply With Quote 
Ok, my mistake... After further trial and error I've discovered I'm a retard and didn't realise that it isn't master_categories_id I need it's categories_id.

Now, I'm not 100% sure, but I think it needs to check categories_id against master_categories_id. If categories_id exists, but master_categories_id doesn't - that's the ghost products.

So to make it work, I think there needs to be a 3-stage query. First to find the mismatch of categories*.*, then find products that are assigned to the mismatch, then finally move the mismatched products to unsorted category. My very special "Coding with Crayons" vision below:

If categories_id != master_categories_id THEN echo results

Then the results have to be queried against the products_to_categories table

SELECT results FROM products_to_categories echo results2

Then the final stage of the process is to move the products_to_categories into the unsorted category

SELECT results2 MOVE categories_id = 6666


I know my expressions are mumbo jumbo, but it makes sense in my head! [Razz]

If you're interested in still helping, the tables are below so you can see what I mean... I really hate this guy.


products table:
code:
CREATE TABLE IF NOT EXISTS `zen_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',
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 AUTO_INCREMENT=1563 ;

--
-- Dumping data for table `zen_products`
--

INSERT INTO `zen_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`) VALUES

Product to category table:
code:
CREATE TABLE IF NOT EXISTS `zen_products_to_categories` (
`products_id` int(11) NOT NULL default '0',
`categories_id` int(11) NOT NULL default '0',
PRIMARY KEY (`products_id`,`categories_id`),
KEY `idx_cat_prod_id_zen` (`categories_id`,`products_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `zen_products_to_categories`
--

INSERT INTO `zen_products_to_categories` (`products_id`, `categories_id`) VALUES

Category table:
code:
CREATE TABLE IF NOT EXISTS `zen_categories` (
`categories_id` int(11) NOT NULL auto_increment,
`categories_image` varchar(64) default NULL,
`parent_id` int(11) NOT NULL default '0',
`sort_order` int(3) default NULL,
`date_added` datetime default NULL,
`last_modified` datetime default NULL,
`categories_status` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`categories_id`),
KEY `idx_parent_id_cat_id_zen` (`parent_id`,`categories_id`),
KEY `idx_status_zen` (`categories_status`),
KEY `idx_sort_order_zen` (`sort_order`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=446 ;

--
-- Dumping data for table `zen_categories`
--

INSERT INTO `zen_categories` (`categories_id`, `categories_image`, `parent_id`, `sort_order`, `date_added`, `last_modified`, `categories_status`) VALUES



[ 03.09.2008, 14:58: Message edited by: sabian ]

--------------------
Evil isn't what you've done, it's feeling bad about it afterwards... Yield to temptation. It may not pass your way again.

Posts: 3793  |  IP: Logged
Waynster

 - posted      Profile for Waynster           Edit/Delete Post   Reply With Quote 
This all looks vaguely familiar - I just started proper with SQL monday (I'm on an Oracle DBA course at the moment)

...though three days ain't really enough to help I'm afraid. And I wish I had done the SQL course first. Is this what you were trying to message me about the other day Sab?

--------------------
Noli nothis permittere te terere

Posts: 4323  |  IP: Logged
Physic
Digital PIMP !
 - posted      Profile for Physic           Edit/Delete Post   Reply With Quote 
quote:
Originally posted by Waynster:
This all looks vaguely familiar - I just started proper with SQL monday (I'm on an Oracle DBA course at the moment)

...though three days ain't really enough to help I'm afraid. And I wish I had done the SQL course first.

If you have any SQL questions you know where to find me amigo..

Sab I would try and answer your query but it's late and to be honest I can't make sense of what on earth your last post meant. If you can't get it sorted feel free to email me the details and I'll see what I can do, hotmail address is best as I can check that at work too..

Posts: 2337  |  IP: Logged


   
Post New Topic  Post A Reply Close Topic   Feature Topic   Move Topic   Delete Topic next oldest topic   next newest topic
 - Printer-friendly view of this topic
Hop To:


Contact Us | The Moon Online

copyright TMO y2k+

Powered by Infopop Corporation
UBB.classic™ 6.6.1