PHP MySQL PDO select foreach loop

Doing a loop through on a MySQL query is super useful and common. It allows you to make tables, rows and output data inĀ  a controlled manner without doing many connections.

PHP PDO with a prepared statement gives you better security against injections.

Assuming you already have your MySQL PDO connection details set like

$db = new PDO('mysql:host=localhost;dbname=DATABASENAME;charset=utf8mb4', 'USERNAME', 'PASSWORD');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Here is how to do a loop on a PDO MySQL select query:

<?php
$location_type = 'Beach';

$statement = $db->prepare("SELECT `location`, `type` FROM `locations` WHERE `type` = :location_type");
$statement->execute(array(':location_type' => $location_type));
while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
    $location = $row['location'];
    $type = $row['type'];
    echo "$location has the type $type<br>";
}

Without a prepared statement query

<?php
$statement = $db->prepare("SELECT `location`, `type` FROM `locations`");
$statement->execute(array());
while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
    $location = $row['location'];
    $type = $row['type'];
    echo "$location has the type $type<br>";
}

This will loop through each of the selected rows and output the `location` and the `type` as a new line due to the break tag (<br>).