Search using Multiple Criteria

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ajoj11
Forum Newbie
Posts: 2
Joined: Sat Dec 13, 2008 2:56 pm

Search using Multiple Criteria

Post by ajoj11 »

Hi

I am new here and new to PHP programing so sry if this is a noob question but i tried to make a search function in PHP using a MySQL database and i have no more idea what to do.

Let say we have a database with ID, NAME, ADDRESS, AGE and COUNTRY.
So on one page i use a form where you can put this search criteria into but you can leave them blank if you want. Now the problem is that if i use:

Code: Select all

("SELECT * FROM user
WHERE NAME='$name' AND ADDRESS='$address' AND AGE='$age' AND COUNTRY='$country'
ORDER BY ID ASC")
 
and someone put a field blank i wont get any result :(
If i use OR instead of AND i will get results that i don't want.

Can anybody help me?
cavemaneca
Forum Commoner
Posts: 59
Joined: Sat Dec 13, 2008 2:16 am

Re: Search using Multiple Criteria

Post by cavemaneca »

Maybe this will help? Just a little something I wrote up once to sort somethings. If any of the fields are not supplied, they will not be submitted. Also, there might be an easier way to do this, but I never took the time to find out.

Code: Select all

if (isset($_GET['name'])) { 
  $name = $_GET['name'];
}
if (isset($_GET['address'])) {
  $address= $_GET['address'];
}
if (isset($_GET['age'])) {
  $age = $_GET['age'];
}
if (isset($_GET['country'])) {
  $country = $_GET['country'];
}
if (!isset($_GET['name']) && !isset($_GET['address']) && !isset($_GET['age']) && !isset($_GET['country'])) {
  $name = '*';
  $address = '*';
  $age = '*';
  $country = '*';
}
$query = "SELECT * FROM `user`";
if ($name != '*' || $address != '*' || $age != '*' || $country != '*') {
  $query .= " WHERE ";
    if ($name != '*'){
      $query .= "`name` = '".$name."'";
      if ($address != '*' || $age != '*' || $country != '*') {
          $query .= " AND ";
        }
    }
    if ($address != '*') {
        $query .= "`address` = '".$address."'";
      if ($age != '*' || $country != '*') {
          $query .= " AND ";
        }
    }
    if ($age != '*') {
        $query .= "`age` = '".$age."'";
      if ($country != '*') {
          $query .= " AND ";
        }
    }
    if ($country != '*') {
        $query .= "`country` = '".$country."'";
    }
}
$query .= " ORDER BY ID ASC"
User avatar
pcoder
Forum Contributor
Posts: 230
Joined: Fri Nov 03, 2006 5:19 am

Re: Search using Multiple Criteria

Post by pcoder »

cavemaneca
Forum Commoner
Posts: 59
Joined: Sat Dec 13, 2008 2:16 am

Re: Search using Multiple Criteria

Post by cavemaneca »

i like pcoder's answer better, than mine.
ajoj11
Forum Newbie
Posts: 2
Joined: Sat Dec 13, 2008 2:56 pm

Re: Search using Multiple Criteria

Post by ajoj11 »

thank you for your help

i used a solution that i similar cavemaneca's one.

thx again :)
Post Reply