Help with a sql query

Hello,

I need to select all products from a certain category out of my current database to export to another database.

How do I make the mysql query do it

This is what I was thinking but didnt work:

SELECT * ROWS FROM zen_products WHERE master_categories_id = 69

All Im trying to do is select all products that are in master category 69

Im sure this is really wrong however this is my first attempt at mysql syntax.

I got:

SELECT master_categories_id = 69 FROM zen_products

which got me closer I think, meaning i didnt get an error but still quite right.

batteryman:

SELECT * ROWS FROM zen_products WHERE master_categories_id = 69

remove the word ROWS, and you'll get all the meta information about your products, but without descriptions

If you want descriptions too, use:

select p.*, pd.* from products p, products_description pd where p.products_id = pd.products_id and p.master_categories_id = 69;

DrByte:

If you want descriptions too, use:

select p., pd. from products p, products_description pd where p.products_id = pd.products_id and p.master_categories_id = 69;

I had to change your sql to include my prefix zen_

select p., pd. from zen_products p, zen_products_description pd where p.products_id = pd.products_id and p.master_categories_id = 69;

Which then produces the result:> MySQL returned an empty result set (i.e. zero rows). (Query took 0.0010 sec)

So what do you think I should do, also what is p. and pd. and how do they represent what they represent?

I have seen those called in another module and wondered what they were then as well.

if you're getting 0 results, then you have nothing matching the master-categories-id = 69.

The p. and pd. are table-aliases... note "products p, products_description pd" ... that's where the aliases are defined.

DrByte:

if you're getting 0 results, then you have nothing matching the master-categories-id = 69.

The p. and pd. are table-aliases... note "products p, products_description pd" ... that's where the aliases are defined.

Im feel so stupid right now, I was running the query in my new install. OOops!

Thank you Dr. Byte

Oh, big question,

Because I used the query you suggested where the product and description are displayed together in the results. How do I import this information into a new database so that the information goes into its table?

I too have a SQL question. I need all (Several) Order Product Attributes to be in their own field and Exported to CVS. I know how to Export using MyPHP but I don't have enough SQL knowledge to combine all the OrderProductAttributes to one line for each product ordered.

BWB:

I too have a SQL question. I need all (Several) Order Product Attributes to be in their own field and Exported to CVS. I know how to Export using MyPHP but I don't have enough SQL knowledge to combine all the OrderProductAttributes to one line for each product ordered.

What are the tables you want to export from and what are the fields you want included??

batteryman:

What are the tables you want to export from and what are the fields you want included??

The file is Orders_Products_Attributes and the fields are
orders_id orders_products_id products_options products_options_values The problem is I have several options and option values for products but need all of them on separated on a single row per order/product. And to make things worse there can be the more than one of the same product with different attributes per order.:blush:

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.

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

Why are you doing all this ?

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 -> https://www.photographicbattery.com
Photo Lithium Batteries -> https://www.photolithium.com
Hotel Door Lock Batteries -> https://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.

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

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.php/Developers_-_Database_Schema#Product_Info

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

avel

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

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_bookwhat is the all table rename method??

Insert Here!! LOL :P

Thank you in advanced

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
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.