Search facility - searching for more than one search term

[Vikki]

Hey, hope you had a good Christmas / NY.

 

I am trying to get my search facility to search for more than one search term. For example:

 

If you search for "london indoors" it will bring back everything in London and eveything that is indoors. What I need it to do is bring back results that are both indoors and in London. Does anyone have any ideas? Here is the code. Thanks for looking

 

<?php

 

  // Get the search variable from URL

 

  $var = @$_GET['q'] ;

  $trimmed = trim($var); //trim whitespace from the stored variable

 

// rows to return

$limit=10;

 

// check for an empty string and display a message.

if ($trimmed == "")

  {

  echo "<p>Please enter a search...</p>";

  exit;

  }

 

// check for a search parameter

if (!isset($var))

  {

  echo "<p>We dont seem to have a search parameter!</p>";

  exit;

  }

 

 

mysql_connect(""); //(host, username, password)

 

//specify database ** EDIT REQUIRED HERE **

mysql_select_db("daysout") or die("Unable to select database");

 

// Build SQL Query

$search_words = explode(' ', $trimmed);

 

// MODIFIED BELOW

$search_fields_array = array(

  "Organisation",

  "Location",

  "Prices",

  "Toddlers",

  "Indoor_Outdoor"

);

$sfc_count = count($search_fields_array);

 

// Loop each KidsOut union.

For($i=0;$i<$sfc_count;$i++){

 

  // Turn that item into an array (easier this way)

  if(!is_array($search_fields_array[$i])){

      $this_where = $search_fields_array[$i];

      $search_fields_array[$this_where] = array();

  }

 

  // Loop each search word (for each KidsOut union)

  Foreach($search_words As $Word){

      // Append an array item containing the where statement

      $search_fields_array[$this_where][] = "`".$this_where."` LIKE '%".mysql_real_escape_string($Word)."%'";

  }

}

 

$query = "select * from `KidsOut` where ".implode(" OR ",$search_fields_array['Organisation'])

      ." UNION select * from `KidsOut` where ".implode(" OR ",$search_fields_array['Location'])

      ." UNION select * from `KidsOut` where ".implode(" OR ",$search_fields_array['Prices'])

      ." UNION select * from `KidsOut` where ".implode(" OR ",$search_fields_array['Toddlers'])

      ." UNION select * from `KidsOut` where ".implode(" OR ",$search_fields_array['Indoor_Outdoor'])

      ." order by `Organisation`";

// MODIFIED ABOVE

 

 

$numresults=mysql_query($query) or die("ERROR - ".mysql_error()."<br />Query: ".$query);

$numrows=mysql_num_rows($numresults);

 

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

 

if ($numrows == 0)

  {

  echo "<h4>Results</h4>";

  echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";

[Catfish]

I think I'm having Deja Vu...

 

 

$query = "select * from `KidsOut` where ".implode(" OR ",$search_fields_array['Organisation'])
      ." UNION select * from `KidsOut` where ".implode(" OR ",$search_fields_array['Location'])
      ." UNION select * from `KidsOut` where ".implode(" OR ",$search_fields_array['Prices'])
      ." UNION select * from `KidsOut` where ".implode(" OR ",$search_fields_array['Toddlers'])
      ." UNION select * from `KidsOut` where ".implode(" OR ",$search_fields_array['Indoor_Outdoor'])
      ." order by `Organisation`";
// MODIFIED ABOVE

 

the " OR "s in the above probably need to be changed to " AND " to to achieve what you want.

If you want it to have a checkbox that makes it flip from OR to AND, you would need to make it flow off the value that says that you want to search for all search terms or any search term and build a seperate SQL query for both.

[JonnoTheDev]

Rather than using the LIKE statement take a look at fulltext searching.

http://dev.mysql.com/doc/refman/5.1/en/fulltext-natural-language.html

[Vikki]

Hi Catfish - I know sorry - I tried the AND and it doesn't work - it brings back no results

[Vikki]

Catfish - just noticed  AND works great for the first line of code

$query = "select * from `KidsOut` where ".implode(" AND ",$search_fields_array['Organisation'])

It searches for both search terms in 'Organisation'

 

But anything below, it ignores, so no results from 'Location', 'Prices', 'Toddlers' etc

 

[Catfish]

Vikki - I am not familiar with UNION in SQL or how it works. You might have to read a bit on that from the mysql manual. If only the mysql manual were as good as the php one.  :-\

[Vikki]

Hi i've taken your advice about fulltext

Something strange happens though - for example

 

SELECT * FROM table WHERE MATCH(col1) AGAINST('searchterm'); - works fine

SELECT * FROM table WHERE MATCH(col1,col2) AGAINST('searchterm'); - doesn't work

Why cant I separate my columns with commas? Have you got any ideas?

[JonnoTheDev]

Where 'database' is the searchterm this is valid

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE);

Remember you must add fulltext indexes to all fields included in the search.

[Vikki]

I have 3 drop down boxes in my form and people choose all three to do a search. This is how I have written it - its wrong though because it thinks I'm searching for the word Array.

 

<form action="fullsearch.php" method="get">

            <table width="300"  border="0" align="left" cellpadding="3" cellspacing="1">

              <tr align="center">

                <td width="200"><strong>Features</strong></td>

                <td><strong>Importance</strong></td>

              </tr>

              <tr>

                <td width="200">Location</td>

                <td align="center"><select name="q[]">

                              <option selected="selected">please select</option>

                              <option>London</option>

                              <option>Wales</option>

                              <option>West Midlands</option>

                              <option>North East </option>

<option>North West</option>

                            </select></td>

              </tr>

              <tr>

                <td width="200">Price Range</td>

                <td align="center"><select name="q[]">

                              <option selected="selected">please select</option>

                              <option>Free</option>

                              <option>Under £5</option>

                              <option>£5 - £10</option>

                              <option>Over £10</option></select></td>

              </tr>

              <tr>

                <td width="200" height="29">Indoors ot Outdoors?</td>

                <td align="center"><select name="q[]">

                              <option selected="selected">please select</option>

                              <option>Indoor</option>

                              <option>Outdoor</option></select></td>

              </tr>

              <tr align="center">

                <td colspan="2"><input type="submit" name="Submit" value="Search days out" class="Button">

                  <input type="reset" name="Reset" value="Reset" class="Button" onClick="clearSelection();"></td>

                </tr>

            </table>

                    </form>

 

 

$var = @$_GET['q'] ;

  $trimmed = trim($var); //trim whitespace from the stored variable

 

// rows to return

$limit=10;

 

// check for an empty string and display a message.

if ($trimmed == "")

  {

  echo "<p>Please enter a search...</p>";

  exit;

  }

 

// check for a search parameter

if (!isset($var))

  {

  echo "<p>We dont seem to have a search parameter!</p>";

  exit;

  }

 

//connect to your database ** EDIT REQUIRED HERE **

//connect to your database ** EDIT REQUIRED HERE **

mysql_connect(host, username, password);

 

//specify database ** EDIT REQUIRED HERE **

mysql_select_db("daysout") or die("Unable to select database");

 

// Build SQL Query

$search_words = explode(' ', $trimmed);

 

$query = "SELECT * FROM `KidsOut` WHERE MATCH(Location,IndoorOutdoor,Prices) AGAINST('$trimmed')";

 

Please help me, I'm really struggling

Thanks

[Catfish]

$var is an array because of: $var = @$_GET['q'] ;

 

therefore, you must work on it's indices not the array name itself. example:

 

$var[0], $var[1] etc. a foreach loop will help.

 

If you are getting unexpected values like it being "Array" that is actually php telling you what you're doing wrong it just takes time to pick up on things like this.

[Vikki]

Hi catfish thanks for replying - I'm really sorry but can you explain more?

 

$var[0], $var[1] etc. I don't understand it.

[Catfish]

from your code:

<form action="fullsearch.php" method="get">
<select name="q[]">
                              <option selected="selected">please select</option>
                              <option>London</option>
                              <option>Wales</option>
                              <option>West Midlands</option>
                              <option>North East </option>
</select>
<select name="q[]">
                              <option selected="selected">please select</option>
                              <option>Free</option>
                              <option>Under £5</option>
                              <option>£5 - £10</option>
                              <option>Over £10</option>
</select>

$var = @$_GET['q'] ;
print_r($var); // add this to your code after the "$var = " line

 

If you add the print_r line, php will show you the raw contents of $var.

$var is the same as $_GET['q']. $_GET['q'] holds the values the user selects in the form on the webpage, ie: <select name="q[]">.

By naming the variable q with array brackets [] you make $_GET['q'] an array, so it can hold multiple values.

Because no key names are specified, integers are used for the key names starting at 0 and incrementing one by one for each entry in the array.

 

Therefore, the variable $var will be an array, and should have 2 values in it. The key names should be 0 and 1, but do not rely on using those keys in your code as it makes your code less portable.

 

For more info on arrays, key names and array types see the php manual: http://www.php.net/manual/en/language.types.array.php

See also, about $_GET array: http://www.php.net/manual/en/reserved.variables.get.php

 

PS: In my last post, I used the words "array indicies". Indicies is plural for index and index is the same thin as an array key name. So, array index/array key/array key name and array indicies/array keys/array key names are all the same thing, just different ways to say it.