Hello all.

I have a search form which I'd like to return values based on values entered. Until now I had been returning results based on values equal to those entered. Basically if the values in the search form entered were:

Nationality:British
Sex:Male
Prefecture:Tokyo
Price:3000

All results returned from the search would be British males from Tokyo whose price is 3000.

However I wished to change the query so that results returned would include values less than or equal to the price entered.

My original query was as follows:

PHP Code:

$query_m = "SELECT TestTable.autonumber, TestTable.sex, TestTable.firstname, TestTable.nationality, TestTable.private_price FROM TestTable WHERE TestTable.language LIKE '%" . $_REQUEST["language"] . "%' AND TestTable.prefecture LIKE '%" . $_REQUEST["prefecture"] . "%' AND TestTable.sex LIKE '%" . $_REQUEST["sex"] . "%'AND TestTable.nationality LIKE '%" . $_REQUEST["nationality"] . "%'  AND TestTable.mm LIKE '%" . $_REQUEST["mm"] . "%'  AND TestTable.ma LIKE '%" . $_REQUEST["ma"] . "%'  AND TestTable.me LIKE '%" . $_REQUEST["me"] . "%'  AND TestTable.tm LIKE '%" . $_REQUEST["tm"] . "%'  AND TestTable.ta LIKE '%" . $_REQUEST["ta"] . "%'  AND TestTable.te LIKE '%" . $_REQUEST["te"] . "%'  AND TestTable.wm LIKE '%" . $_REQUEST["wm"] . "%'  AND TestTable.wa LIKE '%" . $_REQUEST["wa"] . "%'  AND TestTable.we LIKE '%" . $_REQUEST["we"] . "%'  AND TestTable.thm LIKE '%" . $_REQUEST["thm"] . "%'  AND TestTable.tha LIKE '%" . $_REQUEST["tha"] . "%'  AND TestTable.the LIKE '%" . $_REQUEST["the"] . "%'  AND TestTable.fm LIKE '%" . $_REQUEST["fm"] . "%'  AND TestTable.fa LIKE '%" . $_REQUEST["fa"] . "%'  AND TestTable.fe LIKE '%" . $_REQUEST["fe"] . "%'  AND TestTable.sm LIKE '%" . $_REQUEST["sm"] . "%'  AND TestTable.sa LIKE '%" . $_REQUEST["sa"] . "%'  AND TestTable.se LIKE '%" . $_REQUEST["se"] . "%'  AND TestTable.sum LIKE '%" . $_REQUEST["sum"] . "%'  AND TestTable.sua LIKE '%" . $_REQUEST["sua"] . "%'  AND TestTable.sue LIKE '%" . $_REQUEST["sue"] . "%' AND 
TestTable.private_price LIKE '%" . $_REQUEST["private_price"] . "%'"; 


in particular note:
AND

PHP Code:

TestTable.private_price LIKE '%" . $_REQUEST["private_price"] . "%'"; 


This returned all values as expected.
However, I tried replacing this private price to include equal to or less than values, but had no success. I tried the following (in place of the above):

TestTable.private_price <= .'$_REQUEST["private_price"] . '

TestTable.private_price =< .'$_REQUEST["private_price"] . '

TestTable.private_price =< " . $_REQUEST["private_price"] . " ERROR (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 '=< 1000' at line 2)

TestTable.private_price <= '%" . $_REQUEST["private_price"] . "%'

TestTable.private_price <= $_REQUEST["private_price"]

These all returned errors....

Can anyone help???


Thanks for reading this guys

Did you try:

" .... TestTable.private_price <= " . $_REQUEST["private_price"] . " .... "

(the dots just represent the rest of your query.)

What exact errors did they return?

Of course, one thing I would do (just to reduce the confusion of the code and assure yourself that none of the errors are resulting from the mis-handling of the $_REQUEST global) is to assign it to a local variable first then simply include it within the double quoted sql composition:

$privPrice = $_REQUEST[private_price];

$sql = "......... TestTable.private_price <= $privPrice";

Of course, the other issue here could be using the <= with a string -- that could cause some unexpected results unless you are absolutely certain of the homogeny of all the values in that column. I would suggest storing the price as a float and composing the sql without quotes around the $privPrice value.

HTH.

Thanks Oberon, Kyrnt (sorry for submitting twice - not sure what I was doing)

Oberon, as you suggested, I tried

TestTable.private_price <= " . $_REQUEST["private_price"] . "

and it worked!....I'm a bit stunned as I thought I'd tried all the variations.

Well thanks so much. Your response has resolved something I was getting concerned that I might never fix.

Thanks also Kyrnt - Your advice is much appreciated.

Quick observation - On returning to Dreamweaver I see that my recordset (m) associated with the above query(m) no longer appears in my bindings panel and a red exclamation mark appears nex to the (m) repeat region server behaviour in the server behaviour panel.

However, if the set-up works, I'm not too worried about this!