Filter Using AJAX, PHP and MySql

Question

I would like to ask how can I filter my results by 3 dropdown list independently. For example, I will filter it by 1 dropdown and then when I select another dropdown it will refined the results same as when I select the 3rd one.

Here's my code for filtering.php:

<?php
$q=$_GET['q'];
$a=$_GET['a'];
$b=$_GET['b'];

$con = mysql_connect('localhost', 'root', '');
if (!$con)
{
die('Could not connect: ' . mysql_error($con));
}

mysql_select_db("ooh", $con);
$strSQL="SELECT * FROM files WHERE type = '".$q."' AND price = '".$a."' AND location = '".$b."'";
$rs = mysql_query($strSQL,$con);

while($info = mysql_fetch_array($rs)) {

Print "<div id='filtername' class='fluid'>"; 
Print "<img src='images/ad_mock4.jpg'  alt=''/>";
Print "<div class='box'>"; 
Print "<h2>".$info['title']. "<h2>"; 
Print "<p>".$info['shortdescription']. "</p>"; 
Print "<p class='cat'><strong>Price:</strong>".$info['price'] . "</p>";
Print "<p class='cat'><strong>Duration:</strong>".$info['duration'] . "</p>"; 
Print "<p class='cat'><strong>Material:</strong>".$info['material'] . "</p>";
Print "<p class='cat'><strong>Type:</strong>".$info['type'] . "</p>"; 
Print "<p class='cat'><strong>Location:</strong>".$info['location'] . "</p>";
Print "<p class='cat'><strong>Size:</strong>".$info['size'] . "</p>";
Print "</div>";
</div>
Print "<div align='center'><a href='landingpage.php?id=".$info['id']."' class='cssbutton2'>VIEW ITEM</a></div>"; 
}
mysql_close();


?>

So where's the problem? What's your question? Doesn't this work? — geomagas, Oct 20 '13 at 17:08
**Danger**: You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Oct 20 '13 at 17:17
This works actually, but you have to pick all of the 3 dropdown inorder to filter results. My requirements need even if I select 1 dropdown it will show results and if I pick another it will refine the results. Thanks! — Jan Vincent Tagalicud, Oct 20 '13 at 17:19

score 0 · Answer 1 · answered Oct 20 '13 at 17:30

You should move on to PDO on database queries, however - here is a hint on how to solve your goal

$sql = array();

foreach($_GET as $k => $v){

  if(get_magic_quotes_gpc()){
    $v = stripslashes($v);
  } 

  $v = addslashes(htmlspecialchars($v, ENT_QUOTES)); // or whatever

  switch($k){

    case 'q':

    $sql[] = "type = '".$v."'";

    break;
    case 'a':

    $sql[] = "price = '".$v."'";

    break;  
    case 'b':

    $sql[] = "location = '".$v."'";

    break;          
    default:

  }

}

// query part
if(!empty($sql)){

  $strSQL = "SELECT * FROM files WHERE ".implode(' AND ', $sql)."";

}

Thanks for the help! Do you mind giving me keywords to search in Google, in-order to study that specific code or programming lesson? I'm quite new in programming though. Thanks! — Jan Vincent Tagalicud, Oct 20 '13 at 17:37

score 0 · Accepted Answer · answered Oct 23 '13 at 04:55

I posted here the answer to my question, I figure it out. I hope this helps to other people who are facing the same problem. Thanks by the way to the people who write comments and answers my questions!

<?php
$q=$_GET['q'];
$a=$_GET['a'];
$b=$_GET['b'];

$con = mysql_connect('localhost', 'root', '');
if (!$con)
{
die('Could not connect: ' . mysql_error($con));
}

mysql_select_db("ooh", $con);

if($q && $a && $b!='')
{
$strSQL = "SELECT * FROM files WHERE type = '".$q."' AND price = '".$a."' AND location = '".$b."' ORDER BY id DESC";
}

else if($q && $b && $a!='')
{
$strSQL = "SELECT * FROM files WHERE type = '".$q."' AND location = '".$b."' AND price = '".$a."' ORDER BY id DESC";
}

else if($a && $q && $b!='')
{
$strSQL = "SELECT * FROM files WHERE price = '".$a."' AND type = '".$q."' AND location = '".$b."' ORDER BY id DESC";
}

else if($a && $b && $q!='')
{
$strSQL = "SELECT * FROM files WHERE price = '".$a."' AND location = '".$b."' AND type = '".$q."' ORDER BY id DESC";
}

else if($b && $q && $a!='')
{
$strSQL = "SELECT * FROM files WHERE location = '".$b."' AND type = '".$q."' AND price = '".$a."' ORDER BY id DESC";
}

else if($b && $a && $q!='')
{
$strSQL = "SELECT * FROM files WHERE location = '".$b."' AND price = '".$a."' AND type = '".$q."' ORDER BY id DESC";
}

else if($q && $a!='')
{
$strSQL = "SELECT * FROM files WHERE type = '".$q."' AND price = '".$a."' ORDER BY id DESC";
}

else if($q && $b!='')
{
$strSQL = "SELECT * FROM files WHERE type = '".$q."' AND location = '".$b."' ORDER BY id DESC";
}

else if($a && $q!='')
{
$strSQL = "SELECT * FROM files WHERE price = '".$a."' AND type = '".$q."' ORDER BY id DESC";
}

else if($a && $b!='')
{
$strSQL = "SELECT * FROM files WHERE price = '".$a."' AND location = '".$b."' ORDER BY id DESC";
}

else if($b && $q!='')
{
$strSQL = "SELECT * FROM files WHERE location = '".$b."' AND type = '".$q."' ORDER BY id DESC";
}

else if($b && $a!='')
{
$strSQL = "SELECT * FROM files WHERE location = '".$b."' AND price = '".$a."' ORDER BY id DESC";
}

else if($q!='')
{
$strSQL = "SELECT * FROM files WHERE type = '".$q."' ORDER BY id DESC";
}

else if($a!='')
{
$strSQL = "SELECT * FROM files WHERE price = '".$a."' ORDER BY id DESC";
}

else if($b!='')
{
$strSQL= "SELECT * FROM files WHERE location = '".$b."' ORDER BY id DESC";
}

else
{
$strSQL =  "SELECT * from files ORDER BY id DESC";
}

$rs = mysql_query($strSQL,$con);

while($info = mysql_fetch_array($rs)) {
Print "<div id='filtername' class='fluid'>"; 
Print "<img src='images/".$info['file']."' />";
Print "<div class='box'>"; 
Print "<h2>".$info['title']. "<h2>"; 
Print "<p>".$info['shortdescription']. "</p>"; 
Print "<p class='cat'><strong>Price:</strong>".$info['price'] . "</p>";
Print "<p class='cat'><strong>Duration:</strong>".$info['duration'] . "</p>"; 
Print "<p class='cat'><strong>Material:</strong>".$info['material'] . "</p>";
Print "<p class='cat'><strong>Type:</strong>".$info['type'] . "</p>"; 
Print "<p class='cat'><strong>Location:</strong>".$info['location'] . "</p>";
Print "<p class='cat'><strong>Size:</strong>".$info['size'] . "</p>";
Print "</div>";
Print "<div align='center' id='button' class='cssbutton2'><a href='landingpage.php?id=".$info['id']."'>VIEW ITEM</a></div>"; 
}

mysql_close();
?>

Filter Using AJAX, PHP and MySql

2 Answers2