You get to a certain point in most programming languages and you ask yourself, “how do I save data?” I looked whether to tackle sessions and cookies next, but realized that it would make more sense to tackle data-persistence first before moving into those topics.

What is MySQL?

It is an Oracle distributed database system that runs on a server. It uses SQL syntax and is paired with PHP to the point that it’s not common to learn one without the other.

If you’re new to programming, and have been following along this series, you undoubtedly asked yourself, “do I just hardcode everything” and “does the data the user submits somehow integrate into the code?” We’ve been there. The next logical progression is “data must be stored in CSV files. We can crawl CSV files to extract that data.” Finally, you realize that there is a database.

The database is composed of tables and each of these tables has columns and rows. To visualize it simply, imagine the database as an Excel Workbook. Each sheet in the workbook represents a table. When you look at the sheet, you’ll see column titles, like first_name, and under that column title are rows with individuals first names. I purposefully tried not to use the term “table” in Excel, but that’s really what it’s called. When you format it, you click on “Format Table.” Your table has headers and rows of data. It’s something that you should be fairly exposed to.

You can use applications like phpMyAdmin that allow you to look at your database visually. It really does look like an Excel sheet of data.

SQL Syntax

MySQL databases use SQL syntax to:

• insert data into the table
• read data from table
• update data in table
• delete data from table

We’ll go into how to do this, but one simple one is: SELECT * FROM table; where table is the name of some particular table. This specific instance will return all rows from a table.

Always keep a visual of an Excel sheet in your head if you’re having a difficult time wrapping your head around SQL. For example, let’s see how we would perform these operations in an excel sheet.

• insert data into the table: open the excel sheet, go to the bottom of your table, and add the required fields.
• read data from table: open the excel sheet and start reading data from your table.
• update data in table: open the excel sheet, find the entry in the table that you want to edit, and make the changes to the required fields.
• delete data from table: open the excel sheet, find the row that you want to delete, right click on the row number and click “Delete Row.”

I hope that you’re seeing a pattern arise. There’s an algorithmic approach to these steps. For example, let’s say we wanted to read the contents of the authors table.

• Open the excel sheet: establish connection to the database. We’ll look at how to connect to the database programmatically next.
• Start reading data from your authors table: Execute command SELECT * FROM authors; to get a full list of data from the authors table for you to review.

Next Steps

In the next few articles, we’ll learn how to connect to a database programmatically, create our own database and tables, perform operations on those tables, and wrap up with some security since this is one of the most exploited attacks that you’ll face against. We’ll also take a look at “normalizing” data, which just means spreading the data out into multiple tables in order to save space and increase maintainability.