PHP MySQL Select Data

The SELECT statement is used to fetch data from one or more tables. Once the data is retrieved, we can use PHP loops to display it in a browser, typically within an HTML table.

1. The SELECT Statement Syntax

You can select specific columns or use the * wildcard to select all columns from a table.

SELECT column1, column2 FROM table_name;
// OR
SELECT * FROM table_name;

2. Select Data using MySQLi (Object-Oriented)

We use the query() method to execute the statement and fetch_assoc() to put the data into an associative array that we can loop through.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";

$conn = new mysqli($servername, $username, $password, $dbname);

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  // Output data of each row
  while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0 results";
}
$conn->close();
?>

3. Select Data with PDO (+ Prepared Statements)

In PDO, it is best practice to use fetchAll() to retrieve all rows at once, or a loop for very large datasets.

<?php
try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests");
  $stmt->execute();

  // set the resulting array to associative
  $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
  foreach($stmt->fetchAll() as $k=>$v) {
    echo $v['firstname'] . ' ' . $v['lastname'] . "<br>";
  }
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}
$conn = null;
?>

4. Displaying Data in an HTML Table

To make the data readable for users, it is standard to wrap the PHP loop inside HTML <table> tags.

echo "<table><tr><th>ID</th><th>Name</th></tr>";
while($row = $result->fetch_assoc()) {
echo "<tr><td>".$row["id"]."</td><td>".$row["firstname"]."</td></tr>";
}
echo "</table>";

Tip: The num_rows property in MySQLi is very useful to check if the query actually returned any data before you try to start a loop.