Mysql Select Statement And/or

[real_illusions]

Hi all,I'm in the process of building up a simple search script to search through values in a mysql databased based upon some search criteria.The one problem is getting a value range. The user has a choose of selecting an upper and lower range of numbers. The database entries have 1 value for the lower range, and 1 value for the upper range. The idea is to find any entries that fall between the range in the database.Here is my php code:<?php$sql = "SELECT * FROM table WHERE county='" . $county . "' AND catagory='" . $catagory . "'"; if(isset($salaryrangeupper)) { $sql .= "AND (salaryhigher <= '" . $salaryrangeupper ."'"; } if(isset($salaryrangelower)) { $sql .= "OR salarylower >= '" . $salaryrangelower ."')"; }$sql.= " ORDER BY refnumber DESC";?>The code works, but doesnt make a difference in that if the entry has values of 20 and 60, searching between 15 and 50 doesnt return the result, neither does searching for between 30 and 70. I've found it only works if the values searched for are 20 and 60.Does anyone know how to solve this?Any help much appreciated

[Dalvador]

Hi all,I'm in the process of building up a simple search script to search through values in a mysql databased based upon some search criteria.The one problem is getting a value range. The user has a choose of selecting an upper and lower range of numbers. The database entries have 1 value for the lower range, and 1 value for the upper range. The idea is to find any entries that fall between the range in the database.Here is my php code:<?php$sql = "SELECT * FROM table WHERE county='" . $county . "' AND catagory='" . $catagory . "'"; if(isset($salaryrangeupper)) { $sql .= "AND (salaryhigher <= '" . $salaryrangeupper ."'"; } if(isset($salaryrangelower)) { $sql .= "OR salarylower >= '" . $salaryrangelower ."')"; }$sql.= " ORDER BY refnumber DESC";?>The code works, but doesnt make a difference in that if the entry has values of 20 and 60, searching between 15 and 50 doesnt return the result, neither does searching for between 30 and 70. I've found it only works if the values searched for are 20 and 60.Does anyone know how to solve this?Any help much appreciated

Hi, If I understand what you want correctly I think you can use the BETWEEN keyword,-I don't know a lot of PHP but I beleive the SQL would be...

SELECT * FROM table WHERE country = countryAND category = categoryAND salerylower BETWEEN saleryrangelower AND saleryrangehigherOR country = countryAND category = categoryAND saleryhigher BETWEEN saleryrangelower AND saleryrangehigher

-hope this helps.

[real_illusions]

Hi,Thanks for the reply.Its kinda helped but still not solved the problem.I have now got to a point where it seems to think, no matter what I put into the search criteria, all the results are within that range, even though they're not.<?php$sql = "SELECT * FROM table WHERE county='" . $county . "' AND catagory='" . $catagory . "'";if(isset($salaryrangeupper)) { $sql .= "AND salaryhigher BETWEEN'" . $salaryrangeupper ."' AND '" . $salaryrangelower ."'"; }$sql .= "OR county='" . $county . "' AND catagory='" . $catagory . "'"; if(isset($salaryrangelower)) { $sql .= "AND salarylower <= '" . $salaryrangelower ."'"; }$sql .= "OR county='" . $county . "' AND catagory='" . $catagory . "'";if(isset($salaryrangeupper)) { $sql .= "AND salaryhigher <= '" . $salaryrangeupper ."'"; }?>Have tried a variety of different comparisons between the upper and lower values but with no success. Does the query not stop after the one when it finds results? Or does it continue through, and if all the select queries have data, it chooses the last one? I'm mainly trying to work out why it thinks, when the database entries are 20 and 60, that when searching for 70 and 90, the entry with 20-60 is displayed.Thanks:)

[justsomeguy]

Print out the query and see what you're asking the database for. It doesn't think anything, it just does what you tell it to. You're the one who's supposed to be thinking.

[Dalvador]

Print out the query and see what you're asking the database for. It doesn't think anything, it just does what you tell it to. You're the one who's supposed to be thinking.

Hi,I don't think you need these lines...

<?php$sql = "SELECT * FROM table WHERE county='" . $county . "' AND catagory='" . $catagory . "'";if(isset($salaryrangelower)) {$sql .= "AND salarylower BETWEEN'" . $salaryrangelower ."' AND '" . $salaryrangehigher ."'";}$sql .= "OR county='" . $county . "' AND catagory='" . $catagory . "'"; if(isset($salaryrangehigher)) {$sql .= "AND salaryhigher BETWEEN'" . $salaryrangelower ."' AND '" . $salaryrangehigher ."'";}?>

-I'm making the assumption that both salaryrangelower & salaryrangehigher BOTH contain a value,is this correct, it would make sense if the user is checking for ranges.mark.

[real_illusions]

Got it solved.Definatly helped in printing the query, but I was doing that before I posted the 2nd post :)Turned out to be quite simple actually.<?php$sql = "SELECT * FROM table WHERE county='" . $county . "' AND catagory='" . $catagory . "'"; if(isset($salaryrangelower)) { $sql .= "AND salarylower <= '" . $salaryrangeupper ."'"; }if(isset($salaryrangeupper)) { $sql .= "AND salaryhigher >= '" . $salaryrangelower ."'"; }$sql.= " ORDER BY id DESC";?>Thats all I needed, helped to draw out on paper what i was after to create an image of what was needed.