recordsets with multiple options - AND, OR If Else and the "Any" Rule

Report · Mar 29, 2010

Hi,

Been trying to get this to work for days now... I have a form with drop down lists that feeds through variables to a search page... The Recordset Jobsearch2 shown below works great so long as a user chooses and option that has info in the DB.... ie all fields must be present. I cannot seem to work out how to achieve and Any rule?...

I have had 2 options suggested to me...

a) the code below...

if(isset($searchsector) && $searchsector !== "") {
$query_jobsearch2 .= ' AND Sector LIKE "%' . mysql_real_escape_string($searchsector) . '%"';
}

putting this in for each option..... but If Im told I have to add this in before the "$jobsearch2 = mysql_query" line... but when I do this the recordset disappears\is deleted...

b) Using an Array and "Implode" - this wouldbe great I'm sure if I had any clue how to do this and what it meant.

MY CURRENT CODE

<?php require_once('Connections/steelbakercouk_473245_db1.php'); ?>
<?php
$searchsector = $_POST['Sector'];
$searchlocation = $_POST['clocation'];
$searchmin = $_POST['minsalary'];
$searchmax = $_POST['maxsalary'];
$searchtype = $_POST['Type'];

?>
<?php
mysql_select_db($database_steelbakercouk_473245_db1, $steelbakercouk_473245_db1);
$query_jobsearch2 = "SELECT jobs.clocation, jobs.Ref_id, jobs.RefCode, jobs.jobtitle, jobs.blurb, jobs.Consultant, jobs.Salary, jobs.tlocation, jobs.Sector, jobs.Type FROM jobs WHERE jobs.Salary BETWEEN '$searchmin' AND '$searchmax' AND clocation = '$searchlocation' AND jobs.Sector = '$searchsector' AND jobs.Type = '$searchtype'";
$jobsearch2 = mysql_query($query_jobsearch2, $steelbakercouk_473245_db1) or die(mysql_error());
$row_jobsearch2 = mysql_fetch_assoc($jobsearch2);
$totalRows_jobsearch2 = mysql_num_rows($jobsearch2);
?>

Please help... ... pulling my hair out!

Thanks

Report · Mar 30, 2010

See this article that I contributed to the Dreamweaver Cookbook: http://cookbooks.adobe.com/post_Create_search_query_with_optional_fields-16245.html.

Report · Mar 30, 2010

David, as always prompt and helpful advice... though still stuck..

When I created a recordset in Dreamweaver.. it does not autumatically add GetSQLValueString() as you're article suggests it should?

Also.. do I need to have an Arrary in place to use a Loop?

Very new to this and fast realising I need to learn more basics...

thanks

Report · Mar 30, 2010

if(!empty($_GET[postcode]))
{
$query[] = "cars_agents.postcode LIKE '$_GET[postcode]%' ";
}

For every textbox, inputbox, selectbox etc...

What I mean by that is :

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

if(!empty($_GET[salary]))

{

$query[] = "jobs.salary = '$_GET[salary]' ";

}

this creates an array called "query()"
with the values submited from the form and makes a custom query string.
Its custom because it only adds the value if the form inputs have something in them.

So if you do this your half way there.

NEXT:

if(!empty($query))
{
$MyQuery = implode(" and ", $query);
$MyQuery = " and ".$MyQuery;
}

This is the implode code that basically makes the query functional by adding AND and to structure the query

e.g: select * from jobs WHERE location = $location AND

if you did not do that, it would not function. it would come out like this $location$salary

NEXT:

$q1 = "select * from jobs WHERE $MyQuery";

That will select the records that match the inputted results.

THE END 🙂

Report · Mar 30, 2010

Ok.. I've tried all methods and still not getting anywhere..

David... following the link you gave me... and to the letter.. apart from GetSQLValueString()which does not seem to exist.

All it does is return all the records (as per the basic recordset)

Heres the code.... please please tell me where I am going wrong.... downloaded your book Foundation PHP but struggaling.,

<?php require_once('Connections/steelbakercouk_473245_db1.php'); ?>
<?php
$searchsector = $_POST['Sector'];
$searchlocation = $_POST['clocation'];
$searchmin = $_POST['minsalary'];
$searchmax = $_POST['maxsalary'];
$searchtype = $_POST['Type'];
$any = $_POST['any'];
?>
<?php
$currentPage = $_SERVER["PHP_SELF"];

mysql_select_db($database_steelbakercouk_473245_db1, $steelbakercouk_473245_db1);
$expected = array('Sector' => 'text',
                  'clocation'         => 'text',
                  'minsalary'          => 'int',
                  'maxsalary'      => 'int');

$query_jobsearch2 = "SELECT jobs.clocation, jobs.Ref_id, jobs.RefCode, jobs.jobtitle, jobs.blurb, jobs.Consultant, jobs.Salary, jobs.tlocation, jobs.Sector, jobs.Type FROM jobs";
$where = false;
// Loop through the associatiave array of expected search values
foreach ($expected as $var => $type) {
if (isset($_GET[$var])) {
    $value = trim(urldecode($_GET[$var]));
    if (!empty($value)) {
      // Check if the value begins with > or <
      // If so, use it as the operator, and extract the value
      if ($value[0] == '>' || $value[0] == '<') {
        $operator = $value[0];
        $value = ltrim(substr($value, 1));
      } elseif (strtolower($type) != 'like') {
        $operator = '=';
      }
      // Check if the WHERE clause has been added yet
      if ($where) {
        $query_search .= ' AND ';
      } else {
        $query_search .= ' WHERE ';
        $where = true;
      }
      // Build the SQL query using the right operator and data type
      $type = strtolower($type);
      switch($type) {
        case 'like':
          $query_search .= "`$var` LIKE " . GetSQLValueString('%' .
$value . '%', "text");
          break;
        case 'int':
        case 'double':
        case 'date':
          $query_search .= "`$var` $operator " .
GetSQLValueString($value, "$type");
          break;
        default:
        $query_search .= "`$var` = " . GetSQLValueString($value,
"$type");
      }
    }
}
}

$jobsearch2 = mysql_query($query_jobsearch2, $steelbakercouk_473245_db1) or die(mysql_error());
$row_jobsearch2 = mysql_fetch_assoc($jobsearch2);
$totalRows_jobsearch2 = mysql_num_rows($jobsearch2);

$queryString_jobsearch2 = "";
if (!empty($_SERVER['QUERY_STRING'])) {
$params = explode("&", $_SERVER['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
    if (stristr($param, "pageNum_jobsearch2") == false &&
        stristr($param, "totalRows_jobsearch2") == false) {
      array_push($newParams, $param);
    }
}
if (count($newParams) != 0) {
    $queryString_jobsearch2 = "&" . htmlentities(implode("&", $newParams));
}
}
$queryString_jobsearch2 = sprintf("&totalRows_jobsearch2=%d%s", $totalRows_jobsearch2, $queryString_jobsearch2);
?>