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

[alcheme]

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.

[Netizen]

Take a look at full text indexing [dev.mysql.com] in MySQL - that should solve your problems.

[alcheme]

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.

[alcheme]

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

[timster]

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.

[alcheme]

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?

[timster]

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"; 

[alcheme]

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

[jshpro2]

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