Problems with my search box returning values

Posted on 2007-07-21
Last Modified: 2013-12-12

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  
Question by:ayounis
    LVL 14

    Expert Comment

    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']";

    LVL 9

    Expert Comment

    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"])."'";

    Author Comment

    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

    LVL 14

    Expert Comment

    >>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 ; ))
    LVL 42

    Expert Comment

    by:Rob Jurd, EE MVE
    i think a combination of the 2 might work no quotes and the right sign

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

    Author Comment

    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

    Author Comment

    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.firstname, ChinaTEACHERS.teachingprovince, ChinaTEACHERS.privateprice FROM ChinaTEACHERS WHERE ChinaTEACHERS.nationality LIKE '%" . $_REQUEST["nationality"] . "%' AND ChinaTEACHERS.teachingprovince LIKE '%" . $_REQUEST["teachingprovince"] . "%' AND 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 LIKE '%" . $_REQUEST["photo"] . "%'AND LIKE '%" . $_REQUEST["mm"] . "%'  AND LIKE '%" . $_REQUEST["ma"] . "%'  AND LIKE '%" . $_REQUEST["me"] . "%'  AND 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 LIKE '%" . $_REQUEST["fm"] . "%'  AND ChinaTEACHERS.fa LIKE '%" . $_REQUEST["fa"] . "%'  AND ChinaTEACHERS.fe LIKE '%" . $_REQUEST["fe"] . "%'  AND LIKE '%" . $_REQUEST["sm"] . "%'  AND LIKE '%" . $_REQUEST["sa"] . "%'  AND 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"
    LVL 9

    Accepted Solution

    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 ;)

    Author Comment

    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.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now