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

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

PHP Server Side Scripting Forum

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

10+ Year Member



 
Msg#: 6430 posted 1:07 am on Jan 9, 2005 (gmt 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.

 

Netizen

10+ Year Member



 
Msg#: 6430 posted 8:00 pm on Jan 9, 2005 (gmt 0)

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

alcheme

10+ Year Member



 
Msg#: 6430 posted 10:43 pm on Jan 9, 2005 (gmt 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.

alcheme

10+ Year Member



 
Msg#: 6430 posted 6:47 am on Jan 10, 2005 (gmt 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

timster

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 6430 posted 5:51 pm on Jan 10, 2005 (gmt 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.

alcheme

10+ Year Member



 
Msg#: 6430 posted 7:10 pm on Jan 10, 2005 (gmt 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?

timster

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 6430 posted 8:27 pm on Jan 10, 2005 (gmt 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";


alcheme

10+ Year Member



 
Msg#: 6430 posted 8:45 pm on Jan 10, 2005 (gmt 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

jshpro2

10+ Year Member



 
Msg#: 6430 posted 1:47 am on Jan 11, 2005 (gmt 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

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