Need help w/ SQL query in phpMyAdmin

[audradh]

Hey All
I am trying to extract to Excel the following information regarding active products in my database:

• Name
• Description
• image URL
• lowest price (ie sale/special price where applicable, regular price otherwise)
• URL

My SQL skills don't really exist, but I'm capable of following basic tutorials. So, taking things one step at a time...

I have managed the 1st two for all products w/ this:

SELECT products_name, products_description
FROM products_description

I have managed the image filename and regular price w/ this:

SELECT products_image, products_price
FROM products

What I cannot figure now is how to put those 2 queries together to extract those 4 pieces of information at one time.

I figure that is the next step, although I'm sure as I progress I will need a little more prodding

Appreciate any nudges in the right direction!
Peace,
Audra

[audradh]

I have managed the image filename and regular price w/ this:

SELECT products_image, products_price
FROM products

I've improved this to:

SELECT products_image, `products_price`
FROM `products`
WHERE products_status = "1"

Although until I figure out how to query multiple tables in the same statement, I cannot apply that to the 1st query of the products_description table.

Thanks
Audra

[audradh]

OK, I realize I am talking to myself at the moment, but perhaps this will help others learn...and it narrows my questions down to only what I really need help w/.

I love this one Just figured out that the lowest price can be found in the products table in the products_price_sorter column
(much easier than having to compare the regular, sale and special prices.)

But, for my purposes, I will then need to add an Excel column as to whether the item is on sale or not. Back to my figuring.

Still would love to know how to combine queries from 2 different tables...

[audradh]

I'm so getting there... I've graduated to this...

SELECT products_description.products_name, products_description.products_description, products.products_image, products.products_price_sorter
FROM products_description, products
WHERE products.products_status = "1"

So it seems I am now only missing the category path, which Excel can then generate a URL from, and whether or not an item is on sale/special...perhaps a bit more complicated?

[audradh]

Down to only the trickiest part... differentiating between items that are on sale/special and those that are not. Querying for one and then the other and combining the results in an Excel table is fine if that works best, but I'm not sure how to get there as of yet...

Of note, I discovered that the product URL does not need to include category path - Zen only does that if you get to a product through the category tree. Therefore, a simple product ID query, with a formula in Excel should get me the URL for each product.



Any thoughts about the sale/special problem??

[audradh]

Hmmm, can anyone tell me what is wrong w/ this:

SELECT products_description.products_name, products_description.products_description, products_description.products_id, products.products_image, products.products_price_sorter
FROM products_description, products
WHERE products.products_status = "1"

This gives me the same name, description and id attached to each different image, price_sorter, then the next name, description and id attached to each different image, price_sorter, then the next, etc.

Thanks for any help! I'm off to bed for now.
Audra

[hugo13]

Hmmm, can anyone tell me what is wrong w/ this:

SELECT products_description.products_name, products_description.products_description, products_description.products_id, products.products_image, products.products_price_sorter
FROM products_description, products
WHERE products.products_status = "1"

try this one

SELECT products_description.products_name, products_description.products_description, products_description.products_id, products.products_image, products.products_price_sorter
FROM products_description INNER JOIN products ON products_description.products_id = products.products_id
WHERE products.products_status=1

you have forgot the join statement :: http://dev.mysql.com/doc/refman/5.0/en/join.html

[audradh]

Worked a charm, thanks hugo!

I'll have to really look at Join Statements. That might take me a little bit to digest.

I have managed to expand it to find only the products on special by doing this:

SELECT products_description.products_name, products_description.products_description, products_description.products_id, products.products_image, products.products_price_sorter
FROM products_description
INNER JOIN (
products, specials
) ON ( products_description.products_id = products.products_id
AND specials.status = "1"
AND products_description.products_id = specials.products_id )
WHERE products.products_status =1

Now to pull products on sale, must be similar but using master category ids.

And lastly to pull everything not on sale/special, hmmm, not sure how that will work. Any thoughts?

[schoolboy]

All this would be easier if you used phpMyAdmin...

[audradh]

Funny, I actually am doing this in phpMyAdmin, but I'm not familiar w/ using the query tab either and couldn't seem to find what I wanted. Either way had a learning curve for me, but now that I've gotten this far I can see that the join portion might have been easier that way...but then I wouldn't have learned as much! Perhaps it will help me w/ this next portion though.

Thanks
Audra