No products in the cart.
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).
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
jokethat 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
joketable 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
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
jokedatecolumns to tell all the jokes apart. The function of the
idcolumn, 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.
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.ymlfile 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
mysqlpackage, 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.
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:
For the Docker environment we’re using, the information is:
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:
Tip: you can probably use the command
startwill start any existing containers, while
upwill 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.
upwill work regardless of changes on your system, while
startmay 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.jeinto 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.
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
DROPfor deleting things. (Somewhat inconsistently, Delete is also used for some things!)
CREATE SCHEMAcommand 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, 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!)
If you expand your newly created
ijdbschema 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:
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:
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:
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:
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:
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:
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!
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
joketable 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:
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:
So, to add a joke to our table, we can use either of these commands:
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
VALUESlist must be the text that’s going to be placed in the
joketextcolumn. The second column name in the first parentheses gets its values from the same position in the
VALUESlist. 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
If you prefer, you can type text strings surrounded with single quotes (
') instead of double quotes:
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
INSERTcommand for a joke containing single quotes, even though single quotes have been used to mark the string:
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
INSERTqueries. Now that you know how to add entries to a table, let’s see how we can view those entries.
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:
Instead, this is the query that’s generated:
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:
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:
Unfortunately, this can cause MySQL to get a little confused. It may see
SELECTas a command rather than as a table name. What’s worse,
dateis 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?
Because the word
datealready has meaning in SQL, it may not be seen as a column name but as part of the query, like
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
INSERTquery above would be written like this:
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.
The command that we use to view data stored in database tables is
SELECT. You saw an example
SELECTquery generated for you by MySQL Workbench earlier. The
SELECTquery 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
This command will list everything that’s stored in the
This command says “select everything from joke”, with the
*meaning “all columns”. By default, a
SELECTquery 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
idcolumn, even though you didn’t specify them in the
INSERTqueries 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
joketextcolumn. The command for doing this is as follows:
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
SELECTcommand 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
joketextcolumn. Here’s the command we’d use:
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:
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
WHEREclause to the
SELECTcommand. Consider this example:
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:
This query displays the full text of all jokes containing the text “programmer” in their
LIKEkeyword tells MySQL that the named column must match the given pattern. In this case, the pattern we’ve used is
%signs (called wildcards) indicate that the text “programmer” may be preceded and/or followed by any string of text. (Interestingly,
LIKEis case-insensitive, so this pattern will also match a joke that contains “Programmer”, or even “FuNkYProGRammeR”.)
Conditions may also be combined in the
WHEREclause to further restrict results. For example, to display knock-knock jokes from April 2021 only, you could use the following query:
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
You can do a lot with the
SELECTcommand, 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.
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
UPDATEcommand. This command contains elements of the
INSERTcommands, since the command both picks out entries for modification and sets column values. The general form of the
UPDATEcommand is as follows:
So, for example, if we wanted to change the date on the joke we entered earlier, we’d use the following command:
Here’s where that
idcolumn comes in handy, enabling you to easily single out a joke for changes. The
WHEREclause used here works just as it did in the
SELECTcommand. This next command, for example, changes the date of all entries that contain the word “programmer”:
Note: believe it or not, the
WHEREclause in the
UPDATEcommand is optional. Consequently, you should be very careful when typing this command! If you leave the
WHEREclause out, the
UPDATEcommand will then apply to all entries in the table.
The following command will set the date for all the records in the table!
Deleting entries in SQL is dangerously easy, which you’ve probably noticed is a recurring theme. Here’s the command syntax:
To delete all programmer jokes from your table, you’d use the following query:
Note: as with
WHEREclause in the
DELETEcommand is optional. Consequently, you should be very careful when using it. If you leave the
WHEREclause out, the
DELETEcommand will then apply to all entries in the table.
The following command will empty the joke table in one fell swoop:<,em>
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
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
INSERTquery, 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
INSERTquery 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.
Tom Butler is a web developer and university lecturer. He has a PhD in the area of software engineering best practices and enjoys evaluating different approaches to programming problems.
© 2000 – 2023 SitePoint Pty. Ltd.