search string problem: MySQL SELECT * FROM dir WHERE multiple columns

Can't search with multiple search words from multiple fields (columns)

[alcheme]

The search form works but it won't show results for multiple search string words that come from from multiple table fields (columns). i.e. Search String: dave smith new york [dave smith (appears in the name column) new york (appears in the city column)].

Can anyone help me decipher why it won't allow me to search query with words that appear in separate table columns?

----> BEGIN CODE

if ($search) // performs search only if a search string was entered.
{

mysql_connect() or die ("Problem connecting to Database");

$search = trim($_POST['search']);

$sql= mysql_query("SELECT wname, title, keywords, description, city, country, category, url FROM directory WHERE city LIKE '%$search%' ¦¦ wname LIKE '%$search%' ¦¦ title LIKE '%$search%' ¦¦ keywords LIKE '%$search%' ¦¦ description LIKE '%$search%' ¦¦ country LIKE '%$search%' ¦¦ category LIKE '%$search%' ¦¦ url LIKE '%$search%'", $db);

<---- END CODE

~Shane

p.s. it also works with 'OR' in the place of '¦¦'

[coopster]

Have you considered MySQL Full-Text Search Functions [dev.mysql.com]? Maybe that is an option for you?

[alcheme]

These did not even work for a single word search query.

DID NOT WORK, FULL TEXT SEARCH// $sql= mysql_query("SELECT wname, title, keywords, description, city, country, category, url, MATCH (wname, title, keywords, description, city, country, category, url) AGAINST $search FROM directory", $db);

DID NOT WORK, FULL TEXT SEARCH// $sql= mysql_query("SELECT wname, url, title, keywords, description, city, country, category FROM directory MATCH (wname, url, title, keywords, description, city, country, category) AGAINST ('$search')", $db);

DID NOT WORK, FULL TEXT SEARCH// $sql= mysql_query("SELECT wname, url, title, keywords, description, city, country, category FROM directory MATCH (wname, url, title, keywords, description, city, country, category) AGAINST '%$search%'", $db);

DID NOT WORK, FULL TEXT SEARCH// $sql= mysql_query("SELECT wname, url, title, keywords, description, city, country, category FROM directory MATCH (wname, url, title, keywords, description, city, country, category) AGAINST ('%$search%')", $db);

AM I doing these FullText Searches incorrectly?

~Shane

[jusdrum]

One quick solution may be to substitute spaces for percentage signs in your search strings.

$search = trim(str_replace(" ","%",$_POST['search']));

This will get you a rudimentary way of handling multi-word searches.

Another way is to split up words by exploding them and then building your query that way.
$search = trim($_POST['search']);
if (ereg(" ",$search))
{
$Words = explode(" ",$search);
foreach($Words as $Word)
{
$sql .= " field like '%$Word%' or";
}
}

It's just an example, but it hopefully gives an idea on how to split up words and build a (rather long) query.

If that doesn't suit you, definitely check out full text searches.

[alcheme]

Hello jusdrum & thanks,

It did not fully work. I have tried the following code. However, it only searches or shows results for the last word of the search string.

i.e.: search string: (john smith chicago)

* ONLY shows the search results for the word chicago in the city column from the table directory.

-------------> Begin Code

<?

include("inc/*****.php");

if(isset($_POST['search']) &&!empty($_POST['search'])) {

mysql_connect() or die ("Problem connecting to Database");

$search = trim($_POST['search']);

$search = explode(" ",$search);
foreach($search as $searches) {

$sql= mysql_query("SELECT wname, title, keywords, description, city, country, category, url FROM directory WHERE wname LIKE '%$searches%' OR title LIKE '%$searches%' OR keywords LIKE '%$searches%' OR description LIKE '%$searches%' OR city LIKE '%$searches%' OR country LIKE '%$searches%' OR category LIKE '%$searches%' OR url LIKE '%$searches%'", $db);
}

<------------- End Code

So I need to get it to search all the words not just the last word in the search string.