The third module of CIS 2336 builds on the PHP server-side scripting introduced in the second module. The fundamentals of MySQL, an open-source relational database management system, are explored, as well as MySQL's integration with PHP, which allows for even more advanced dynamic websites to be developed than is possible with PHP alone. More specifically, the combination of MySQL plus PHP allows end-user interactivity with databases.
In the previous module, I learned how to create PHP scripts that could process data submitted from an HTML form, but there was limited useful application of that knowledge. In Module 3, with the introduction of MySQL, I now understand that the data submitted from an HTML form and processed by PHP can be inserted into the table of a database. With this capability, websites may have a high degree of interactivity with its end-users.
The materials from Module 3 were taught through four assignments: Bucky Roberts' MySQL Database Tutorials, PHP and MySQL for Dynamic Web Sites, Zend's PHP 101, and W3Schools. The Bucky Roberts assignment was composed of a series of YouTube videos that showed me how to retrieve, store, and manipulate data in databases by using Structured Query Language (SQL) commands. It was reminiscent of Eli the Computer Guy's tutorials from Module 2 in that it, too, featured a laid-back, welcoming approach to learning dry, technical material. Additionally, it featured simplified examples with a real-world feel. In that sense, it was superior to the W3Schools assignment, which was once again–just like in previous modules–a very straightforward reference. Again, the W3Schools assignment served its purpose as an excellent source to reference and review, but it was not an effective tool for learning and applying the basics of MySQL.
The Zend assignment was composed of two web tutorials. The first tutorial revisited how to use PHP to interact with external files, a concept already introduced in Module 2's YouTube tutorials by Eli the Computer Guy. The second tutorial, however, showed me new material, namely how PHP and MySQL can work in concert to develop dynamic pages capable of interaction with databases. The example pages from both tutorials were easy to follow, as the tutorial shows the source code in its entirety and then explains chunks of it at a time. What was also helpful was the fact that the tutorials demonstrated multiple ways of achieving the same result but still by following the same principles. I felt that understanding these principles was far more important than memorizing code because as PHP, MySQL, and other technologies change with revisions, the specific syntax for certain commands may become different, but the basic ideas behind them will not.
The PHP and MySQL for Dynamic Web Sites assignment, which included chapters 4, 5, and 9 of the book, covered much of the same ground as the Zend assignment but in even greater detail, which is why I have chosen it as the assignment to represent Module 3 of my learning portfolio. The assigned chapters reviewed much of the same materials covered in the other assignments, including the basics of MySQL, SQL, and their integration with PHP to develop dynamic web pages capable of interacting with a database stored on a server. Many examples of web pages that utilize these technologies were created and demonstrated in each chapter's lessons, and I was able to download them and easily follow along with the step-by-step instructions provided. This hands-on experience in seeing a dynamic page being built from scratch was vital in my understanding of MySQL's relationship with PHP and reinforced my understanding of PHP's relationship with HTML and CSS.
This example demonstrates all technologies learned up to this point in the course and illustrates how those technologies are related to each other. As I learned in Module 1, HTML is responsible for the content of a page, and CSS is responsible for the presentation of that page. As I learned in Module 2, PHP may be used to customize pages (e.g., using the include function to include code from an external file, as needed), and it may also be used to accept and process data that has been submitted using an HTML form. As I learned in Module 3, PHP may be used to connect to a MySQL database, validate that data, and then add that data to a table using a passed query. In addition, other queries may also be used, like those for selecting records or updating records.
Here, the content of the website has been created with HTML and styled with CSS. The header and footer are both included using PHP. The three sections, "Register," "View Users," and "Change Password," allow for new users to be registered in a MySQL database, all registered users from the database to be selected and viewed, and existing users to change the stored passwords in the database.
The "Register" section of the site shows how PHP can accept data submitted from an HTML form, connect to a MySQL database, validate that data, and then add that data to a table. The form allows for a user to submit their first name, last name, email address, and password. As shown in Module 2, the form uses the POST method to make the submitted data available for PHP. The PHP script begins by checking for how the script is requested–in this case, POST. It then creates a connection to the MySQL server and selects the database with the mysqli_connect function. Next, it validates the data submitted from the HTML form and uses the mysqli_real_escape_string function to ensure the data is syntactically valid (i.e., making sure that potentially problematic characters are properly escaped). Finally, the validated data is then added to a table in the database via the mysqli_query function, which takes two arguments: the database connection and the query. An INSERT statement is used here for the query because data is to be added to the table.
The "View Users" section of the site allows all registered users to be viewed in a list. It illustrates how PHP can be used to retrieve database query results and display the data. As in the previous example, a connection to the MySQL database must initially be established using the mysqli_connect function. Next, a query using the mysqli_query function (with a SELECT statement used for the query) is made to return all users' names and their registration dates. Then, the number of returned records is counted using the mysqli_num_rows function. If the number of returned records is greater than zero, then the returned records are fetched as arrays using the mysqli_fetch_array function and printed row by row. The final HTML output is a two-column table that displays all users' names and their registration dates.
Finally, the "Change Password" section of the site shows how PHP can accept data submitted from an HTML form, connect to a MySQL database, validate that data, and then use that data to update database records. The form is designed for a user to submit his email address, current password, and desired new password. It uses the POST method to make the submitted data available for PHP. The PHP script begins by checking for how the script is requested–in this case, POST. It then creates a connection to the MySQL server and selects the database with the mysqli_connect function. Next, it validates the data submitted from the HTML form and uses the mysqli_real_escape_string function to ensure the data is syntactically valid (i.e., making sure that potentially problematic characters are properly escaped). Then, a query is run to retrieve the user's ID by using the user's email address and current password in a WHERE clause to filter the records. After the relevant record has been retrieved, another query using an UPDATE statement is run to change the user's password.
The example may be previewed below or viewed in a separate tab/window here.