You need a dictionary.
Interestingly, in PHP arrays can work as dictionaries. I discovered this now by searching to answer the question. So let’s get started:
$dicionario = array();
The logic is as follows: make a dictionary where the keys are the words, and the value is the number of occurrences.
Since each record has several words separated by commas, let’s start by extracting the words from each record. In most languages we call it dividing (split
), but since PHP is a terrorist thing in it we use the function explode
:
$palavras = explode(",", $registro);
And then we include the words in the dictionary as follows: if the key does not exist, it is created with zero value. Then, regardless of whether it exists or not, we increase its value.
foreach ($palavras as $chave) {
if (!$dicionario[$chave]) {
$dicionario[$chave] = 0;
}
$dicionario[$chave]++;
}
Note that we have to do the explosion and the addition to the dictionary once for each record.
Finally, we need to get the records at the top ten. The algorithm below removes the top ten values from the dictionary while neatly including them in another dictionary.
$dezMaiores = array();
for ($i = 1; $i <= 10; $i++) {
$maiorValor = 0;
$maiorChave = "";
foreach ($dicionario as $chave => $valor) {
if ($valor > $maiorValor) {
$maiorValor = $valor;
$maiorChave = $chave;
}
}
$dezMaiores[$i] = $maiorChave;
unset($dicionario[$maiorChave]);
}
Now you can use the ten most commonly used expressions on your system :)
Put in question the table structure and examples of data that would be stored. Have you tried anything? At least describe how you think it can be done.
– Woss
Is there any possibility of you playing these words in an auxiliary table, or working with them in json?
– Papa Charlie
It is not clear what the structure is like, making an objective answer impossible. For now, see if this can be useful: https://stackoverflow.com/questions/748276
– Daniel Omine
Gabriel, in this case the column is the
hashtags
? Have you ever considered creating a many-to-many relationship between the tableposts
and a tablehashtags
? Probably the solution would be more semantic and it would be possible (I believe) to solve the problem only with SQL.– Woss
@Andersoncarloswoss However, I already have a system that separates the tags from within the text of
post
and add in columnhashtags
, when the post is inserted in the posts table.– Gabriel Henrique