Count characters and get percentage of occurrence in Php or SQL

Question

I have a database with one table of two columns FirstName and LastName and my names are Bruce Wayne, so what I want is an output that will give me this:

a 1 10% B 1 10% c 1 10% e 2 20% n 1 10% r 1 10% u 1 10% W 1 10% y 1 10%

How many times each character has occurred in both names and what is the percentage of occurrence overall. I am new to php and mysql a little help would be welcome. Thus far I have found this http://php.net/manual/en/function.substr-count.php

$text = 'This is a test';
echo strlen($text); // 14

echo substr_count($text, 'is'); // 2

I don't know if the best approach would be php or sql. Thanks!

This can help you http://stackoverflow.com/questions/933253/what-is-the-most-efficient-way-to-count-all-the-occurrences-of-a-specific-charac — Web Artisan, Apr 28 '16 at 13:23
You want to know how many times each character in a string appears in that string. I don't understand what this has to do with SQL — Strawberry, Apr 28 '16 at 13:24
For example your name is `Strawberry` and you want to know what is composing your name what you would do is to query the name from the database first then echo is with php right? now. if you want to know each character that in in your name the first thing you will do is to change the string into maybe and array of characters then from there check how many `s` you have in it and how many `r` etc... that's my question — Second Son, Apr 28 '16 at 13:29

score 1 · Accepted Answer · answered Apr 28 '16 at 13:33

First split all letters in an array. Count the array. Loop through the array and count how often we see them. Then loop another time to print out the result:

<?php
$text = 'This is a test';
$text = str_replace(' ', '', $text );
$arrLetters = str_split($text);
$countLetters = count($arrLetters);

$letters = [];

foreach($arrLetters as $letter){
    if(isset($letters[$letter])){
        $letters[$letter] += 1;
    } else {
        $letters[$letter] = 1;  
    }
}

foreach($letters as $letter => $total){
    echo $letter.":".$total.":".round(($total/$countLetters*100),2)."%<br />";
}

Result:

T:1:9.09%
h:1:9.09%
i:2:18.18%
s:3:27.27%
a:1:9.09%
t:2:18.18%
e:1:9.09%

@SecondSon See. No database required. – Strawberry Apr 28 '16 at 14:36 — Strawberry, Apr 28 '16 at 14:36

score 0 · Answer 2 · answered Apr 28 '16 at 13:29

You asked:

I don't know if the best approach would be php or sql.

For this character-frequency application, you should fetch the strings with a MySQL query and count their frequencies in your php client side language.

You could work out some MySQL code to do the counts server side, but it will be quite complex.

Count characters and get percentage of occurrence in Php or SQL

2 Answers2