Why can't I get info from a PDO associative array to echo?

Question

In the code below, the echo statement doesn't happen. This is particularly frustrating as I have an identical while loop on a previous page with a nearly identical query, plus buttons with GET and POST values, that function perfectly fine.

Also, as you can see I have this line: print_r($dispData = $getData->fetch(PDO::FETCH_ASSOC)) And it prints the results of associative array perfectly.

Below is the entire document.

<?php

  error_reporting(E_ALL);
  ini_set('display_errors', 1);

  session_start();

  include 'connect.php';

  $username = $_SESSION['username'];

?>

<html lang="en">

  <head>
    <meta charset="utf-8">
      <meta http-equiv="X-UA-Compatible" content="IE=edge">
      <meta name="viewport" content="width=device-width, initial-scale=1">
      <title>47th Personnel Management Portal</title>
      <!-- Bootstrap -->
      <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
      <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
      <script src="js/bootstrap.js"></script>
      <script src="js/bootstrap.min.js"></script>
  </head>

  <body>

    <?php
      #Include navigation
      include 'navbar.php';

      #Verify edit button's GET value was set
      if(isset($_GET['edit']))
      {
        try
        {
          #Assign variable to edit button's GET value which is members.member_ID
          $member_id = $_GET['edit'];
          #Prepare query
          $getData = $db->prepare("SELECT members.member_id, groups.g_title, members.members_display_name, members.title, pfields_content.field_17, pfields_content.field_18, members.email, from_unixtime(members.last_visit), members.m_awards_display, members.m_awards
                                    FROM members
                                      INNER JOIN groups ON members.member_group_id = groups.g_id
                                      INNER JOIN pfields_content ON members.member_id = pfields_content.member_id
                                    WHERE members.member_id = :member");
          #Sanitize data by binding SQL variable to PHP variable
          $getData->bindParam(':member', $member_id, PDO::PARAM_INT);
          $getData->execute();
        }

        catch(PDOException $e)
        {
            echo 'ERROR: ' . $e->getMessage();
        }
      }

      else
      {
          echo 'This user does not exist.';
          print_r($_GET);
      }
     ?>

      <div class="container">
        <div class="jumbotron">
            <div class="table-responsive">

              <table class="table" id="table">

                <thead>
                  <tr>
                    <th>47ID</th>
                    <th>Rank/Grade</th>
                    <th>Name</th>
                    <th>Title</th>
                    <th>Vexillation</th>
                    <th>Section</th>
                    <th>Email</th>
                    <th>Last Visit</th>
                    <th>Awards</th>
                  </tr>
                </thead>

              <tbody>

                <?php
                    #Print array for debugging purposes
                    print_r($dispData = $getData->fetch(PDO::FETCH_ASSOC));

                    //Fetch data and display items
                    while ($dispData = $getData->fetch(PDO::FETCH_ASSOC)) {

                      #Replace keys with human readable
                      if ($dispData['field_17'] == "c") {
                        $dispData['field_17'] = "Century";
                      }
                      if ($dispData['field_17'] == "v") {
                        $dispData['field_17'] = "Civilian";
                      }
                      if ($dispData['field_17'] == "a") {
                        $dispData['field_17'] = "Alliance";
                      }
                      if ($dispData['field_17'] == "r") {
                        $dispData['field_17'] = "Reception";
                      }
                      if ($dispData['field_17'] == "1") {
                        $dispData['field_17'] = "1st";
                      }
                      if ($dispData['field_17'] == "2") {
                        $dispData['field_17'] = "2nd";
                      }
                      if ($dispData['field_17'] == "3") {
                        $dispData['field_17'] = "3rd";
                      }
                      if ($dispData['field_17'] == "4") {
                        $dispData['field_17'] = "4th";
                      }
                      if ($dispData['field_17'] == "e") {
                        $dispData['field_17'] = "Reserve";
                      }
                      if ($dispData['field_17'] == "l") {
                        $dispData['field_17'] = "Legion";
                      }
                      if ($dispData['field_18'] == "n") {
                        $dispData['field_18'] = "N/A";
                      }
                      if ($dispData['field_18'] == "a") {
                        $dispData['field_18'] = "Alpha";
                      }
                      if ($dispData['field_18'] == "b") {
                        $dispData['field_18'] = "Bravo";
                      }
                      if ($dispData['field_18'] == "c") {
                        $dispData['field_18'] = "Charlie";
                      }
                      if ($dispData['field_18'] == "d") {
                        $dispData['field_18'] = "Delta";
                      }
                      if ($dispData['field_18'] == "h") {
                        $dispData['field_18'] = "HQ";
                      }

                      echo "<tr><td>"
                      .$dispData['member_id']."</td><td>"
                      .$dispData['g_title']."</td><td>"
                      .$dispData['members_display_name']."</td><td>"
                      .$dispData['title']."</td><td>"
                      .$dispData['field_17']."</td><td>"
                      .$dispData['field_18']."</td><td>"
                      .$dispData['email']."</td><td>"
                      .$dispData['from_unixtime(members.last_visit)']."</td></tr>

                      <form action='update_row.php' method='POST' style='display:inline-block;'>
                        <button type='submit' class='btn btn-primary' name='update' value='".$dispData['member_id']."' onclick='return confirm_update()'>Update</button>
                      </form></td></tr>";
                  }
                ?>

          </tbody>
        </table>

        </div>
      </div>
    </div>

  </body>

</html>

<script>
  function confirm_update() {
    return confirm('Are you sure you want to delete this user?');
  }
</script>

do you really have a DB column named from_unixtime(members.last_visit) ? and no error from PHP ? — OldPadawan

u_mulder u_mulder · Accepted Answer · 2017-05-07T20:57:55

Your code looks fine unless one logical error.

Function $getData->fetch(PDO::FETCH_ASSOC) fetches one row from a resultset. So when you run $getData->fetch(PDO::FETCH_ASSOC) again it fetches second row, with third call - it fetches third row, etc.

So, if you have one row as a result of a query, line

print_r($dispData = $getData->fetch(PDO::FETCH_ASSOC));

will fetch it. But second call in

while ($dispData = $getData->fetch(PDO::FETCH_ASSOC))

will fetch nothing, as one row has already been fetched and there's nothing left.

This also means, that when you will have two or more rows in a resultset, your code

print_r($dispData = $getData->fetch(PDO::FETCH_ASSOC));

will print first row, and all other rows except first will be used for html output.

So, I advise you to remove from your code line:

print_r($dispData = $getData->fetch(PDO::FETCH_ASSOC));

And for debugging purposes use something like:

while ($dispData = $getData->fetch(PDO::FETCH_ASSOC)) {
    print_r($dispData);

    // other codes
}

Why can't I get info from a PDO associative array to echo?

1 Answers