Using phpMyAdmin as a Checklist for Columns Used in a Script

One of my many coding habits I've been changing is the use of select all (SELECT *) in MySQL queries. Scripts usually don't need all the columns and grabbing unnecessary data reduces efficiency. Fixing the references usually involves me digging through the code, remembering the column names actually being used, and updating the query. This method works well when there are only a few columns to worry about. It gets more complicated with more columns or when they're used throughout a large script. That's where phpMyAdmin comes to the rescue.

Background

While using phpMyAdmin, I've always appreciated the fact that the individual rows turn orange when clicked. It's handy for comparing raw data to what's displayed online. Highlighting rows makes it easy to jump between the website and database without losing my place.

For the longest time, I've wanted this same ability for columns. But clicking the column headings just sorts the table (under the "Browse" tab). It wasn't until recently that I realized the "Structure" tab was the answer to my wish.

Highlighting Columns

Let's say we have the following code which displays a list of staff members and their e-mail addresses:

<?php
//INITIALIZE VARIABLES
$staffMembers = array();
 
//GET STAFF MEMBER INFORMATION
$sql = "SELECT * FROM staffMembers WHERE active='1' ORDER BY lastName, firstName";
$result = $mysqli->query($sql);
while($row = $result->fetch_assoc()) {
     $staffMembers[] = "<li>{$row['firstName']} {$row['lastName']}, {$row['jobTitle']} (<a href='mailto:{$row['email']}'>{$row['email']}</a>)</li>";
}
 
//DISPLAY STAFF LIST
print '<ul>' . implode('', $staffMembers) . '</ul>';
?>

Since the code references "staffMembers", open the table in phpMyAdmin and click the "Structure" tab. As fields are used throughout the code, mark the corresponding checkboxes. In this case, firstName, lastName, email, and jobTitle should be highlighted (see Figure 1).

Figure 1. Highlighted Columns

We now have a clear picture of the columns which replace "SELECT *"

<?php
$sql = "SELECT firstName, lastName, email, jobTitle FROM staffMembers WHERE active='1' ORDER BY lastName, firstName";
?>

Conclusion

In this particular case, it wasn't really necessary to highlight the columns in phpMyAdmin. It doesn't take much to spot four different columns. The difficulty increases when a single script has dozens of columns, thousands of lines of code, and several database queries.