shop by price

[ianhaney]

Hello

On my website, I would like to have a shop by price, something like the following

£0.01 - £2.99
£2.99 - £3.99
£3.99 - 6.99
£6.99 - £9.99
£9.99 or more

How would I do that and get the results from the mysql database when click on one of the above price links

[bradgrafelman]

Which DBMS? I'll assume MySQL.

For all cases but the last, you could use MySQL's BETWEEN comparison operator. For the last case, it's obviously a simple greater-than-or-equal-to comparison.

[ianhaney]

Hi

Thank you so much for the reply

Yeah its MySQL sorry should of mentioned that, I clicked on the link you gave and saw this part

mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1;
-> 1, 0
mysql> SELECT 1 BETWEEN 2 AND 3;
-> 0
mysql> SELECT 'b' BETWEEN 'a' AND 'c';
-> 1
mysql> SELECT 2 BETWEEN 2 AND '3';
-> 1
mysql> SELECT 2 BETWEEN 2 AND 'x-3';
-> 0

How would I make it to that when click on £0.01 - £2.99 for example, it returns the data for just the products that is between that price range

[bradgrafelman]

Have the links be in the form of:

<a href="showProducts.php?minPrice=0.01&amp;maxPrice=2.99">£0.01 - £2.99</a>

Then, in your showProducts.php (or whatever) script, you'd do something like:

if(!empty($_GET['minPrice']) && !empty($_GET['maxPrice']))
{
    $comparison = sprintf('priceCol BETWEEN %f AND %f', $_GET['minPrice'], $_GET['maxPrice']);
}
elseif(!empty($_GET['minPrice']))
{
    $comparison = 'priceCol >= ' . (float)$_GET['minPrice'];
}
elseif(!empty($_GET['maxPrice']))
{
    $comparison = 'priceCol <= ' . (float)$_GET['maxPrice'];
}
else
{
    // no min or max price given... error? show all products? something else?
}

[ianhaney]

That looks like what I need, thank you so much, I will try that out now and create a new php page and incorporate it in my oscommerce website

Thank you so much Bradgrafelman, if I get stuck is it ok to ask for help

[bradgrafelman]

ianhaney;11008265 wrote:

if I get stuck is it ok to ask for help

Of course; that's the general reason this entire community was created in the first place.

[ianhaney]

Thank you appreciate it

I need bit of help

I copied the coding into a new page called showproducts.php and made a div box on the front page but when I click on it I get the following error

Not Found

The requested URL /http://www.irhtoys.co.uk/showproducts.php was not found on this server.

Apache/2 Server at www.irhtoys.co.uk Port 80

My website is www.irhtoys.co.uk

I am using oscommerce, in includes/filenames I got this

define('FILENAME_SHOW_PRODUCTS', 'http://www.irhtoys.co.uk/showproducts.php');

In the div box I got the following

<a href="<?php echo tep_href_link(FILENAME_SHOW_PRODUCTS);?>"><div id="priceone"><div id="textone">£0.01 - £2.99</div></div></a>

Where am I going wrong

Sorry for all the questions, I really appreciate all the help

[ianhaney]

I have got the link working now but now the page is empty if you click the link £0.01 - £2.99 on the www.irhtoys.co.uk website

I tried pasting in the following in the includes/languages/showproducts.php page but didnt work and I pasted the below in the root of the site and didnt work

<?php
    if(!empty($_GET['minPrice']) && !empty($_GET['maxPrice'])) 
{ 
    $comparison = sprintf('priceCol BETWEEN %f AND %f', $_GET['minPrice'], $_GET['maxPrice']); 
} 
elseif(!empty($_GET['minPrice'])) 
{ 
    $comparison = 'priceCol >= ' . (float)$_GET['minPrice']; 
} 
elseif(!empty($_GET['maxPrice'])) 
{ 
    $comparison = 'priceCol <= ' . (float)$_GET['maxPrice']; 
} 
else 
{ 
    // no min or max price given... error? show all products? something else? 
}  

?>

[Derokorian]

You're not including minPrice and/or maxPrice in a query string for the link so there are no $_GET variable set. Look again at the code brad provided, specifically the building of the links - you need to have values in the query string if you want to pull values from the query string.

[ianhaney]

Sorry my mistake

I now got

<a href="showproducts.php?minPrice=0.01&amp;maxPrice=2.99">£0.01 - £2.99</a>

But when I click on the link it goes to a empty page apart from the heading

See link below

www.irhtoys.co.uk/showproducts.php

I guess it is to do with the showproducts.php in the includes/languages/english folder but what do I put in that file to return the results

[bradgrafelman]

ianhaney;11008297 wrote:

what do I put in that file to return the results

All of the code necessary to query the database for relevant information, retrieve the results, and output an HTML document containing the information.

[ianhaney]

bradgrafelman;11008305 wrote:

All of the code necessary to query the database for relevant information, retrieve the results, and output an HTML document containing the information.

So I am guessing that would be all this below

<?php 
    if(!empty($_GET['minPrice']) && !empty($_GET['maxPrice']))  

{  

    $comparison = sprintf('priceCol BETWEEN %f AND %f', $_GET['minPrice'], $_GET['maxPrice']);  

}  

elseif(!empty($_GET['minPrice']))  

{  

    $comparison = 'priceCol >= ' . (float)$_GET['minPrice'];  

}  

elseif(!empty($_GET['maxPrice']))  

{  

    $comparison = 'priceCol <= ' . (float)$_GET['maxPrice'];  

}  

else  

{  

    // no min or max price given... error? show all products? something else?  

}   

?>

In this includes/languages/showproducts.php file, where would be the best place to put the above coding

<?php



/*



  $Id$







  osCommerce, Open Source E-Commerce Solutions



  http://www.oscommerce.com







  Copyright (c) 2002 osCommerce







  Released under the GNU General Public License



*/







define('NAVBAR_TITLE', 'Products Price Range Results');



define('HEADING_TITLE', 'Products Price Range Results');







define('TEXT_INFORMATION', '');

?>

[bradgrafelman]

ianhaney;11008309 wrote:

So I am guessing that would be all this below

No... what I gave you was an example of how to complete a small part of only one of the items I listed above (namely, querying the database).

I left the rest up to you (and/or the programmer you hire to do this for you).