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…

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:

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.

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):

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:

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:

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:

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

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.