Introducing MySQL: A Beginner’s Guide

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. In this third tutorial in the series, 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!)

Creating a Table

If you expand your newly created ijdb schema by pressing the arrow next to the name, you’ll see a few entries.

The only one we’re concerned with for the purposes of this book is the Tables entry. Because your schema has just been created, it doesn’t have any tables.

A table describes the format of your data. You’ll need to know the structure of the data you’d like to store. Before creating a table, you need to think about exactly what you want to store. For the jokes example, we want to store these pieces of information:

• the text of the joke
• the date it was added

Along with the text and date, we’ll also need some way to identify each joke. To do this, we’ll give each joke a unique ID.

Each piece of information is placed in a field in the table, and each field has a “type”. Types can be used to store data in different formats like numbers, text and dates.

There are three main kinds of types that you’ll encounter:

• numbers, for storing numeric values
• text, for storing strings
• dates/times, for storing timestamps

There are lots of column types in MySQL, but you only really need an understanding of three for most purposes!

To create a table using MySQL Workbench, expand the database in the SCHEMAS list, then right-click on the Tables entry and select Create Table.

The middle panel of the window will change to show you something like what’s pictured below.

Every table is given a name to identify it and a series of columns. Firstly, enter the table’s name as “joke” and add the following columns in the column list:

• id, which will act as a unique identifier for each joke so we can retrieve it later
• joketext, which will store the text of the joke
• jokedate, which will store the date the joke was added

You’ll notice there’s a second column called Datatype. Each column in a database table must be assigned a type. The three types we will need are:

• INT, meaning “integer” for the numeric jokeid
• TEXT, to store some text for the joke
• DATE, to store the date the joke was published

This helps to keep your data organized, and allows you to compare the values within a column in powerful ways, as we’ll see later.

If we were to stop setting up the table at this point, you could start adding records to the table (and I’ll show you how to do that very shortly!). However, you’d have to provide all three pieces of information: the joke ID, the joke text, and the joke date. This means that, to add the next joke, you’d need to keep track of how many were in there in order to assign the next ID.

This sounds like extra work, but fortunately MySQL Workbench provides a convenient way of avoiding it. Along with the name of the column and data type it stores, you’ll notice there’s a series of checkboxes for each field in the table.

There are three we’re interested in here for our ID field:

• PK. This means “primary key”. Ticking this box specifies that this column is to act as a unique identifier for the entries in the table, so all values in this column must be unique, ensuring that no two jokes will share the same ID.
• NN. This stands for “Not Null”, and means that when a record is added, a value must be placed in the field. For our ID column, ticking this box tells MySQL not to accept jokes that don’t have an ID.
• AI. This is the clever bit that will save us work. No, “AI” is not “Artificial Intelligence”, some kind of computer-brain doing our work for us. In this case, it stands for “Auto Increment”, and by checking this box (and it’s only allowed on INT fields!), whenever a record (in our case, a joke) is added to the table, it will automatically be assigned the next available ID. This is a real time saver and a feature worth remembering.

Your table should now look like the one pictured below.

Press the Apply button, and the joke table will be created. You’ll see the following query appear in the window:

CREATE TABLE `ijdb`.`joke` ( `id` INT NOT NULL AUTO_INCREMENT, `joketext` TEXT NULL, `jokedate` DATE NULL, PRIMARY KEY (`id`));

You’ll notice a lot of the same information has been repeated that we entered into the GUI. The GUI just generates this code for us, which is a much quicker and easier way of creating tables than remembering all of the syntax and vocabulary needed to write the query yourself.

As a developer, you don’t need to create tables often. But you will need to interact with them — adding and removing records and retrieving them from the database — so it’s worth spending time learning how to write queries to do this. For creating tables, however, it’s usually a lot quicker and easier to use the MySQL Workbench GUI, because once a table has been created, you won’t need to write another create table statement.

We need to look at just one more task: deleting a table. This task is frighteningly easy, so be careful! If you delete a table, you can’t get it back.

In the SCHEMAS list, right-click on the table you want to delete and select Drop Table. Don’t run this command with your joke table unless you actually do want to be rid of it. If you really want to try it, be prepared to recreate your joke table from scratch. When you delete a table, the table is removed permanently, along with any data stored inside it. There’s no way to recover the data after the table has been dropped, so be very careful when using this command!

Adding Data

Now that the table has been created, it’s time to add some data to it. Although this can be done using MySQL Workbench’s GUI, this time we’re going to write the query ourselves. Eventually, we’ll need to be able to write our own database queries directly from PHP, so it’s good to get some practice writing them.

To run a query, you need to open up a query window. The simplest way to do this is to expand your database in the SCHEMAS list. Expand the Tables entry, and you’ll see the joke table that you just created. Right-click on the table and click on the topmost option “Select Rows – Limit 1000”.

This will give you a slightly different screen that’s split into two panels horizontally.

The top half is a text box into which you can type commands to ask your database server questions or make it perform tasks. The bottom half is the result of that query. You’ll see there’s already a query in the top panel:

SELECT * FROM `ijdb`.`joke`;

We’ll come back to what this means shortly. Along with this query in the top panel, there’s a list of rows in the bottom panel — or rather there would be, if there were anything in the table! Because the table was just created, it’s currently empty. Before you can view the contents of the table, you need to add some records.

All that’s left is to put some jokes into the database. The command that inserts data into a database is called, appropriately enough, INSERT. This command can take two basic forms:

INSERT INTO tableName SET column1Name = column1Value, column2Name = column2Value, …

INSERT INTO tableName (column1Name, column2Name, …) VALUES (column1Value, column2Value, …)

So, to add a joke to our table, we can use either of these commands:

INSERT INTO joke SET
joketext = "A programmer was found dead in the shower. The instructions read: lather, rinse, repeat.",
jokedate = "2021-10-29"

INSERT INTO joke
(joketext, jokedate) VALUES ( "A programmer was found dead in the shower. The instructions read: lather, rinse, repeat.", "2021-10-29")

Note that the order of the column/value pairs isn’t important, but pairing the right values with the right columns, position-wise, is. If the first column mentioned in the first set of parentheses is joketext, then the first entry in the VALUES list must be the text that’s going to be placed in the joketext column. The second column name in the first parentheses gets its values from the same position in the VALUES list. Otherwise, the order of the columns isn’t important. Go ahead and swap the order of the column and value pairs and try the query.

As you typed the query, you’ll have noticed that we used double quotes (") to mark where the text of the joke started and ended. A piece of text enclosed in quotes this way is called a text string, and this is how you represent most data values in SQL. For instance, the dates are typed as text strings, too, in the form "YYYY-MM-DD".

If you prefer, you can type text strings surrounded with single quotes (') instead of double quotes:

INSERT INTO joke SET
joketext = '',
jokedate = '2021-10-29'

You might be wondering what happens when there are quotes used within the joke’s text. Well, if the text contains single quotes, surround it with double quotes. Conversely, if the text contains double quotes, surround it with single quotes.

If the text you want to include in your query contains both single and double quotes, you’ll have to escape the conflicting characters within your text string. You escape a character in SQL by adding a backslash () immediately before it (which, conveniently, is the same as in PHP). This tells MySQL to ignore any “special meaning” this character might have. In the case of single or double quotes, it tells MySQL not to interpret the character as the end of the text string.

To make this as clear as possible, here’s an example of an INSERT command for a joke containing single quotes, even though single quotes have been used to mark the string:

INSERT INTO joke
(joketext, jokedate) VALUES ( '!false - it's funny because it's true', "2021-10-29")

As you can see, I’ve marked the start and end of the text string for the joke text using single quotes. I’ve therefore had to escape the two single quotes (the apostrophes) within the string by putting backslashes before them. MySQL would see these backslashes and know to treat the single quotes as characters within the string, rather than end-of-string markers.

If you’re especially clever, you might now be wondering how to include actual backslashes in SQL text strings. The answer is to type a double-backslash (\), which MySQL will treat as a single backslash in the string of text.

Write your insert query into the top text box in MySQL Workbench and press the yellow lightning bolt icon above it to execute the query.

When the query executes, a panel will appear at the bottom of the screen telling you if the query was executed successfully.

If you get an error and the query isn’t successful, take a look at the error message. It should give you a hint where to look. Double-check your syntax, and check your quotes and parentheses are in the right place.

Tip: if you have a lower screen resolution than it’s expecting, MySQL Workbench hides the bottom panel. To display it, hover your mouse just below the scroll bar at the bottom of the window and you’ll get a resize cursor. You can then drag the panel into view.

Add both the jokes (and any others you can think of!) to the database using INSERT queries. Now that you know how to add entries to a table, let’s see how we can view those entries.

A Word of Warning

You’ll have noticed something slightly peculiar about the queries that have been generated by MySQL Workbench. We don’t get a query that looks like this:

SELECT * FROM joke

Instead, this is the query that’s generated:

SELECT * FROM `joke`

There are strange quotes around `joke`. Those aren’t actually quotes, or even apostrophes like we’ve been using to designate strings. They’re backticks.

This is a safety precaution. There are lots of words in SQL that have meaning to the language. You’ve seen a few already: SELECT, FROM and INSERT. But there are hundreds of others, known as reserved words. Imagine if you called your table SELECT. The query you would need to run would look like this:

SELECT * FROM SELECT

Unfortunately, this can cause MySQL to get a little confused. It may see SELECT as a command rather than as a table name. What’s worse, date is one of these words, and it’s not improbable that you might think to create a column in one of your tables called date. What would you expect to happen when the following query runs?

INSERT INTO joke
(joketext, date) VALUES ( '!false - it's funny because it's true', "2021-04-01")

Because the word date already has meaning in SQL, it may not be seen as a column name but as part of the query, like VALUES or INTO.

MySQL is usually good at guessing whether you’re referring to a table/column name or a command it needs to follow, but there are times when it isn’t able to make that distinction. To avoid this kind of confusion, it’s good practice to surround all table and column names with backticks. The backticks tell MySQL to treat the string as a name rather than an instruction. It’s good to get into the habit of doing this from the very start, as it avoids issues later on that often aren’t immediately obvious.

From now on, I’ll surround all table, schema and column names with backticks. This will also help you — as a programmer — to distinguish between commands and column names. For instance, the INSERT query above would be written like this:

INSERT INTO `joke`
(`joketext`, `date`) VALUES ( '!false - it's funny because it's true', "2021-04-01")

Tip: on many English-language keyboard layouts, the backtick key is the one to the immediate left of the numeric 1 key and below Esc. Its location may differ on non-English keyboards and/or on various devices such as laptops and tablets.

Viewing Stored Data

The command that we use to view data stored in database tables is SELECT. You saw an example SELECT query generated for you by MySQL Workbench earlier. The SELECT query is the most complicated command in SQL. The reason for this complexity is that the chief strength of a database is its flexibility in data retrieval. At this early point in our experience with databases, we need only focus on fairly simple lists of results, so let’s consider the simpler forms of the SELECT command here.

This command will list everything that’s stored in the joke table:

SELECT * FROM `joke`

This command says “select everything from joke”, with the * meaning “all columns”. By default, a SELECT query will return every record in the table. If you try this command, your results will resemble the image below.

Notice that there are some values in the id column, even though you didn’t specify them in the INSERT queries you ran earlier. MySQL has automatically assigned an ID to the joke. This is because you checked the “AI” (Auto Increment) checkbox when you created the table. If you hadn’t checked the box, you’d have needed to specify the ID for each joke you inserted.

If you were doing serious work on such a database, you might be tempted to stop and read all the hilarious jokes in the database at this point. To save yourself the distraction, you might want to tell MySQL to omit the joketext column. The command for doing this is as follows:

SELECT `id`, `jokedate` FROM joke

This time, instead of telling it to “select everything”, we told it precisely which columns we wanted to see. The result should look like the image below.

What if we’d like to see some of the joke text? As well as being able to name specific columns that we want the SELECT command to show us, we can use functions to modify each column’s display. One function, called LEFT, enables us to tell MySQL to display a column’s contents up to a specified number of characters. For example, let’s say we wanted to see only the first 20 characters of the joketext column. Here’s the command we’d use:

SELECT `id`, LEFT(`joketext`, 20), `jokedate` FROM `joke`

See how that worked? Another useful function is COUNT, which lets us count the number of results returned. If, for example, you wanted to find out how many jokes were stored in your table, you could use the following command:

SELECT COUNT(`id`) FROM `joke`

As you can see in the image below, you have just two jokes in your table.

Note: you can use COUNT(*) for the same result, but this is slower, as all the columns will be selected from the table. By using the primary key, only one column needs to be retrieved.

So far, the examples we’ve looked at have fetched all the entries in the table. However, you can limit your results to only those database entries that have the specific attributes you want. You set these restrictions by adding what’s called a WHERE clause to the SELECT command. Consider this example:

SELECT COUNT(*) FROM `joke` WHERE `jokedate` >= "2021-01-01"

This query will count the number of jokes that have dates greater than or equal to January 1, 2021. In the case of dates, “greater than or equal to” means “on or after”. Another variation on this theme lets you search for entries that contain a certain piece of text. Check out this query:

SELECT `joketext` FROM `joke` WHERE `joketext` LIKE "%programmer%"

This query displays the full text of all jokes containing the text “programmer” in their joketext column. The LIKE keyword tells MySQL that the named column must match the given pattern. In this case, the pattern we’ve used is "%programer%". The % signs (called wildcards) indicate that the text “programmer” may be preceded and/or followed by any string of text. (Interestingly, LIKE is case-insensitive, so this pattern will also match a joke that contains “Programmer”, or even “FuNkYProGRammeR”.)

Conditions may also be combined in the WHERE clause to further restrict results. For example, to display knock-knock jokes from April 2021 only, you could use the following query:

SELECT `joketext` FROM `joke` WHERE
`joketext` LIKE "%knock%" AND
`jokedate` >= "2021-04-01" AND
`jokedate` < "2021-05-01"

Enter a few more jokes into the table. (For example, “Why did the programmer quit his job? He didn’t get arrays.”) Then experiment with SELECT queries.

You can do a lot with the SELECT command, so I’d encourage you to become quite familiar with it. We’ll look at some of its more advanced features later, when we need them.

Modifying Stored Data

Having entered data into a database table, you might find that you’d like to change it. Whether you’re correcting a spelling mistake, or changing the date attached to a joke, such alterations are made using the UPDATE command. This command contains elements of the SELECT and INSERT commands, since the command both picks out entries for modification and sets column values. The general form of the UPDATE command is as follows:

UPDATE `tableName` SET `colName` = newValue, …
WHERE conditions

So, for example, if we wanted to change the date on the joke we entered earlier, we’d use the following command:

UPDATE `joke` SET `jokedate` = "2021-04-01" WHERE id = "1"

Here’s where that id column comes in handy, enabling you to easily single out a joke for changes. The WHERE clause used here works just as it did in the SELECT command. This next command, for example, changes the date of all entries that contain the word “programmer”:

UPDATE `joke` SET `jokedate` = "2021-04-01"
WHERE `joketext` LIKE "%programmer%"

Note: believe it or not, the WHERE clause in the UPDATE command is optional. Consequently, you should be very careful when typing this command! If you leave the WHERE clause out, the UPDATE command will then apply to all entries in the table.

The following command will set the date for all the records in the table!

UPDATE `joke` SET `jokedate` = "2021-04-01"

Deleting Stored Data

Deleting entries in SQL is dangerously easy, which you’ve probably noticed is a recurring theme. Here’s the command syntax:

DELETE FROM `tableName` WHERE conditions

To delete all programmer jokes from your table, you’d use the following query:

DELETE FROM `joke` WHERE `joketext` LIKE "%programmer%"

Note: as with UPDATE, the WHERE clause in the DELETE command is optional. Consequently, you should be very careful when using it. If you leave the WHERE clause out, the DELETE command will then apply to all entries in the table.

The following command will empty the joke table in one fell swoop:<,em>

DELETE FROM `joke`

Scary, huh?

Let PHP Do the Typing

There’s a lot more to the MySQL database server software and SQL than the handful of basic commands I’ve presented here, but these commands are by far the most commonly used and most useful!

At this stage, you might be thinking that databases seem a little cumbersome. SQL can be tricky to type, as its commands tend to be long and verbose compared with those of other computer languages. You’re probably dreading the thought of typing in a complete library of jokes in the form of INSERT commands.

Don’t sweat it! As we proceed through this book, you’ll be surprised how few SQL queries you actually type by hand. Generally, you’ll be writing PHP scripts that type your SQL for you. For example, if you want to be able to insert a bunch of jokes into your database, you’ll typically create a PHP script for adding jokes that includes the necessary INSERT query, with a placeholder for the joke text. You can then run that PHP script whenever you have jokes to add. The PHP script prompts you to enter your joke, then issues the appropriate INSERT query to your MySQL server.

For now, however, it’s important to develop a good feel for typing SQL by hand. It will give you a strong sense of the inner workings of MySQL databases, and will make you appreciate all the more the work that PHP will save you from having to do!

To date, we’ve only worked with a single table, but to realize the true power of a relational database, you’ll need to learn how to use multiple tables together to represent potentially complex relationships between the items stored in your database. I’ll cover all this and more in Chapter 5, in which I’ll discuss database design principles and show off some more advanced examples.

In the meantime, we’ve accomplished our objective, and you can comfortably interact with MySQL using the MySQL Workbench query window. In Chapter 4, the fun continues as we delve into the PHP language, and use it to create several dynamically generated web pages.

If you like, you can practice with MySQL a little before you move on, by creating a decent-sized joke table (for our purposes, five should be enough). This library of jokes will come in handy when you reach Chapter 5.