Multiple Term Search Engine

[barrow]

Hi,

 

I have a database that looks like this

-----------------------------------------------

name | url | keywords | description |

-----------------------------------------------

 

My users will be entering search terms like \"20 something chat\" my problem is that if I use \"where\" and \"%like%\" to search the keywords and description columns, it will show very few results. How can I get the script to return all rows that contain ANY of the terms searched for?

 

Also.... if teen is a keyword and someone searches for \"teens\" how can I get this row to be returned without changing the keyword in the database to \"teens\"?

 

 

My database is of chat site links so I would delete common words like chat and free from the search field before the query.

 

Thanks a lot if you guys can help out.

[effigy]

use a function such as split to break the search terms apart, thus:

 

20

something

chat

 

then assemble a query from that array:

 

select * from table where field like \'%20%\' or field like \'%something%\' or field like \'%chat%\'

 

for \"teens\" — you could run a regular expression to check for words ending in \"s\" and chop it off for the query.

[barrow]

Sorry maybe I should have posted this under the newb category but how do I \"assemble a query from an array\"?

 

$arr = split (\" \", $searchstring);

 

$query = \"select name, url from chat_sites as c where c.description like ????\";

[effigy]

check this post: http://forums.phpfreaks.com/viewtopic.php?t=1088

some example code is half way down the page.

[barrow]

Thanks, that helped me out a lot but I want to select more than one column but not all of them. I can\'t figure it out with that sample code. Below is what I have managed so far it works but I don\'t know how to get it to include conditions such as

 

c.name.\"%\' and

c.keywords.\"%\' and

c.description.\"%\'

 

below is the code I have so far.

---------------------------------------------------------

 

 

[php:1:cc514f0d4f]<?php

<?

 

trim($searchstring);

$searchstring = addslashes($searchstring);

 

if (!$searchstring)

{

echo \"You have not entered any search terms. Please go back and try again.\";

exit;

}

 

// split searchstring

$words = split( \" \", $searchstring);

// echo split

echo(\'<b>split query:</b><br>\');

for($i=0; $i < count($words); $i++)

{

echo($words[$i] . \'<br>\');

}

echo(\'<br>\');

 

 

@ $db = mysql_pconnect(\"host address\", \"user\"password\");

 

if (!$db)

{

echo \"Error: Could not connect to database. Please try again later.\";

exit;

}

 

mysql_select_db(\"chat_directory\");

 

$query = \"select name, from chat_sites as c where\";

 

for($i=0;$i<count($words);$i++)

{

$query.=\" c.name like \'%\".$words[$i].\"%\'\";

 

if($i!=count($words)-1)

{

// this can be changed to \" and\"

$query.=\" and \";

}

}

 

$result = mysql_query($query);

$num_results = mysql_num_rows($result);

 

echo \"<p>Number of chat sites found: \".$num_results.\"</p>\";

 

for ($i=0; $i <$num_results; $i++)

{

$row = mysql_fetch_array($result);

echo \"<p><strong>\".($i+1).\". Title: \";

echo htmlspecialchars( stripslashes($row[0]));

echo \"</strong></p>\";

}

 

 

?>

?>[/php:1:cc514f0d4f]

[effigy]

what if you change...

 

[php:1:f4e2a213e3]

$query.=\" c.name like \'%\".$words[$i].\"%\'\";

[/php:1:f4e2a213e3]

 

to...

 

[php:1:f4e2a213e3]

$query.=\" c.name like \'%\".$words[$i].\"%\' and c.keywords like \'%\".$words[$i].\"%\' and c.description like \'%\".$words[$i].\"%\'\";

[/php:1:f4e2a213e3]

 

thus checking for that word in each field... so after the loop is done each word is accounted for in each field.

[barrow]

nope, can\'t do that

[barrow]

Sorry I think it does work.... I gotta keep testing to see if its doing what I want but I think thats it.

 

[php:1:129c1a4a14]<?php

for($i=0;$i<count($words);$i++)

{

$query.=\" c.name like \'%\".$words[$i].\"%\' or c.keywords like \'%\".$words[$i].\"%\' and c.description like \'%\".$words[$i].\"%\'\";

 

if($i!=count($words)-1)

{

// this can be changed to \" and\"

$query.=\" and \";

}

}

?>[/php:1:129c1a4a14]