PHP & MySQL - Select data in a database Tutorial
In this tutorial we learn how to find and select data elements in a database table with PHP and an SQL query. We also cover how to refine our selections with extra SQL clauses.
For this tutorial, please start the MySQL module in your XAMPP control panel.
How to select data in a table
To return data from our database, we need to select it from the table first. We do this with the SELECT FROM query.
SELECT column FROM tableWe can select multiple columns from a table by separating each column name with a comma.
SELECT column1, column2, column3 FROM tableWe can select all the columns in a table with the asterisk * symbol.
SELECT * FROM table<?php
$serverName = "localhost";
$userName = "root";
$password = "";
$dbName = "trainingDB";
$conn = mysqli_connect($serverName, $userName, $password, $dbName);
if (!$conn) { die("Connection failed: " . mysqli_connect_error()); }
// select data
$sql = "SELECT userID, name, email FROM Users";
// execute query and store result
$result = mysqli_query($conn, $sql);
// print data until no more rows in result
while($row = mysqli_fetch_assoc($result)) {
echo "User ID: " . $row['userID'] . "<br>" .
"User Name: " . $row['name'] . "<br>" .
"User Email: " . $row['email'] . "<br><br>";
}
mysqli_close($conn);
?>
In the example above, we select data from all three of the columns in our Users table. Then, we execute the query and store the data into the $result variable.
To be able to perform operations on our data, we fetch it into and associative array with the mysqli_fetch_assoc() function.
The mysqli_fetch_assoc() function returns $row as an associative array where each key of the array represents the column name of the table.
It will return NULL if there are no more rows, so we can use a while loop to iterate over the array.
In the while loop is where we perform whatever operations we need to. In this case, we simply print out the data to the page.
How to refine selection
It may be that we only want to select specific records from the table based on a condition. We can add this condition to our query with the WHERE clause.
SELECT column FROM table WHERE column=valueIf the value is a string type, it must be wrapped in quotes.
<?php
$serverName = "localhost";
$userName = "root";
$password = "";
$dbName = "trainingDB";
$conn = mysqli_connect($serverName, $userName, $password, $dbName);
if (!$conn) { die("Connection failed: " . mysqli_connect_error()); }
// select data
$sql = "SELECT userID, name, email FROM Users WHERE name='John Doe'";
// execute query and store result
$result = mysqli_query($conn, $sql);
// print data until no more rows in result
while($row = mysqli_fetch_assoc($result)){
echo "User ID: " . $row['userID'] . "<br>" .
"User Name: " . $row['name'] . "<br>" .
"User Email: " . $row['email'] . "<br><br>";
}
mysqli_close($conn);
?>
In the example above, we select only the users in the table that have the name John Doe.
The WHERE clause is especially useful in situations such as eCommerce applications, where users want to see only specific products or products in specific price ranges.
How to limit selection results
We can limit the amount of selections from a table with the LIMIT clause.
SELECT column FROM table LIMIT number<?php
$serverName = "localhost";
$userName = "root";
$password = "";
$dbName = "trainingDB";
$conn = mysqli_connect($serverName, $userName, $password, $dbName);
if (!$conn) { die("Connection failed: " . mysqli_connect_error()); }
// select data
$sql = "SELECT userID, name, email FROM Users LIMIT 2";
// execute query and store result
$result = mysqli_query($conn, $sql);
// print data until no more rows in result
while($row = mysqli_fetch_assoc($result)){
echo "User ID: " . $row['userID'] . "<br>" .
"User Name: " . $row['name'] . "<br>" .
"User Email: " . $row['email'] . "<br><br>";
}
mysqli_close($conn);
?>
When we run the example above, it will display only the first two entries in the table because we limit the selection to 2 with the LIMIT clause.
How to order selection results
We can choose to fetch our data by column in either ascending order, or descending order, with the ORDER BY clause. By default, data is ordered in ascending format.
SELECT column FROM table ORDER BY column DESC<?php
$serverName = "localhost";
$userName = "root";
$password = "";
$dbName = "trainingDB";
$conn = mysqli_connect($serverName, $userName, $password, $dbName);
if (!$conn) { die("Connection failed: " . mysqli_connect_error()); }
// select data
$sql = "SELECT userID, name, email FROM Users ORDER BY userID DESC";
// execute query and store result
$result = mysqli_query($conn, $sql);
// print data until no more rows in result
while($row = mysqli_fetch_assoc($result)){
echo "User ID: " . $row['userID'] . "<br>" .
"User Name: " . $row['name'] . "<br>" .
"User Email: " . $row['email'] . "<br><br>";
}
mysqli_close($conn);
?>
In the example above, we select our data by the userID column in descending order.
As noted before, data is ordered in ascending format by default so we don’t have to explicitly specify it when we use the ORDER BY clause.
<?php
$serverName = "localhost";
$userName = "root";
$password = "";
$dbName = "trainingDB";
$conn = mysqli_connect($serverName, $userName, $password, $dbName);
if (!$conn) { die("Connection failed: " . mysqli_connect_error()); }
// select data
$sql = "SELECT userID, name, email FROM Users ORDER BY userID";
// execute query and store result
$result = mysqli_query($conn, $sql);
// print data until no more rows in result
while($row = mysqli_fetch_assoc($result)){
echo "User ID: " . $row['userID'] . "<br>" .
"User Name: " . $row['name'] . "<br>" .
"User Email: " . $row['email'] . "<br><br>";
}
mysqli_close($conn);
?>
In the example above, we select our data by the userID again. This time though, it will fetch our data in ascending order because we didn’t specify the clause.
Summary: Points to remember
- We select data in a table with the SELECT query.
- We can refine the selection with the WHERE clause.
- We can limit our query to a specific amount of results with the LIMIT clause.
- We can order data by a specific column in ascending or descending order by using the ORDER BY and DESC clauses.