count multiple values in multiple column PHP-CodePudding

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>";
}