Add values from mysqli_fetch_array

Question

I'm this is my first coding project from school. I have a task to check, whether two conditions are true and if they are, I have to count the occurrences. I'm really stuck right now and I would appreciate every helpfull answer.

So this is part of the code:

$verbindung = mysqli_connect($server, $user, $pass, $database)
                    or die ($meldung);

    $driverID = $_POST['fahrer'];

    $datum_von = $_POST['date_von'];
    //Change Date Format
    $datum_von_new = date_format(new DateTime($datum_von), 'Y-m-d');

    $datum_bis = $_POST['date_bis'];
    //Change Date Format
    $datum_bis_new = date_format(new DateTime($datum_bis), 'Y-m-d');

    $sql = "SELECT drivers.forename, drivers.surname, results.positionOrder, races.name, races.date ";
    $sql.= "FROM drivers ";
    $sql.= "INNER JOIN results ON drivers.driverId = results.driverId ";
    $sql.= "INNER JOIN races ON results.raceId = races.raceId ";
    $sql.= "WHERE drivers.driverId = '$driverID' "; 
    $sql.= "AND races.date BETWEEN '$datum_von_new' ";
    $sql.= "AND '$datum_bis_new' ";
    $sql.= "AND results.positionOrder <= 10;"   

<table class="table table-striped">
        <thead class="thead-dark">  
            <tr>
              <th scope="col">Fahrername</th>
              <th scope="col">Streckenname</th>
              <th scope="col">Datum</th>
              <th scope="col">Platzierung</th>
              <th scope="col">Häufigkeit</th>
            </tr>
        </thead>


    $ergebnis = mysqli_query($verbindung, $sql);
    $countPosition = 0

    if ($ergebnis != False) {
                while($zeile = mysqli_fetch_array($ergebnis)) { 
                    echo "<tr>";
                    echo "<td>" . $zeile['forename'] . ' ' . $zeile['surname'] . "</td>";
                    echo "<td>" . $zeile['name'] . "</td>";
                    echo "<td>" . $zeile['date'] . "</td>";
                    echo "<td>" . $zeile['positionOrder'] . "</td>";
                    for($i = 0; $i<=sizeof($zeile); $i++) {
                        for($j = 0; $j <= sizeof ($zeile); $j++) {
                            if($zeile['name'][$i] == $zeile['name'][$j] && 
                            $zeile['positionOrder'][$i] == $zeile['positionOrder'][$j]) {
                                $countPosition += 1;
                                echo "<td>" . $countPosition . "</td>";
                        }
                    }
                } 
                    echo "</tr>";

            } else {
                echo mysqli_error($verbindung);
            }

            $result = mysqli_close($verbindung);

So my goal is to check, whether the name in line 1 is equal to the name in line 2 AND if the positionOrder in line1 is equal to posisitionOrder in line 2. If so, count how many times this is true. Help is appreciated. Thank you!

Answer 1

So, a few things.

1. You're going to want to change <= to < in your for loop or else you're going to get an index out of bound exception. See Should one use < or <= in a for loop

2. You could solve this much easier using MySql, unfortunately you haven't shared your SQL query so I'm not able to write this for you. But it would be some form of group by.

3. I noticed that you're doing $zeil['name'][$j] for a lot of things as compared to $zeil[$j]['name'], which is odd to me since that means you have an array of each element, instead of an array of "objects" (sort of) with properties attached to them. This isn't common practice, but I will try to work with it none the less.

Let's assume your array looks like this:

$zeil = [
    'name' => [
         'nathan',
         'raverx1',
         'someone',
         'nathan',
         'nathan',
         'nathan',
         'anyone',
         'nathan',
         'anyone',
    ],
    'positionOrder' => [
         4,
         7,
         9,
         4,
         4,
         4,
         7,
         4,
         7
    ]
];

You would need this code to accomplish your task:

// Loop through your main array while tracking
// the current index in the variable $i.
// Since you're using sub arrays, we use them
// as the index. Which means the sub arrays
// have to be of identical size, else you will
// get an index out of bounds error.
for($i = 0; $i < sizeof($zeil['name']); $i++) {

    // We set this to an initial value of 1 since
    // it is the first occurrence.
    $occurrenceCount = 1;

    // Create local variables containing the current values.
    $name = $zeil['name'][$i];
    $positionOrder = $zeil['positionOrder'][$i];

    // Loop through all previous entries to
    // compare them to the current one.
    for($j = $i-1; $j > -1; $j--) {
        if (
            $zeil['name'][$j] == $name &&
            $zeil['positionOrder'][$j] == $positionOrder
        ) {
            $occurrenceCount += 1;
        }
    }

    // If multiple occurrences were found
    // for this entry, output them.
    if ($occurrenceCount > 1)
        echo 'Occurrence Count ('.$name.') : '.$occurrenceCount.PHP_EOL;
}

The output would look like:

Occurrence Count (nathan) : 2
Occurrence Count (nathan) : 3
Occurrence Count (nathan) : 4
Occurrence Count (nathan) : 5
Occurrence Count (anyone) : 2

A better way to do this, would be to store the number of occurrences for anything with higher than 1 occurrence in it's own array, and then print them out.

$multipleOccurrences = [];

// Loop through your main array while tracking
// the current index in the variable $i.
// Since you're using sub arrays, we use them
// as the index. Which means the sub arrays
// have to be of identical size, else you will
// get an index out of bounds error.
for($i = 0; $i < sizeof($zeil['name']); $i++) {

    // We set this to an initial value of 1 since
    // it is the first occurrence.
    $occurrenceCount = 1;

    // Create local variable containing the current values.
    $name = $zeil['name'][$i];
    $positionOrder = $zeil['positionOrder'][$i];

    // Loop through all previous entries to
    // compare them to the current one.
    for($j = $i-1; $j > -1; $j--) {
        if (
            $zeil['name'][$j] == $name &&
            $zeil['positionOrder'][$j] == $positionOrder
        ) {
            $occurrenceCount += 1;
        }
    }

    // If multiple occurrences were found
    // for this entry, store them using $name as the key.
    if ($occurrenceCount > 1)
        $multipleOccurrences[$name] = $occurrenceCount;
}

// Print the occurrences.
echo 'All occurrences greater than \'1\''.PHP_EOL;
echo '--------------------------------'.PHP_EOL;
foreach ($multipleOccurrences as $name => $occurrenceCount) {
    echo 'Occurrences ('.$name.') : '. $occurrenceCount . PHP_EOL;
}

The output would look like this:

All occurrences greater than '1'
--------------------------------
Occurrences (nathan) : 5
Occurrences (anyone) : 2

Keep in mind, that there are a few things WRONG with your initial approach.

1. You're not actually keeping track of the occurrences, since each iteration will also add all of the previous occurrences to the $occurrenceCount variable as well.

2. The data structure you're using isn't particularly friendly to properly tracking the number of occurrences. Storing sub arrays for the values is abnormal, a more common way would be something like:

   $zeil = [
       [
           'name' => 'nathan',
           'positionOrder' => 4
       ],
       [
           'name' => 'bob',
           'positionOrder' => 10
       ],
   
       . . .
   ];
   

3. The way your data structure is built requires you to have identical number of indexes for both of your sub arrays. Which can end up causing unwanted errors if you forget to have it be so.

Answer 2

When you iterate over the results, you would want the current row being read compared with the last one you processed.

for($i = 0; $i<=sizeof($zeile); $i++) {
    if($i != 0 && $zeile['name'][$i] == $zeile['name'][$i - 1] && 
        $zeile['positionOrder'][$i] == $zeile['positionOrder'][$i - 1]) {
        $countPosition += 1;
        echo "<td>" . $countPosition . "</td>";
    }
}

The above code will iterate over the rows only once but will always compare the last read row with the current one. It will not check earlier rows (only the previous row). So this might not be the ideal solution you are looking for. A better option might be to use group by option in MySQL SELECT. You might want to read the this: Mysql Tutorials - Group by