homepage Welcome to WebmasterWorld Guest from 54.205.144.54
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
search string problem: MySQL SELECT * FROM dir WHERE multiple columns
Can't search with multiple search words from multiple fields (columns)
alcheme




msg:1274812
 7:44 am on Jan 20, 2005 (gmt 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 ''

 

coopster




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

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

alcheme




msg:1274814
 8:30 pm on Jan 21, 2005 (gmt 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

jusdrum




msg:1274815
 9:17 pm on Jan 21, 2005 (gmt 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.

alcheme




msg:1274816
 8:48 pm on Jan 22, 2005 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved