Custom inventory query statement - Need help please

[0be1]

OK, I admit I hacked my way this far and have now become stuck

What I am trying to do is a custom query that will give the the following that I can save after the query runs.

I need the:

Product URL (comes from products_description table)
Product Name (comes from products_description table)
Product Description (comes from products_description table)
Image URL (comes from products_description table)
Product Category (I believe comes from categories_description table - categories_name)
Product Price (comes from products table)

Here is my query I hacked together so far:

SELECT p.products_id, p.products_model, pd.products_name, pd.products_description, pd.products_url, p.products_image, p.master_categories_id
FROM zen_products p
LEFT JOIN zen_products_description pd ON (p.products_id = pd.products_id)
LEFT JOIN zen_product_types pt ON (p.products_type=pt.type_id)
WHERE p.products_status = 1
ORDER BY p.products_last_modified DESC

I got half of the correct results. Can anyone please assist me in finishing it off???

tia...

0be1

[AndyM]

Would something like this do it?

SELECT categories_name "Category", products_url "Url", products_name "Name", products_description "Desc", products_image "Image", products_price "Price" FROM zen_products_description JOIN products USING(products_id) JOIN zen_products_to_categories USING(products_id) JOIN zen_categories USING(categories_id) JOIN zen_categories_description USING(categories_id) WHERE products_status = 1 ORDER BY products_last_modified DESC

[0be1]

AndyM;

That got me closer and there was a typo in your post but i figured it out. I also tried to change the order of the query and when I did all I got was a blank screen. It must of had something to do with the joins. I have been working on it on and off today and have not quite gotten it yet.


I was trying to change the order to:

1) Product URL
2) Product Name
3) Product Description
4) Image URL
5) Product Category
6) Product Price (even though it is not needed for this particular project)

Also is there a way to append some sort of path to the images. For example my images reside in the root directory of zen cart in a sub folder called images. This particular organization that is getting our data needs the full path to each image for some reason not just the file name.

Thanks again for your help and quick reply

Sincerely,

0be1

[AndyM]

Sorry about the typo but I'm glad you're closer than you were. As far as I know, only relative paths are stored in the db.

If your images are stored under /home/0be1/cart/images, you could use something like CONCAT('/home/0be1/cart/images/', products_image) in the query.

[0be1]

Could you please help me switch around the query that you had sent me with the order of my last post?

thanks

0be1

[AndyM]

To change the field order in the results, rearrange the fields in the SELECT statement to reflect that. To change sort order, add fields to the ORDER BY clause.

Keep experimenting with the query. That's the best way to learn.

Anyway, I changed the query to create rows that look like:
URL, Name, Desc, Image, Category, Price

SELECT products_url "Url", products_name "Name", products_description "Desc", CONCAT('/home/0be1/cart/images/', products_image) "Image", categories_name "Category", products_price "Price" FROM zen_products_description JOIN zen_products USING(products_id) JOIN zen_products_to_categories USING(products_id) JOIN zen_categories USING(categories_id) JOIN zen_categories_description USING(categories_id) WHERE products_status = 1 ORDER BY products_last_modified DESC

[0be1]

I figured I better stay on this post as it is pretty much related and do not want to make the powers that be mad

I had to modify the query a little more (here is the current one)

SELECT CONCAT('http://', products_url) "Product Url", products_name "Product Name", products_description "Product Description", CONCAT( 'http://www.swansoninc.com/images/', products_image ) "Image URL", categories_name "Product Category", products_price "Product Price"
FROM zen_products_description
JOIN zen_products
USING ( products_id )
JOIN zen_products_to_categories
USING ( products_id )
JOIN zen_categories
USING ( categories_id )
JOIN zen_categories_description
USING ( categories_id )
WHERE products_status =1
ORDER BY products_last_modified DESC

Now the only problem I am having is that there are numerous products that are linked and I cannot find a way to remove those during the query process. Instead of having like 4500 products I am ending up with 6500 or more due to the linked products. Any ideas???

tia...

0be1

[AndyM]

If I understand the problem, try adding "GROUP BY products_id" between the WHERE and ORDER clauses. I don't have any linked products in my database to test this with, so all bets are off here.

You should end up with something like this:

WHERE products_status =1
GROUP BY products_id
ORDER BY products_last_modified DESC

[0be1]

AndyM;

I tried this and here is what came back:

#1052 - Column: 'products_id' in group statement is ambiguous

And here is what the query now looks like:

SELECT CONCAT('http://', products_url) "Product Url", products_name "Product Name", products_description "Product Description", CONCAT( 'http://www.swansoninc.com/images/', products_image ) "Image URL", categories_name "Product Category", products_price "Product Price"
FROM zen_products_description
JOIN zen_products
USING ( products_id )
JOIN zen_products_to_categories
USING ( products_id )
JOIN zen_categories
USING ( categories_id )
JOIN zen_categories_description
USING ( categories_id )
WHERE products_status =1
GROUP BY products_id
ORDER BY products_last_modified DESC

Also since it was never mentioned I am on a server that is running MySQL 4.0.26. Please make sure any post replies refer to MySQL 4, not version 5.

thanks...

0be1

[AndyM]

None of this should be specific to 5. It's pretty generic SQL.

Try using GROUP BY zen_products.products_id instead. MySQL can't figure out which products_id you mean (even though they're JOINed and should be the same anyway).