Iterating Through Multiple Rows and Outputting the Result Inside a Function

Question

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.

It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Apr 10 '20 at 23:04
@Dharman Oh thank you, that helps a lot, I didn't know it would leak info to attackers, It's just what I'm used to doing. I'll change it from now :) — , Apr 11 '20 at 13:40

score 0 · Accepted Answer · answered Apr 10 '20 at 16:10

0

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 } ?>

answered Apr 10 '20 at 16:10

terrymorse

6,771
1
21
27

1

Thanks man! I knew there was a way to minimise code and it was on the tip of my head but I couldn't quite see it. This worked :D – Apr 10 '20 at 16:18
By the way, in the functions to getAllModules, is it better to have "return $this->execute("Put the query in here"), or is it better to have the SQL query in a separate variable (in the same function like above) and then "return $this->execute($qryVariable)"? – Apr 10 '20 at 16:23
Better is totally a question of style, but I like to keep my SQL query strings separate from function calls. – terrymorse Apr 10 '20 at 16:28

Iterating Through Multiple Rows and Outputting the Result Inside a Function

1 Answers1