PHP MySQL Select Query

In this tutorial you’ll learn how to select records from a MySQL table using PHP.

The SELECT statement is used to select data from one or more tables.

SELECT statement syntax

SELECT column_name(s) FROM table_name

You can use the * character to select ALL columns from a table.

SELECT * FROM table_name

Examples

So far you have learnt how to create database and table as well as inserting data. Now it’s time to retrieve data what have inserted in the preceding tutorial. 

The following example selects the id, firstname and lastname columns from the Students table and displays it on the page.

Example – MySQLi Procedural

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

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT id, firstname, lastname FROM Students";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
  // output data of each row
  while($row = mysqli_fetch_assoc($result)) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0 results";
}

mysqli_close($conn);
?>

Explanation

• First, we set up an SQL query that selects the id, firstname and lastname columns from the Students table.
• The next line of code runs the query and puts the resulting data into a variable called $result.
• Function mysqli_num_rows() checks if there are more than zero rows returned.
• If there are more than zero rows returned, the function mysqli_fetch_assoc() puts all the results into an associative array that we can loop through.
• The while() loop loops through the result set and outputs the data from the id, firstname and lastname columns.
• The while loop is used to loops through all the rows in the result set.

Example – MySQLi Object-oriented

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

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname FROM Students";
$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();
?>