MySQL Query to include sales/specials

[LissaE]

On my newest site which is under construction I am using vbadvanced cmps for the portal page, I dredged up an old old module for displaying latest ZC products on the portal page and after many hours of playing with the query (I am not a programmer LOL) I finally managed to get it to show them...the issue I have though is that it won't display sale price or special price...can anyone help me with the following code to what I need to add in to get these to show?

SELECT distinct p.products_id, p.products_image, pd.products_name, p.products_tax_class_id, p.products_price, p.products_status, p.products_date_added, p.master_categories_id, m.manufacturers_name
FROM products p
LEFT JOIN manufacturers m
ON (p.manufacturers_id = m.manufacturers_id), products_description pd
WHERE p.products_id = pd.products_id
AND p.products_status = 1
and p.products_price > 0
ORDER BY p.products_date_added
LIMIT $numofprod;";

TIA

[Absolute]

Within your template you just want to iterate through the products, and then just use:

echo zen_get_products_display_price($products_id);

This handles everything for you - styles, prices etc.

If you just want the sale price as a value, use zen_get_products_special_price($products_id, false);

If you just want the specials prices as a value, use zen_get_products_special_price($products_id, true);

Absolute

[LissaE]

Hi Absolute, thanx for your reply :)

The code is not for a template or file within zen cart, it's a vbadvanced (portal page system through vBulletin forum) module that calls information from the database to display the latest products on an outside page, which is why I need the correct query to call the sales/specials price...if that makes sense lol

[Absolute]

In that case, need to do a left join to the SPECIALS table, on products_id = . The SALES are a little harder, as they are all category driven, and then need to take a percentage off of the products price. If I were you, I'd take a look in the includes/index_filters folder - specifically the default search file in there. It has exactly what you need, as there is a search in there based on the actual price.

This type of SQL search is EXTREMELY resource hungry, as it needs to join so many tables, and calculate values on the fly. If you have a large number of products, you may notice a considerable return time when this SQL is run. Can you use an XML import to get the data into vbadvanced? If so, that may be quicker, because you can then do a single search for products, and run through with the functions mentioned above, just on the products you need. You could then export this as a dynamic XML output which can then be read by your other script. Just an idea, in case you've got hundreds of products.

Absolute

[LissaE]

Thanx so much....I will have a look at that file :)

The store will most likely end up with hundreds of products but it is kind of like a consignment store where new ones will be added continually so even if I knew how to do the xml file thing it wouldn't suit lol if I notice it start to slow down I can always return it to the query I have now...thanx again

[LissaE]

Blah...I just can't get this lol I don't know enough about mysql queries and syntax and stuff to get it to show the specials/sales prices so I am going to leave it as is for now...probably just put a blurb that items shown may be on sale and to check out the product to see...thanx anyways lol :)