Databases, MYSQL, PHP: A Basic Gradebook
Made With: HTML, CSS, MySQL, PHP, Brackets
Time in Development: 3 Weeks
Go to the Site: Gradebook Database
Databases
For as many inventory spreadsheets as I had built in my days managing a movie camera rental house, I somehow hadn’t made use of their powerful web-based brother until undertaking this project.
Databases are a central part of the infrastructure of the web. They serve as the memory centers for dynamic websites and apps, capable of storing and organizing massive amounts of data. As the hippocampus helps us to form, sort, and store human memories, so do databases for digital information.
MySQL
Of course, deep storage is of little use if we can’t retrieve, manage, and manipulate the data — and for those purposes we use a relational database management system like MySQL, SQLite, or PostgreSQL.
While learning a new programming language might always spark some initial cross-eyed confusion, the syntax of MySQL was surprisingly and indeed pleasantly similar to spoken English – at least for the basic statements (known as queries) that I explored here.
Learning how to select (SELECT FROM), insert (INSERT INTO), update (UPDATE__SET), and delete (DELETE FROM) records in a database gave a solid foundation of simple queries to build on moving forward.
PHP
With some front-end web design skills in my toolbox, I needed a way to dynamically communicate between the websites I’m learning to build and my newly-created MySQL databases. This middleman of a language – called, appropriately, “middleware” – gathers information on a webpage to be stored, assigns it to variables, and uses those variables to send SQL statements to a database.
In the other direction, middleware takes information sent from a database and processes it to be displayed on the web. While developers can choose from a host of languages to write middleware files, I focused for this project on learning PHP.
PHP had crossed my radar on occasion as I learned the basics of WordPress, but I hadn’t yet examined this server-side scripting language in any real depth. The practice of storing information in variables (in PHP using the $ symbol) and executing WHILE loops were familiar from my forays into Javascript, but most of the other PHP code explored in this project was new to me.
To build this basic gradebook, I learned how to:
Securely connect to a database:
include(“connect_info.php”);
$connection=mysql_connect($hostname, $mysql_login, $mysql_password);
$dbs = mysql_select_db($database, $connection);
Take data from an HTML form and assign it to a variable:
$firstName = mysql_real_escape_string($_POST[“firstName”]);
$lastName = mysql_real_escape_string($_POST[“lastName”]);
Run SQL queries:
$sql = “SELECT * FROM database.tGradebook WHERE (firstName = ‘$firstName’ AND lastName = ‘$lastName’)”;
$result = mysql_query($sql);
Generate tables from the query results using the echo command, create XML files, write conditional statements, and more!
Moving Forward
This early exploration into databases and their associated management systems, languages, and uses has, perhaps more than anything, opened my eyes to just how prevalent they are in the digital services I access daily. Entering grades, surfing on Reddit, or logging into my bank account — each user experience combines dynamic websites and databases in different ways, for different purposes, and with different security practices.
In addition to obvious room for growth in my capabilities with MySQL and PHP, I also hope to gain further experience using AJAX to allow for asynchronous updates on the websites I build in the future.
Check out my MySQL and PHP project here:
A Basic Gradebook Database