CODEWITHSUNDEEP
sql
jaw
jaw

Reputation: 153

results query that handles AND, OR as well as "any values"

I have a search page with a form where a user can set values for 2 different field (name and city). It works fine, but for both fields, the user can choose "ANY" as an option for one when they only want results for that city. But if they choose ANY, they will get 0 results because my query is an AND query. I can't figure out how to construct this query so it shows results for both values or just for one value if they choose ANY for the second field. I can't use "OR" because they might also want to have results for specific names in cities as well. What is the best query to allow for all options?

 <?
 //Connection

 $name = $_GET['name'];
 $city = $_GET['city'];


 $link = mysqli_connect($dbhost, $dbuser, $dbpass) or die('MySQL 
 error:'.mysql_error());
 mysqli_select_db($link, $dbname) or die('MySQL error:'.mysql_error());

 $query = "(SELECT * FROM people where name='$name' and city='$city')";

 $result = mysqli_query($link, $query);

 $num_rows = mysqli_num_rows($result);
 echo "$num_rows Rows\n";

 echo "<table border='1'>
 <tr>
 <th>School</th>
 <th>City</th>

 </tr>";

 /* fetch associative array */
 while ($row = mysqli_fetch_assoc($result)) {
 echo "<tr>";
 echo "<td>" . $row['name'] . "</td>";
 echo "<td>" . $row['city'] . "</td>";
 echo "</tr>";
 }

 /* free result set */
 mysqli_free_result($result);

 echo "</table>";

 mysqli_close($link);
 ?>

Upvotes: 0

Views: 50

Answers (3)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

Please look at this demo and translate to php code

http://rextester.com/live/XSPV71393

SELECT
  *
FROM mytable
WHERE name_ LIKE (CASE
  WHEN @searchname = 'any' THEN '%%'
  ELSE concat('%', @searchname, '%')
END)
AND city LIKE (CASE
  WHEN @searchcity = 'any' THEN '%%'
  ELSE concat('%', @searchcity, '%')
END);

so php code should be something like below

$query = "SELECT  * FROM mytable WHERE name_ LIKE (CASE   WHEN ".$name." = 'any' THEN '%%'   ELSE concat('%', ".$name.", '%') END) AND city LIKE (CASE   WHEN ".$city." = 'any' THEN '%%'  ELSE concat('%',".$city"., '%') END);";

Upvotes: 0

jwolf
jwolf

Reputation: 938

Try this:

$query = "(SELECT * FROM people where (name='$name' or '$name' = 'ANY') and (city='$city' or '$city' = 'ANY')";

Updated

Sorry, it's the quotes around the ANY. Try this instead:

$query = "(SELECT * FROM people where (name='$name' or '$name' = \'ANY\') and (city='$city' or '$city' = \'ANY\')";

-or-

$query = "(SELECT * FROM people where (name='$name' or '$name' = \\\'ANY\\\') and (city='$city' or '$city' = \\\'ANY\\\')"

depending on whatever other escaping may be going on.

Also be aware of code injection, hopefully those user-entered strings were already escaped with something such as mysqli_real_escape_string().

I hope this helps.

Upvotes: 2

Emilio Lucas Ceroleni
Emilio Lucas Ceroleni

Reputation: 1598

 Please try the following:

$query = "(SELECT * FROM people where name =  case  '$name' when 'ANY' then name else '$name' end and city= case '$city' when 'ANY' then city else '$city' end)";

Upvotes: 0

Related Questions