patsfans;10976717 wrote:
I'm looking to set up a filter system to narrow down prices and am running into an issue with the data outputting correctly.
What's the purpose? To reduce load on DB or to make things easier for clients? Or both?
patsfans;10976717 wrote:
0-$699 (5)
$700 - $999 (15)
$1000 - $1299 (13)
(...)
$4900 - $5199 (2)
$5200 - $5499 (1)
Personally I wouldn't want to have to click a link to see just one item, then click another link to see another two items, so I don't believe an approach where you subdivide into equal price ranges with varying number of items per price range is satisfactory from a client perspective.
Since you are setting your price ranges after what prices you have, the lowest range should go from a multiple of $100 that is below the lowest price. E.g. $500 rather than $0.
It should also be noted that your price range has gaps in it. Where would a product costing $699.95 end up? Perhaps you have no such prices, but I'd still go with
$500-$700
$700-$1000
$1000-$1300
And instead of between, use 500 <= price AND price < 700, 700 <= price AND price < 1000.
patsfans;10976717 wrote:
$sql = mysql_query("SELECT * FROM products WHERE cat = '125' ORDER BY price");
And if the reason is to reduce DB load, you have gained nothing through what you're doing since the above query retrieves all products for a category in the table every time.
patsfans;10976717 wrote:
while ($row = mysql_fetch_array($sql)) {
$prices = array($row['price']);
}
This piece of code means $prices is an array that will always contain only one element, and when the loop is completed, it contains one element with the last retrieved price from the table.
To make things easy for both you and the clients, I'd rather go with a price range filter, adjustable by the client, in combination with an ordinary pagination 1 ... N, with the possibility to "show all products" (in the price range). The absolutely simplest way would be two inputs of type text, where the user can enter min and max price. If more than some number of products is in this price range, the pagination appears.
If you prefer setting up something similar to what you are after right now though, I'd do the work once, store this information in two tables, and only update it when something changes. Once your code decides on price ranges to use, store these in a table
price_ranges_for_category
-----------------------------
range_id
category_id
min_price
max_price
PRIMARY KEY(range_id)
FOREIGN KEY (category_id) REFERENCES category(id)
And when you go over your products, you stick each product's id into a second table relating it to its price range for this category
product_in_price_range
--------------------------
range_id
product_id
PRIMARY KEY(range_id, product_id)
FOREIGN KEY(range_id) REFERENCES price_ranges_for_category(range_id)
FOREIGN KEY(product_id) REFERENCES product(id)
To get all price ranges for a category
SELECT range_id, min_price, max_price FROM price_ranges_for_category WHERE category_id = @category
After a user clicks a price range for this category, retrieve all products by
SELECT relevant, fields, in, products, table
FROM product AS p
INNER JOIN product_in_price_range AS ppr ON p.id = ppr.product_id
WHERE ppr.range_id = @range
If a product is deleted (but before actually deleting it), find out in what price range it is. If, by removing this product, there'd be too few products
SELECT count(*) FROM products_in_price_range WHERE range_id = @range_id_of_product_being_deleted
in the price range (0 or some other threshold), you may wish to recalculate price ranges for this product category. If you still have an acceptable number of products in this price range, just delete the product (and its entries in related tables. Having ON DELETE CASCADE for the foreign key constraint in table product_in_price_range will do this automatically for you.
The same reasoning is used if the price for a product is changed. If the price change means the product would move to a new price range, check if you should rebuild the entire category's ranges, or if it's acceptable to just move the product into a new price range.
Also note that a product potentially could belong to multiple price ranges without problems.
