Count the number of rows in a MySQL table have a qualifying number in two columns

I have a database with a table user_entries and two columns max_val and num.

I have used the following code to extract my two columns into arrays and then use the below function numberTimesOver to determine how many times $num[$i] > $limit when $val[$i] = 10. The numbers range from 0 to 10.

When I use my function with my two arrays from my table I get an output of zero, when it should be 4. When I use my $a and $b arrays I get an output of 2.

Where am I going wrong? I think I am going wrong when I try to convert my table columns into arrays.

<?php
require_once('functions.php');
$mysqli = new mysqli('localhost', 'root', NULL, 'practice');

$val_sql = "SELECT `max_val` FROM `user_entries`";
$valData = $mysqli->query($val_sql);

$num_sql = "SELECT `num` FROM `user_entries`";
$numData = $mysqli->query($num_sql);

$val = array();
while($row = $valData->fetch_assoc())
{
    $val[]= $row;
}

$num = array();
while ($row = $numData->fetch_assoc())
{
    $num[] = $row;
}

//$a = [10, 4, 5, 8, 3];
//$b = [10, 6, 10, 10, 2];

function numberTimesOver($number, $max, $limit) {
    
    $sum = 0;
    
    for ($i = 0; $i < count($max); $i++)
    {
        if ($max[$i] == 10 && $number[$i] > $limit)
        {
            $sum += 1;
        }
    }
    print($sum);
}

numberTimesOver($val, $num, 6)

Solution

I think you're approaching this from the wrong angle.

It looks like you want to count the number of rows where max_val is greater than $limit (6) and num is equal to 10.

Relational databases are very good at this sort of thing. All you need is the following...

$maxVal = 6;
$num = 10;
$stmt = $mysqli->prepare(
    'SELECT COUNT(1) FROM `user_entries` WHERE `max_val` > ? AND `num` = ?');
$stmt->bind_param('ii', $maxVal, $num);
$stmt->execute();
$stmt->bind_result($sum);
$stmt->fetch();

echo $sum;

To summarise...

I've created a COUNT query as that's the data you're after. The query includes the parameters / conditions required in the WHERE clauses.
The prepare method prepares an SQL statement. With this, you can bind parameters (see next point). Think of the query as a function and the ? characters as arguments to that function. Reference - http://php.net/manual/mysqli.prepare.php
bind_param binds the PHP variables to be used as parameters to the query. These are in the order that the ? characters appear. The "ii" string denotes the format, i meaning integer, so two integers. See http://php.net/manual/mysqli-stmt.bind-param.php
execute simply executes the query on the database server. See http://php.net/manual/mysqli-stmt.execute.php
bind_result binds the statement column results (in order) to the supplied PHP variables. As we only have one column (COUNT(1)), we only need to bind one variable ($sum). See http://php.net/manual/mysqli-stmt.bind-result.php
fetch simply fetches the next row result and populates the bound result variables. As a COUNT statement only has one row, we only need to run this once. See http://php.net/manual/mysqli-stmt.fetch.php