Basic CRUD Loop Tutorial
In software development, databases are one of the most essential tools in creating useful, dynamic applications. We use them for EVERYTHING!...storing and retrieving users, passwords, pictures, Booleans, text, loggings, settings, etc. Anything, really! Anywhere something needs to be "remembered for later," a database is key. There are a few "plug-and-play" front end solutions that allow for non-database storage, but they are mostly limited in scope and very unsecure. To really take advantage of the full potential of data storage and retrieval, a back-end language and database should be utilized.
Enter our dream team…PHP and MySQL!
If PHP is peanut butter, MySQL is jelly…or lamb and tuna fish if you prefer. In the primordial ooze that was the creation of this universe, these two found each other amongst all the chaos and destruction…they were just meant to be.
But enough sappy and more technical…
(If you want to get to the actual tutorial, scroll down to the TUTORIAL part. The following is useful, but not essential if you are just looking to get your hands dirty)
PHP and MySQL are founding members of the LAMP software bundle. LAMP stands for Linux, Apache, MySQL, and PHP, and is a development solution stack who's members combine in an easy, yet powerful way to create dynamic web pages that actually interact with their visitors. No additional software is required to support their applications. HTML and CSS, alone, can create pretty and well formatted web pages. But they can't DO much. But with the addition of PHP and MySQL, sites can collect data from users, create specific content on the fly, and do many other things that HTML alone can't. The beauty of LAMP is that it is designed to be used along with HTML. Another beauty of it is that it isn't super complicated to implement.
For the intents and purposes of this tutorial, you really only need to be actively familiar with PHP and MySQL. Linux and Apache are components that will operate in the background, but without need for input from you.
So what are these things?
PHP is a server-side scripting language that is often embedded into HTML to add functions HTML alone can't do. PHP allows you to collect, process and utilize data to create a desired output. In short, it lets users interact with web pages by making those pages DO something! PHP is able to perform a number of tasks including printing data, making numeric calculations, making comparisons, and making simple Boolean choices. From this you can create more complex loops and functions to make your page generate or get more specialized data.
MySQL is a relational database system developed by Oracle that is used to store information. Using SQL commands, MySQL can store many types of data from something as tiny as a single character to as large as complete files or graphics. Although it can be accessed by most programing languages, it is often coupled with PHP because they work together with ease, which you will see shortly. Information stored in a MySQL database hosted on a web server can be accessed from anywhere in the world with access to the internet. This makes it a good way to store information that needs the ability to change over time, but also needs to be accessed over the internet. To give you an example, the original Facebook page was written, almost exclusively, in PHP with a MySQL database. I use it frequently, as well, and the CRUD loop below is the base I build off of in all my PHP apps.
PHP and MySQL complement each other to do what neither can do alone. PHP can collect data, and MySQL can, in turn, store the information. PHP can create dynamic calculations, and MySQL can provide it with the variables it uses. PHP can create a shopping cart for a web store, and MySQL can keep the data in a format PHP can use to create receipts on demand, show current order status, or even suggest other related products. When you put these two components together it opens up countless possibilities for any website, helping it transition from a static site into a useful app users will want to return to.
In the next part, I will go through how to implement a basic CRUD loop into a static HTML website using PHP and MySQL as we create a simple, dynamic contact list.
What the heck is CRUD?
You know that stuff you get on the bottom of your shoe?
Just kidding! CRUD is an acronym that stands for "Create, Read, Update, and Delete." Essentially, it is a set of methods or functions to allow an application to access and modify data from a database. This, in turn, allows for rich, useful, and real-time applications that are elegantly written. A CRUD loop is the backbone of any data-driven application, and, as such, it should be one of the first back-end components created, while becoming common across the entire application. As we develop our contact list app below, you will start to see why.
To further understand what each component does, this table shows the relationship between CRUD and the corresponding SQL and HTTP requests:
|Term|| ||SQL|| ||Data|
|CREATE|| | ||INSERT|| | ||Create|
|READ|| | ||SELECT|| | ||Get|
|UPDATE|| | ||UPDATE|| | ||Modify|
|DELETE|| | ||DELETE|| | ||Delete|
Getting Set Up
1) The source code for this entire tutorial can be found in GitHub
Head on over and fork the repository to your computer. Or, if you are unfamiliar with forking and/or GitHub in general, feel free to simply copy and paste the code from each file into a same-named file in your text editor. There should be six files, total.
I have in-line comments that we will be referencing and removing as we progress through building the CRUD loop.
2) MySQL - Our Open-Source (free) Database
-Use the Community Install for MySQL 5.7 for Windows (DMG file for OSx)
-Make sure to include the Workbench in your install for easier use. We will be using Workbench here.
-During installation, when prompted, set root username to “root” and password to “password", and create a connection with the same name. **Note: When you ultimately integrate MySQL and PHP into your website, these settings NEED to be changed for security. We are simply using this set up for this tutorial.
-After creating the root connection, click on it and you will see that connection open in the Workbench. Once inside, at the top, click the "Create Schema" button
and create a schema with default settings named “contacts”. A schema is a subsection of a database used for organization and built a certain way to efficiently house the data it holds…think of a schema like a folder on your computer where you keep certain files…the schema is the parent folder and the data are the files inside. Organization is key to an efficient database structure. Also, please keep in mind that character case is very important with referencing MySQL names in our code.
3) Sublime Text - Our Text Editor
-I recommend Sublime Text for your text editor, but you may use any one you are comfortable with.
4) MAMP - Our Local Server
You are probably familiar with the first two things we installed. This one may be foreign. HTML and CSS can be run locally in any web browser because they are client-side languages…this means the browser you use actually reads their instructions and creates the webpage as a result. This is why, if you open any HTML file on your computer, it will pop open a browser on your desktop. However, with PHP, a server-side language, the browser, being client-sided, cannot understand its instructions. So, to read our PHP to access MySQL, we need a server that can read this type of file. Normally, if you uploaded a PHP file to the server used to host your website along with your HTML, it would work online. But in building and testing, it is much more efficient to turn your computer into that server. MAMP allows us to do that.
-When you click on the download link, it will ask if you want to download PRO also…just say "yes." You won't be charged for it unless you buy it.
-After installing, we need to make sure the ports for MySQL and MAMP do not overlap. These are the ports both of those components are using. Think of it like two cars going down a one-lane road together, side-by-side…it won't work. We need two lanes. Open MAMP and wait for MySQL and Apache boxes to both turn green. If they don't click on "Preferences" and then "Ports." Set Apache to 8888 and MySQL to 8889…this should be the default. If this configuration doesn’t work, try Apache: 80 and MySQL: 3306. Ultimately, you want to see two green boxes by MySQL and Apache. Once that happens, you are golden.
-In addition, we have to set the document root folder. This is the folder from which MAMP references all of our files…in this case our project. All files created in this tutorial NEED to be saved in that folder in order for MAMP to display them in our browser. By default, they are in C:\MAMP\htdocs, but you may change it to any folder you would like. To do so, open MAMP and go to "Preferences" and then "Web Server" to set the root.
Now on to the fun stuff!
Setting up our database
SQL data is housed inside database objects called "tables," which are, in turn, housed inside schemas. Each table should be responsible for a very finite amount of data. In doing so, it maintains the integrity of a database. When tables need to reference other tables, this can be done through something called a Foreign Key…but that's for a later episode. Since we're focusing on basic here, we will only be using one table.
First we need to create that table in our contacts database. Open up MySQL Workbench and click on the root connection to open it. Double click on the "contacts" schema on the bottom left to make it bold, and then click on the "Create New SQL Tab" button
at the top. In this window we will give MySQL instructions to create a new table called "contacts“ with the following SQL query:
CREATE TABLE `contacts` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 100 ) NOT NULL ,
`email` VARCHAR( 100 ) NOT NULL ,
`mobile` VARCHAR( 100 ) NOT NULL
) ENGINE = INNODB;
This new table will house everything we use for this tutorial. Copy and paste the above code into the new SQL query window in Workbench and then click the "Execute" lightning button
above it. Now, if you right click on the "contacts" schema, and click "refresh," you should see the "Contacts" table listed there.
Now to explain what we just did…
Reading like a book in the above code, we just created a table called "contacts" in the "contacts" schema (database subsection). In that table, we have 4 columns. The first column is called "id" and this houses our row's Id. Set up this way, each entry in our database will receive an always-unique integer(number) corresponding to its data. This value can never NOT be there (NOT NULL) because, if it was not there, we could not reference that unique data. Think of it like this…if we got rid of your name, how would someone reference you in a large group? We call this value the table's Primary Key. It is the key with which all row data is referenced in that table. Additionally, we set that Primary Key to AUTO_INCREMENT, meaning we do not have to set that value at all, as you will see. We just set the name, email, and mobile values. Upon entering the name, email, and mobile, the "id" will always be the "next number in line." …and thus, adding to its uniqueness…no two will ever be the same in that table.
Our second, third, and fourth columns will contain our contact's name, email address, and phone number. The values of each of those are VARCHAR(100), meaning the data we insert into each of those individual fields can be alpha-numeric UP TO 100 characters. If we set it up as VARCHAR(2), that would not be very helpful or efficient, as only two characters could go into that field…any more and we would receive an error when trying to insert that data. Alternately, we could say VARCHAR(60000) also, but if the value is too big, that would leave room for SQL injection (bad security) and other organizational nightmares. 100 is a pretty good length for our purpose.
Finally, ENGINE=INNODB gives the table a MySQL framework compatibility, giving it permissions to access things such as foreign keys to other tables. For the point of this tutorial, we won't be using it much, but it is always good to include because one can never know how deep or complex a simple table could get in the future. That line ends with a semi-colon, the syntax used to show that the query has ended.
Building the CRUD Loop
If you forked over the GitHub files, simply open each here from the parent directory. If not, create one file for each file in the source code and name it accordingly. Either way, the files should be housed in the MAMP root folder as set above.
First we will create our database connection in "database.php." This file will contain the PHP class named “Database” and it will handle all database connections for our project. This will also be common across all files (all of our methods will reference it to connect to the database). As you can see, there is no HTML in this code. For security reasons, when someone inspects the page source, all they will see is that the individual page references "database.php" and our login credentials will remain secure.
Go ahead and open the "database.php" file and walk through each line, reading my comments at each stage to understand what is going on. Keep in mind that this tutorial is not teaching you PHP or MySQL. The aim here is to show the ease of HOW to create a CRUD loop you can use in any web site. It is also providing you with a launch pad from which you can further study these technologies and their implementations into websites. For more detailed tutorials on PHP and MySQL, please reference a site like Codecademy
, who, I feel, does an excellent job teaching the actual language. When you are finished reading through "database.php," return to this point.
Our next step is creating our Index page. Like HTML, this Index will be the file a browser looks for first, as a homepage, despite the ".php" file extension. Because of that, this is the page that will house the front end code for the grid that will house our back end contacts data. For styling, I have chosen to use the Bootstrap CDN rather than have you download the whole file or create your own…again, for ease. Go ahead and read through "index.php" and my in-line comments. You will notice that all of the methods for Create, Read, Update, and Delete are in the code, just commented out. We will un-comment them as we explore each method-file. When you are finished reading through "index.php," return to this point.
Now that we have our database connection and index page set up, we can start working on the individual CRUD element pages…starting with "create.php." Go to your "create.php" file and read through it. Once you have finished, go into MAMP and click "Open Start Page." This will open a browser to MAMP's server homepage. To access our tutorial from here, in the address bar, simply delete MAMP back to your port number and replace it with the folder your files are in inside the root directory followed by "/index.php" (example: http://localhost:8888/CRUDLoop/index.php)…or, if the files are not in any folder inside the root, simply "index.php" (example: http://localhost:8888/index.php) and hit enter. Your screen should look something like this:
Now, go into your "index.php" file and uncomment the "create.php" button paragraph. Save your file and refresh your web app. A green "Create" button should appear, and when you click on it, your browser should take you to "create.php." From here, we can enter our first contact! …so go ahead! …and click "Create!"
The redirect in our code takes us back to "index.php" and we see our newly minted contact right there in our table! Cool, huh?! Now! If you really want to have some fun, open up MySQL and connect to your root connection. Once there, click on the "contacts" schema to expand it, expand its tables, right click on the "contacts" table, and click "Select Rows - Limit 1000," and BOOM! There it is!! PHP took our inputs from our HTML form and inserted them right into our MySQL database! Kinda' falls into place like melted butter, huh?
From here, read through each of the next three files…read.php, update.php, and delete.php…and after each file, go back into "index.php" and uncomment the corresponding function button. You'll notice, now, the commenting syntax for these buttons in "index" looks different than the create button's did. That is because we are not writing in HTML anymore. We are writing in PHP! It has been inserted into the HTML and, now, PHP is the main language "echoing" the HTML! Pretty cool, huh?! PHP started where you see "." Everything inside that is no longer HTML. As such, we use PHP comments, indicated by "//"…so simply delete the "//" next to each echo and refresh your index page in the browser to see the buttons!
If you play around with each of the buttons you will see what each can do...and in real-time!
That's it! You have just created a basic CRUD application to house your contacts! …and look at how easy it was to insert into a static HTML file! This website DOES something now! It has value! …and it wouldn't be hard to amend this to fit any number of applications! Feel free to play around with it and see how each CRUD element affects the database! See if you can add a "Notes" field to the database so you can save notes on each contact. Want to really learn? …See how you can, purposefully, get SQL errors! …and believe me…there are ways! If you break something, go back and reset to the original files. After all, breaking stuff is how you learn!
I just wanted to make a few caveats to this tutorial. First, this is a very basic CRUD loop. It only deals with one field value (VARCHAR), and, more importantly for this note, it is extremely unsecure. To use this in an actual application, the inputs would need to be sanitized to disallow malicious code from getting inserted into your database. Also, In order to host a MySQL database online, your schema would need to be physically transferred to a hosting server…it cannot just run from your computer (unless your computer is the server). Outside of that, look at what you can do with a very basic CRUD loop! In about an hour, you were given the skills to turn a static website into a dynamic web app! You gave value to that site! More importantly, because of that value, you gave value to your users and a reason for them to return! Yes, this is basic, and yes, there are many more things you could probably know and learn…but an hour ago, you didn't have any of it, and now you do! Imagine what you could do with an hour more! I'd love to see!