Search Functions

[JSHINER]

Using the following:

 

SELECT * FROM table WHERE name LIKE sandra bolton OR city LIKE sandra bolton

 

Does not return any results even though there is a Sandra in the system, and she is from Bolton.

 

However, if I do name LIKE sandra OR city LIKE bolton - it works. Is there anyway to fix this?

 

Problem is I want one search box, not a name and city box.

[The Little Guy]

Try:

"SELECT * FROM table WHERE name LIKE 'sandra bolton' OR city LIKE 'sandra bolton'"

 

Edit:

You need quotes around the search strings.

[trq]

Your first query is simply invalid if you want to search two fields of the database. You'll need to explode your form data into the two required parts. eg;

 

<?php

  if (isset($_POST['search'])) {
    $arr = explode(' ',$_POST['search']);
    $name = $arr[0];
    $loc = $arr[1];
  }

?>

 

This method needs some work, but its a start.

[Wildbug]

You'll have to break up the search terms by whitespace and write a query like the following:

 

SELECT * FROM table WHERE city LIKE '%sandra%' OR city LIKE '%bolton%' OR name LIKE '%sandra%' OR city LIKE '%bolton%'

[JSHINER]

How can I make it by , so if its john smith, boston, office - it would explode to john smith and boston and office. Also, if there is no , will it know to only use john smith?

[chigley]

$array = explode(", ", $string); // Explodes a string into bits separated by ", "

// john smith, boston, office
// ||
// \/
// john smith
// boston
// office

[The Little Guy]

<?php
$variable = 'sandra bolton';
$nv = str_replace(' ','%',$variable);
$query = "SELECT * FROM `table` WHERE `name` LIKE '$nv' OR city LIKE '$nv'";
?>

[The Little Guy]

Or like this:

 

<?php
$remove_vals = array('/,/','/ /');  #Strings you would like to replace with a % sign. to use in the like statement.
$variable = 'sandra bolton, New York';
$nv = preg_replace($remove_vals,'%',$variable);
echo "SELECT * FROM `table` WHERE `name` LIKE '$nv' OR city LIKE '$nv'";
?>

[JSHINER]

Or like this:

 

<?php
$remove_vals = array('/,/','/ /');  #Strings you would like to replace with a % sign. to use in the like statement.
$variable = 'sandra bolton, New York';
$nv = preg_replace($remove_vals,'%',$variable);
echo "SELECT * FROM `table` WHERE `name` LIKE '$nv' OR city LIKE '$nv'";
?>

 

This returns

SELECT * FROM `table` WHERE `name` LIKE 'sandra%bolton%%New%York' OR city LIKE 'sandra%bolton%%New%York'

 

And although there is a sandra bolton in new york, it does not return any results.

[The Little Guy]

maybe these extra % signs will work in the query string.

<?php
$remove_vals = array('/,/','/ /');  #Strings you would like to replace with a % sign. to use in the like statement.
$variable = 'sandra bolton, New York';
$nv = preg_replace($remove_vals,'%',$variable);
echo "SELECT * FROM `table` WHERE `name` LIKE '%$nv%' OR city LIKE '%$nv%'";
?>

 

btw: the above wasn't supposed to return results (if that was what you were expecting), just output a select string.

[JSHINER]

That didn't do it either ...

[Wildbug]

Creating a string like "%term1%term2%" won't work ideally because it'll only match both terms, in order.  Like I said earlier, you'll need to separate all terms and search for them with OR conditions.  If you're using commas as separators, then preg_split with '/\s*,\s*/' and build a query string.  You should be able to figure out how to assemble a string.

 

$columns = array('city','name');
$terms = array();
foreach (preg_split('/\s*,\s*/',$search_string,-1,PREG_SPLIT_NO_EMPTY) as $term) {
  foreach ($columns as $col) $terms[] = "$col LIKE '%$term%'";
}
$query = 'SELECT * FROM TABLE WHERE ' . implode(' OR ',$terms);