Displaying dynamic "from" price on home page

I hope I have placed this in the right area of the forum.
Basically I want to put a from price on the main page for each category, but I would want it to be dynamic so that I don't have to keep editing the main page everytime I upload a new product or remove an old product.
I want to clarify that I do know how to edit the main page text, I would just like to know to create the functions for the "from" price feature I want to create.

I want something that will work along the lines of:

• CatA - Starting From <?php echo CATA_FROM ?>
• CatB - Starting From <?php echo CATB_FROM ?>
• CatC - Starting From <?php echo CATC_FROM ?>

I hope someone can help.

Regards,
KCorbishley

You would have to look up all the products under each category, then look up the base price for each of those. This would give the data to establish the lowest price for the category. If you have attributes that affect the price, it gets much more complicated.

Thanks for your reply.
Luckily attributes don't affect the price. I'm not sure how I would look up the base price, I'm assuming a php script would be needed, but how to link it to the main page is also another challenge.

Regards

You could put the PHP in define_main_page.php - that's the easy part. It would take one or more SQL queries to look up the products for each category and the price for each product and return them. Then you can take the returned info and check the prices for lowest value, and make your output from that. Do your categories have subcategories, or are all the products directly in the categories you want to display?

I've been trying my best with this, but I just cant figure it out.
I know that I get the price from the 'products' table and the prices are in 'products_price_sorter' field and I'll have put the results in desc order and limit 1.
But it'll have to be linked someone to 'products_to_categories' to filter out products only from a specific category id, eg 7.
Can someone post a script I can use in the main page to get the lowest price for category id 7 and echo the result in a line such as "Prices from <result>".
I can then edit it for all the from prices for all the categories.

I hope someone can help.

Regards,
KCorbishley

just refreshin the thread in hope that someone can help.

kcorbishley:

I've been trying my best with this, but I just cant figure it out.
I know that I get the price from the 'products' table and the prices are in 'products_price_sorter' field and I'll have put the results in desc order and limit 1.
But it'll have to be linked someone to 'products_to_categories' to filter out products only from a specific category id, eg 7.
Can someone post a script I can use in the main page to get the lowest price for category id 7 and echo the result in a line such as "Prices from <result>".
I can then edit it for all the from prices for all the categories.

I hope someone can help.

Regards,
KCorbishley

I don't have access to the ZC database right now but maybe a point in the right direction will suffice...

Google "MySQL select query" and take a look at the tutorials out there regarding syntax. Realize that ZC has already established the connection to the DB so you won't need any code to do that.

What you're looking to do is to select two fields from RELATED tables's....the relationship is the key. Figure out which field the two tables and add a "Where" statement in your query.

So your basic query will follow this formula:

SELECT MIN(tableA.field1) FROM tableA, tableB
WHERE tableA.relatedField = tableB.relatedField AND
WHERE tableB.field2 = YourCategoryID

You'll then have the result you're looking for. Some considerations though: Depending on the number of categories you have, you will be running this query numerous times which will slow down your page load. It might make more sense (depending on the number of categories & products) to run a single query also selecting the category ID, then manipulating the results via PHP & arrays.

If this is a bit more than you bargained for but you still want this feature, seriously consider the ZC forum for hiring help (sorry, not me!). You don't want to affect the performance of your site negatively. And this is something that an experience programmer could pull off fairly quickly for you. And since ZC is such a great price (free...but please donate!), you can afford little "luxuries" like this.:wink:

Patti