Advanced Search Form

[rach99]

Hi Everyone,

I currently have a working search based website, a user types in their search into a small search form and searches.

What i would like now is to also have and 'advanced search' on a page where instead of having just the one search box i have it set out like the following:

Name
Cuisine
Location

then the user can type in more than on thing to search for like a restaurant name and a location where it could be?

How would i set up my form so that my search.php will display these results or will i have to have a whole new search.php?

Any help would be awesome.

CODE BELOW

CURRENT FORM WHERE TYPE IN SEARCH:

<form action="search.php" method="get" name="search">

  <input name="q" type="text" value=" [code=php]<?php echo $q; ?>[/code] " size="15">
  <input name="search" type="submit" value="Search">

</form>

MY SEARCH PAGE: (search.php)

<?php
// Set up some vars to use:
$tablename = 'tassearch'; // Change to the table to search
$searchcolumn = 'ResName'; // Change to the column to search
$searchcolumnb = 'ResSuburb';
$searchcolumna = 'ResCuisine';
$ordercolumn = 'ResName'; // Change to the column to order by
?>
<P>Welcome to example</P>

<?php

  // Get the search variable from URL
  $var = $_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable

// If s is sent, then grab it.  If it's less than 0, make it 0 ;)
  $s = 0;
  if(isset($_GET['s']))
  {
      $s = $_GET['s'];
      if($s < 0)
          $s = 0;
  }

// rows to return
$limit=10;

// check for an empty string and display a message.
if ($trimmed == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

// secure the "trimmed" value
$trimmed = mysql_real_escape_string($trimmed);

// check for a search parameter
if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }

// Build SQL Query  

$query = "SELECT COUNT(*)
          FROM `{$tablename}`
          WHERE `{$searchcolumn}` LIKE '%$trimmed%' or `{$searchcolumnb}` LIKE '%$trimmed%'
          ORDER BY `{$ordercolumn}`"; // EDIT HERE and specify your table and field names for the SQL query

$sql = "SELECT a.tascuisine, t.ResName
        FROM tascuisine a
            INNER JOIN linktable lt ON a.CusId = lt.FK_CusId
            INNER JOIN tablesearch t ON lt.FK_ResNameId = t.ResNameId
        WHERE a.CusId = $selectedCusId";


$rslt=mysql_query($query) or die('MySQL Error: ' . mysql_error());
$numrows = mysql_result($rslt, 0, 0);
@mysql_free_result($rslt);

// If we have no results, offer a google search as an alternative

if ($numrows == 0)
{
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results</p>";
}

// get results
  $query = "SELECT *
            FROM `{$tablename}`
            WHERE `{$searchcolumn}` LIKE '%{$trimmed}%' or `{$searchcolumnb}` LIKE '%{$trimmed}%'
            ORDER BY `{$ordercolumn}`
            LIMIT {$s}, {$limit}";


  $result = mysql_query($query) or die("Couldn't execute query");

// display what the person searched for
echo "<p>You searched for: &quot;" . $var . "&quot;</p>";

// begin to show results set
echo "<p>Results</p>";
$count = 1 + $s ;

while ($row = mysql_fetch_array($result)) {
  $link = $row['ResLink'];
  $link_title = $row['ResName'];

  echo $count . '.) <a href="' . $link . '">' . $link_title . '</a><br />';
  $count++;
}

$currPage = (($s/$limit) + 1);

//break before paging
  echo "<br />";

  // next we need to do the links to other results
  if ($s>=1) { // bypass PREV link if s is 0
  $prevs=($s-$limit);
  print "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt;
  Prev 10</a>&nbsp&nbsp;";
  }

// calculate number of pages needing links
  $pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division

  if ($numrows%$limit) {
  // has remainder so add one page
  $pages++;
  }

// check to see if last page
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

  // not last page so give NEXT link
  $news=$s+$limit;

  echo "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 &gt;&gt;</a>";
  }

  $a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<p>Showing results $b to $a of $numrows</p>";

?> 

[krotkruton]

You'd need to change both the search form and the search.php file... It's not too hard, but it's kinda tedious so you'll have to figure most of it out on your own.

For the form, you need to have extra text fields for each variable you want to check (name, cuisine, location, or others).

<form action="search.php" method="get" name="search">

<input name="name" type="text" value="" size="15">
<input name="cuisine" type="text" value="" size="15">
<input name="location" type="text" value="" size="15">
<input name="search" type="submit" value="Search">

</form>

For the php side of it, you'd need to do something like this:

if (isset($_GET['search'])) {
  $query_sub = "";
  //set a flag to use if you need a comma
  $comma_flag = 0;
  //see if 'name' is not blank
  $trimmed = trim($_GET['name']);
  if ($trimmed != "") {
    $query_sub .= "table.name LIKE '%$trimmed%'";
    $comma_flag = 1;
  }
  $trimmed = trim($_GET['cuisine']);
  if ($trimmed != "") {
    if ($comma_flag == 1) {
      $query_sub .= ", ";
    }
    $query_sub .= "table.cuisine LIKE '%$trimmed%'";
    $comma_flag = 1;
  }
  $trimmed = trim($_GET['location']);
  if ($trimmed != "") {
    if ($comma_flag == 1) {
      $query_sub .= ", ";
    }
    $query_sub .= "table.location LIKE '%$trimmed%'";
  }
  $query = "SELECT COUNT(*)
          FROM `{$tablename}`
          WHERE $query_sub
          ORDER BY `{$ordercolumn}`";
  //do the rest of the query stuff for the advanced form
}

Now, I think this will fail if the user doesn't enter anything for name, cuisine, or location, so you'll have to handle those cases. I also haven't tested this so there are probably some errors. The $comma_flag variable is just used for a cheap hack to figure out if you need to put commas between the search fields... and now that I look at it again, you should set it to " AND " instead of ", " because they belong in the where clause, but I'm too lazy to go back and change all of that. Hopefully this will get you started in the right direction.

[rach99]

Thanks very much for you help 😃

I am new to understand all this php code.

So in the code you provided do i need to change

table.cuisine LIKE '%$trimmed%

to

tascuisine.ResCuisine LIKE '%$trimmed%

or do i leave that?

And when you mean change the "," to "AND" do i do this?

$trimmed = trim($_GET['location']);
  if ($trimmed != "") {
    if ($comma_flag == 1) {
      $query_sub .= ", ";

to

$trimmed = trim($_GET['location']);
  if ($trimmed != "") {
    if ($comma_flag == 1) {
      $query_sub .= "AND";

Sorry to be a pain, but i would like to understand what i am doing to the code 😃

thanks heaps 😃

[krotkruton]

First question: Yes.
Second question: close. It needs to be " AND " (notice the spaces between the quotations and the word AND). Otherwise your query string would get messed up because it would think it's one word.

The code I provided isn't the most elegant by any means, but it should be pretty straightforward and easy to understand. Good luck.

[rach99]

Thanks 😃

It is not working though. Not sure what i have done. changed a few things but didn't help so changed them back. heres the error:

Parse error: syntax error, unexpected $end in /home/rach99/public_html/client/search_adv.php on line 297

And my code that i changed:

<?php
// Set up some vars to use:
$tablename = 'tassearch'; // Change to the table to search
$searchcolumn = 'ResName'; // Change to the column to search
$searchcolumnb = 'ResSuburb';
$searchcolumna = 'ResCuisine';
$ordercolumn = 'ResName'; // Change to the column to order by
?>
<P>Welcome to example</P>

<?php

  // Get the search variable from URL
  $var = $_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable

// If s is sent, then grab it.  If it's less than 0, make it 0 ;)
  $s = 0;
  if(isset($_GET['s']))
  {
      $s = $_GET['s'];
      if($s < 0)
          $s = 0;
  }

// rows to return
$limit=10;

// check for an empty string and display a message.
if ($trimmed == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

// secure the "trimmed" value
$trimmed = mysql_real_escape_string($trimmed);

// check for a search parameter
if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }

  if (isset($_GET['search'])) {
  $query_sub = "";
  //set a flag to use if you need a comma
  $comma_flag = 0;
  //see if 'name' is not blank
  $trimmed = trim($_GET['name']);
  if ($trimmed != "") {
    $query_sub .= "tassearch.ResName LIKE '%$trimmed%'";
    $comma_flag = 1;
  }
  $trimmed = trim($_GET['cuisine']);
  if ($trimmed != "") {
    if ($comma_flag == 1) {
      $query_sub .= " AND ";
    }
    $query_sub .= "tascuisine.ResCuisine LIKE '%$trimmed%'";
    $comma_flag = 1;
  }
  $trimmed = trim($_GET['suburb']);
  if ($trimmed != "") {
    if ($comma_flag == 1) {
      $query_sub .= " AND ";
    }
    $query_sub .= "tassearch.ResSuburb LIKE '%$trimmed%'";
}
  $query = "SELECT COUNT(*)
          FROM `{$tablename}`
          WHERE {$query_sub}`
          ORDER BY `{$ordercolumn}`";

// Build SQL Query  

$query = "SELECT COUNT(*)
          FROM `{$tablename}`
          WHERE `{$searchcolumn}` LIKE '%$trimmed%' or `{$searchcolumnb}` LIKE '%$trimmed%'
          ORDER BY `{$ordercolumn}`"; // EDIT HERE and specify your table and field names for the SQL query

$sql = "SELECT a.tascuisine, t.ResName
        FROM tascuisine a
            INNER JOIN linktable lt ON a.CusId = lt.FK_CusId
            INNER JOIN tablesearch t ON lt.FK_ResNameId = t.ResNameId
        WHERE a.CusId = $selectedCusId";


$rslt=mysql_query($query) or die('MySQL Error: ' . mysql_error());
$numrows = mysql_result($rslt, 0, 0);
@mysql_free_result($rslt);

// If we have no results, offer a google search as an alternative

if ($numrows == 0)
{
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results</p>";
}

// get results
  $query = "SELECT *
            FROM `{$tablename}`
            WHERE `{$searchcolumn}` LIKE '%{$trimmed}%' or `{$searchcolumnb}` LIKE '%{$trimmed}%'
            ORDER BY `{$ordercolumn}`
            LIMIT {$s}, {$limit}";


  $result = mysql_query($query) or die("Couldn't execute query");

// display what the person searched for
echo "<p>You searched for: &quot;" . $var . "&quot;</p>";

// begin to show results set
echo "<p>Results</p>";
$count = 1 + $s ;

while ($row = mysql_fetch_array($result)) {
  $link = $row['ResLink'];
  $link_title = $row['ResName'];

  echo $count . '.) <a href="' . $link . '">' . $link_title . '</a><br />';
  $count++;
}

$currPage = (($s/$limit) + 1);

//break before paging
  echo "<br />";

  // next we need to do the links to other results
  if ($s>=1) { // bypass PREV link if s is 0
  $prevs=($s-$limit);
  print "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt;
  Prev 10</a>&nbsp&nbsp;";
  }

// calculate number of pages needing links
  $pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division

  if ($numrows%$limit) {
  // has remainder so add one page
  $pages++;
  }

// check to see if last page
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

  // not last page so give NEXT link
  $news=$s+$limit;

  echo "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 &gt;&gt;</a>";
  }

  $a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<p>Showing results $b to $a of $numrows</p>";

?> 

if a hint to where i am going wrong would be great 😃 thanks again for all your help i really appreciate it.

[rach99]

Hi All,

Looked into the error it means i have a bracket missing somewhere, cant seem to work out where though.

Thanks

Rachael