Peter Valek Peter Valek - 5 months ago 9
PHP Question

Count the same numbers in rows and columns php mysql

I have MySql table like this :

+----+----+----+
| g1 | g2 | g3 |
+----+----+----+
| 1 | 2 | 5 |
+----+----+----+
| 5 | 1 | 3 |
+----+----+----+
| 1 | 3 | 4 |
+----+----+----+


And I need get output in PHP like:

number 1 is used 4 times
number 2 is used 1 time
number 3 is used 2 times


I make some code but it only write me how many time I used the number and I don't know how to add whitch number.
Thi is my code:

for ( $i=1 ; $i<=20; $i++){

$query = mysql_query("SELECT g1, g2, g3d FROM users
WHERE g1 = $i OR g2 = $i OR g3 = $i ") or die(mysql_error()) ;

$row1 = mysql_num_rows($query);

$row = mysql_fetch_array($query) ;
if ($row1 >=1){echo $row1; }
}


Thanks for any help.

Answer

I would recommend doing it this way:

// Will be used to keep track of how many times a certain number has appeared
$counter = array();

// Just get all of the rows from the table
$query = mysql_query("SELECT g1, g2, g3 FROM users");

// Loop all rows
while($row = mysql_fetch_assoc($query))
{
    // Loop each column: g1, g2, and g3
    foreach($row as $k=>$v)
    {
        // Use the column's value as the array key and let the value be the count of occurrences
        $counter[$v] = (isset($counter[$v]) ? ++$counter[$v] : 1);
    }
}

// Sort based on key from low to hi
ksort($counter);

// Loop the tracker and echo whatever you need
foreach($counter as $k=>$v)
{
    echo 'number '.$k.' is used '.$v.' time'.($v === 1 ? '' : 's').'<br>';
}
Comments