Introducing MySQL: A Beginner’s Guide

This content originally appeared on SitePoint and was authored by Tom Butler

The following article is an excerpt from PHP & MySQL: Novice to Ninja, 7th Edition, a hands-on guide to learning all the tools, principles, and techniques needed to build a professional web application using PHP and MySQL. In this tutorial, you’ll learn what a database is, and how to work with your own databases using Structured Query Language (SQL).

---

As I explained in the last chapter, PHP is a server-side scripting language that lets you insert instructions into your web pages that your web server software will execute before it sends those pages to browsers that request them. We’ve looked at a few basic examples, including generating random numbers and using forms to capture input from a user.

Now, that’s all well and good, but it really gets interesting when a database is added to the mix. In this chapter, we’ll learn what a database is, and how to work with your own databases using Structured Query Language (SQL).

An Introduction to Databases

A database server is a program that can store large amounts of information in an organized format that’s easily accessible through programming languages like PHP. For example, you could tell PHP to look in the database for a list of jokes that you’d like to appear on your website.

In this example, the jokes would be stored entirely in the database. The advantage of this approach is twofold. First, instead of writing an HTML page for each joke, you could write a single PHP script designed to fetch any joke from the database and display it by generating an HTML page for it on the fly. Second, adding a joke to your website would be a simple matter of inserting the joke into the database. The PHP code would take care of the rest, automatically displaying the new joke along with the others when it fetched the list from the database.

Let’s run with this example as we look at how data is stored in a database. A database is composed of one or more tables, each of which contains a list of items, or things. For our joke database, we’d probably start with a table called joke that would contain a list of jokes. Each table in a database has one or more columns, or fields. Each column holds a certain piece of information about each item in the table. In our example, our joke table might have one column for the text of the jokes, and another for the dates on which the jokes were added to the database. Each joke stored in this way would be said to be a row or entry in the table. These rows and columns form a table that’s represented in the image below.

If you’ve ever created a spreadsheet, this will look familiar to you. A database table is similar, in that data is stored in rows and columns. The only difference is that, unlike Excel — where the columns are named A, B, C, and so on — when you create a database table you choose a name for each column.

Notice that, in addition to columns for the joke text (joketext) and the date of the joke (jokedate), there’s also a column named id. As a matter of good design, a database table should always provide a means by which each row can be identified uniquely. Since it’s possible that two identical jokes could be entered on the same date, we can’t rely on the joketext and jokedate columns to tell all the jokes apart. The function of the id column, therefore, is to assign a unique number to each joke, so that we have an easy way to refer to them and to keep track of which joke is which. We’ll take a closer look at database design issues like this in Chapter 5.

Note: it’s also possible to use a combination of columns as a unique identifier — such as manufacturer name and product name together. One manufacturer will likely have more than one product, and two manufacturers may have products with the same name. By combining the two names, it’s possible to uniquely identify each product.

To review, the table pictured above is a three-column table with two rows (or entries). Each row in the table contains three fields, one for each column in the table: the joke’s ID, its text, and the date of the joke. With this basic terminology under your belt, you’re ready to dive into creating a database yourself.

MySQL

The title of this book is PHP and MySQL: Novice to Ninja — where MySQL refers to the database we’re using. However, if you peruse the docker-compose.yml file you downloaded as part of setting up the Docker environment, you’ll notice that it actually installs a database called MariaDB.

In 2009, MySQL was bought by Oracle, a massive software company. Unsure about MySQL’s future, Michael Widenius — one of the founders of the original MySQL database — decided to fork MySQL to create a new database called MariaDB. (Forking means creating a new project from an existing project, using the original project’s source code as a basis.) As well as not being controlled by Oracle, MariaDB has some performance advantages over MySQL, which makes it a great choice.

MariaDB is a drop-in replacement for MySQL, and any tutorials you follow that teach you how to use MySQL will work exactly the same way with MariaDB. As a PHP developer, you won’t notice any difference between the two, and it’s possible to swap one out for the other.

Note: over time, the differences in MySQL and MariaDB have grown slightly, but the fundamental commands, tools and techniques you’ll use will be the same. There are some minor differences when it comes to some of the more advanced features supported by the two databases.

Despite this happening over ten years ago, most developers and package management systems use the two interchangeably. On Arch Linux, for example, if you install the mysql package, it actually installs MariaDB instead, and the XAMPP package I discussed in the first chapter installs MariaDB instead of MySQL.

Despite this, if you start developing PHP code using MariaDB, you’ll see constant references to MySQL, not MariaDB. That’s because client software (anything that connects to the server to interact with the database) doesn’t know if it’s connecting to MySQL or MariaDB. To this client software, MySQL is a protocol. In the same way you can plug in a keyboard or mouse via a USB port on your computer, you can connect to a MySQL server or MariaDB server via the MySQL protocol.

So when you hear a developer talk about “adding records to a MySQL database”, they’re often referring to using the MySQL protocol to manage a database, regardless of the specific implementation being used.

You’ll find developers frequently using the term “MySQL” even though they’re actually using MariaDB. For consistency’s sake, I’m going to do the same in this book. Rather than refer to the server as MariaDB and use the term MySQL when discussing connecting from PHP, I’ll just use MySQL throughout.

Using MySQL Workbench to Run SQL Queries

Just as a web server is designed to respond to requests from a client (a web browser), a database server responds to requests from client programs. Later in this book, we’ll write our own MySQL client programs in the form of PHP scripts, but for now we can use a client program written by the same people who write MySQL: MySQL Workbench. You can download MySQL Workbench for free from mysql.com/products/workbench/.

There are many different MySQL clients available to use, and earlier editions of this book used phpMyAdmin, a web-based MySQL client that has many of the same features. However, it’s not as easy to use as MySQL Workbench, and can often be very slow.

Once you’ve downloaded and installed MySQL Workbench, open it up, and you should see a screen like the one shown below.

Before you can add any data to your database, you need to connect to it. A MariaDB server is running in the Docker Environment you downloaded in Chapter 1, and you can connect to it using a MySQL client such as MySQL Workbench.

Connecting to the database requires three pieces of information:

• a server address
• a username
• a password

For the Docker environment we’re using, the information is:

• Server: v.je
• Username: v.je
• Password: v.je

You’ll notice that the server name is identical to the URL you’ve been connecting to in your web browser to view your PHP scripts. The Docker environment is running both the web server and the database server, so you only need to remember a single address.

To connect to a database in MySQL Workbench, press the + button next to the “MySQL Connections” label in the centre of the window. (Admittedly, it isn’t very clearly labeled, and its purpose isn’t very clear, but never mind!)

When you press the + button, you’ll see a new window.

Enter the server address and username. You’ll also need to give your connection a name. I’ve called it v.je, but you can call it whatever you like. This is just a name it’s listed as for future reference in MySQL Workbench.

Once you’ve entered the username and server, you can try connecting to the database by pressing the Test Connection button at the bottom of the window.

You should get a password prompt box.

If you don’t, follow these steps:

1. Double-check that your environment is running. (If you’ve rebooted your PC since setting it up, you may need to run docker-compose up again in the project’s folder to get it running!)
2. Make sure the username, password and server address are correct.

Tip: you can probably use the command docker-compose start here. start will start any existing containers, while up will create them if they don’t exist and then start them. Depending on what you’ve done on your computer between the chapters of this book, the containers (and other things like network connections) created by Docker will be recreated if needed. If you’ve moved between computers, performed a system restore or certain software updates, it’s possible the containers may need to be created again.

As such, up will work regardless of changes on your system, while start may or may not work depending on what’s happened since last time you ran it.

Note: usernames and passwords are case-sensitive, so make sure you type them both in lowercase!

Enter the password v.je into the box and tick the box that says “Save password”. By checking the box, you won’t have to enter the password each time you connect. Then press OK.

If the password was entered correctly, you’ll see a message telling you the connection was successful. Press OK in the “Set up new connection” window and you’ll see a box appear in the main MySQL window with some of the information you entered.

Now that the connection is set up, it will be there each time you open MySQL workbench. You won’t need to add the connection each time.

You’re finally ready to actually connect to the database. To do this, simply double-click on the newly created box representing your connection and you’ll be presented with a different screen.

This looks a little daunting at first, as there are lots of different buttons and panels all representing different things. Down the left-hand side is a menu with lots of different options. The only one you need to worry about is the bottom section titled “SCHEMAS”.

Schema is just a fancy word for “database”. MySQL is a database server. In practical terms, this means that it can host lots of different databases, similarly to how a web server can host lots of different websites.

Creating a Database

Before you can add any information to a database, you need to create one. To create a database, right-click in the SCHEMAS panel and select Create schema. This gives you a window with several options, but you only need to enter one: the schema name.

I chose to name the database ijdb, for Internet Joke Database (with a tip of the hat to the Internet Movie Database), because that fits with the example I gave at the beginning of this chapter: a website that displays a database of jokes. Feel free to give the database any name you like, though. (You’ll need to type it out frequently as you progress through this book, so don’t pick anything too complicated!)

Once you’ve typed a name, you can safely leave the other options at their default values and press Apply. When you do this, MySQL Workbench will ask you to confirm your action. (Get used to these dialogs. MySQL Workbench insists on confirmation for almost everything you do!) Press Apply again on the screen shown below.

Once you’ve pressed Apply, you’ll need to press Finish on the next screen. This is one of the annoying things about MySQL Workbench: it forces you to confirm and then Finish every action. However, it’s better than the alternative, as we’ll see shortly!

In the screenshot above, you’ll see a white panel with the words CREATE SCHEMA `ijdb`. This is an SQL Query, and you’ll see a lot more of these throughout this book. You could have typed out this command yourself and run it, avoiding the GUI and saving yourself going through MySQL Workbench’s confirmation dialogs. And for a command as simple as CREATE SCHEMA `ijdb`, the GUI is probably overkill. However, as you’ll see shortly, not all of the commands are this simple, and it’s a lot easier to use MySQL Workbench’s GUI for some of the more complex queries.

If you want to be able to delete databases (and this is probably a good ability to have, given the amount of experimentation I’m going to encourage you to do in this book), MySQL Workbench makes this easy. In the SCHEMAS panel in the main window, right-click on the schema you want to delete and select DROP Schema. MySQL uses the word DROP for deleting things. (Somewhat inconsistently, Delete is also used for some things!)

Structured Query Language

Like the CREATE SCHEMA command we just saw, the commands we’ll use to direct MySQL throughout the rest of this book are part of a standard called Structured Query Language, or SQL (pronounced as either “sequel” or “ess-cue-ell”, so take your pick). Commands in SQL are also referred to as queries, and I’ll use these two terms interchangeably.

SQL is the standard language for interacting with most databases, so even if you move from MySQL to a database like Microsoft SQL Server in the future, you’ll find that the majority of commands are identical. It’s important that you understand the distinction between SQL and MySQL. MySQL is database server software that you’re using — and MariaDB, which you’re using, follows the same standards. SQL is the language that you use to interact with that database.

Most of these commands can be generated by MySQL Workbench, and that’s what we’ll use to create the structure of our database. However, you’ll need to learn some commands, as you’ll be executing them from your PHP scripts rather than MySQL Workbench!

Tip: in this book, I’ll teach you the essentials of SQL that every PHP developer needs to know. If you decide to make a career out of building database-driven websites, it pays to know some of the more advanced details of SQL, especially when it comes to making your sites run as quickly and smoothly as possible. To dive deeper into SQL, I highly recommend the book Simply SQL, by Rudy Limeback, or Jump Start MySQL, by Timothy Boronczyk.

Note: most MySQL commands are not case-sensitive, which means you can type CREATE DATABASE, create database, or even CrEaTe DaTaBaSe, and it will know what you mean. Database names and table names, however, are case-sensitive when the MySQL server is running on an operating system with a case-sensitive file system (such as Linux, macOS, or when running inside Docker).

Additionally, table, column, and other names must be spelled exactly the same when they’re used more than once in the same query.

For consistency, this book will respect the accepted convention of typing database commands in all capitals, and database entities (databases, tables, columns, and so on) in all lowercase.

This also makes it easier for people (like you!) to read the queries. MySQL doesn’t care, but you’ll be able to identify a command quickly and easily because it’s in capitals, and a reference to a table, column or database because it’s in lowercase.

Once your database has been created, it will appear in the SCHEMAS list on the left-hand side.

Now that you have a database, you need to tell MySQL Workbench that you want to use it. To do this, simply double-click the newly created schema and its name will go bold. You can only have one schema selected at a time, and you need to tell MySQL Workbench which you’d like to use.

You’re now ready to use your database. Since a database is empty until you add tables to it, our first order of business is to create a table that will hold your jokes. (Now might be a good time to think of some!)

Continue reading Introducing MySQL: A Beginner’s Guide on SitePoint.

This content originally appeared on SitePoint and was authored by Tom Butler