Help with a sql query

[BWB]

I also have tried the Admin DOWNLOAD Export Shipping Info 1.2.2 to CSV and it works great except it places ALL of the Attributes for each product ordered into a single field that I cannot break out.

[batteryman]

I just came up with a simpler solution. I will import all of my products and descriptions to my new zencart install and then simply run a sql query that deletes all products not associate with master_category _id 69 and it decriptions.

So now all I need help with is writing the delete command excluding the Master_Category_Id = 69 and associated descriptions.

thank you for help in advanced

[DrByte]

Why are you doing all this ?

[batteryman]

because I have a huge site with so many products of some many unrelated products, batteries

So I am developing small specialized sites focused on a particular group of products ie..

Photographic Batteries -> www.photographicbattery.com
Photo Lithium Batteries -> www.photolithium.com
Hotel Door Lock Batteries -> www.hoteldoorlocks.com

and so on. I have like 10 sites total that I will be developing. They will all be linked back to my main site and then in each site I will be implementing blogs and feeds in each site to creative a network of information and support. So I need to easily extract the groups of products for each site and re upload them into the new databases.

[batteryman]

I figured it out... this is what I did

first i exported the database info that I needed for the new database..

1. categories
2. categories_descriptions
3. products
4. products_descriptions
5. products_to_categories

There are more I should have included but I didnt because I was testing my process.

second I uploaded them into my new database

third I ran this sql query to delete the descriptions of the products I didnt want, which you must do first because the query compares description to product based on the master_id in the product table

delete pd.* from zen_products p, zen_products_description pd where p.products_id = pd.products_id and p.master_categories_id != 69

fouth, now you can delete the product

delete from zen_products where master_categories_id != 69

fifth, you must now go thru and delete all other information relating to the categories and old products that are no longer there

deletes products that went to categories no longer there

delete from zen_products_to_categories where categories_id != 69

deletes the categories no longer there

delete from zen_categories where categories_id != 69

Im sure theres more like if you have special pricing for a product (products_discount_quantity) upload it and then delete out the unnecessary info and so on..

thank you dr. byte for the lesson

[DrByte]

Sounds like you've got it.

Step 4 was redundant due to step 3.

You might also consider the products_options_* tables and products_attributes_* tables if you have attributes on those products. And, you mentioned the quantity discounts already.


Table reference: http://www.zen-cart.com/wiki/index.p...a#Product_Info

[batteryman]

thank you dr. byte for your suggestion and the link. That will be very helpful for future development.

avel

[batteryman]

delete pd.* from zen_products p, zen_products_description pd where p.products_id = pd.products_id and p.master_categories_id != 69

I want to use this mysql to delete products from my database but I need to delete all excepts a few categories. How would I accomplish that?

The categories I want to keep are 91, 92, 93, 94, 95, 96, 97, 298

Thank you
avel

[batteryman]

Hello, I would like to remove all the zen_ prefixes from my new site database that I just imported over to a new store I am working on. I know how to do it on a 1 by 1 basis but I would like to do it for all 95 tables at once


this is the 1 by 1 method

RENAME TABLE zen_address_book TO address_book

what is the all table rename method??

Insert Here!! LOL

Thank you in advanced

[DrByte]

Hello, I would like to remove all the zen_ prefixes from my new site database that I just imported over to a new store I am working on. I know how to do it on a 1 by 1 basis but I would like to do it for all 95 tables at once


this is the 1 by 1 method
what is the all table rename method??

You might try it on a test site ... but ... if you use the zc_install tool, click into database-upgrade mode, ignore all the checkboxes and scroll down further: you'll see input fields near the bottom of that upgrade page, which can be used to do the bulk rename.