CODEWITHSUNDEEP
phpmysql
Cristine Charming
Cristine Charming

Reputation: 3

PHP multi option search query ....?

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.

Upvotes: 0

Views: 4126

Answers (2)

elxordi
elxordi

Reputation: 486

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'];
}

Upvotes: 2

Sumesh TG
Sumesh TG

Reputation: 450

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

Upvotes: 0

Related Questions