limit/sort most common words in column help

[paulmo]

first I'm inserting in table (this works):

mysql_query("INSERT INTO user (name, message, created) VALUES ('$name', '$message', NOW()) ") or die(mysql_error());

next I need to retrieve (limit) the top 15 words in message column, user table, sorted vertically with occurrence # beside the word, like:

sea 23

tree 12

leaf 3

etc.

 

I've started this but I need help.

$archive = mysql_query("SELECT * FROM user ORDER BY message") or die (mysql_error());

$results = mysql_query($archive);

[fenway]

MySQL can't help you there -- you'll have to write a stored procedure or do this in application code.

[paulmo]

thanks, real helpful. 

 

go sox.

[fenway]

thanks, real helpful. 

 

go sox.

???

[paulmo]

after some consideration i'm pretty sure i'll do this with mysql. this is getting closer but it's only returning user text submit words, not the top 15 words in column 'message', which is what I want.

mysql_query("INSERT INTO user (name, message, created) VALUES ('$name', '$message', NOW()) ") or die(mysql_error());
$archive = mysql_query("SELECT CONCAT(message, ' ', COUNT(*)) FROM `user`
GROUP BY message
ORDER BY COUNT(*) DESC
LIMIT 15") or die (mysql_error());
$n=0;
while($row = mysql_fetch_assoc($archive)){
$n++;
echo ($row['message']);
echo "<br>";
}

[fenway]

Huh? I don't see any searching.

[kickstart]

Hi

 

Crude way of doing it in php would be something like this (not tested):-

 

<?php

$archive = mysql_query("SELECT message FROM user ORDER BY message") or die (mysql_error());
$results = mysql_query($archive);

$WordCount = array();

while ($Messages = mysql_fetch_array($result))
{
$Words = explode(' ',$Messages);
foreach($Words AS $Word) $WordCount[$Word]++;
}

arsort($WordCount);
$iCnt = 0;
foreach($WordCount AS $field=>$value)
{
echo "$field  occurs $value <br/>";
$iCnt++;
if ($iCnt >= 15) break;
}
?>

 

Only splits words up based on spaces, so you would probably want to modify it to split the messages up properly

 

While I can think of a way of doing this in pure MySQL it would not be nice code and I wouldn't want to use it for real.

 

All the best

 

Keith

[ignace]

thanks, real helpful. 

 

go sox.

 

I'm guessing you are a redneck

[paulmo]

thanks Keith! helps to see how a guru would do it. I'll have to study your arsort () more closely.