SQL Search question...

[chelnov63]

I have a search form which is as follows:

 

 

<form action="reports_filter.php" method="post" enctype="multipart/form-data">
  <select name="company" id="company">
    <option value="Any" selected="selected">Any</option>
    <option value="hot">Primark</option>
    <option value="warm">Nike</option>
    <option value="cool">Puma</option>
  </select>
  
  <select name="temperature" id="temperature">
    <option value="Any" selected="selected">Any</option>
    <option value="hot">Hot (1-3 month opportunity)</option>
    <option value="warm">Warm (3-6 month opportunity)</option>
    <option value="cool">Cool (6-12 month opportunity)</option>
    <option value="cold">Cold (no interest)</option>
  </select>
</form>

 

 

 

$company = $_POST["company"];
$temperature = $_POST["temperature"];

$result_contacts = mysql_query("SELECT * FROM contacts WHERE company = '$company' AND temperature= $temperature");

 

now when they select from the dropdown lists, all works fine.. but not sure what to do if they choose 'ANY' in the drop down lists, how would I do the search then.. any help is appreciated..thanks

 

[kickstart]

Hi

 

Something like this I think:-

 

$company = $_POST["company"];
$temperature = $_POST["temperature"];

$result_contacts = mysql_query("SELECT * FROM contacts WHERE ".(($company == 'Any') ? "" : "company = '$company'")." AND ".(($temperature == 'Any') ? "" : "temperature= '$temperature'");

 

All the best

 

Keith

[chelnov63]

aah thanks mate but its giving me a Parse error: parse error in C:\htdocs\reports\reports_filter.php on line 15 [\b] for that line

 

[chelnov63]

the logic seems slighlty off example:

 

echo $company; // any
echo $temperature; //any


$result_contacts = "SELECT * FROM contacts WHERE ".(($company == 'Any') ? "" : "company = '$company'")." AND ".(($temperature == 'Any') ? "" : "temperature= '$temperature'");
   
echo $result_contacts;

 

echo $result_contacts; gives "SELECT * FROM contacts WHERE AND "

[ninedoors]

Don't use "", use % which is the wildcard character.  It should return a match to anything in the company and temperture column.

[chelnov63]

thanks mate..i'll give it a shot

[kickstart]

Hi

 

Sorry, my fault for doing it in a hurry.

 

$company = $_POST["company"];
$temperature = $_POST["temperature"];
$whereClause = "";
$whereClause .= (($company == 'Any') ? "" : (($whereClause == "") ? " WHERE " : " AND ")."company = '$company'")
$whereClause .= (($temperature == 'Any') ? "" : (($whereClause == "") ? " WHERE " : " AND ")."temperature = '$temperature'")
$result_contacts = mysql_query("SELECT * FROM contacts $whereClause");

 

You could use a like clause and a %, but suspect it would be less efficient (which is probably not important with a select that is only occasionally executed).

 

All the best

 

Keith

[chelnov63]

thanks for all your help guys..sorry about the late reply... i ended up thinking of another way to do it, as the above was just a small percentage of the search criteria.. there are about 15 different criterias to choose from.. i ended up doing it like this.. so for e.g for temperature and company criteria i did:

 

$tempParams = array();
$companyParams = array();

$i = 0; 
$j = 0;


foreach ($temperature as $key)
{
 $tempParams[$i] = "temperature LIKE '$key' ";
 $i++;
}


foreach ($company_value as $key_value)
{
 $companyParams[$j] = "company_value LIKE '$key_value'";
 $j++;
}


$temperatureSQL = join($tempParams,' OR ');
$companySQL = join($companyParams,' OR ');

$result = mysql_query("SELECT id, company, first_name, surname, email, job_title, address1, address2,address3,address4,postcode FROM contacts JOIN companies ON(companies.company = contacts.company) WHERE (".$temperatureSQL.") AND (".$companySQL. ") ORDER BY company");