Iterating Through Multiple Rows and Outputting the Result Inside a Function

I have a PHP file for one of my website pages which has all my HTML and PHP code in it. I have a table on the website and I want to populate it with data from an SQL table. I currently have all code (HTML, Javascript, SQL, PHP) in one file called “modules.php”. This is the code I have to populate the table:

<?php
      // Shows all the modules in the table
      $module_sql = "SELECT *, course.course_name FROM module INNER JOIN course on module.course = course.course_id";
      $module_qry = mysqli_query($link, $module_sql);
      while ($module = mysqli_fetch_array($module_qry)) { ?>
        <tr>
          <td><?php echo $module['module_name']; ?></td>
          <td><?php echo $module['course_name']; ?></td>
          <td><?php echo $module['perc_worth']; ?></td>
          <td><?php echo $module['credit']; ?></td>
          <td><?php echo $module['uni_year']; ?></td>
        </tr>
      <?php } ?>

This works perfectly when it is all in one file. It gets all the rows in the database, iterates through them and prints the results in the table until there are none left.

Now I am making a class file “queries.php” which will contain all my SQL code in separate functions. I will call each function from my “module.php” file. This is so far working great when I only have to execute a query or return a single row from the database, but I am having problems when I have to return multiple rows from the database. Here is the code i’ve implemented it so far:

queries.php

    function executeGetQry($qry) {
        $execute = mysqli_query($this->databaseConnect(), $qry) or die(mysqli_error(
            $this->databaseConnect()));

        if ($execute == null) {
            return null;
        } else {
            return mysqli_fetch_array($execute);
        }
    }

    function getAllModules() {
        $module_sql = "SELECT *, course.course_name FROM module INNER JOIN course on module.course = course.course_id";
        return $this->executeGetQry($module_sql);
    }

module.php

         <?php
          while ($module = $query->getAllModules()) { ?>
            <tr>
              <td><?php echo $module['module_name']; ?></td>
              <td><?php echo $module['course_name']; ?></td>
              <td><?php echo $module['perc_worth']; ?></td>
              <td><?php echo $module['credit']; ?></td>
              <td><?php echo $module['uni_year']; ?></td>
            </tr>
          <?php } ?>

However this is not working because every time the “while” loop is executed, it simply runs the function again and returns the same row over and over again and never iterates and returns the other rows.

QUESTION: How do I fix my function so that it will iterate through all the rows and return each row as an array so I can successfully put all my SQL in a different file and have the same result?

I have multiple SQL queries where I will need to iterate over the results, so I think I need to fix my executeGetQuery function.

Answer

Since mysqli_fetch_array() only returns one row on each call, that won’t work as a return value from getAllModules().

You could have getAllModules() return $execute, then your PHP code would be:

    <?php
        $execute = $query->getAllModules();
        while ($module = mysqli_fetch_array($execute)) { ?>
        <!-- your table row -->
    <?php } ?>

Advertisement

Answer