[SOLVED] Search form MySQL problem

[stuieb]

Hi,

 

I have a search form on a page that has three select fields.

 

make

 

location

 

price

 

Each is dynamicly generated from information in the database.

 

The form goes to a results page (GET) and displays the results.

 

The problem I am having is with the MySQL SELECT on the results page. Basicly I want the user to be able to search for all records from 'make' from within the table, or all records from 'location' or all records from 'price' or by a combination of all three. Make, location and price are all cols in a table bye the way.

 

MySQL Version 4.1

 

 

Please help!

 

Stu.

 

[rhodesa]

This is one way:

 

<?php
$keys = array('make','location','price');
$qry = "SELECT * FROM products";
$where = array();
foreach($keys as $key){
  if(strlen($_GET[$key]))
    $where[] = "`{$key}` = '{$_GET[$key]}'"
}
if(count($where))
  $qry .= " WHERE ".implode(' AND ',$where);

$result = mysql_query($qry);
//etc

?>

[stuieb]

Wow,

 

Thanks for that. You mentioned that that was one way. Is there another way?

 

Many thanks in advance,

 

Stu.

[gluck]

Good  solution>>

This is one way:

 

<?php
$keys = array('make','location','price');
$qry = "SELECT * FROM products";
$where = array();
foreach($keys as $key){
  if(strlen($_GET[$key]))
    $where[] = "`{$key}` = '{$_GET[$key]}'"
}
if(count($where))
  $qry .= " WHERE ".implode(' AND ',$where);

$result = mysql_query($qry);
//etc

?>

Stu: The other solutions would just have different styles of coding. This is pretty sleek. 

[stuieb]

Thanks,

I'll try it but I must admit I'm a little confused with it. I'll keep you posted as to its progress. (I'm a little new to this, sorry!)

 

Many thanks,

 

Stu.

[rhodesa]

Same code with some comments. I was also missing a semi-colon:

 

<?php
$keys = array('make','location','price'); //List of columns to filter on
$qry = "SELECT * FROM products"; //Start the SQL statement
$where = array(); //Initiate array to store the WHERE items
foreach($keys as $key){ //Loop over each filter item
  if(strlen($_GET[$key])) //Check to see if the filter item is set
    $where[] = "`{$key}` = '{$_GET[$key]}'"; //Add filter item to WHERE array
}
if(count($where)) //If there are any WHERE pieces
  $qry .= " WHERE ".implode(' AND ',$where); //Add them to the query here

$result = mysql_query($qry);
//the rest is standard MySQL calls

?>

[gluck]

Same thing a bit differently

 

 

$sql = "SELECT * FROM products"; 

$sqlWhere = '';
$sqlAnd = '';

if (!empty($_GET['make']))
{
$sqlWhere.= " make ='".$_GET['make']."'"; 
$sqlAnd = " And ";
}

if (!empty($_GET['price']))
{
$sqlWhere.= $sqlAnd." price ='".$_GET['price']."'"; 
$sqlAnd = " And ";

}
if (!empty($_GET['location']))
{
$sqlWhere.= $sqlAnd." location ='".$_GET['location']."'"; 

}


if (!empty($sqlWhere))
{
$sql.=" WHERE ".$sqlWhere;
}

[fenway]

Both of those code snippets assume that the part of the where clause you're building doesn't have ORs in it... and definitely use the array method.

[stuieb]

Many thanks to you all for your brill help.

 

Stu.