Adding more filters to mysql query

amg182 · August 2, 2011

Hi guys, Wondering if anyone can help me out.

I have some php code displaying results from a mysql database and would like to add some filters so the user can narrow their results down using combobox filters.

Currently i have one filter working but would like to add 3 more filters. For example users can filter by car make, but i would like them to be able to filter the results further by model and fuel type etc..

Here is some of the coding which shows the combo filter and the query builder.

<?php session_start();
  if (isset($_GET["order"])) $order = @$_GET["order"];
  if (isset($_GET["type"])) $ordtype = @$_GET["type"];

  if (isset($_POST["filter"])) $filter = @$_POST["filter"];
  if (isset($_POST["filter1"])) $filter1 = @$_POST["filter1"];

  if (!isset($order) && isset($_SESSION["order"])) $order = $_SESSION["order"];
  if (!isset($ordtype) && isset($_SESSION["type"])) $ordtype = $_SESSION["type"];
  if (!isset($filter) && isset($_SESSION["filter"])) $filter = $_SESSION["filter"];

  ?>

.......


<form name="filter" action="" method="post">
Make filter: <Select name="filter">
<option "Input" value="<?php echo $filter ?>">
<option selected="selected" value="0">All Models</option>
<option value="Ford">Ford</option>
<option value="Honda">Honda</option>
<option value="Lexus">Lexus</option>
</select>
<input type="submit" name="action" value="Apply Filters">
</form>

....


<?php function viewrec($recid)
{
  $res = sql_select();
  $count = sql_getrecordcount();
  mysql_data_seek($res, $recid);
  $row = mysql_fetch_assoc($res);
  showrecnav("view", $recid, $count);
?>
<br>
<?php showrow($row, $recid) ?>
<?php
  mysql_free_result($res);
} ?>

<?php function connect()
{
  $conn = mysql_connect("localhost", "root", "");
  mysql_select_db("cars");
  return $conn;
}

function sqlstr($val)
{
  return str_replace("'", "''", $val);
}

function sql_select()
{
  global $conn;
  global $order;
  global $ordtype;
  global $filter;

  $filterstr = sqlstr($filter);
  if (!$filterstr!='') $filterstr = "%" .$filterstr ."%";
  $sql = "SELECT * FROM (Select * FROM cars WHERE Make='Lexus' ORDER By Price) subq";
  if (isset($filterstr) && $filterstr!='' && isset($filterfield) && $filterfield!='') {
    $sql .= " where " .sqlstr($filterfield) ." like '" .$filterstr ."'";
  } elseif (isset($filterstr) && $filterstr!='') {
    $sql .= " where (`Description` like '" .$filterstr ."') or (`Title` like '" .$filterstr ."') or (`Make` like '" .$filterstr ."') or (`Model` like '" .$filterstr ."') or (`Year` like '" .$filterstr ."') or (`Price` like '" .$filterstr ."') or (`Fuel` like '" .$filterstr ."') or (`Location` like '" .$filterstr ."') or (`Image1` like '" .$filterstr ."')";
  }
  if (isset($order) && $order!='') $sql .= " order by `" .sqlstr($order) ."`";
  if (isset($ordtype) && $ordtype!='') $sql .= " " .sqlstr($ordtype);
  $res = mysql_query($sql, $conn) or die(mysql_error());
  return $res;
}

function sql_getrecordcount()
{
  global $conn;
  global $order;
  global $ordtype;

  $filterstr = sqlstr($filter);
  if ($filterstr!='') $filterstr = "%" .$filterstr ."%";
  $sql = "SELECT COUNT(*) FROM (Select * FROM cars WHERE Make='Lexus' ORDER By Price) subq";
  if (isset($filterstr) && $filterstr!='' && isset($filterfield) && $filterfield!='') {
    $sql .= " where " .sqlstr($filterfield) ." like '" .$filterstr ."'";
  } elseif (isset($filterstr) && $filterstr!='') {
    $sql .= " where (`Make` like '" .$filterstr ."') or (`Model` like '" .$filterstr ."') or (`Year` like '" .$filterstr ."')";
  }
  $res = mysql_query($sql, $conn) or die(mysql_error());
  $row = mysql_fetch_assoc($res);
  reset($row);
  return current($row);
} ?>

Any help would be much appreciated.

Nodral · August 2, 2011

Without writing the code for you,

Have each user response return a value to search for and then add it to a variable which you can include in a SQL query

eg if

$_POST['filter1]=ford

$_POST['filter2']=escort

create variables which are

$filter1=" make = '".$_POST['filter1]"'";

$filter2=" AND model = '".$_POST['filter2]"'";

etc

Then run a query of

$sql="SELECT count(*) WHERE".$filter1.$filter2.etc.etc;

hope this helps

amg182 · August 2, 2011

oh i see use variables to build the query! What should i do in the event the user leaves a field blank. I have been working on this and works but only shows results if the var = something.

Can i have it echo out results if the var is blank?

$model='is200';


if ( $model == "" ) {

  $model='';
}

else {

  $model=$model;
}

$result = mysql_query("SELECT * FROM cars WHERE Model='$model'");

is there a wildcard operator that can do this, i thought '%' did this but obivously not..

Cheers!

voip03 · August 2, 2011

can u use trim

WebStyles · August 2, 2011

Are you sure about this?

if ( $model == "" ) {
   $model='';
}else{
  $model=$model;
}

it's basically saying: If $model is empty, then set $model to empty, otherwise, (if $model is not empty) make $model = $model...

it's kind of redundant no?

amg182 · August 2, 2011

Yeah, i am aware that this is what it is doing but is there any syntax i can place in $model='';?

For example something that would do this:

if ( $model == "" ) {
   $model='ALL models';
}else{
  $model=$model;
}

Here is what i am working with.:


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

mysql_select_db("cars", $con);



?>

<form name="input" action="" method="post">
Model: <Select name="model">
<option "Input" value="<?php echo $_POST['model']; ?>"><?php echo $_POST['model']; ?></option>
<option value="All Models">All Models</option>
<option value="IS300">IS300</option>
<option value="IS200">IS200</option>
</select>

Fuel: <Select name="fuel">
<option "Input" value="<?php echo $_POST['fuel']; ?>"><?php echo $_POST['fuel']; ?></option>
<option value="Any Fuel">Any Fuel</option>
<option value="Petrol">Petrol</option>
<option value="Diesel">Diesel</option>
</select>


Year: <Select name="year">
<option "Input" value="<?php echo $_POST['year']; ?>"><?php echo $_POST['year']; ?></option>
<option value="All Years">All Years</option>
<option value="2001">2001</option>
<option value="2008">2008</option>
</select>

<input type="submit" value="Search Cars" />
</form>

<?php
$model=$_POST['model'];

if ( $model == "" ) {

  $model='';
}

else {

  $model=$model;
}


//fuel


$fuel=$_POST['fuel'];


if ( $fuel == "" ) {

  $fuel='';
}

else {

  $fuel=$fuel;
}



//year

$year=$_POST['year'];


if ( $year == "" ) {

  $year='';
}

else {

  $year=$year;
}

$result = mysql_query("SELECT * FROM cars WHERE model='$model' AND Fuel='$fuel' AND Year='$year'");

WebStyles · August 2, 2011

$model = $model is still redundant.

you don't need the 'else'

if ( trim($model) == "" )  $model='ALL models';

amg182 · August 2, 2011

Oh, i see now. Sorry I am very new to php.

I tried the trim but still no joy, it works but if "All models" is selected there are no results echoed out...

What am I doing wrong?

<form name="input" action="" method="post">
Model: <Select name="model">
<option "Input" value="<?php echo $_POST['model']; ?>"><?php echo $_POST['model']; ?></option>
<option value="All models">All models</option>
<option value="Civic">Cvic</option>
<option value="Accord">Accord</option>
</select>


<input type="submit" value="Search" />
</form>

<?php
$model=$_POST['model'];

if ( trim($model) == "" )  $model='All models';

$result = mysql_query("SELECT * FROM cars WHERE Model='$model'");

Thanks!

WebStyles · August 2, 2011

$model = isset($_POST['model']) && trim($_POST['model']) != '' ? $_POST['model'] : '';
$query = "SELECT * FROM cars WHERE Model='$model'";
if($model) == "")  $query = "SELECT * FROM cars";
$result = mysql_query($query);

amg182 · August 2, 2011

:-\

I kinda see what its doing, is there a simpler way? I have no idea how to implement this as i have 3 different filters....

Would this need AND + WHERE statements built into the query?

I appreciate your help, i'm way over my head with this....

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("cars", $con);
?>

<form name="input" action="" method="post">
Model: <Select name="model">
<option "Input" value="<?php echo $_POST['model']; ?>"></option>
<option value="All Models">All Models</option>
<option value="IS300">IS300</option>
<option value="IS200">IS200</option>
</select>

Fuel: <Select name="fuel">
<option "Input" value="<?php echo $_POST['fuel']; ?>"></option>
<option value="Any Fuel">Any Fuel</option>
<option value="Petrol">Petrol</option>
<option value="Diesel">Diesel</option>
</select>


Year: <Select name="year">
<option "Input" value="<?php echo $_POST['year']; ?>"></option>
<option value="All Years">All Years</option>
<option value="2001">2001</option>
<option value="2008">2008</option>
</select>
<input type="submit" value="Search Cars" />
</form>

<?php

   // no idea what to do here....

$result = mysql_query("SELECT * FROM cars WHERE model='$model' AND Fuel='$fuel' AND Year='$year'");

while($row = mysql_fetch_array($result))
  {
  echo $row['Make'];
  echo $row['Model'];
  echo $row['Year'];
  echo $row['Fuel'];
  echo $row['Advert_Type'];

  }
?>

Nodral · August 3, 2011

Instead of

$result = mysql_query("SELECT * FROM cars WHERE model='$model' AND Fuel='$fuel' AND Year='$year'");

try

if(strlen($_POST['fuel']>0)){
$fuel=" fuel='$_POST['fuel']";
$where++
}

if(strlen($_POST['model']>0)){
$model=" model='$_POST['model']";
$where++
}

if(strlen($_POST['year']>0)){
$year=" year='$_POST['year']";
$where++
}

if($where=2){
$and=" AND ";
}

if($where=3){
$AND=" AND ";
}

if($where>0){
$where=" WHERE ";
}

$result=mysql_query("SELECT * FROM cars ".$where.$model.$and.$fuel.$AND.$year);

Then make sure the first option of each filter dropdown is blank.

If they select a blank then the filter variable will not be set. Whereas if they set the filter it will populate the query with WHERE and AND. If you're putting loads of filters on, it may be worthwhile changing the if($where=........ statements to a switch.

amg182 · August 3, 2011

Hi nodral, thanks for reply.

I am getting the following error:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in

on line:

$model=" model='$_POST['model']";

I have changed the first option to blank, but no joy.

Thanks again.

<form name="input" action="" method="post">
Model: <Select name="model">
<option "Input" value="<?php echo $_POST['model']; ?>"><?php echo $_POST['model']; ?></option>
<option value="">All</option>
<option value="IS300">IS300</option>
<option value="IS200">IS200</option>
</select>

Fuel: <Select name="fuel">
<option "Input" value="<?php echo $_POST['fuel']; ?>"><?php echo $_POST['fuel']; ?></option>
<option value="">All</option>
<option value="Petrol">Petrol</option>
<option value="Diesel">Diesel</option>
</select>


Year: <Select name="year">
<option "Input" value="<?php echo $_POST['year']; ?>"><?php echo $_POST['year']; ?></option>
<option value="">All</option>
<option value="2001">2001</option>
<option value="2002">2002</option>
</select>

<input type="submit" value="Search Cars" />
</form>

<?php

if(strlen($_POST['model']>0)){
$model=" model='$_POST['model']";
$where++
}

if(strlen($_POST['fuel']>0)){
$fuel=" fuel='$_POST['fuel']";
$where++
}

if(strlen($_POST['year']>0)){
$year=" year='$_POST['year']";
$where++
}

if($where=2){
$and=" AND ";
}

if($where=3){
$AND=" AND ";
}

if($where>0){
$where=" WHERE ";
}

$result=mysql_query("SELECT * FROM cars ".$where.$model.$and.$fuel.$AND.$year);


while($row = mysql_fetch_array($result))
  {

Nodral · August 3, 2011

sorry missed a closing quote

try amending the following lines, adding a single quote prior to the double quotes at the end of each line.

$model=" model='$_POST['model']'";
$fuel=" fuel='$_POST['fuel']'";
$year=" year='$_POST['year']'";

You may want to include an echo statement to show exactly what sql query is running.

Try including this for debugging but remove it prior to production

$result=mysql_query("SELECT * FROM cars ".$where.$model.$and.$fuel.$AND.$year);
echo $result;

This will show exactly what sql you are running and if you have any sort of error with it, will show you where you need to amend.

amg182 · August 3, 2011

Hi again, thanks for reply. Add the single quotes before double but still no joy :shrug: ...

Same error appearing

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING

sorry for being a burden, i appreciate your time and help.

if(strlen($_POST['model']>0)){
$model=" model='$_POST['model']'";
$where++
}

if(strlen($_POST['fuel']>0)){
$fuel=" fuel='$_POST['fuel']'";
$where++
}

if(strlen($_POST['year']>0)){
$year=" year='$_POST['year']'";
$where++
}

if($where=2){
$and=" AND ";
}

if($where=3){
$AND=" AND ";
}

if($where>0){
$where=" WHERE ";
}

Nodral · August 3, 2011

can you repost all code and highlight which line the error points to

TeNDoLLA · August 3, 2011

$model = $model is still redundant.

you don't need the 'else'
if ( trim($model) == "" )  $model='ALL models';

Offtopic (and nitpicking ), but you can just do it even without the comparison since empty string is same as false.

if (trim($model))  $model='ALL models';

Nodral · August 3, 2011

if(strlen($_POST['model']>0)){
$model=" model='$_POST['model']'";
$where++
}

if(strlen($_POST['fuel']>0)){
$fuel=" fuel='$_POST['fuel']'";
$where++
}

if(strlen($_POST['year']>0)){
$year=" year='$_POST['year']'";
$where++
}

if($where==2){
$and=" AND ";
}

if($where==3){
$AND=" AND ";
}

if($where>0){
$where=" WHERE ";
}

Sorry missed a couple of = signs out too

amg182 · August 3, 2011

Thanks!

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in C:\xampp\htdocs\testing\query.php on line 38

?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("cars", $con);
?>
<form name="input" action="" method="post">
Model: <Select name="model">
<option "Input" value="<?php echo $_POST['model']; ?>"><?php echo $_POST['model']; ?></option>
<option value="">All</option>
<option value="IS300">IS300</option>
<option value="IS200">IS200</option>
</select>

Fuel: <Select name="fuel">
<option "Input" value="<?php echo $_POST['fuel']; ?>"><?php echo $_POST['fuel']; ?></option>
<option value="">All</option>
<option value="Petrol">Petrol</option>
<option value="Diesel">Diesel</option>
</select>


Year: <Select name="year">
<option "Input" value="<?php echo $_POST['year']; ?>"><?php echo $_POST['year']; ?></option>
<option value="">All</option>
<option value="2001">2001</option>
<option value="2002">2002</option>
</select>

<input type="submit" value="Search Cars" />
</form>

<?php

if(strlen($_POST['model']>0)){
****LINE 38***** $model=" model='$_POST['model']'";****
$where++
}

if(strlen($_POST['fuel']>0)){
$fuel=" fuel='$_POST['fuel']'";
$where++
}

if(strlen($_POST['year']>0)){
$year=" year='$_POST['year']'";
$where++
}

if($where=2){
$and=" AND ";
}

if($where=3){
$AND=" AND ";
}

if($where>0){
$where=" WHERE ";
}

$result=mysql_query("SELECT * FROM cars ".$where.$model.$and.$fuel.$AND.$year);


while($row = mysql_fetch_array($result))
  {
  echo $row['Make'];
  echo $row['Model'];
  echo $row['Year'];
  echo $row['Fuel'];
  echo $row['Description'];

  }
?>

:confused:

Nodral · August 3, 2011

try

$model=" model='".$_POST['model']."'";****

You'll probably have to do this with the other varibale in the query too

amg182 · August 3, 2011

Yeah, removes the parse error but now the closed curly bracket is not being liked?

38 $model=" model='".$_POST['model']."'";
39 $where++
40 }

Parse error: syntax error, unexpected '}' in C:\xampp\htdocs\testing\query.php on line 40

Thanks again, i am being a real pain now....

Nodral · August 3, 2011

semi colon req after $where++

amg182 · August 3, 2011

Hi, thanks i cant believe i missed the semicolons.... As you can tell I wasnt joking about being new to php.

For some reason it says the variables in the line below are undefined, but when i echo the variable they echo out fine?

Is there an issue with this line?

$result=mysql_query("SELECT * FROM cars".$where.$model.$and.$fuel.$AND.$year);

I do apologies for my ignorance. :-[

<form name="input" action="" method="post">
Model: <Select name="model">
<option "Input" value="<?php echo $_POST['model']; ?>"><?php echo $_POST['model']; ?></option>
<option value="">All</option>
<option value="IS300">IS300</option>
<option value="IS200">IS200</option>
</select>

Fuel: <Select name="fuel">
<option "Input" value="<?php echo $_POST['fuel']; ?>"><?php echo $_POST['fuel']; ?></option>
<option value="">All</option>
<option value="Petrol">Petrol</option>
<option value="Diesel">Diesel</option>
</select>


Year: <Select name="year">
<option "Input" value="<?php echo $_POST['year']; ?>"><?php echo $_POST['year']; ?></option>
<option value="">All</option>
<option value="2001">2001</option>
<option value="2008">2008</option>
</select>

<input type="submit" value="Search Cars" />
</form>

<?php


if(strlen($_POST['model']>0)){
$model=" model='".$_POST['model']."'";
$where++;
}


if(strlen($_POST['fuel']>0)){
$fuel=" fuel='".$_POST['fuel']."'";
$where++;
}

if(strlen($_POST['year']>0)){
$year=" year='".$_POST['year']."'";
$where++;
}

if($where=2){
$and=" AND ";
}

if($where=3){
$AND=" AND ";
}

if($where>0){
$where=" WHERE ";
}


$result=mysql_query("SELECT * FROM cars".$where.$model.$and.$fuel.$AND.$year);

while($row = mysql_fetch_array($result))
  {
  echo $row['Make'];
  echo $row['Model'];
  echo $row['Year'];
  echo $row['Fuel'];
  }
?>

Nodral · August 3, 2011

It will tell you they are undefined as you really should define a vairiable before you use / call it. You'll see that these are only Notices rather than warnings. Not declaring variables doesn't tend to make your script fall over, but some purists will pick fault at it.

All you need to do is at the start of the script just list each variable you are going to use and make it ="".

eg $model="";

Is it all working apart from that? Do you understand what we have done?

Muddy_Funster · August 3, 2011

Just an FYI -

Instead of concatenating the strings using . here:

$model=" model='".$_POST['model']."'";

The following should have the same effect, and can oftentimes be easier to read:

$model=" model='{$_POST['model']}'";

amg182 · August 3, 2011

Yeah, understand most of it, just the $where++ part i don really get.

I defined the varibales a the start and has removed the notice warnings!

But the results wont echo out for some reason.

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\testing\query.php on line 76

76 while($row = mysql_fetch_array($result))
  77 {
  78 echo $row['Make'];
  79 echo $row['Model'];

i tried

echo $result;

but just does nothing no warnings, nothing...

Getting close I think, :-\

Cheers again

Sign In

Adding more filters to mysql query

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information