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

Problems with my search box returning values

Hello,

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  
0
ayounis
Asked:
ayounis
  • 4
  • 2
  • 2
  • +1
1 Solution
 
xberryCommented:
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']";

0
 
under_dogCommented:
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"])."'";
0
 
ayounisAuthor 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

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
xberryCommented:
>>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 ; ))
0
 
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"]);
0
 
ayounisAuthor 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
0
 
ayounisAuthor 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'); ?>
<?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"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
0
 
under_dogCommented:
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 ;)
0
 
ayounisAuthor 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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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