• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

Problems with my search box returning values


I am working on a complex search form for a dynamic php website. One of the search boxes in the form is called price, and I want the form to return all values of entries in the SQL table which equal or are greater to the value specified in the search box. The line of code I am using for this is shown below, but it seems not to be working - i.e it does not return the values which it should. What's pretty strange is that I've used this line of code on an almost identical search box in a different page and it seemed to work. The line I use is...
AND ChinaTEACHERS.privateprice <=".$_REQUEST["privateprice"]."";

(ChinaTEACHERS is the table name, privateprice is the field name  
  • 4
  • 2
  • 2
  • +1
1 Solution
SQL is sometimes critical against wrong quotation,
also if the expected value is an integer or float then no quotation necessary, but
not quite sure with that, try

AND ChinaTEACHERS.privateprice <= $_REQUEST['privateprice']";

If you want equal to or greater than you're using the wrong symbol :P

Also try enclosing the price in single quotes, and you should use mysql_real_escape_string to avoid injection attacks:

AND ChinaTEACHERS.privateprice >='".mysql_real_escape_string($_REQUEST["privateprice"])."'";
AdamTrying to learn phpAuthor Commented:
Thanks Xbrry and under_dog. I tried both of these quickly and it seems not to have made a difference. I've got to go out now but when I am next online I'll take another look at the database itself to see if the problem lies in there perhaps? Would it be helpful if I post some more of the code? Thanks

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

>>Would it be helpful if I post some more of the code? Thanks

Unless one is expert in telepathy it is always helpful to read more of your data ; ))
RobOwner (Aidellio)Commented:
i think a combination of the 2 might work no quotes and the right sign

AND ChinaTEACHERS.privateprice >=".mysql_real_escape_string($_REQUEST["privateprice"]);
AdamTrying to learn phpAuthor Commented:
Thanks for these responses.

Apologies that I've not been able to check out these potential solutions and alocate points - my PC with all the dreamweaver stuff is not yet online due to me moving flats. I'll respond to these suggestions and award points asap - sorry
AdamTrying to learn phpAuthor Commented:
Thanks everyone for helping with this so far. Tagit, I got an MySQL error message when I tried your variation unfortunately. Underdog's variation now works - i.e. doesn't bring me any error message but still isn't returning the values I would expect (i.e values greater than the figure selected in the privateprice box.

I've added some more code below. Hope this helps. Many thanks

A code snippet used for the private price box is:

 <td colspan="3"><select name="privateprice" id="privateprice">
                      <option value="">list all</option>
                      <option value="15">15</option>
                      <option value="30">30</option>
                      <option value="45">45</option>
                      <option value="60">60</option>
                      <option value="75">75</option>

Code from the php selection part of the site is:

<?php require_once('../Connections/connectionc.php'); ?>
$currentPage = $_SERVER["PHP_SELF"];
$maxRows_m = 10;
$pageNum_m = 0;
if (isset($_GET['pageNum_m'])) {
  $pageNum_m = $_GET['pageNum_m'];
$startRow_m = $pageNum_m * $maxRows_m;

mysql_select_db($database_connectionc, $connectionc);
$query_m = "SELECT ChinaTEACHERS.autonum, ChinaTEACHERS.sex, ChinaTEACHERS.firstname, ChinaTEACHERS.teachingprovince, ChinaTEACHERS.privateprice FROM ChinaTEACHERS WHERE ChinaTEACHERS.nationality LIKE '%" . $_REQUEST["nationality"] . "%' AND ChinaTEACHERS.teachingprovince LIKE '%" . $_REQUEST["teachingprovince"] . "%' AND ChinaTEACHERS.sex LIKE '%" . $_REQUEST["sex"] . "%'AND ChinaTEACHERS.nativelanguage LIKE '%" . $_REQUEST["nativelanguage"] . "%'AND ChinaTEACHERS.blankagain LIKE '%" . $_REQUEST["blankagain"] . "%'AND ChinaTEACHERS.teachingprovince LIKE '%" . $_REQUEST["teachingprovince"] . "%'AND ChinaTEACHERS.teacherroom LIKE '%" . $_REQUEST["teacherroom"] . "%'AND ChinaTEACHERS.studentroom LIKE '%" . $_REQUEST["studentroom"] . "%'AND ChinaTEACHERS.publicplace LIKE '%" . $_REQUEST["publicplace"] . "%'AND ChinaTEACHERS.privateprice LIKE '%" . $_REQUEST["privateprice"] . "%'AND ChinaTEACHERS.mandarinability LIKE '%" . $_REQUEST["mandarinability"] . "%'AND ChinaTEACHERS.cantoneseability LIKE '%" . $_REQUEST["cantoneseability"] . "%'AND ChinaTEACHERS.photo LIKE '%" . $_REQUEST["photo"] . "%'AND ChinaTEACHERS.mm LIKE '%" . $_REQUEST["mm"] . "%'  AND ChinaTEACHERS.ma LIKE '%" . $_REQUEST["ma"] . "%'  AND ChinaTEACHERS.me LIKE '%" . $_REQUEST["me"] . "%'  AND ChinaTEACHERS.tm LIKE '%" . $_REQUEST["tm"] . "%'  AND ChinaTEACHERS.ta LIKE '%" . $_REQUEST["ta"] . "%'  AND ChinaTEACHERS.te LIKE '%" . $_REQUEST["te"] . "%'  AND ChinaTEACHERS.wm LIKE '%" . $_REQUEST["wm"] . "%'  AND ChinaTEACHERS.wa LIKE '%" . $_REQUEST["wa"] . "%'  AND ChinaTEACHERS.we LIKE '%" . $_REQUEST["we"] . "%'  AND ChinaTEACHERS.thm LIKE '%" . $_REQUEST["thm"] . "%'  AND ChinaTEACHERS.tha LIKE '%" . $_REQUEST["tha"] . "%'  AND ChinaTEACHERS.the LIKE '%" . $_REQUEST["the"] . "%'  AND ChinaTEACHERS.fm LIKE '%" . $_REQUEST["fm"] . "%'  AND ChinaTEACHERS.fa LIKE '%" . $_REQUEST["fa"] . "%'  AND ChinaTEACHERS.fe LIKE '%" . $_REQUEST["fe"] . "%'  AND ChinaTEACHERS.sm LIKE '%" . $_REQUEST["sm"] . "%'  AND ChinaTEACHERS.sa LIKE '%" . $_REQUEST["sa"] . "%'  AND ChinaTEACHERS.se LIKE '%" . $_REQUEST["se"] . "%'  AND ChinaTEACHERS.sum LIKE '%" . $_REQUEST["sum"] . "%'  AND ChinaTEACHERS.sua LIKE '%" . $_REQUEST["sua"] . "%'  AND ChinaTEACHERS.sue LIKE '%" . $_REQUEST["sue"] . "%' AND ChinaTEACHERS.privateprice >='".mysql_real_escape_string($_REQUEST["privateprice"])."'";
$query_limit_m = sprintf("%s LIMIT %d, %d", $query_m, $startRow_m, $maxRows_m);
$m = mysql_query($query_limit_m, $connectionc) or die(mysql_error());
$row_m = mysql_fetch_assoc($m);

if (isset($_GET['totalRows_m'])) {
  $totalRows_m = $_GET['totalRows_m'];
} else {
  $all_m = mysql_query($query_m);
  $totalRows_m = mysql_num_rows($all_m);
$totalPages_m = ceil($totalRows_m/$maxRows_m)-1;

$colname_d = "-1";
if (isset($_GET['recordID'])) {
  $colname_d = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
mysql_select_db($database_connectionc, $connectionc);
$query_d = sprintf("SELECT autonum,  firstname, surname, sex, age, nationality, nativelanguage, otherlanguage, teachingexperience, schools, languagequalifications, otherqualifications, otherexperience, mandarinability, cantoneseability, likes, dislikes, movie, teachingprovince, `teaching area`, teacherroom, studentroom, publicplace, other, privateprice, firstlesson, grouplesson, message, telephone, email, password, mm, ma, me, tm, ta, te, wm, wa, we, thm, tha, the, fm, fa, fe, sm, sa, se, `sum`, sua, sue, imagelocation, video, photo, v, blank, blankagain FROM ChinaTEACHERS WHERE autonum = %s", $colname_d);
$d = mysql_query($query_d, $connectionc) or die(mysql_error());
$row_d = mysql_fetch_assoc($d);
$totalRows_d = mysql_num_rows($d);

$queryString_m = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_m") == false && 
        stristr($param, "totalRows_m") == false) {
      array_push($newParams, $param);
  if (count($newParams) != 0) {
    $queryString_m = "&" . htmlentities(implode("&", $newParams));
$queryString_m = sprintf("&totalRows_m=%d%s", $totalRows_m, $queryString_m);
?><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
You have privateprice twice in your SQL statement:

Once here:

ChinaTEACHERS.privateprice LIKE '%" . $_REQUEST["privateprice"] . "%'

and then again here:

ChinaTEACHERS.privateprice >='".mysql_real_escape_string($_REQUEST["privateprice"])."

If you remove the first once, you should be good to go ;)
AdamTrying to learn phpAuthor Commented:
Hi Under dog. It seems to be working fine now. Sorry for my stupidity - I'm surprised I couldn't notice this myself.

Many thanks to you - and everyone else who tried to help me with this.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now