How to know the most used word in all records

Question

I’m doing a top trendig system, but I’m very confused: How do I select to pull only the 10 most commonly used words on all records.

However, the words are in a column and separated by a comma.

NOTE: I am using PHP 5.3

Tabela posts
Estrutura: 
id
post_id
post
author_id
author
hashtags
date

Answer 1

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 :)

Answer 2

Another solution running all the logic in PHP is to use the native functions array_merge and array_map to create the word list, the function array_count_values to calculate the frequency of each word and the function arsort to order them downwards.

<?php

$rows = [
    "abacate,banana,caqui",
    "banana,melão,goiaba",
    "laranja,banana,abacate"
];

$palavras = array_merge(
    ...array_map(
        function ($value) { 
            return explode(',', $value); 
        }, 

        $rows
    )
);

$palavras = array_count_values($palavras);

arsort($palavras);

print_r($palavras);

The above solution uses the splat Operator, so it only works in PHP 5.6 versions+.

The output produced is:

Array
(
    [banana] => 3
    [abacate] => 2
    [caqui] => 1
    [melão] => 1
    [goiaba] => 1
    [laranja] => 1
)

Answer 3

$link = new mysqli ("localhost", "USUARIO", "SENHA", "NOME_BANCO");

$query = "SELECT Nome_Coluna FROM Nome_Tabela";

$results = mysqli_query($link,$query);

while($row = mysqli_fetch_assoc($results)) {

  $words .= $row["Nome_Coluna"].",";

}

$palavras = explode(',', $words);

//numero total de palavras
//echo count($palavras); 

$ocorrencias = array();

for($i = 0; $i<count($palavras); $i++){
    $palavra = $palavras[$i];

      // Evitar erros Undefined index vide OBS no final da resposta
      if (!isset($ocorrencias[$palavra])) {
          $ocorrencias[$palavra] = 0;
      }

    $ocorrencias[$palavra]++;
}

arsort($ocorrencias);

//mostra todo o array estruturado
//var_dump($ocorrencias);

    //mostra os dez primeiros valores e respectivas chaves
    $i=0;
     foreach($ocorrencias as $chave => $valor){
            if($i < 10){  
                echo $chave.' - '.$valor.'<br>';
            }
            $i=$i+1;
        }

Note: In the first word, $ocorrencias is an empty list and when you do $ocorrencias[$palavra] you will be accessing a non-existent key. To avoid the error, just check if the key exists and, if it does not exist, create it with zero.
Source