37 PDO for SELECT

Download associative_arrays.php from the Full Stack Example Pack and load it into a browser through a local server. Then take a look at the code to see examples of associative array literals, echo statements with associative arrays, and the foreach loop. Map each PHP statement to the output displayed on the web page.

You will see an example of how to build an associative array one key at a time:

$user = []; 
$user["studentid"] = "928374532";
$user["age"] = 22;
$user["access"] = true;

And with a single associative array literal:

$user = ["studentid" => "6534654754", "age" => 18, "access" => false];

You will see examples of how to output associative array items in echo statements and using var_dump for debugging:

echo "<p>Your Userid: $user[studentid]</p>";
echo "<p>Your Userid: $user[$key]</p>";
var_dump($user);

And you will see an example of how to iterate through an associative array using the PHP foreach loop to extract keys and values like this:

foreach ($user as $key => $value) {
    echo "<p>$key: $value</p>";
}

Try modifying the code in associative_arrays.php to add and remove key-value pairs to the $user array and make sure you can echo the values and make sense of the results.

Get the select folder from the data folder of the Full Stack Example Pack and modify the connect.php script to add your login information. If you didn’t already do this in the last chapter, then in XAMPP or similar, get grades.sql from the data folder and import it into an existing database to get the grades table.

Run the example.php code by loading it into a browser through a server. It should show you the names of any student in the grades table with a final exam grade greater than 50, presented in an HTML unordered list. Try changing the $limit variable to change the number of records retrieved. What do you expect to happen if you set the limit too high?

Debugging: Using the var_dump() function is a great way to see the full contents of each row, especially if you are getting an index error when you try to access it. Try adding a call to var_dump($row) inside the while loop to display the full contents of each row. You might notice that the associative array contains the information twice – once using the column names as keys, and again using integer keys.

Optional: If you are used to thinking in an object-oriented way, it might occur to you that it would be a good idea to create an array of objects from the result set. If you’d like to see what that could look like in action, the example_oo.php and user.php files in the select folder give you an idea of it could work.

Writing the HREF Attribute

Get the select_to_code folder from the Full Stack Example Pack and modify the connect.php script to add your login information. If you didn’t already do this in the last chapter, then in XAMPP or similar, get grades.sql from the data folder and import it into an existing database to get the grades table.

Run the example.php code by loading it into a browser through a server. It should show you the names of the students in the grades table, but this time each name should be a clickable link. This is accomplished by selecting the primary key (student_id) from the database along with the student names:

$command = "SELECT firstname, lastname, student_id FROM grades ORDER BY lastname";

Then when the code outputs the data, it uses $row[student_id] to write a GET parameter named id directly into the href attribute of an <a> element. The code for this is below, with critical parts underlined.

while ($row = $stmt->fetch()) {
    echo "<li><a href='showsingleuser.php?id=$row[student_id]'>";
    echo "$row[firstname] $row[lastname]"; 
    echo "</a></li>";
}

The links look something like this when they arrive at the browser:

<li><a href='showsingleuser.php?id=6'>Drizzt Do'Urden</a></li>

The showsingleuser.php file retrieves the id parameter and uses it to launch a SELECT query to retrieve and display the rest of the user information. Note that the code in showsingleuser.php is just “proof of concept”. It uses a var_dump() to display the information retrieved. Making this more user-friendly is left for an exercise.

Optional: There’s another version of this code in example_oo.php that uses user.php to accomplish the same thing in an object oriented way.

Writing a Hidden Input Element

Get the select_to_code folder from the Full Stack Example Pack and modify the connect.php script to add your login information. If you didn’t already do this in the last chapter, then in XAMPP or similar, get grades.sql from the data folder and import it into an existing database to get the grades table.

Run the example.php code by loading it into a browser through a server and click a link to get to showsingleuser.php.

The showsingleuser.php file gets the student id from a GET parameter. It uses a SELECT query to get the rest of the user information to display it, and then it creates a special delete form using the id using a special input element with type=”hidden”:

<form action="deleteuser.php" method="post">
    <input type="hidden" name="id" value="<?= $row["student_id"] ?>">
    <input type="submit" value="Yes, Please!">
</form>

An input element with type=”hidden” will send a parameter when the form is submitted, but it does not appear on the page or allow the user to change its value attribute. So you can fix its value to be whatever you want. This is handy for passing values along from page to page. (It’s actually a primitive form of session management because it lets you keep track of the user from one page to the next. See the PHP Session Management chapter for a more sophisticated way to do this.)

When the user presses the submit button in this form, a POST request will be launched to deleteuser.php with the value set to the id of the student that is being displayed. Then deleteuser.php can use an SQL DELETE query to remove the student record from the table. Note that the code in deleteuser.php just “proof of concept”. The deleteuser.php file simply reports that it received a delete request rather than performing the delete. Finishing it is left as an exercise.

Displaying an Associative Array in a Table

Get the tables.php file from the Full Stack Example Pack and run it by loading it into a browser through XAMPP or a similar server. You will see an example of a table that was produced by looping over a PHP associative array, using the above tips to create a striped effect.

Here’s the PHP while loop that dumps the keys and values of an associative array into the HTML of the page:

foreach ($user as $key => $value) {
    echo "<tr><td>$key</td><td>$value</td></tr>";
}

And here’s the CSS rule that creates the striped effect:

tbody tr:nth-child(odd) {
    background-color: lightgray;
}

Try changing even to odd in the above rule. Does it have the effect you expected? What happens if you change tbody to thead, or remove tbody altogether? Do these changes have the effects you expected?