select table filter by querystring php

ok so I've been trying for a while now to get this to work but there has to be a better solution than what im thinking about. I'm fairly new to php/mysql so not sure how to do the fol开发者_运维百科lowing:

I have a search box that contains dropdowns for country, state, city Now if the user only selects country and clicks on search it needs to filter the select by just country and show everything else.

if(!empty($_REQUEST['city']))
    $city = $_REQUEST['city'];
else
    $city= "%";

if(!empty($_REQUEST['state']))
    $state= $_REQUEST['state'];
else
    $state= "%";

if(!empty($_REQUEST['country']))
    $country= $_REQUEST['country'];

select * from table where country = $country and state = $state and city = $city

problem with this is that those columns are ints so I can't use the "%" to filter it. I hope I was able to explain it any help is more than welcome. Thanks in advance

---

1. If you don't want to constrain a column, simply omit it from your query
2. never insert a string from $_REQUEST directly into a query string -- classic SQL injection flaw.
3. you probably want to enforce some sort of limit, lest the query return every single result in your database.

example:

<?php
$conditions = array();

if(!empty($_REQUEST['city']))
    $conditions[] = "city = " . mysql_real_escape_string($_REQUEST['city']);

if(!empty($_REQUEST['state']))
    $conditions[] = "state = " . mysql_real_escape_string($_REQUEST['state']);

if(!empty($_REQUEST['country']))
    $conditions[] = "country = " . mysql_real_escape_string($_REQUEST['country']);

$sql = 'select * from table ';
if(!empty($conditions))
    $sql .= ' where '. implode(' AND ', $conditions);
$sql .= ' LIMIT 1000';

---

$where = array();
if(!empty($_REQUEST['city'])) $where[] = "city = '".(int)$_REQUEST['city']."'";
if(!empty($_REQUEST['state'])) $where[] = "state = '".(int)$_REQUEST['state']."'";
if(!empty($_REQUEST['country'])) $where[] = "country = '".(int)$_REQUEST['country']."'";

$wherestring = if(count($where) != 0) ? " WHERE ".implode(' AND ', $where) : "" ;

$query = "SELECT * FROM table".$wherestring;

---

You may want to consider writing several query strings, one for just country, one for state and country and one for city, state and country. Alternatively you can assemble the query string based upon the different parameters you have to work with.

Example:

if(isset() || isset() || isset() ) //make sure at least one is set
{
  $query_string = "SELECT * FROM table WHERE ";

  if(isset($_REQUEST['country']))
  {
      $country = $_REQUEST['country'];
      $query_string .= " country = $country";
  }

  if(isset($_REQUEST['state']))
  {
      $state = $_REQUEST['state'];
      $query_string .= " state = $state";
  }

  if(isset($_REQUEST['city']))
  {
      $city = $_REQUEST['city'];
      $query_string .= " city = $city";
  }
}
else
{
    //Else, if none are set, just select all the entries if no specifications were made
    $query_string = "SELECT * FROM table";
}

  //Then run your query...

So in english, the first thing you do is check your parameters, making sure you have something to work with before you try and concatenate empty variables together. Then you make the base query string (as long as we have parameters) and leave it open ended so that we can add whatever parameters you need. Next check each parameter, and if it is set, then concatenate that parameter onto the end of the query string. Finally process the query by sending it to the SQL server.

Good luck!

h

---

Here're my suggestions.

I'm giving you an answer, even though you have three already. I'm thinking mine may be easier on the code-eyes.

1. Do not use the raw $_REQUEST value, as it's likely that the user can poison your database by feeding it fake $_REQUEST data. Though there may be better ways to do it, keep in mind the command "mysql_real_escape_string($string)".
2. A common method I've seen for solving this problem is written below. (The implode idea, basically. Frank Farmer does it as well in his.)

-

$__searchWheres = array(); //Where we'll store each requirement used later
foreach( array('city','state','country') as $_searchOption) {
   if ( ! empty( $_REQUEST[$_searchOption] ) ) {
       $__searchWheres[] = $_searchOption . '= "' . mysql_real_escape_string( $_REQUEST[$_searchOption] ) . '"';
   }
}
$__query = 'select * from table' . (count($__searchWheres) > 0 ? ' WHERE ' . implode(' AND ',$__searchWheres) : '');  //Implode idea also used by Frank Farmer
//Select from the table, but only add the 'WHERE' key and where data if we have it.
mysql_query($__query);