Page 1 of 1

Search using Multiple Criteria

Posted: Tue Dec 16, 2008 1:43 am
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?

Re: Search using Multiple Criteria

Posted: Tue Dec 16, 2008 2:09 am
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"

Re: Search using Multiple Criteria

Posted: Tue Dec 16, 2008 2:56 am
by pcoder

Re: Search using Multiple Criteria

Posted: Tue Dec 16, 2008 2:59 am
by cavemaneca
i like pcoder's answer better, than mine.

Re: Search using Multiple Criteria

Posted: Tue Dec 16, 2008 12:46 pm
by ajoj11
thank you for your help

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

thx again :)