Dynamic HTML drop-down with PHP and MySQL

After spending a great deal of time in my day job developing a web reporting dashboard/interface with PHP and MySQL, I feel that I have found my stride in back-end web development with PHP. Learning to develop web applications using the LAMP stack – or one of its derivatives (E.g., WordPress, CodeIgniter, Laravel, etc..) – is a huge focus for me right now. It’s no secret I am a MySQL fanatic – hence this blog – and have written about Python code along with the MySQL Shell a great deal here. However, as it normally goes for developers, the PHP/MySQL back-end web development has inspired me to, of course, start a side project. Go figure right! What developer doesn’t have a side project, or two? Be on notice that you will see more and more PHP/MySQL and LAMP-stack related posts in the future, in addition to the MySQL posts I regularly publish. In the meantime, I’ll share something super neat o (to me at least) I learned while working on my side project. I’m sure it is simple and standard for seasoned web developers, but sparks that exciting, exploratory learning feel for me. Continue reading to see more…

several pairs of shoes piled on top of one another
Photo by Jakob Owens on Unsplash
Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!


Choices. Everyone likes choices right? Can there be choices in an HTML web form? Yes, there can, by using the select element. How about one that is dynamically populated with PHP based on available data from a MySQL database? That is a resounding yes as well.

I have a ‘shoes_worn’ MySQL table that stores various shoes I wear – or have worn – on daily walks and hikes:

phpMyAdmin table data
Current table data for export…

I created this simple form to capture walking stats (E.g., distance, time, pace, etc…) I am going to store in the database. Instead of entering in a shoe name or shoe id number by hand, I would rather have a drop-down of the available data from the ‘shoes_worn’ table. Just as important, should I: add another row, update a row, or remove any rows from the ‘shoes_worn’ table, the drop-down always reflects what is current in the database. Additionally, it is far easier to use and less prone to mistakes such as mis-spelling, entering non-existent values, or omitting values all-together.

html basic web form
Base form

The long of the short of it is, it only makes sense to query the database.

At the beginning of my store_walk.html.php source file – which generates the HTML web form – I’ll include the following section of PHP. Be conscious there is no ORDER BY clause in this MySQL query. We are well aware that the database can – and will – return query results (if any) in an arbitrary ordering. However, for the sake of this post, ordering is not so much the focus as is the dynamic select element (however, ordering, will be the subject of a future blog post):

1
2
3
4
5
6
7
8
9
10
<!--?php
include __DIR__ .'/../includes/DatabaseConn.php';
try {
    $query = 'SELECT shoe_id, brand_name FROM shoes_worn';
    $shoes = $pdo--->query($query);
}
 catch (PDOException $e) {
    echo 'Database Error '.$e->getMessage().' in '.$e->getFile().
    ': '.$e->getLine();
}
(Note: According to my understanding, echoing database errors in production is a no-no. Yet, I have included that potential in the catch section of the try/catch block for this blog post.)

Here’s a run-down of the above code:

  • The include directive on line 2, provides all of the database connection information (not shown) including the $pdo variable used on line 5.
  • On line 4, the SELECT statement is assigned to the $query variable, which in turn, is the argument in the PDO query() method on line 5. (Lot’s of query going on here right?)
  • Results from the query() method call is stored in a $shoes array.

I can verify the $shoes array has data using the echo construct in the browser:

1
2
foreach ($shoes as $shoe => $value) {
    echo $value['shoe_id'].' '.$value['brand_name'].'<br />';

browser view of php array
Browser view of array from select query

Leveraging foreach, I am essentially stepping through all the elements of the $shoes array.

This same approach provides the data for the select element drop-down.

Let’s move on to that functionality.

I can incorporate the $shoes array data values into the form select drop-down with this PHP included in the HTML markup:

1
2
3
4
5
6
7
8
</p><div class="form-group">
    <label for="shoes_worn">Shoe Worn</label>
        <select name="shoes_worn">
            <!--?php foreach ($shoes as $shoe =--> $value):?>
                <option value="<?=$value['shoe_id']?>"><!--?php echo htmlspecialchars($value['brand_name']);?--></option>
            <!--?php endforeach;?-->
        </select>
</div>

The number and values for the select option element are created – and populated – dynamically based off of the $shoes array data from the database query. Pretty nifty right!?!

The form has this structure as of now:

web form with dynamic select drop-down
Base form with available drop-down select

And the final product, a working dynamic select drop-down field populated with data from the database:

html form with dynamic drop-down
Form with dynamic drop-down.

Works as desired and I am extremely excited to have figured out how to do this. Look for more ramblings on PHP in the near future as I continue learning it…

Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.


Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

2 thoughts on “Dynamic HTML drop-down with PHP and MySQL

  1. This guide has nice flow. However, for the student this isn’t well presented. For example, what is to make of

    Please for the sake of helping those who follow your guide, include a download of the full code that’s been tested and working, with a sample database dump. Otherwise noobs will waste hours of time putting it together. Much easier to learn to edit rather than piecing it all together.

    Thanks

    • Thank you, Brian. I appreciate your comment and insight. That is a great idea to include the code as a download with the articles and makes sense. The whacked-out syntax is something a syntax highlighting plugin is doing and I definitely need to fix that so thanks for pointing that out.

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.