PHP multi option search query ....?

Question

I'm trying with search box, I created a form as below:

<form method="post" action="search.php">
 <select name="purpose">
 <option value="" selected="selected">-any-</option>
 <option value="For Sale">For Sale</option>
 <option value="For Rent">For Rent</option>
 </select>

 <select name="type">
 <option value="" selected="selected">-any-</option>
 <option value="Bungalow">Bungalow</option>
 <option value="Apartment">Apartment</option>
 </select>

 <select name="location">
 <option value="" selected="selected">-any-</option>
 <option value="Norway">Norway</option>
 <option value="Itley">Itley</option>
    </select>
 <input type="submit" value="Search">
</form>

Search.php

I'm trying with these queries but getting problem:

$purpose=$_POST['purpose'];
$type=$_POST['type'];
$location=$_POST['location'];

If I put AND query like this:

SELECT * FROM test WHERE purpose='$purpose' AND location='$location' AND type='$type'

then it not filter one by one result its appear blank.

If I put OR query like this:

SELECT * FROM test WHERE purpose='$purpose' OR location='$location' OR type='$type'

Then it filter mix results.

I want if all selected it filter (purpose >> type >> location) AND if one selected then filter by this but exact result else show

Result not found!

EDIT Adding update made in comments:

I'm doing like this but it showing error:

$qry = "SELECT * FROM test WHERE 1=1";

if($purpose!="")
  $qry .= " AND purpose='$purpose'";

if($location!="")
  $qry .= " AND location='$location'";

if($type!="")
  $qry .= " AND type='$type'";

while ($row = mysql_fetch_array($sql)) 

echo $row['purpose']; 

echo $row['location']; 

echo $row['type'];

I want if not match display result not found else it filter by all and one by one.

elxordi · Accepted Answer · 2012-08-14 09:00:59Z

2

Put that after obtaining the variables:

$sql = array();

if (!empty($purpose)) {
    $sql[] = "purpose='$purpose'";
}

if (!empty($type)) {
    $sql[] = "type='$type'";
}

if (!empty($location)) {
    $sql[] = "location='$location'";
}

$sql = implode(' AND ', $sql);
$sql = "SELECT * FROM test" . (!empty($sql)?: " WHERE " . $sql: '');

EDIT: After seeing your comments

First, you should make the connection to the database with the mysql_connect functions. Let's say, for example, that you have this piece of code:

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');

if (!$link) {
    die('Could not connect: ' . mysql_error());
}

In the $link var now you have your mysql handle. This piece of code must go at the first of every page where you wanna use the mysql connection. Once you have the SQL sentence (in the code I first wrote, the $sql var), use this after:

$result = mysql_query($sql);

if (mysql_num_rows($result) === 0) {
    echo 'Result not found';
}

while ($row = mysql_fetch_array($result)) {
   echo $row['purpose'] . '<br/>';
   echo $row['location'] . '<br/>';
   echo $row['type'];
}

edited Aug 14, 2012 at 9:00

answered Aug 7, 2012 at 10:20

elxordi

4863 silver badges11 bronze badges

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

5 Comments

Waygood Over a year ago

+1 I use this method too, but with mysql_real_escape_string() aswell

elxordi Over a year ago

That's correct. mysql_real_escape_string() should be used always. The reason i didn't put it is that he said that he's new to php, so I can't know if he has magic_quotes enabled or not. So, if enabled, he will have problems with the mysql_real_escape_string() function.

Cristine Charming Over a year ago

sir i'm doing like this but it showing error '$sql = array($purpose, $location, $type); if (!empty($purpose)) { $sql[] = "purpose='$purpose'"; } if (!empty($Pro_type)) { $sql[] = "type='$type'"; } if (!empty($location)) { $sql[] = "location='$location'"; } $sql = implode(' AND ', $sql); $sql = "SELECT * FROM test" . (!empty($sql)? " WHERE " . $sql: '');

Cristine Charming Over a year ago

while ($row = mysql_fetch_array($sql)) { echo $row['purpose']."</br>"; echo $row['location']."<br>"; echo $row['type']; }

elxordi Over a year ago

Answered. Please, tell me if any doubt. If you liked the answer, please mark it as correct. Sorry for the delay.

Sumesh TG · Accepted Answer · 2012-08-07 10:16:33Z

0

$qry = "SELECT * FROM test WHERE 1=1";

if($purpose!="")
  $qry .= " AND purpose='$purpose'";

if($location!="")
  $qry .= " AND location='$location'";

if($type!="")
  $qry .= " AND type='$type'";

answered Aug 7, 2012 at 10:16

Sumesh TG

4501 gold badge4 silver badges15 bronze badges

Collectives™ on Stack Overflow

PHP multi option search query ....?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related