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.


Screenshot showing how to highlight data records in phpMyAdminWhile 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:

$staffMembers = array();
$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>";
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).

Screenshot showing how to highlight columns in phpMyAdmin
Figure 1. Highlighted Columns

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

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


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.


There are currently no comments.

Leave a Comment