Page 1 of 1

Similar words

Posted: Thu Sep 01, 2005 7:51 am
by online
Hello all!

This is the structure of my mysql table:

CREATE TABLE `terms` (
`termID` int(10) unsigned NOT NULL auto_increment,
`keyword` text,
`date` date default NULL,
`type` enum('web','images','news','video','audio','shopping') default 'web',
`count` int(11) default NULL,
PRIMARY KEY (`termID`)
) TYPE=MyISAM AUTO_INCREMENT=50242 ;

and some records...

INSERT INTO `terms` VALUES (1, '12+more', '2005-08-03', 'web', 408);
INSERT INTO `terms` VALUES (2, 'sexy+3gp', '2005-08-03', 'video', 88);
INSERT INTO `terms` VALUES (3, 'download+crack+mp3', '2005-08-03', 'web', 73);
INSERT INTO `terms` VALUES (4, 'me', '2005-08-03', 'audio', 28);
INSERT INTO `terms` VALUES (5, 'time+public', '2005-08-03', 'web', 168);
INSERT INTO `terms` VALUES (6, 'hussyfan+pthc', '2005-08-03', 'web', 122);
INSERT INTO `terms` VALUES (7, 'child+hentai', '2005-08-03', 'images', 66);
INSERT INTO `terms` VALUES (8, 'remix', '2005-08-03', 'web', 167);
... i have more then 50.000 records...

What i need...

I have some words, for example: music+remix+mp3

And..i want to display the similar words from table(row keyword)!
The first 20 phrases from mysql table that contain music or remix or mp3

You understand?

Thanks!

Re: Similar words

Posted: Thu Sep 01, 2005 8:10 am
by JAM
online wrote:You understand?

Thanks!
I might...

split() the music+remix+mp3 string with '+' so that you get each words on their own. Then use that in the sql query. Example;

Code: Select all

select fields from terms where keywords like '%music%' or keywords like '%remix%' or keywords like '%mp3%' limit 20

Yes..

Posted: Thu Sep 01, 2005 8:18 am
by online
And how do i write for a different phrases... (music+time or all+about+you...etc)?

The php and mysql code...


Thank you!

Posted: Thu Sep 01, 2005 9:13 am
by JAM
Example to get you going. Play around with it.

Code: Select all

<?php
    $test_string = "FOO+BAR+OTHERWORD HERE";

    $array = split('[/.+]', $test_string); // split it
    $out = ""; // assign empty var
    foreach ($array as $val) { // loop each
        $out .= " field LIKE '%{$val}%' OR"; // ... and add them
    }
    echo "select * from table where". substr($out, 0, -3) ." LIMIT 20"; // dirty substr out the last 'OR'
?>

Posted: Thu Sep 01, 2005 9:19 am
by s.dot
or you could just build your query using something that will always be true and use the OR at the beginning, so you don't have an extra OR at the end.

Code: Select all

$query = "SELECT * FROM table WHERE id != '' ";

foreach($array as $value)
{
   $query .= "OR field = '$value' ";
}

$query .= "LIMIT 20";
$result = mysql_query($query);
Edit: Well poop, I guess that wouldn't work. But the logic remains the same.
Edit #2: Ignore Edit #1, I guess I felt brilliant for a moment.

Posted: Thu Sep 01, 2005 9:59 am
by online
JAM wrote:Example to get you going. Play around with it.

Code: Select all

<?php
    $test_string = "FOO+BAR+OTHERWORD HERE";

    $array = split('[/.+]', $test_string); // split it
    $out = ""; // assign empty var
    foreach ($array as $val) { // loop each
        $out .= " field LIKE '%{$val}%' OR"; // ... and add them
    }
    echo "select * from table where". substr($out, 0, -3) ." LIMIT 20"; // dirty substr out the last 'OR'
?>
\

I try... and i modify after...and it display this error: Resource id #3

Posted: Thu Sep 01, 2005 10:11 am
by feyd
that's not an error, that's a resource identifier. You'll need to use mysql_fetch_assoc() or a sibling function.

For my problem..

Posted: Thu Sep 01, 2005 12:50 pm
by online
Somebody...can post the all php code for my problem, please?

Posted: Thu Sep 01, 2005 12:54 pm
by feyd
look at the examples given on the mysql_fetch_assoc() and other mysql pages.

But

Posted: Thu Sep 01, 2005 3:05 pm
by online
But i'm new in php..so could you help me, please?

Posted: Thu Sep 01, 2005 11:23 pm
by josh
have you read the manual pages feyd posted? after and only after reading those ask questions. We are not here to program for you, if you want that you can hire a freelance programmer.

up

Posted: Sun Sep 04, 2005 10:03 am
by online
up

Posted: Sun Sep 04, 2005 10:26 am
by patrikG
There's also MySQL's SOUNDEX which might come in handy.