Welcome to WebmasterWorld Guest from 54.156.90.21

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Search multiple fields without having exact matches (PHP, MySQL)

     
1:07 am on Jan 9, 2005 (gmt 0)

Junior Member

10+ Year Member

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


I just discovered a weakness in my current search engine. It won't show results if I don't have an exact match

or

if I use two or more words that appear in different fields of the table.

For example, search term 'John' appears in field[wname] and 'canada' appears in field[country]. But when I type 'john canada' into my search form I get no results - unless I only use one or the other in the search form. It also does not work if I type in any extra words whether they are in the table or not.

It probably has something to do with this: wname LIKE '%$search%'

I need to search multiple fields without having exact matches:

------> Begin Code

$sql= mysql_query("SELECT wname, url, title, keywords, description, city, country, category 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

Sheesh... why didn't I see this before?

~Shane

p.s. I think I live here.

8:00 pm on Jan 9, 2005 (gmt 0)

Full Member

10+ Year Member

joined:May 29, 2003
posts:273
votes: 0


Take a look at full text indexing [dev.mysql.com] in MySQL - that should solve your problems.
10:43 pm on Jan 9, 2005 (gmt 0)

Junior Member

10+ Year Member

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


I looked it up and tried this:

if ($search) // performs search only if a search string was entered.
{
mysql_connect() or die ("Problem connecting to Database");

// Define a SQL Select Query

$sql= mysql_query("SELECT wname, url, title, keywords, description, city, country, category MATCH (wname, url, title, keywords, description, city, country, category) AGAINST $search FROM directory", $db);

But it does not work.

6:47 am on Jan 10, 2005 (gmt 0)

Junior Member

10+ Year Member

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


When trying to search with multiple search words this does not work either:

----Start Code

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

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

$terms = preg_split("/\s+/",$search);

$term = "%".$terms."%";

// Define a SQL Select Query

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

---- End Code

Any ideas?

~Shane

5:51 pm on Jan 10, 2005 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member 10+ Year Member

joined:Aug 1, 2003
posts:815
votes: 0


Your fulltext query is not well-formed. Try:


SELECT wname, url, title, keywords, description, city, country, category
FROM directory
MATCH (wname, url, title, keywords, description, city, country, category)
AGAINST ('$search')

You'll want to use

mysql_escape_string()
on your $search variable before supplying it to your SQL query.
7:10 pm on Jan 10, 2005 (gmt 0)

Junior Member

10+ Year Member

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


I tried a FUll-TEXT SEARCH but it did not work:

--->Begin Code

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

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

$terms="%".$search."%";
$escaped_terms = mysql_escape_string($terms);

// Define a SQL Select Query

$sql= mysql_query("SELECT wname, url, title, keywords, description, city, country, category
FROM directory MATCH (wname, url, title, keywords, description, city, country, category) AGAINST ('$escaped_terms')", $db);

[Line 95] $num_rows = mysql_num_rows($sql); // Number of search results

if ($sql)
{
mysql_connect() or die ("Problem connecting to Database");
echo "<b>&nbsp;&nbsp;Found <u>".$num_rows."</u> results to match search string: ".$search."</b><br><br>";

<---End Code

RESULT = Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/****/public_html/search_results3.php on line 95

ANy suggestions?

8:27 pm on Jan 10, 2005 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member 10+ Year Member

joined:Aug 1, 2003
posts:815
votes: 0


You'll get more info if you check your query when you run it:

$query_text = "SELECT wname, url, title, keywords, description, city, country, category
FROM directory MATCH (wname, url, title, keywords, description, city, country, category) AGAINST ('$escaped_terms')";

if(! $sql= mysql_query($query_text, $db) )
echo "Query failed: \n[$query_text]\n" . mysql_error()."\n";

8:45 pm on Jan 10, 2005 (gmt 0)

Junior Member

10+ Year Member

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


It did not work:

Query failed: []
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/deep1/public_html/search_results3.php on line 97
problems....

----- Begin Code

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

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

$terms="%".$search."%";
$escaped_terms = mysql_escape_string($terms);

// Define a SQL Select Query

$sql= mysql_query("SELECT wname, url, title, keywords, description, city, country, category
FROM directory MATCH (wname, url, title, keywords, description, city, country, category) AGAINST $escaped_terms", $db);

echo "Query failed: \n[$sql]\n" . mysql_error()."\n";

[line 97] $num_rows = mysql_num_rows($sql); // Number of search results

----- End Code

Line 97 is not the problem... it works with this single search word only search string:

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

But, like I said, the above won't work for multi-word search strings...

~Shane

1:47 am on Jan 11, 2005 (gmt 0)

New User

10+ Year Member

joined:Dec 25, 2004
posts:32
votes: 0


try looking at a handy function called mysql_error();

Also RTFM -> [dev.mysql.com...]

If you get an error check your SQL syntax like trimster suggested with echo'ing it out.

goodluck

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members