Help with a sql query

[batteryman]

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.

[batteryman]

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.

[DrByte]

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;

[batteryman]

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.

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

[batteryman]

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

[batteryman]

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?

[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