PHP form to display query between 2 prices

[crowds40]

 I have a clothing store website which displays all my products with a price range of $0-$25 great on left side of my webpage with the count number beside it like so.

 

$0-$25 (12)

 

My code is

 

     <?php 


    $sidebar_price ="";

    $price_sql = mysql_query("SELECT *,COUNT(sale_price) FROM products WHERE subcategory =      

    'Shirts' AND category = 'Women' AND (sale_price BETWEEN '$00.00' AND '$25.00')");

  

  

    $prCount = mysql_num_rows($price_sql);

    if ($prCount > 0) {

while($row = mysql_fetch_array($price_sql)){



$sale_price = $row["sale_price"];



$sidebar_price .= '<div id="sidebar"><label><form action="?"method="POST"><input type="checkbox" name="sale_price" value="'.$sale_price.'" 

     onClick="submit(); return false" ' . (isset($_POST['sale_price']) && $_POST['sale_price']==$row['sale_price'] ? 'checked="checked"' : '') . ' /><a href="?sale_price='.$sale_price.'">$0-$25 ('.$row["COUNT(sale_price)"].')</a></form></label></div>';



}

      }

     ?>

 

 

How do I get it so when a customer clicks on either the checkbox or the href the products will display? 

 

Right now when I click on the href or checkbox it will only display the products with prices for example just $24.99 and not all the products between $0.00-$25.00 like $24.99, $22.6, 19.35, etc..

 

If it is not clear enough I will try to explain further.

 

Any ideas?

Edited June 2, 2014 by mac_gyver
code tags please

[mac_gyver]

there are some problems with the posted query/code.

 

because you are using an aggregate function in the sql query - count(), but are not using group by in the query, you are only getting one row in the result set. this will give you a count for the products having that particular price range, but doesn't scale to handling other ranges of prices using one query.

 

also, by storing the prices with the $ character and as strings, you will not be able to handle anything over $99.99, based on what you have shown. a more correct approach would be to not store the $ character in the table (it's a human convention and computers have no use for it) and store the prices as a decimal data type, i.e. just the 24.99 values would be stored. this will allow any value to work and work correctly when comparing values by magnitude.

 

next to allow any (arbitrary) price ranges, you should have a database table that stores the ranges, one per row, with an id, the minimum value, the maximum value, and an optional display label (or you could use the min and max values to produce the display label - i.e. $0-$25.) you would join this table to your products table in a sql query to get the count of products for each range of prices. the id in this table would be used by the check box(s) to specify the range(s) to query for when displaying products.

 

the query to display products would be similar to the joined query described above, except it would not have the count() and group by terms in it and it would limit the row(s) matched from the range table to the id's that were submitted from the checkbox(s)

Edited June 2, 2014 by mac_gyver

[mac_gyver]

some sql queries that demonstrate the method described above.

 

get the range id (to identify each range when build the checkboxes and using the submitted checkbox data), low/high values (to produce the range label), and the count of products for each ranges (including ranges with no matching products, to get only ranges with products, change the left join to just a join) -

Select r.id, r.low, r.high, count(p.price) as num
from ranges r left join products p on p.price between r.low and r.high
group by r.id order by r.low

get the products that match a selected range(s) (range id's 1 or 2 in this example) -

Select p.* from products p
join ranges r on p.price between r.low and r.high and r.id in(1,2)

sample of what the ranges table would look like -

"id","low","high"
1,   0.00, 25.99
2,  26.00, 50.99
3,  51.00, 99.99

[crowds40]

Thanks for the responses. I was using GROUP BY (sale_price BETWEEN '$0' AND '$25')  before and it didn't seem to make a difference using it and not using it.

 

For the href the main problem seems to be     '.$sale_price.'

 

Is there any chance something like this or along these lines will work      

 

href="?sale_price='.$sale_price < '25'.'

 

or

 

$range = "'.$sale_price.' BETWEEN $0 AND $25"

 

href="?sale_price='$range'"

 

 

Before I had a price_range column in my database but it just takes to long to put each one in manually and was hoping there was a code to read sale_price column automatically. Would this work or is it just to far fetched?

[Jacques1]

I really don't know what you're trying to do with that URL parameter.

 

No, you cannot magically execute SQL queries through the URL. This would obviously be a security nightmare.

 

Just pass the range information to the script. This is as simple as using a from and a to parameter. Those two parameters also have the nice side-effect of allowing users to specify custom ranges. 

[Psycho]

Step 1 is to accurately get the results for the price ranges. As Jacques1 states you should absolutely be storing prices as numerical values - not text.

 

 

Once you are able to get the counts by price range you can create links to return the products in those price ranges. What that link looks like is up to you. You could create hard-coded "tags" within those URLs and translate them in the code. E.g.

<a href="getProducts?pricerange=a">Products ($0.00 - $25.00)</a>

But, that's pretty limiting. If you change your ranges later you have to go and change code. Plus, it only allows you to filter by one thing - price. It is much better, in my opinion, to create the URL parameters so it is much more flexible. I would pass two parameters: minPrice and maxPrice. Then in the page that receives the submission, it would use the two values that are passed to query the right products. You could also add other filters to 'add' to the URL.

 

Rough example:

<a href="getProducts?minPrice=0&maxPrice=25">Products ($0.00 - $25.00)</a>

$minPrice = isset($_GET['minPrice']) ? floatval($_GET['minPrice']) : false;
$maxPrice = isset($_GET['maxPrice']) ? floatval($_GET['maxPrice']) : false;
 
$WHERE_PARAMS = array();
if($minPrice)
{
    $WHERE_PARAMS[] = 'price >= $minPrice';
}
if($maxPrice)
{
    $WHERE_PARAMS[] = 'price <= $maxPrice';
}
 
$query = "SELECT * FROM table";
if(count($WHERE_PARAMS))
{
    $query .= "WHERE " . implode(" AND ", $WHERE_PARAMS);
}

Edited June 2, 2014 by Psycho

[crowds40]

 

Once you are able to get the counts by price range you can create links to return the products in those price ranges. What that link looks like is up to you. You could create hard-coded "tags" within those URLs and translate them in the code. E.g.

<a href="getProducts?pricerange=a">Products ($0.00 - $25.00)</a>

 

My code does display the proper counts by price range. For example:

 

Products $0.00-$25.00 (12)

 

 

But I cant get the right link to display the all 12.

<a href="?sale_price='.$sale_price.'">

This code will only display the $24 products and not the rest lower. It there an href that will display all 12 from the query?

[Psycho]

But I cant get the right link to display the all 12.

<a href="?sale_price='.$sale_price.'">

This code will only display the $24 products and not the rest lower. It there an href that will display all 12 from the query?

 

You can't pass a 'single' price and expect to get a result for a range of prices. It is up to YOU to determine what parameters to put on the URL and how they will be processed. I provided an option of passing a min and max value on the query string.