select table filter by querystring php

Question

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 following:

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

Frank Farmer · Accepted Answer · 2011-05-10 18:16:34Z

3

If you don't want to constrain a column, simply omit it from your query
never insert a string from $_REQUEST directly into a query string -- classic SQL injection flaw.
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';

answered May 10, 2011 at 18:16

Frank Farmer

39.6k12 gold badges73 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Joshua - Pendo Over a year ago

only comment I have is to use (int) instead of mysql_real_escape_string. He states the fields are integers. Beside that, seems we had the same idea ;-)

hypervisor666 Over a year ago

@Frank Farmer, nice, and you totally beat me to it, wish I could type faster ... :) @PENDO, you also have a good method, although I think, and I might be wrong, when dealing with integers and MySQL you have to omit the single quotes around the value itself, the single quotes are for characters and strings, not integers.

Joshua - Pendo Over a year ago

just a thing I'm used to since years.. it works perfect but your point is taken, I must 'un-teach' myself to quote integers.

Frank Farmer Over a year ago

Quoting ints doesn't hurt too much in mysql (although I'm not immediately aware of any benefits either); mysql casts as needed (does it even raise a warning in this case?). However, you have to be more careful with some other databases -- postgres versions 8.3 and higher will refuse to automatically cast quoted ints in comparisons against int columns.

Andres Over a year ago

This works great but for some odd reason it returns all cities even if I filter it.

|

Joshua - Pendo · Accepted Answer · 2011-05-10 18:21:01Z

1

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

answered May 10, 2011 at 18:21

Joshua - Pendo

4,4016 gold badges39 silver badges52 bronze badges

1 Comment

Joshua - Pendo Over a year ago

seems I was a bit too late :D

hypervisor666 · Accepted Answer · 2011-05-10 18:25:30Z

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

SoreThumb · Accepted Answer · 2011-05-10 18:28:33Z

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.

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

Collectives™ on Stack Overflow

select table filter by querystring php

4 Answers 4

6 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest