explode() function problem maybe

Vikki · December 21, 2009

Hi I'm having trouble searching my database. When I type two words in the search field it only searches for the second word. For example: if I typed london zoo , it will only search for zoo.

Is this something to do with the explode function? I am stuck, any help would be amazing. Thanks

Here is my code:

<?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;

}

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

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

//specify database ** EDIT REQUIRED HERE **

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

// Build SQL Query

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

foreach ($search_words as $word) {

$query = "select * from table where Organisation like '%$word%' UNION select * from table where Location like '%$word%' UNION select * from table where Prices like '%$word%' UNION select * from table where Toddlers like '%$word%' UNION select * from table where Indoor_Outdoor like '%$word%'

order by Organisation"; }

$numresults=mysql_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>";

// google

echo "<p><a href=\"http://www.google.com/search?q="

. $trimmed . "\" target=\"_blank\" title=\"Look up

" . $trimmed . " on Google\">Click here</a> to try the

search on google</p>";

}

// next determine if s has been passed to script, if not use 0

if (empty($s)) {

$s=0;

}

// get results

$query .= " limit $s,$limit";

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

// display what the person searched for

echo "<p>You searched for: "" . $var . ""</p>";

// now you can display the results returned

// begin to show results set

echo "Results <br /><br />";

$count = 1 + $s ;

// now you can display the results returned

while ($row= mysql_fetch_array($result)) {

$title = $row["Result"];

$col = $row["Organisation"];

$col1 = $row["Location"];

$col2 = $row["Indoor_Outdoor"];

$col3 = $row["Prices"];

$col4 = $row["Toddlers"];

$col5 = $row["Primary"];

$col6 = $row["Junior"];

$col7 = $row["High_School"];

$col8 = $row["All_Family"];

$col9 = $row["Telephone"];

$col10 = $row["Website_Address"];

echo '<div class=\"results\">';

echo "<div class=\"title\"> $col</div>";

echo "<div class=\"subheadings\">Indoors or Outdoors? $col2</div>";

echo "<div class=\"subheadings\">Price Range in £'s: $col3</div>";

echo "<div style=\"clear:both\"></div>";

echo "<div class=\"suitable\">Suitable for toddlers? $col4<br /> Suitable for primary schools? $col5 <br />Suitable for junior schools? $col6 <br />Suitable for high schools?$col7<br /> Suitable for all the family? $col8</div>";

echo "<div class=\"lo\">Location: $col1</div>";

echo "<div style=\"clear:both\"></div>";

echo "<div class=\"lo\">Telephone Number: $col9</div>";

echo "<div style=\"clear:both\"></div>";

echo "<div class=\"website\">Website Addess: <a href=\"$col10\">$col10</a></div>";

echo "<br /><br />";

echo "</div>\n";

$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 " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><<

Prev 10</a>&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 " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";

}

$a = $s + ($limit) ;

if ($a > $numrows) { $a = $numrows ; }

$b = $s + 1 ;

echo "<p>Showing results $b to $a of $numrows</p>";

?>

Catfish · December 21, 2009

// Build SQL Query 
$search_words = (explode(' ', $trimmed));
foreach ($search_words as $word) {
$query = "select * from table where Organisation like '%$word%' UNION select * from table where Location like '%$word%' UNION select * from table where Prices like '%$word%' UNION select * from table where Toddlers like '%$word%' UNION select * from table where Indoor_Outdoor like '%$word%'
order by Organisation"; }

foreach($search_words as $word)

{

$query = "...";

}

there's your problem. if there's two words in the search term, the second loop overwrites the value of the first loop.

Vikki · December 21, 2009

Thank you for replying. How would I go about writing it differently so it wouldn't overwrite?

Catfish · December 21, 2009

Try:

// Build SQL Query
$query = '';
$search_words = (explode(' ', $trimmed));
foreach ($search_words as $word)
{
$query .= "select * from table where Organisation like '%$word%' UNION select * from table where Location like '%$word%' UNION select * from table where Prices like '%$word%' UNION select * from table where Toddlers like '%$word%' UNION select * from table where Indoor_Outdoor like '%$word%'
order by Organisation UNION";
}
rtrim($query, ' UNION');

I have never done SQL queries like this before so I don't know if it will work. The principle is there but. each time the foreach loops, you are appending to the $query string instead of writing over the top of it. The rtrim() call after the loop removes the final ' UNION' off the end of the query so mySQL doesn't spit an error.

Another option would to make an array of sql queries:

// Build SQL Query
$search_words = (explode(' ', $trimmed));
foreach ($search_words as $word)
{
$query[] = "select * from table where Organisation like '%$word%' UNION select * from table where Location like '%$word%' UNION select * from table where Prices like '%$word%' UNION select * from table where Toddlers like '%$word%' UNION select * from table where Indoor_Outdoor like '%$word%'
order by Organisation";
}

// and then when you are ready to do your mysql_query() calls:

foreach ($query as $num => $queryString)
{
if ($dbResult = mysql_query($queryString, $dbLink)
{
  // query worked - now work on the result $dbResult
}
else
  // query failed. handle it.
}

I think doing a single query to the DB (first example) is probably better than many (second example).

hope this helps.

Vikki · December 22, 2009

Thanks for those. I tried both, it didn't like the second option at all.

The first option, rtrim($query, ' UNION'); didn't make a difference its still missing out my first search word. I also tried implode('UNION', $query);

but that does the same.

Am I just going about it in the wrong way altogether?

teynon · December 22, 2009

$query = "select * from table where Organisation like '%$word%' UNION select * from table where Location like '%$word%' UNION select * from table where Prices like '%$word%' UNION select * from table where Toddlers like '%$word%' UNION select * from table where Indoor_Outdoor like '%$word%'

order by Organisation"; }

--

$sql="SELECT * from table WHERE";

foreach ($keyword as $word) {

$sql.=" Organisation like '%{$word}%' OR Location like '%{$word}%' OR Prices like '%{$word}%' OR Toddlers like '%{$word}%' Organisation like '%{$word}%' OR Indoor_Outdoor like '%{$word}%'";

}

Vikki · December 22, 2009

Hi, thanks for the code it wouldnt execute the query though

I already have a $query .= could that be why it failed?

Buddski · December 22, 2009

Do you get any errors whilst trying to execute the query?

Vikki · December 22, 2009

no it just says couldn't execute the query

Buddski · December 22, 2009

Can you post a snippet of where your query is derived and the executing commands and perhaps echo out the query itself..

ChemicalBliss · December 22, 2009

This code works for a single search term, correct?

<?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;
  }

//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("host","username","password");

//specify database ** EDIT REQUIRED HERE **
mysql_select_db("database_name") or die("Unable to select database");

// Build SQL Query 
$search_words = (explode(' ', $trimmed));
foreach ($search_words as $word) {
$query = "select * from table where Organisation like '%$word%' UNION select * from table where Location like '%$word%' UNION select * from table where Prices like '%$word%' UNION select * from table where Toddlers like '%$word%' UNION select * from table where Indoor_Outdoor like '%$word%'
order by Organisation"; }

$numresults=mysql_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>";

// google
echo "<p><a href=\"http://www.google.com/search?q="
  . $trimmed . "\" target=\"_blank\" title=\"Look up
  " . $trimmed . " on Google\">Click here</a> to try the
  search on google</p>";
  }

// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }

// get results
  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");

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


// now you can display the results returned
// begin to show results set
echo "Results <br /><br />";
$count = 1 + $s ;

// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
$title = $row["Result"];
$col = $row["Organisation"];
$col1 = $row["Location"];
$col2 = $row["Indoor_Outdoor"];
$col3 = $row["Prices"];
$col4 = $row["Toddlers"];
$col5 = $row["Primary"];
$col6 = $row["Junior"];
$col7 = $row["High_School"];
$col8 = $row["All_Family"];
$col9 = $row["Telephone"];
$col10 = $row["Website_Address"];

echo '<div class=\"results\">';
echo "<div class=\"title\"> $col</div>";
echo "<div class=\"subheadings\">Indoors or Outdoors? $col2</div>";
echo "<div class=\"subheadings\">Price Range in £'s: $col3</div>";
echo "<div style=\"clear:both\"></div>";
echo "<div class=\"suitable\">Suitable for toddlers? $col4<br /> Suitable for primary schools? $col5 <br />Suitable for junior schools? $col6 <br />Suitable for high schools?$col7<br /> Suitable for all the family? $col8</div>";
echo "<div class=\"lo\">Location: $col1</div>";
echo "<div style=\"clear:both\"></div>";
echo "<div class=\"lo\">Telephone Number: $col9</div>";
echo "<div style=\"clear:both\"></div>";
echo "<div class=\"website\">Website Addess: <a href=\"$col10\">$col10</a></div>";
echo "<br /><br />";
echo "</div>\n";

$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 " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><<
  Prev 10</a>&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 " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
  }

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

?>

If so, this code should work for multiple search terms.

<?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;
  }

//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("host","username","password");

//specify database ** EDIT REQUIRED HERE **
mysql_select_db("database_name") or die("Unable to select database");

// Build SQL Query 
$search_words = explode(' ', $trimmed);

// MODIFIED BELOW 
$search_fields_array = array(
"Organisation",
"Locatation",
"Prices",
"Toddlers",
"Indoor_Outdoor"
);

// Loop each table union.
For($i=0;$i<count($search_fields_array);$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 table 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 `table` where ".implode(" OR ",$search_fields_array[0])
	."UNION select * from `table` where ".implode(" OR ",$search_fields_array[1])
	."UNION select * from `table` where ".implode(" OR ",$search_fields_array[2])
	."UNION select * from `table` where ".implode(" OR ",$search_fields_array[3])
	."UNION select * from `table` where ".implode(" OR ",$search_fields_array[4])
	"order by `Organisation`";
// MODIFIED ABOVE

$numresults=mysql_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>";

// google
echo "<p><a href=\"http://www.google.com/search?q="
  . $trimmed . "\" target=\"_blank\" title=\"Look up
  " . $trimmed . " on Google\">Click here</a> to try the
  search on google</p>";
  }

// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }

// get results
  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");

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


// now you can display the results returned
// begin to show results set
echo "Results <br /><br />";
$count = 1 + $s ;

// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
$title = $row["Result"];
$col = $row["Organisation"];
$col1 = $row["Location"];
$col2 = $row["Indoor_Outdoor"];
$col3 = $row["Prices"];
$col4 = $row["Toddlers"];
$col5 = $row["Primary"];
$col6 = $row["Junior"];
$col7 = $row["High_School"];
$col8 = $row["All_Family"];
$col9 = $row["Telephone"];
$col10 = $row["Website_Address"];

echo '<div class=\"results\">';
echo "<div class=\"title\"> $col</div>";
echo "<div class=\"subheadings\">Indoors or Outdoors? $col2</div>";
echo "<div class=\"subheadings\">Price Range in £'s: $col3</div>";
echo "<div style=\"clear:both\"></div>";
echo "<div class=\"suitable\">Suitable for toddlers? $col4<br /> Suitable for primary schools? $col5 <br />Suitable for junior schools? $col6 <br />Suitable for high schools?$col7<br /> Suitable for all the family? $col8</div>";
echo "<div class=\"lo\">Location: $col1</div>";
echo "<div style=\"clear:both\"></div>";
echo "<div class=\"lo\">Telephone Number: $col9</div>";
echo "<div style=\"clear:both\"></div>";
echo "<div class=\"website\">Website Addess: <a href=\"$col10\">$col10</a></div>";
echo "<br /><br />";
echo "</div>\n";

$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 " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><<
  Prev 10</a>&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 " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
  }

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

?>

Hope this helps,

-CB-

Vikki · December 22, 2009

Thanks for taking the time to help me.

I get an error when I try your code

Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING

on line 94 which is "order by `Organisation`";

ChemicalBliss · December 22, 2009

oops, sorry, forgot to concatenate that line. put a . (period) before the quoted string .

-CB-

ChemicalBliss · December 22, 2009

In Fact that code is wrong lol, was wrong one - this is the correct one:

<?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;
  }

//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("host","username","password");

//specify database ** EDIT REQUIRED HERE **
mysql_select_db("database_name") or die("Unable to select database");

// Build SQL Query 
$search_words = explode(' ', $trimmed);

// MODIFIED BELOW 
$search_fields_array = array(
   "Organisation",
   "Locatation",
   "Prices",
   "Toddlers",
   "Indoor_Outdoor"
);
$sfc_count = count($search_fields_array);

// Loop each table 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 table 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 `table` where ".implode(" OR ",$search_fields_array['Organisation'])
      ." UNION select * from `table` where ".implode(" OR ",$search_fields_array['Locatation'])
      ." UNION select * from `table` where ".implode(" OR ",$search_fields_array['Prices'])
      ." UNION select * from `table` where ".implode(" OR ",$search_fields_array['Toddlers'])
      ." UNION select * from `table` where ".implode(" OR ",$search_fields_array['Indoor_Outdoor'])
      ." order by `Organisation`";
// MODIFIED ABOVE

$numresults=mysql_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>";

// google
echo "<p><a href=\"http://www.google.com/search?q="
  . $trimmed . "\" target=\"_blank\" title=\"Look up
  " . $trimmed . " on Google\">Click here</a> to try the
  search on google</p>";
  }

// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }

// get results
  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");

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


// now you can display the results returned
// begin to show results set
echo "Results <br /><br />";
$count = 1 + $s ;

// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
$title = $row["Result"];
$col = $row["Organisation"];
$col1 = $row["Location"];
$col2 = $row["Indoor_Outdoor"];
$col3 = $row["Prices"];
$col4 = $row["Toddlers"];
$col5 = $row["Primary"];
$col6 = $row["Junior"];
$col7 = $row["High_School"];
$col8 = $row["All_Family"];
$col9 = $row["Telephone"];
$col10 = $row["Website_Address"];

echo '<div class=\"results\">';
echo "<div class=\"title\"> $col</div>";
echo "<div class=\"subheadings\">Indoors or Outdoors? $col2</div>";
echo "<div class=\"subheadings\">Price Range in £'s: $col3</div>";
echo "<div style=\"clear:both\"></div>";
echo "<div class=\"suitable\">Suitable for toddlers? $col4<br /> Suitable for primary schools? $col5 <br />Suitable for junior schools? $col6 <br />Suitable for high schools?$col7<br /> Suitable for all the family? $col8</div>";
echo "<div class=\"lo\">Location: $col1</div>";
echo "<div style=\"clear:both\"></div>";
echo "<div class=\"lo\">Telephone Number: $col9</div>";
echo "<div style=\"clear:both\"></div>";
echo "<div class=\"website\">Website Addess: <a href=\"$col10\">$col10</a></div>";
echo "<br /><br />";
echo "</div>\n";

$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 " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><<
  Prev 10</a>&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 " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
  }

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

?>

Sorry for the confusion.

-CB-

Vikki · December 22, 2009

Thank you so much but now it says (using the second bit of code you just sent)

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

ChemicalBliss · December 22, 2009

strange, its not matching any records.

make sure the query is working.

Change this:

 $numresults=mysql_query($query);

to this:

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

Tell us what it says .

Vikki · December 22, 2009

It says:

Parse error: syntax error, unexpected ';'

on the line we just changed

ChemicalBliss · December 22, 2009

lol i'm not getting it right at all tonight .

forgot the bracket on the end (end of die() command).

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

should be

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

EDIT: Oh and add

echo($query);

just above that line too.

-CB-

Vikki · December 22, 2009

Hi added those bits, this is what it outputs:

select * from `KidsOut` where `Organisation` LIKE '%london%' OR `Organisation` LIKE '%zoo%' UNION select * from `KidsOut` where `Locatation` LIKE '%london%' OR `Locatation` LIKE '%zoo%' UNION select * from `KidsOut` where `Prices` LIKE '%london%' OR `Prices` LIKE '%zoo%' UNION select * from `KidsOut` where `Toddlers` LIKE '%london%' OR `Toddlers` LIKE '%zoo%' UNION select * from `KidsOut` where `Indoor_Outdoor` LIKE '%london%' OR `Indoor_Outdoor` LIKE '%zoo%' order by `Organisation`ERROR - Unknown column 'Locatation' in 'where clause'

Query: select * from `KidsOut` where `Organisation` LIKE '%london%' OR `Organisation` LIKE '%zoo%' UNION select * from `KidsOut` where `Locatation` LIKE '%london%' OR `Locatation` LIKE '%zoo%' UNION select * from `KidsOut` where `Prices` LIKE '%london%' OR `Prices` LIKE '%zoo%' UNION select * from `KidsOut` where `Toddlers` LIKE '%london%' OR `Toddlers` LIKE '%zoo%' UNION select * from `KidsOut` where `Indoor_Outdoor` LIKE '%london%' OR `Indoor_Outdoor` LIKE '%zoo%' order by `Organisation`

The error with Location isn't a worry, its just spelt wrong is all.

ChemicalBliss · December 22, 2009

lol, correct the spelling, see what it does .

(Correct the spelling in the array above).

-CB-

Vikki · December 22, 2009

Hi I changed Location

Now it outputs

select * from `KidsOut` where `Organisation` LIKE '%london%' OR `Organisation` LIKE '%zoo%' UNION select * from `KidsOut` where `Location` LIKE '%london%' OR `Location` LIKE '%zoo%' UNION select * from `KidsOut` where `Prices` LIKE '%london%' OR `Prices` LIKE '%zoo%' UNION select * from `KidsOut` where `Toddlers` LIKE '%london%' OR `Toddlers` LIKE '%zoo%' UNION select * from `KidsOut` where `Indoor_Outdoor` LIKE '%london%' OR `Indoor_Outdoor` LIKE '%zoo%' order by `Organisation`

...and then some results. A pretty odd bunch of results but its definitely searching for both words which is infinitely better than before.

Vikki · December 22, 2009

When I search for "London indoors" it comes back with Abergavenny castle in Wales lol

I guess what's happening is its bringing back anything that is either indoors or in London. Is that because of OR? Can we use AND or does it not work like that?

ChemicalBliss · December 22, 2009

Yay at least there is results .

Ok if you use 'AND' instead of 'OR', you will only match results that contain _BOTH_ search phrases. The OR lets it match any result that has Either of those phrases.

What you can do, is supply a checkbox next to the search, where it will ask "Match all phrases", if checked, it will switch the OR's to AND's, otherwise it will leave the query as is.

There are methods that can sort results into a more accurate result set. But this is slightly beyond me for now as i havn't ventured down that route yet .

Good Luck though .

(This thread i guess has been solved as your original question is answered, it's also quite big, i'd suggest starting a new thread about search optimization etc if you want that).

-CB-

Vikki · December 23, 2009

Cool beans - I will give that a go.

Thank you!

Sign In

explode() function problem maybe

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information