Home > database >  count multiple values in multiple column PHP
count multiple values in multiple column PHP

Time:11-03

i want to count multiple values from 3 columns and sorted descending using PHP mysqli, and I can't do it.

name1 name2 name3
mike jack rose
jack mike Mary
jack Mary John

expect output:

jack 3 
mike 2
mary 2
rose 1
John 1

My code:

$query = "select count(*) as c from work" ;
$result = $con->query($query);
$count = $result->fetch_object()->c;

CodePudding user response:

Use UNION to get them all into a single column. Then use COUNT(*) and GROUP BY:

$query = "
SELECT name, COUNT(*) AS c
FROM (
    SELECT name1 AS name
    FROM work
    UNION ALL
    SELECT name2 AS name
    FROM work
    UNION ALL
    SELECT name3 AS name
    FROM work
) AS x
GROUP BY name
ORDER BY c DESC";
$result = $con->query($query);
while ($row = $result->fetch_object()) {
    echo "$row->name $row->c<br>";
}
  • Related