Fetch and display an unsorted list using PDO-CodePudding

I try to fetch and display a results of a SELECT query using PDO in a unsorted list, but I can get only the half of my first item from that list. My code, so far, is:

<?php
  try {
    $conn = new PDO('sqlite:db/MyDatabase.db');
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare("SELECT genus, species FROM MyTable ORDER BY genus ASC, species ASC");
    $stmt->execute();
    $data = $stmt->fetchColumn(); 
    echo '<ul>' . '<li>' . $data . '<br/>' . '</li>' . '</ul>';
}
  catch(PDOException $e) {echo "Error: " . $e->getMessage();}
  $conn = null;
?>

But I only getting echoed the first item of the column "genus". Can you help me to get this unsorted list in a more friendly form of "genus (space) species"?

CodePudding user response：

fetchColumn() only returns the first column from a result set fetchAll() will return all rows from a table. Then loop through the array using foreach or while.

Trying to echo $data will not work since you cannot echo an array, you would need to specify the array keys which in this case would be the column names.

<?php
  try {
    $conn = new PDO('sqlite:db/MyDatabase.db');
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare("SELECT genus, species FROM MyTable ORDER BY genus ASC, species ASC");
    $stmt->execute();
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo '<ul>';
    if ( !empty($data) ) {
      foreach ( $data as $row ){
         echo '<li>'. $row['genus'] .' '. $row['species'] .'</li>';
      }
    } else {
      // something to show when no results.
    }
    echo '</ul>';
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}
$conn = null;
?>