Overview of This Lesson

Refresh this page because I am probably still making changes to it.

In the previous tutorial you learned how to perform INSERT queries. In this lesson you can read those records back and display them in an HTML page using SELECT queries.

Pre-Requisites

Before doing this lesson, make sure you've completed the CRUD Operations: Create lesson.

Resources

For more advanced things you can do with SELECT queries, see MySQL Manual: SELECT Statements.

SQL SELECT Query Syntax

A SELECT query is used to select or retrieve a set of records from one or more tables in a database. You can even return other information with a SELECT query, such as the number of records that match a certain criteria or the minimum or maximum values in a specific column. You'll learn how to do all of these things in a datbase class. For this lesson, we'll just look at basic SELECT statements.

The syntax of the most basic SELECT statement is:

SELECT field1, field2, ... FROM tableName;

field1, field2, ... refers to the names of the columns or fields you want to retrieve. For example, you can try this in PHPMyAdmin in the SQL tab for your Inventory table:

SELECT name, quantity FROM Inventory;

If your column or table name has a space, put the name in quotes, e.g.

SELECT title FROM 'My Songs';

You can actually put single quotes around the names even if there isn't a space; many developers prefer to do that.

To select all the columns, you can just use the wildcard * symbol instead of listing all the column names:

SELECT * FROM Inventory;

Note that this statement says, "select all the columns from the Inventory table", it's not selecting certain records (by default, all rows/records will be returned).

If you'd like to sort your records by one or more columns, use ORDER BY after you specify the table name. You can add ASC or DESC to change the default sort order. To sort by more than one column, separate them with commas. Try these in PHPMyAdmin:

SELECT * FROM `Inventory` ORDER BY name;
SELECT * FROM `Inventory` ORDER BY quantity DESC;
SELECT * FROM `Inventory` ORDER BY quantity, price;

You can also select which set of records you'd like to view by using a WHERE clause. The WHERE clause is accompanied by boolean expressions: each record is evaluated using those expressions, and included in the returns results if they evalate to true. Examples to try:

SELECT * FROM `Inventory` WHERE quantity > 7;
SELECT * FROM `Inventory` WHERE quantity > 7 ORDER BY name;
SELECT * FROM `Inventory` WHERE price < 10 AND price >= 5;

Notice that if you want to use both WHERE and ORDER BY, the WHERE goes first, then ORDER BY.

You can also look for specific words inside a string column using the LIKE keyword. The expression column LIKE value will search inside each record's column to see if it contains value. The value should be enclosed within single quotes. For example

SELECT * FROM `Inventory` WHERE name LIKE 'cherries';

This statement will search the Inventory table for any records whose name column contains the word "cherries". This will look for an exact match, so it will only match "cherries" and it will not match "cherries, basket". If your database is set to a case-insensitive collation, then it will match any column whose name is exactly equal to "cherries", "Cherries", or "CHERRIES".

Wildcard LIKE statements are much more powerful. When using LIKE, the wildcard symbol on most database systems is the % (percent) symbol. For example:

SELECT * FROM `Inventory` WHERE name LIKE 'cherries%';

will match any records who's name value starts with "cherries".

SELECT * FROM `Inventory` WHERE name LIKE '%cherries';

will match any records who's name value ends with "cherries."

SELECT * FROM `Inventory` WHERE name LIKE '%cherries%';

will match any records who's name value contains "cherries".

Practice using SELECT queries in PHPMyAdmin, and feel free to check the resources at the beginning of this section if you'd like to learn more about what you can do with a SELECT statement.

Executing SELECT Queries in PHP

You can execute a SELECT query in PHP code just like an INSERT query. The big difference is that where INSERT queries return an integer representing the number of rows/records inserted, a SELECT query returns an entire set of records! This set of records is often referred to as a result set. Note that it's possible the result set of a SELECT query could be empty! If you executed query and there were no matching records, then an emtpy result set is returned. This is something we have to consider when coding.

To start with, we use the same prepare() and execute() statement that we're already familiar with:

<?php
if (!isset($dbConn))
    $dbConn = require("connect.php");
            
$sql = "SELECT * FROM Inventory ORDER BY name;";
$statement = $dbConn->prepare($sql);
$result = $statement->execute();
?>

As with the INSERT query, the $result variable contains true if the query succeeded and false if it failed. The records or results that have been returned by the query (if any) can be accessed via the $statement object. Note that even if a SELECT query returns no records, that's still a successful query. An unsuccessful query is one that can't execute due to some problem with the query itself or the table you're trying to query (e.g. if you use an incorrect column name or table name in your query or the column(s) you're trying to access don't exist).

The PDOStatement object contains a fetch() method that retrieves a single record from the result set as an associative array. In this associative array, the keys are the column/field names in the table, and the values are the actual field values for that record.

For example, if your first record in your Inventory table was:

Then executing $statement->fetch() would return an associative array where the ["id"] value is 1, the ["title"] value is "Apples, Basket", the ["quantity"] value is 7, and the ["price"] value is 3.5.

The fetch() method returns different kinds of things, depending on what kind of query you executed, and depending on some optional arguments you might have passed the fetch() method (see the documentation at the link posted earlier for information on these optional parameters). In general, the fetch() method will return one of the following values:

• the boolean value FALSE if there is some kind of problem fetching a record
• null if there are no records in the result set
• an array containing the record data

Note that in the documentation you'll see that there are a few other things it could return if you use different fetch types.

Assuming we're executing a standard SELECT query and retrieving the result set as a set of records, we could do the following to fetch and echo the first row of a Songs table:

<?php
$row = $statement->fetch();
echo "ID: " . $row["id"] . " Title: " . $row["title"] . " Artist: " . $row["artist"] . 
" Price: $" . $row["price"];
?>

If you wanted to make sure there was a record before you attempted to fetch it, you could use an if statement, since the fetch() method returns false or null if there was nothing returned:

<?php
$row = $statement->fetch();
if ($row) {
echo "ID: " . $row["id"] . " Title: " . $row["title"] . " Artist: " . $row["artist"] . 
     " Price: $" . $row["price"];
} else {
// action to take if there's no records or failure to get a record
}?>

In this example, $row will contain FALSE if there's a failure of some kind, null if there are no records returned, or an associative array of records. Recall that the value in the parentheses of an if statement must be a boolean or boolean expression. Because PHP is weakly-typed, the value null will be automatically cast into the value FALSE, and an array will automatically be cast into the value TRUE. Therefore, if ($row) will be true if there is a record to fetch, and false otherwise.

Iterating Through the Records

Ideally, your result set will contain many records or rows, so you'll want to iterate through the result set and do something with each row returned. To iterate through the records, you can use a while-loop with your fetch() call:

<?php
while ($row = $statement->fetch()) {
echo "ID: " . $row["id"] . " Title: " . $row["title"] . " Artist: " . $row["artist"] . 
     " Price: $" . $row["price"];
}
?>

This looks odd, right? We know that a while loop condition must be a boolean or boolean expression, so why does $row = $statement->fetch() work here? Because, as mentioned earlier, null will cast into FALSE and an array will cast into TRUE. Even though $row = $statement->fetch() is an assignment statement, an assignment statement also has a data type: it's the type of the value that was assigned. So if you use Java to give the double variable dblNumber a value of 2.3 in the statement dblNumber = 2.3;, the data type of this expression is double.

Therefore, the value of $row = $statement->fetch() is either going to be false, null, or an array. If null, this will cast into FALSE, and if an array, that will cast into TRUE.

Try it out: add the code to your inventoryList.php file to display all the inventory records in a nice, formatted HTML table.

Getting the Number of Rows Returned

If you want to count the rows returned by your query, you can use a counter in your loop, and this is a perfectly fine solution:

<?php
while ($row = $statement->fetch()) {
    echo "ID: " . $row["id"] . " Title: " . $row["title"] . " Artist: " . $row["artist"] . 
         " Price: $" . $row["price"];
    $numRows++;
}
echo "<p>$numRows records.</p>";
?>

However, if you need the number of rows before or without iterating through your result set, things are a little more complicated. There is no function to do this in PHP; you'll have to do it using another SQL query that counts the rows. This means you might have to execute 2 queries - one to count the rows and one to acquire the rows:

<?php
    // query to count all the rows in the Music table
    $countStmt = $dbConn->prepare("SELECT count(*) FROM Music;");
    $execOk = $countStmt->execute();  // execute the query

    if ($execOk) { // if it was able to count successfully

        // fetch the count number in the result set
        $rows = $countStmt->fetch(PDO::FETCH_NUM);
        if ($rows >= 1) {
            // we have rows
            echo "<p>$rows[0] records.</p>";
        } else {
            // we have no rows
            echo "<p>Sorry, no music records.</p>";
        }
    }    
?>

In this example, the query executes the SQL count() method: count(*) always counts the number of records. Here we're telling it to count all of the records in the Music table. After we execute the statement and make sure it was successful, we use the fetch() method to fetch the first item in the record set. In this case, the record set is just a value containing the number of records. The constant PDO::FETCH_NUM tells fetch() to return the results as a numerically-indexed array. This makes things easier since there's only one value being returned: we can just use $rows[0] to get the single value returned (the number of records).

You can see that this is not the most effective solution, so if you need to iterate your rows anyway, it's better to just use the first solution and increment a row counter.

Try it out: Add the code to display the number of records in your Inventory table. Use the most efficient technique for your program.

Filtering Records

Sometimes you may need to design a page that allows the user to view only certain records. Earlier you learned how you can use a WHERE clause in your SELECT query to filter results.

Edit your inventory list example to allow a user to search for records that contain a specific string value in the name column. For example, maybe a user wants to search for all inventory items with "basket" in the name or they want to search for all records for "apples".

Add a form to your inventory list page. When the user first loads the page, or if they try to submit the form with no search value, then all the records are displayed. If the form is submitted with a search value, perform a search where the name column contains the search value, using the LIKE operator. As with any form input, make sure you sanitize the search value.

Also, don't forget to use a parameterized query for your SELECT statement! DON'T put the wildcard symbols around the parameter in your statement: add them to the value when you add the search parameter to your array e.g. $params = [":searchKey" => "%$searchKey%"];

Exercises

1. Put your inventory examples in a single application: you have the form page for adding a new inventory record and the search/list page for viewing the records. On the form page, below the form, display the same list output as the list page: display all the records in an HTML table. Create a library function in a separate file that accepts a collection of Inventory rows and displays an HTML table.

It's vital to keep your program fast and efficient by avoiding unnecessary database accesses. In both your form and list pages, you shouldn't perform the select query unless you have to. Store the collection of inventory records in a SESSION variable. On both pages, if the session variable doesn't exist or is empty, retrieve the list of inventory records from the database. This also ensures you can see the same list of inventory records across all pages in your application!

What happens after you add a new record? It's standard practice to refresh your collection whenever a new record is updated/added/deleted, so after a new record is added, retrieve a new set of inventory records and update the session variable.

2. Create a simple application to manage the Courses table. A main index page shows a list of courses. Below the list is a count of the number of courses and the average grade of all the courses.

Include a second page where a user can add a new course to the Courses table. After adding a new course, the user should end up back on the main page with the course listing.

3. Create a page called "selectInventory.php" that lists all the inventory items inside a form as a series of radio buttons: each inventory item should have it's own radio button. Each radio butotn's label should be the inventory item name. The value returned to the server when a radio button is selected (i.e. when the user chooses a radio button and then clicks the SUBMIT button) is the record ID of the inventory item.

You can process your form submission by displaying the selected inventory item's information. We'll actually determine what we do with the selected item in an upcoming lesson.