asked on

Gather Price range value with PHP MySQL

I am setting up a search query for a jewellry website using PHP and MySQL.

All queries work fine ie colour, size etc.

I need to add a function where the user can search a price range. ie 'less than £50' £50 - £100' '£100 or more'. how would i set this up so this query reads the prices in the 'prices' column of the database and then shows the results?

The dropdown on the form which gathers the search is curently:

<select name="range" class="black" id="range">
<option value="Less than £50">Less than £50</option>
<option value="£51 - £100">£51 - £100</option>
<option value="£101-£200">£101-£200</option>
<option value="£201 - £300">£201 - £300</option>
<option value="£300 +">£300 +</option>
<option selected="selected">All</option>
</select>

john-formby

Hi,

I am guessing (hoping) the price in your database table is stored like this 12.50 and not £12.50? You need it to be numeric (without currency symbol) to be able to do a comparison.

Please see the code example below. What I have done is changed the values of the select options to numeric, separated with a -

When you post this, it explodes the posted value by the dash to give you two distinct values in an array called $range. Then you can do your query using BETWEEN to find items with a price between the starting and ending range.

I have also included the MySQL table I used to test for reference.

Hope this helps,

John

page.php
========
<html>
<head>
<title>MySQL BETWEEN</title>
</head>
<body>
<?php
if(isset($_POST['submit'])) {
	$dbHost = "localhost";
	$dbUser = "YOUR_USERNAME";
	$dbPass = "YOUR_PASSWORD";
	$dbName = "YOUR_DATABASE";
	$db = mysql_connect($dbHost,$dbUser,$dbPass);
	mysql_select_db($dbName,$db);
	
	$range = $_POST['range'];	
	$range = explode('-',$range);
	$query = mysql_query("SELECT * FROM tblitems WHERE price BETWEEN $range[0] AND $range[1]") or die(mysql_error());	
	$numrows = mysql_num_rows($query);
	if($numrows <= 0) {
		echo 'No Results Returned';
	} else {
		while($row = mysql_fetch_array($query)) {
			echo 'Item: '.$row['item'].'<br />';
		}	
	}
}
?>
<form action="<?php $_SERVER['PHP_SELF']; ?>" method="post">
	<select name="range" class="black" id="range">
	<option value="0-50">Less than £50</option>
	<option value="51-100">£51 - £100</option>
	<option value="101-200">£101-£200</option>
	<option value="201-300">£201 - £300</option>
	<option value="300-999999">£300 +</option>
	<option selected="selected">All</option>
</select>
<input type="submit" name="submit" value="Submit" />
</form>
</body>
</html>




tblitems
========
CREATE TABLE `tblitems` (
  `item` varchar(255) NOT NULL,
  `price` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tblitems`
--

INSERT INTO `tblitems` (`item`, `price`) VALUES
('Item 1', '12.50'),
('Item 2', '250.95');

Open in new window

NeoAshura

i must comment on that code john. looks good to me looks like it should work.

BrighteyesDesign

ASKER

Thanks for that John,

Can i just check that i have understood it correctly.

(The search results show on a different page by the way)

My form is as follows:

<select name="range" class="black" id="range">
<option value="0-50">Less than £50</option>
<option value="51-100">£51 - £100</option>
<option value="101-200">£101-£200</option>
<option value="201-300">£201 - £300</option>
<option value="300-99999999">£300 +</option>
<option selected="selected">All</option>
</select>

And the query code on the PHP is:

$range = explode('-',$range);

mysql_select_db($database_Leyla, $Leyla);
$query_rs_items = "SELECT name, image, id, name, price FROM products WHERE $colour_query AND $style_query AND $size_query AND price BETWEEN $range[0] AND $range[1] ORDER BY price ASC";

I know i have done something wrong as it's not working!

Thanks in advance!

john-formby

How are the values stored in your database? Are they numeric (12.50) or are they a string (£12.50)?

Also, I made a typo in my code. You said in your post that the field is called prices. I have been using price. I can see you have that in the query you posted just now so perhaps it needs changing?

BrighteyesDesign

ASKER

Hi John,

It is price not prices (see pic). I noticed i called price twice so i have removed the first one, made no difference though.

The query code is now:

$range = explode('-',$range);

mysql_select_db($database_Leyla, $Leyla);
$query_rs_items = "SELECT name, image, id, name FROM products WHERE $colour_query AND $style_query AND $size_query AND price BETWEEN $range[0] AND $range[1] ORDER BY price ASC";

When i view the results page live the followng error shows:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND ORDER BY price ASC' at line 1

I'll attach the whole pge code too in case thats of use.

Thanks again!

<?php require_once('Connections/Leyla.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$colour = $_GET["colour"];
if($colour == 'ANY'){   //Where Any just takes on a value of ANY.
  $colour_query = "colour LIKE '%'" ;
} else {
  $colour_query = "colour = '$colour'" ;
}

$style = $_GET["style"];
if($style == 'ANY'){   //Where Any just takes on a value of ANY.
  $style_query = "style LIKE '%'" ;
} else {
  $style_query = "style = '$style'" ;
}


$size = $_GET["size"];
if($size == 'ANY'){   //Where Any just takes on a value of ANY.
  $size_query = "size LIKE '%'" ;
} else {
   $size_query = "size LIKE '%$size%'" ;
}

  $range = explode('-',$range);

mysql_select_db($database_Leyla, $Leyla);
$query_rs_items = "SELECT name, image, id, name FROM products WHERE $colour_query AND $style_query AND $size_query AND price BETWEEN $range[0] AND $range[1] ORDER BY price ASC";

$rs_items = mysql_query($query_rs_items, $Leyla) or die(mysql_error());
$row_rs_items = mysql_fetch_assoc($rs_items);
$totalRows_rs_items = mysql_num_rows($rs_items);

mysql_select_db($database_Leyla, $Leyla);
$query_rs_style = "SELECT * FROM ringstyle";
$rs_style = mysql_query($query_rs_style, $Leyla) or die(mysql_error());
$row_rs_style = mysql_fetch_assoc($rs_style);
$totalRows_rs_style = mysql_num_rows($rs_style);

mysql_select_db($database_Leyla, $Leyla);
$query_rs_colour = "SELECT * FROM colour";
$rs_colour = mysql_query($query_rs_colour, $Leyla) or die(mysql_error());
$row_rs_colour = mysql_fetch_assoc($rs_colour);
$totalRows_rs_colour = mysql_num_rows($rs_colour);

mysql_select_db($database_Leyla, $Leyla);
$query_rs_size = "SELECT * FROM ringsize";
$rs_size = mysql_query($query_rs_size, $Leyla) or die(mysql_error());
$row_rs_size = mysql_fetch_assoc($rs_size);
$totalRows_rs_size = mysql_num_rows($rs_size);



?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Cocktail Rings, Earings, Bracelets &amp;  Jewellry</title>
<script src="Scripts/swfobject_modified.js" type="text/javascript"></script>
<link href="leyla.css" rel="stylesheet" type="text/css" />
</head>

<body>
<table width="900" border="0" align="center" cellpadding="0" cellspacing="0">
  <tr>
    <td align="center"><img src="images/leya-logo.jpg" width="242" height="100" alt="Leya Bijoux Logo" /></td>
  </tr>
  <tr>
    <td align="center"><table width="209" height="10" border="0" cellpadding="0" cellspacing="0">
      <tr>
        <td><!-- saved from url=(0014)about:internet -->
            <div id='menu' style='display:none;'>
              <div><a href='index.html'>HOME</a></div>
              <div><a href='jewellery.php'>RINGS</a></div>
              <div><a href='bracelets.php'>BRACELETS</a></div>
              <div><a href='earrings.php'>EAR RINGS</a></div>
              <div><a href='necklaces.php'>NECKLACES</a></div>
              <div><a href='bangles.php'>BANGLES</a></div>
              <div><a href='about.php'>ABOUT LEYLA</a></div>
              <div><a href='faqs.php'>FAQ%5C%27S</a></div>
              <div><a href='contact.php'>CONTACT</a></div>
          
          </div>
          <div id="f_source_navigation" style="position:relative; top:0px; left:0px; width:928px;">
            <!-- Don't change any part of the DIV code (Press Ctrl+Shift+F to edit) -->
            <script src='Menu/MenuScript.js' type='text/javascript'></script>
            <!-- saved from url=(0014)about:internet -->
           
            <!-- Flash Object code (EMBED tag will be generated automatically) -->
            <div id="f-source-menu" style="position:relative;" onmouseover="if(!ied){i= fdiv.style.height.indexOf('px');h=Number(fdiv.style.height.substring(0,i));fdiv.style.height=h+1+'px';}">
              <object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,28,0" id="Menu" width="928" height="33" >
                <param name="movie" value="Menu/menu.swf" />
                <param name="quality" value="high" />
                <param name="wmode" value="transparent" />
                <param name="FlashVars" value="flashlet={bg_Pic_URL:'None',xml_Path:'',stretch_width_to:'928px',_TransparencyShadow:30,_removeSubDelay:500,_minSubWidth:60,_max_height:550,_removeDividers:false,showSearchField:false,searchFieldWidth:80,searchTextColor:#000000,searchFieldColor:#FFFFFF,mainSoundURL:'',clickSoundURL:'',subSoundURL:'',_mainButtonHeight:28,_menuColor:#DDDDDD,_mainFont:'Times Roman',_mainFontSize:10,_mainButtonTextColor:#5e5e5e,_mainButWidthExt:6,_mainHighlightColor:#CCCCCC,_TransparencyMain:100,_subButtonHeight:20,_subMenuColor:#FEFEFE,_subFont:'Times New Roman',_subFontSize:10,_subButtonTextColor:#333333,_subHighlightColor:#999999,_TransparencySub:100,_pos_type:'relative',_pos_centered:false,_pos_top:0,_pos_left:0,_pos_insert:'the insertion point',_file_folder:'Menu'}" />
                <noscript>
                  The <a href="http://f-source.com/">flash menu</a> provided by f-source.com. All Rights Reserved
                  </noscript>
              </object>
              <script type="text/javascript">var connected; var message="Hello!"; if (connected){ Run_f_source_menu() }else{ alert("The file MenuScript.js could not be loaded!") }</script>
            </div>
          </div></td>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td><div>
      <table width="930" border="0" cellspacing="0" cellpadding="0">
        <tr>
          <td align="center"><table width="929" border="0" cellpadding="0" cellspacing="0" class="querybar">
            <tr>
              <td width="122" bgcolor="#BBCACC" class="type"><table width="109" height="18" border="0" cellpadding="0" cellspacing="0" class="type">
                <tr>
                  <td><?php echo $row_rs_items['type']; ?></td>
                </tr>
              </table></td>
              <td width="598" align="center" bgcolor="#BACACC"><form id="form1" name="form1" action="jewelleryrings.php" method="get">

                <table width="560" border="0" cellspacing="0" cellpadding="0">
                  <tr>
                    <td width="518" align="center" valign="middle"><p class="black">Ring type 
                      <select name="style" class="black" id="style">
                        <option value="" <?php if (!(strcmp("", $row_rs_style['style']))) {echo "selected=\"selected\"";} ?>>All</option>
                        <?php
do {  
?>
<option value="<?php echo $row_rs_style['style']?>"<?php if (!(strcmp($row_rs_style['style'], $row_rs_style['style'])))  ?>><?php echo $row_rs_style['style']?></option>
                        <?php
} while ($row_rs_style = mysql_fetch_assoc($rs_style));
  $rows = mysql_num_rows($rs_style);
  if($rows > 0) {
      mysql_data_seek($rs_style, 0);
	  $row_rs_style = mysql_fetch_assoc($rs_style);
  }
?>
                      </select>
                    Colour 
                    <select name="colour" class="black" id="colour">
                      <option value="" <?php if (!(strcmp("", $row_rs_colour['colour']))) {echo "selected=\"selected\"";} ?>>All</option>
                      <?php
do {  
?>
                      <option value="<?php echo $row_rs_colour['colour']?>"<?php if (!(strcmp($row_rs_colour['colour'], $row_rs_colour['colour'])))  ?>><?php echo $row_rs_colour['colour']?></option>
                      <?php
} while ($row_rs_colour = mysql_fetch_assoc($rs_colour));
  $rows = mysql_num_rows($rs_colour);
  if($rows > 0) {
      mysql_data_seek($rs_colour, 0);
	  $row_rs_colour = mysql_fetch_assoc($rs_colour);
  }
?>
                    </select>
                    Size 
                    <select name="size" class="black" id="size">
                      <option value="" <?php if (!(strcmp("", $row_rs_size['size']))) {echo "selected=\"selected\"";} ?>>All</option>
                      <?php
do {  
?>
                      <option value="<?php echo $row_rs_size['size']?>"<?php if (!(strcmp($row_rs_size['size'], $row_rs_size['size']))) ?>><?php echo $row_rs_size['size']?></option>
                      <?php
} while ($row_rs_size = mysql_fetch_assoc($rs_size));
  $rows = mysql_num_rows($rs_size);
  if($rows > 0) {
      mysql_data_seek($rs_size, 0);
	  $row_rs_size = mysql_fetch_assoc($rs_size);
  }
?>
                    </select>
Price Range
<select name="range" class="black" id="range">
  <option value="0-50">Less than £50</option>
  <option value="51-100">£51 - £100</option>
  <option value="101-200">£101-£200</option>
  <option value="201-300">£201 - £300</option>
  <option value="300-99999999">£300 +</option>
                      <option selected="selected">All</option>
                    </select>
                    </p></td>
                    <td width="62" align="left" valign="middle"><span class="black">
                      <input name="submit" type="image" id="submit" src="images/search.png" alt="Add to Basket" />
                    </span></td>
                  </tr>
                </table>
              </form></td>
              <td width="209" bgcolor="#BACACC"><table width="237" border="0" cellpadding="0" cellspacing="0" class="checkoutbar">
                <tr>
                  <td align="right" class="black">No of items:
                      <script language="JavaScript" src="http://www.romancart.com/cartinfo.asp?storeid=50225&amp;type=1" type="text/javascript"></script>
                    Total:
                    <script language="JavaScript" src="http://www.romancart.com/cartinfo.asp?storeid=50225&amp;type=2" type="text/javascript"></script>
                    <a href="http://www.romancart.com/cart.asp?storeid=50225" class="price">Checkout</a></td>
                  <td align="right"><img src="images/bag.png" alt="checkout" width="35" height="33" border="0" usemap="#Map4" /></td>
                </tr>
              </table>
                <map name="Map4" id="Map42">
                  <area shape="rect" coords="9,4,28,29" href="#" />
                </map>
                <map name="Map4" id="Map4">
                  <area shape="rect" coords="9,4,28,29" href="#" />
                </map></td>
            </tr>
          </table></td>
        </tr>
        <tr>
          <td><table cellpadding="0" cellspacing="1" >
            <tr>
              <?php
$rs_items_endRow = 0;
$rs_items_columns = 4; // number of columns
$rs_items_hloopRow1 = 0; // first row flag
do {
    if($rs_items_endRow == 0  && $rs_items_hloopRow1++ != 0) echo "<tr>";
   ?>
              <td><table cellpadding="0" cellspacing="0" >
                <tr>
                  <td><table border="0" cellpadding="0" cellspacing="0" class="frame">
                    <tr>
                      <td height="166" align="center" valign="middle"><table width="230" border="0" cellpadding="0" cellspacing="0" class="prodholder">
                        <tr>
                          <td align="center" valign="middle"><a href="product.php?id=<?php echo $row_rs_items['id']; ?>""><img src="uploadsmall/<?php echo $row_rs_items['image']; ?>" alt="" border="0" /></a></td>
                        </tr>
                      </table>
                        <a href="product.php"></a></td>
                    </tr>
                    <tr>
                      <td><table width="228" border="0" cellpadding="5" cellspacing="0" class="h40">
                        <tr>
                          <td valign="top" class="prodtitle"><table width="220" height="16" border="0" cellpadding="0" cellspacing="0">
                            <tr>
                              <td><span class="name"><?php echo $row_rs_items['name']; ?></span>
<input name="id" type="hidden" id="id" value="<?php echo $row_rs_items['id']; ?>" />
                                <br />
                                <span class="price">£<?php echo $row_rs_items['price']; ?></span></td>
                            </tr>
                          </table></td>
                        </tr>
                      </table></td>
                    </tr>
                  </table></td>
                </tr>
              </table></td>
              <?php  $rs_items_endRow++;
if($rs_items_endRow >= $rs_items_columns) {
  ?>
            </tr>
            <?php
 $rs_items_endRow = 0;
  }
} while ($row_rs_items = mysql_fetch_assoc($rs_items));
if($rs_items_endRow != 0) {
while ($rs_items_endRow < $rs_items_columns) {
    echo("<td>&nbsp;</td>");
    $rs_items_endRow++;
}
echo("</tr>");
}?>
          </table></td>
        </tr>
        <tr>
          <td>&nbsp;</td>
        </tr>
      </table>
    </div></td>
  </tr>
  <tr>
    <td><table width="410" border="0" cellpadding="10" cellspacing="0">
      <tr>
        <td class="copyright">© 2010 Leylas Design : Terms &amp; conditions : Delivery</td>
      </tr>
    </table></td>
  </tr>
</table>
</body>
</html>
<?php
mysql_free_result($rs_items);

mysql_free_result($rs_style);

mysql_free_result($rs_colour);

mysql_free_result($rs_size);
?>

Open in new window

Picture-7.png

ASKER CERTIFIED SOLUTION

john-formby

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BrighteyesDesign

ASKER

Thanks a million John, all working now!