Need a SQL query

Discussion in 'Programming' started by ahkip, Jun 28, 2007.

0
  1. #1
    basically all the query have to do is to count the occurrence of each word in the database with this structure

    CREATE TABLE `lyrics` (
    `id` int(7) NOT NULL auto_increment,
    `artist` varchar(250) NOT NULL default ´´,
    `title` varchar(250) NOT NULL default ´´,
    `album` varchar(250) NOT NULL default ´´,
    `letter` char(2) NOT NULL default ´´,
    `lyrics` text NOT NULL,
    PRIMARY KEY (`id`),
    FULLTEXT KEY `artist` (`artist`),
    FULLTEXT KEY `title` (`title`),
    FULLTEXT KEY `album` (`album`)
    ) ENGINE=MyISAM AUTO_INCREMENT=413965 DEFAULT CHARSET=latin1;

    You will only do the lyric column.

    example is like i have a lyric like this "i love you long time. Do you love me or not. I don't want to waste my time to love you" and the output will be

    love 3
    you 3
    to 2
    time 2
    i 1
    long 1
    do 1

    etc

    Let me know if you understand it and quote me the price
     
    ahkip, Jun 28, 2007 IP
  2. smellynose

    smellynose Peon

    Messages:
    90
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    As Seller:
    100% - 0
    As Buyer:
    100% - 0
    #2
    I wrote this in the little quick reply box at the bottom of the page, so it might not work :)
    
<?php
db_connect();
$q = "select id, lyrics from lyrics";
$r = db_q_raw($q);

while($row = mysql_fetch_assoc($r)) {
    $words = array_map("removeCommas", explode(" ", $row['lyrics']));
    foreach($words as $k=>$word) {
        $count[$word]++;
    }
}

foreach($count as $k=>$v) {
    echo "{$k} - {$v}";
}

function removeCommas($str) {
    $arr1 = Array(",", ".", "!", "?", "\"", "'");
    return str_replace("", "", $str);
}
?>
    Code (markup):
     
    smellynose, Jun 28, 2007 IP