Similar words

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
online
Forum Newbie
Posts: 8
Joined: Thu Sep 01, 2005 7:48 am

Similar words

Post 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!
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Re: Similar words

Post 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
online
Forum Newbie
Posts: 8
Joined: Thu Sep 01, 2005 7:48 am

Yes..

Post 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!
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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'
?>
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
online
Forum Newbie
Posts: 8
Joined: Thu Sep 01, 2005 7:48 am

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

that's not an error, that's a resource identifier. You'll need to use mysql_fetch_assoc() or a sibling function.
online
Forum Newbie
Posts: 8
Joined: Thu Sep 01, 2005 7:48 am

For my problem..

Post by online »

Somebody...can post the all php code for my problem, please?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

look at the examples given on the mysql_fetch_assoc() and other mysql pages.
online
Forum Newbie
Posts: 8
Joined: Thu Sep 01, 2005 7:48 am

But

Post by online »

But i'm new in php..so could you help me, please?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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.
online
Forum Newbie
Posts: 8
Joined: Thu Sep 01, 2005 7:48 am

up

Post by online »

up
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

There's also MySQL's SOUNDEX which might come in handy.
Post Reply