CODEWITHSUNDEEP
phpdrop-down-menufilter
Kumar Papneja
Kumar Papneja

Reputation: 1

PHP search form filters using multiple drop down

Here is revised code. Still no luck ! pls. help

<html>
<body>

<?php

 $mysqli = new mysqli("localhost", "root", "password", "test");

 $whereClauses = '';
 $numLocations = count($_POST['Locations']);
 $numJobs = count($_POST['Jobs']);
 $i = 0;
 if (! empty($_POST['Locations'])) {
  foreach ($_POST['locations'] as $location) {
    $whereClauses .="Locations='".mysql_real_escape_string($location)."'";
    if ($i++ == $numLocations) {
     $whereClauses .= " AND";
    }
  }
  }
  if (! empty($_POST['Jobs'])) {
  foreach ($_POST['Jobs'] as $job) {
    $whereClauses .="Jobs='".mysql_real_escape_string($job)."'";
  }
  if ($i++ == $numJobs) {
     $whereClauses .= " AND";
  }
   }
  $sql = "SELECT * FROM mytable '".$whereClauses."' ORDER BY id DESC '".$limit."'";

$result=mysql_query($sql);

while ($row = mysql_fetch_array($result)) {
echo $row['Locations'];
echo $row['Jobs'];
}

?>
 </body>
</html>

=============================================== I created a HTML and PHP file to filter web form data based on multiple drop down filters. Here goes the form. When I run the form and PHP, I don't see any results in the browser. There is no error either. I'm working on an example posted by another forum member. Please help. Thanks in advance. 

<form action="showJobs_new.php" method="post">
<select name="Locations">
<option value="" selected="selected">All Locations</option>
<option value="arizona">Arizona</option>
<option value="alaska">Alaska</option>
</select>
<select name="Jobs">
<option value="" selected="selected">All jobs</option>
<option value="Carpenter">Carpenters</option>
<option value="Plumbers">Plumbers</option>
</select>
<input type="submit" value="search jobs" />
</form>

showJobs_new.php:

<html>
<body>

<?php

$username="root";
$password="password";
$database="test";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$whereClauses = array(); 
if (! empty($_POST['Locations'])) $whereClauses[] ="Locations='".mysql_real_escape_string($_POST['Locations'])."'"; 
if (! empty($_POST['Jobs'])) $whereClauses[] ="Jobs='".mysql_real_escape_string($_POST['Jobs'])."'"; 
$where = ''; 
if (count($whereClauses) > 0) { $where = ' WHERE '.implode(' AND ',$whereClauses); } 

$sql = mysql_query("SELECT * FROM mytable ORDER BY id DESC $limit" .$where);  

$result=mysql_query($sql);
or die("Error: ".mysql_error()."<br />Query: ".$sql);

while ($row = mysql_fetch_assoc($result)) {
echo $row['Locations'];
echo $row['Jobs'];
}

?>
</body>
</html>

Upvotes: 0

Views: 3525

Answers (2)

Awais Qarni
Awais Qarni

Reputation: 18006

Hi dear You have two errors in query.

1-this is because you are putting wrong clause order. You need to learn what is the priority of clauses in query. When you run query, order of clause should look like

  • Select

  • From

  • Where

  • Group by

  • Having

  • Order BY

  • Limit

2- using mysql_query two times as suggested by user1599669

So your query should looks like

 $sql = mysql_query("SELECT * FROM mytable $where ORDER BY id DESC $limit");

For more reference read from mysql dev.

Upvotes: 1

User 99x
User 99x

Reputation: 1021

Change the query to 

$sql = mysql_query("SELECT * FROM mytable " .$where." ORDER BY id DESC $limit");

The order of the query seems to be wrong.

Upvotes: 3

Related Questions