PHP MySQL SELECT Query

Retrieve records from a MySQL Database Table

SELECT is the SQL command used to retrieve records from your tables. This command syntax can be totally simple or very complicated, depending on which fields you want to select, whether you want to select from multiple tables, and what conditions you plan to impose.

Its basic syntax is as follows:

SELECT column1_name, column2_name, columnN_name FROM table_name;

Example Download View

$sql = "SELECT * FROM book";
if($result = mysqli_query($dbcon, $sql)){
if(mysqli_num_rows($result) > 0){
echo "<table>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>first name</th>";
echo "<th>Phone</th>";
echo "<th>email</th>";
echo "</tr>";
while($row = mysqli_fetch_array($result)){
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['userName'] . "</td>";
echo "<td>" . $row['phone'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
// Free result set
mysqli_free_result($result);
} else{
echo "No records matching your query were found.";
}
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($dbcon);
}

If you want to select specific columns only, replace the * with the names of the columns, separated by commas. The following statement selects just the id, item_name, and curr_qty fields from the grocery_inventory table:

Its basic syntax is as follows:

SELECT id, item_name, curr_qty FROM grocery_inventory;

Ordering SELECT Results

Results of SELECT queries are ordered as they were inserted into the table and should not be relied on as a meaningful ordering system. If you want to order results a specific way, such as by date, ID, name, and so on, specify your requirements using the ORDER BY clause.

In the following statement, the intention is a resultset ordered alphanumerically by item_name:

Its basic syntax is as follows:

SELECT id, item_name, curr_qty FROM grocery_inventory ORDER BY item_name;

The default sorting of ORDER BY results is ascending (ASC); strings sort from A to Z, integers start at 0, and dates sort from oldest to newest. You can also specify a descending sort, using DESC: SELECT id, item_name, curr_qty FROM grocery_inventory ORDER BY item_name DESC;

You’re not limited to sorting by just one field—you can specify as many fields as you want as long as they are separated by commas. The sorting priority is the order in which you list the fields.

Example Download View

$sql = "SELECT * FROM book ORDER BY phone ASC";
if($result = mysqli_query($dbcon, $sql)){
if(mysqli_num_rows($result) > 0){
echo "<table>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>first name</th>";
echo "<th>Phone</th>";
echo "<th>email</th>";
echo "</tr>";
while($row = mysqli_fetch_array($result)){
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['userName'] . "</td>";
echo "<td>" . $row['phone'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
// Free result set
mysqli_free_result($result);
} else{
echo "No records matching your query were found.";
}
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($dbcon);
}

Limiting Your Results

You can use the LIMIT clause to return only a certain number of records from your SELECT query result. Two requirements apply when using the LIMIT clause: the offset and the number of rows. The offset is the starting position, and the number of rows should be self-explanatory.

Its basic syntax is as follows:

SELECT id, item_name, curr_qty FROM grocery_inventory ORDER BY curr_qty LIMIT 2;

The LIMIT clause can prove useful in an actual application. For example, you can use the LIMIT clause within a series of SELECT statements to travel through results in steps (first two items, next two items, next two items after that):

1. SELECT * FROM grocery_inventory ORDER BY curr_qty LIMIT 0, 2;

2. SELECT * FROM grocery_inventory ORDER BY curr_qty LIMIT 2, 2;

3. SELECT * FROM grocery_inventory ORDER BY curr_qty LIMIT 4, 2;

Example Download View

$sql = "SELECT * FROM book ORDER BY phone ASC LIMIT 2";
if($result = mysqli_query($dbcon, $sql)){
if(mysqli_num_rows($result) > 0){
echo "<table>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>first name</th>";
echo "<th>Phone</th>";
echo "<th>email</th>";
echo "</tr>";
while($row = mysqli_fetch_array($result)){
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['userName'] . "</td>";
echo "<td>" . $row['phone'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
// Free result set
mysqli_free_result($result);
} else{
echo "No records matching your query were found.";
}
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($dbcon);
}

Using WHERE clause

In the previous examples we've learnt how to fetch all the records from a table or table columns. But, in real world scenario we generally need to select, update or delete only those records which fulfill certain condition like products which belongs to a certain quantity, or price, etc.

The WHERE clause is used with the SELECT, UPDATE, and DELETE. However, you'll see the use of this clause with other statements in upcoming chapters.

Its basic syntax is as follows:

SELECT column_list FROM table_name WHERE condition;

An example is to retrieve all the records for items with a quantity of 500:

SELECT * FROM grocery_inventory WHERE curr_qty = 500;

Example Download View

$sql = "SELECT * FROM book WHERE id > 2";
if($result = mysqli_query($dbcon, $sql)){
if(mysqli_num_rows($result) > 0){
echo "<table>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>first name</th>";
echo "<th>Phone</th>";
echo "<th>email</th>";
echo "</tr>";
while($row = mysqli_fetch_array($result)){
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['userName'] . "</td>";
echo "<td>" . $row['phone'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
// Free result set
mysqli_free_result($result);
} else{
echo "No records matching your query were found.";
}
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($dbcon);
}

Using Operators in WHERE Clauses

You’ve used the equal sign (=) in your WHERE clauses to determine the truth of a condition—that is, whether one thing is equal to another. You can use many types of operators, with comparison operators and logical operators being the most popular types.

Example:

SELECT * FROM grocery_inventory WHERE item_price BETWEEN 1.50 AND 3.00;