asked on

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

xberry

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

under_dog

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

Adam

ASKER

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

xberry

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

Rob

i think a combination of the 2 might work no quotes and the right sign

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

Adam

ASKER

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

Adam

ASKER

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>

ASKER CERTIFIED SOLUTION

under_dog

membership

This solution is only available to members.

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

Start Free Trial

Adam

ASKER

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.