Jump to content


Photo

Multiple Term Search Engine


  • Please log in to reply
7 replies to this topic

#1 barrow

barrow
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 28 January 2003 - 04:44 AM

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.

#2 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 28 January 2003 - 06:03 AM

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.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#3 barrow

barrow
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 28 January 2003 - 10:11 AM

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

#4 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 28 January 2003 - 07:31 PM

check this post: http://forums.phpfre...opic.php?t=1088
some example code is half way down the page.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#5 barrow

barrow
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 29 January 2003 - 05:04 AM

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]

#6 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 29 January 2003 - 05:55 AM

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.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#7 barrow

barrow
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 29 January 2003 - 06:04 AM

nope, can\'t do that :(

#8 barrow

barrow
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 29 January 2003 - 07:02 AM

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]




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users