[Resolved] a little Search engine.. searching by keywords

sapoxgn

Hi there folks..

I m making a little project and i m too damn noob in mysql

And i need to make a search on the database.. i have this:

Description
Keywords

I ll put the entries on the database and it is not going to search in the description field but in the keywords field..

How is the best way to put it in the database? using spaces or what?

And how is my query going to looks like... like this:

If somebody search for:

Unreal tournament 2004 cheats

I ll have unreal tournament , unreal tournament 2004 but i dont have cheats... and i need to display this like:

Unreal tournament 2004
unreal tornament

cause the first one has found 3 keywords.

Is there any example or something like this for me to take a look at?

Thanx for any help 🙂

sfullman

Algorithms are complex.

Unreal tournament 2004 cheats

I'll just number the words 1-4 for ease.

You could set up a search like this:

where

keywords like '%1 2 3 4%' OR
keywords like '%1%' OR
..
keywords like '%4%'
ORDER BY
IF(keywords like '%1 2 3 4%',16,0) +
IF(keywords like '%1%',4,0) +
IF(keywords like '%2%',4,0) +
IF(keywords like '%3%',4,0) +
IF(keywords like '%4%',4,0)
DESC

[NOTE: you might also be able to remove the where clause in some cases and just select * to see if you get better speed. Since results are desc, you can use php to decide if the first record has 0 as a value]

so you see that something having all four would rank the highest numerically and be at the top.

I don't know how to get a COUNT of matches in a string, which ironically I was searching for just before your post.

You would need tofigure out the weighting of the number values.

Also, consider this phrase:

a red pig in scarlet clothing

a and in are not significant, and you can "split" the string into "red pig" and "scarlet clothing" using a good regex pattern - so some pre parsing would be helpful so that you don't kill your db with a long query.

Those are the two main concepts I'm aware of in searching. However you might also look at soundex() as a function and other similar at dev.mysql.com.

If anyone has a solution along these lines, I'm signed upf for the thread :-)

Sam

sapoxgn

ok, i think i undestood your solution... btw

How am i going to split the phrase and then query?

if somebody types: i m searching for unreal tournamente 2004 cheats

I ll have to search for each word..

and as i said i m tottally noob on all this 🙁

Edit: i think this should solve my problem:

#

mysql> SELECT 'justalnums' REGEXP '[[:alnum:]]+';       -> 1
mysql> SELECT '!!' REGEXP '[[:alnum:]]+';               -> 0

#

[[:<:]] , [[:>:]]


mysql> SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]';   -> 1
mysql> SELECT 'a xword a' REGEXP '[[:<:]]word[[:>:]]';  -> 0

Btw.. how should i use it? 😕

sapoxgn

nobody else can help?

I think that using regexp is perfect.. but i need to understant what those symbols do.. and the mysql manual isnt very clear about that...

thanks for any help 🙂

This is the better solution for me:

mysql> SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]'; -> 1

But how am i going to chance the word to a var?
😕

sapoxgn

So.. i ve made it by myself (thanx to jonas galvez for the regexp help) 😃

here´s the code:

function csv_explode($str, $delim = ' ', $qual = '"')
{
   $len = strlen($str);
   $inside = false;
   $word = '';
   for ($i = 0; $i < $len; ++$i) {
       if ($str[$i]==$delim && !$inside) {
           $out[] = $word;
           $word = '';
       } else if ($inside && $str[$i]==$qual && ($i<$len && $str[$i+1]==$qual)) {
           $word .= $qual;
           ++$i;
       } else if ($str[$i] == $qual) {
           $inside = !$inside;
       } else {
           $word .= $str[$i];
       }
   }
   $out[] = $word;
   return $out;
} 

// Test...

$title = $_GET['title'];
$search = csv_explode($title);
//echo "test: <pre>".print_r( csv_explode($_GET['title']), true )."</pre>";
$search2 =  stripslashes(implode("|", $search));
$query = "SELECT * FROM sites WHERE keywords REGEXP '(^|[ \"\\'])$search2([ \"\\']|$)' ORDER BY priority ASC, data ASC LIMIT $rstart,$sizeLimit";
$query_result = mysql_query($query) or die(mysql_error());

okuto1973

Originally posted by sapoxgn
So.. i ve made it by myself (thanx to jonas galvez for the regexp help) 😃

here´s the code:

function csv_explode($str, $delim = ' ', $qual = '"')
{
   $len = strlen($str);
   $inside = false;
   $word = '';
   for ($i = 0; $i < $len; ++$i) {
       if ($str[$i]==$delim && !$inside) {
           $out[] = $word;
           $word = '';
       } else if ($inside && $str[$i]==$qual && ($i<$len && $str[$i+1]==$qual)) {
           $word .= $qual;
           ++$i;
       } else if ($str[$i] == $qual) {
           $inside = !$inside;
       } else {
           $word .= $str[$i];
       }
   }
   $out[] = $word;
   return $out;
} 

// Test...

$title = $_GET['title'];
$search = csv_explode($title);
//echo "test: <pre>".print_r( csv_explode($_GET['title']), true )."</pre>";
$search2 =  stripslashes(implode("|", $search));
$query = "SELECT * FROM sites WHERE keywords REGEXP '(^|[ \"\\'])$search2([ \"\\']|$)' ORDER BY priority ASC, data ASC LIMIT $rstart,$sizeLimit";
$query_result = mysql_query($query) or die(mysql_error());

[/B]

I think there is an error in your typing....it doesnt work your query....
What's exactly your query to db?

SELECT * FROM sites WHERE keywords REGEXP '(^|[ \"\'])$search2([ \"\']|$)'

SELECT * FROM sites WHERE keywords REGEXP '(^{|["'])$search2([} "']|$)'

okuto1973

well it doesn't appear the backslashe before the char ' when i write it in this post.....

okuto1973

For what i have understood

if i make a regexp '$search2' it find also a "championship" if you search for "ship"

'(^|[ \"'])

tell that all the words in $search2 have to start with what's in $search2 but before the double brakets you write a \ becouse you start the string $query="....." with double brakets but before the single one ' how many \ must be exactly in the query databases?