How to get the number of times a word is repeated in MYSQL

Question

How to get the number of times a word is repeated in MYSQL

Navigation

#1 by (6 votes)
#2 by (0 votes)
#3 by (0 votes)

3

How can I get the number of times the word "false" is repeated, in a column from mysql.

example:

id |  read  |
-------------
1  | false  |
-------------
2  | true   |
-------------
3  | false  |
-------------
4  | false  |
-------------

total = 3 times

I tried to make the query like this:

    $query = mysqli_query($connect, "SELECT 
(length(read)-length(replace(read ,'false','')))/5 as COUNT   
FROM  ge_mensajes_col");

    $cant = mysqli_fetch_array($query);

    $json = '{"newMSJ": [';

        $char ='"';

        $json .= 
        '{
            "total":"'.$query.'"
        },';    

    // buat menghilangkan koma diakhir array
    $json = substr($json,0,strlen($json)-1);
    $json .= ']}';
    // print json
    echo $json; 
    mysqli_close($connect);

But it does not work for me, I do not get the amount in the Json, it shows me empty.

mysql sql php

asked by Rick 15.09.2018 в 21:11

source

3 answers

Difference between new Array () in ES5 and Array.of () in ES6 in JavaScript know the auto_increment of a MySQL table after deleting the last ID's?

score 6 · Answer 1

From the MySQL console, to get the number of times the word false is repeating would be enough to do the following

SELECT COUNT(read) AS Total 
FROM nombreTabla
WHERE read = 'false';

What I did was that the aggregation function COUNT() would pass the name of the column where I need the count to be made.

Later so that you only return me as many times as the word false is repeated, I use a WHERE at the end where I indicate that only do the count where the word is equal to false

I leave this functional example where I put into practice the theory that explains you

LINK

How does the aggregation function COUNT() work?

The aggregation function COUNT() will return the total number of rows that meet a certain condition

score 0 · Answer 2

If you want to count them all in a single query, try this:

SELECT 'reader', COUNT('reader') 
FROM ge_mensajes_col
GROUP BY 'reader'

The "GROUP BY" helps to group the records of a column, in this case it groups by reader .

score 0 · Answer 3

This solution is very simple and will help you count the rows that contain the word false in the column read I see that you are using php Then it would be using mysqli_num_rows to get the rows that meet the condition WHERE read LIKE '%false%'

$sql = "SELECT read FROM ge_mensajes_col WHERE read LIKE '%false%'   ";
$result = mysqli_query($conn, $sql);

echo mysqli_num_rows($result)