Adding more filters to mysql query

[Nodral]

Repost your whole code as the error is with the construction of the sql statement.

 

All that $where does, is increment everytime a filter is used so the WHERE or AND's can be put in.

 

You basic SELECT statement is

 

SELECT * FROM CARS

 

if $where is 1 (ie a filter has been set) this will then produce

 

SELECT * FROM CARS WHERE filter = condition.

 

if $where is 2 this adds in the first AND clause

 

SELECT * FROM CARS WHERE filter = condition AND filter=condition

 

if $where is 3 this adds in the second AND clause

 

SELECT * FROM CARS WHERE filter = condition AND filter=condition AND filter=condition

 

Others may offer different ways of acheieving this, but I find this easy to follow through and understand

 

 

 

 

 

[amg182]

I see, that make sense now! I have got my hands on a php book to read through so hopefully I wont have to annoy you guys on here so much!

 

here is the full code:

 

<?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="2008">2008</option>
</select>

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

<?php

$model="";
$year="";
$fuel="";
$where="";


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

  }
?>

 

You are a true gentleman! Thanks ever so much!

[Nodral]

$result="SELECT * FROM cars".$where.$model.$and.$fuel.$AND.$year";
echo $result;
$result=mysql_query($result);
while($row = mysql_fetch_array($result))
  {
  echo $row['Make'];
  echo $row['Model'];
  echo $row['Year'];
  echo $row['Fuel'];

  }
?>

 

Try this.  What is displayed for $result?

[amg182]

For the combos I chose Model = IS200  Fuel = Diesel  Year = 2008 .

 

this is the $result: (Seems to only recognise the year variable)

 

SELECT * FROM cars WHERE AND AND year='2008'

[Nodral]

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++;
}

 

Try this, just moved a few brackets around to make the if test run correctly

[amg182]

Kinda working!

 

The results are being echoed out but only if ALL the variables are set, if one or more is unset then it wont do anything?

[Nodral]

if($where==2){

$and=" AND ";

}

 

if($where==3){

$AND=" AND ";

}

[amg182]

:D :D :D :D :D :D :D :D :D :D :D :D :D :D

 

 

Works Perfectly!

 

I cant thank you enough, never in a million years would i managed to get that working!

 

I owe you mate, will make note of your business URL and keep you in mind when i need IT Gear/support!

 

Where you based?

 

:D :D :D :D :D :D :D :D :D :D :D :D :D :D :D