Welcome to WebmasterWorld Guest from 54.197.187.2

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

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

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

     
7:44 am on Jan 20, 2005 (gmt 0)

Junior Member

10+ Year Member

joined:June 17, 2003
posts:107
votes: 0


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 ''

1:49 pm on Jan 21, 2005 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12547
votes: 2


Have you considered MySQL Full-Text Search Functions [dev.mysql.com]? Maybe that is an option for you?
8:30 pm on Jan 21, 2005 (gmt 0)

Junior Member

10+ Year Member

joined:June 17, 2003
posts:107
votes: 0


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

9:17 pm on Jan 21, 2005 (gmt 0)

Junior Member

10+ Year Member

joined:Feb 29, 2004
posts:194
votes: 0


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.

8:48 pm on Jan 22, 2005 (gmt 0)

Junior Member

10+ Year Member

joined:June 17, 2003
posts:107
votes: 0


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.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members