My PHP PDO fetchAll() code returns one row instead of all results on MySql column table

Question

I'm trying to populate all results from one column of MySql table through PHP and PDO FetchAll() with Prepared Statements but my code just return one row instead of all results. I saw this question here (PHP PDO returning single row) that is similar to my problem but i didn't get improve my code with this question.

I followed an example into PHP Manual page (http://php.net/manual/en/pdostatement.fetchall.php) to try to structure/adapt my code to fetchAll() results of one column in a MySql table:

PHP manual Page - FetchAll example with print:

<?php
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll();
print_r($result);
?>

And I adapted my code based on the example above using HTML select tag:

<select name="category" class="form-control" required>
   <option value="0" selected="selected" disabled="disabled" style="display: none">Select one category</option>
   <?php
      require_once 'pdo_connection.php';
      $sth = $dbh-> prepare( 'SELECT * FROM categories' );
      $sth-> execute();
      $result = $sth-> fetchAll(); ?>
   <?php foreach( $result as $row ) ?>
   <option value="<?php echo $row['category_id'];?>"><?php echo $row['category'];?></option>
</select>

And, this my PDO database connection:

<?php

$dbh = new PDO('mysql:host=localhost;dbname=system_vip;charset=utf8', 'root', '');

?>

What can I do to improve my code to populate all results from one column on MySql table using prepared statements and PDO connection?

What is the output of print_r($result); in the first example? — , Feb 14 '19 at 22:51

Don't Panic · Accepted Answer · 2019-02-14T23:12:17.590

The fetchAll() is fine. If you var_dump($result) you'll see all the rows are there.

This is the problem:

<?php foreach( $result as $row ) ?>
<option value="<?php echo $row['category_id'];?>"><?php echo $row['category'];?>

Without using curly braces or alternate syntax to group the statements after foreach, only the first statement after the foreach will be repeated. In this case, the first statement after the foreach is nothing. The closing PHP tag implies an instruction separator, so you're effectively saying:

foreach( $result as $row );

i.e. for each row, do nothing. $row is still defined after the loop as the last row from the array, which is the one option you end up with.

Here's an example of alternate syntax to enclose the two statements:

<?php foreach( $result as $row ): ?>
<option value="<?php echo $row['category_id'];?>"><?php echo $row['category'];?></option>
<?php endforeach; ?>

That's right. There's no way (and no reason) for PHP to "understand" your `option` HTML tag and know that wanted it to form a complete loop body. — Lightness Races in Orbit, Feb 14 '19 at 22:56
In general try to avoid interleaving HTML and PHP like this. It's messy at best and error-prone at worst (as shown). — Lightness Races in Orbit, Feb 14 '19 at 22:57

My PHP PDO fetchAll() code returns one row instead of all results on MySql column table

1 Answers1