Welcome to part III of the PHP & MySQL course. So far, you've learned how PHP works, but you're probably still not feeling like you can build a real website with what you've got. That's totally normal,there's one key ingredient missing: the database.

A database lets you store data in an organized, structured way. Sure, you know about variables, but they only exist while the page is loading. You've also learned how to write to files, but let's be honest... things get messy fast when there's a lot of data involved.

At some point, you'll need to keep track of your members, forum messages, user preferences, and more. Databases are by far the cleanest and most efficient way to handle all that. So let's dive in and get to know them better!

MySQL... who's that?

A database (or DB for short) is a system that stores information. Like a text file? Not quite. The key difference is that in a database, everything is organized. That's what makes databases so useful — they give your data structure.

Yeah... I used to think like that too. I figured, "Sure, some things might need organizing, but most of the time I won't need it."

Big mistake. You'll see soon enough — 99% of the time, you'll want to use a database. That other 1%? Sure, save to a text file if you like (or JSON, or XML)... but once you've had a taste of databases, it's really hard to go back.

Think of it like this: a filing cabinet where every folder is exactly where it's supposed to be.

When everything's in its place, it's way easier to find what you're looking for, right? Same idea here — organize your data (say, info about your users), and pulling it up later becomes a breeze.

SQL and Databases

I gave you a quick intro to DBMSs (Database Management Systems) back in the first chapter. These are the programs responsible for storing your data.

DBMS: the storage managers

Here's a quick refresher on the most well-known ones:

• MySQL: Free, open-source, and probably the most popular. We'll be using it in this course.
• PostgreSQL: Also free and open-source, with even more features than MySQL, though slightly less well-known.
• SQLite: Very lightweight and free, but limited in functionality.
• Oracle: Used by big corporations, one of the most complete (and expensive) DBMSs out there.
• Microsoft SQL Server: Microsoft's own take — extremely powerful, but also comes with a hefty price tag.

So, you'll need to choose a DBMS to handle your data. I highly recommend sticking with the free and open-source ones at first: MySQL, PostgreSQL, or SQLite. In practice, MySQL offers a nice balance between simplicity and power.

We'll be working with MySQL, but don't worry — almost everything you'll learn applies just as well to other systems. This course is built so that switching later won't require a full brain transplant.

Talking to your DBMS in SQL

You'll need to send commands to your DBMS — like "give me this info" or "save that stuff." To do this, we use a special language called SQL.

The good news? SQL is a standard language. Whether you're using MySQL, PostgreSQL, or something else, you'll still use SQL to communicate.

The bad news? Each DBMS has its own little quirks and differences, especially when it comes to advanced commands.

You guessed it — we'll need to learn some SQL to work with databases. It's a completely different beast from PHP, but it's essential.

Here's a sample SQL command, just to whet your appetite:

SELECT id, author, message, datemsg FROM guestbook ORDER BY datemsg DESC LIMIT 0, 10

By the end of this section, you'll be able to fire off commands like:

• "Give me the 10 latest news posts"
• "Delete the last message posted in this forum"
• "Update the email address for user #47"

PHP: the go-between for you and MySQL

Of course, things can't be too easy — we don't get to talk directly to MySQL. Nope, only PHP can do that.

So PHP becomes the middleman. You'll say, "Hey PHP, tell MySQL to do this," and PHP will handle the rest.

A diagram would probably help here...

How PHP and MySQL talk to each other

Let's walk through what happens when a user posts a message on your forum:

• The server receives the request and calls up PHP, as usual.
• PHP starts doing its thing, then hits a line of code that says: "Tell MySQL to save this message."
• PHP relays the task to MySQL: "Hey, save this for me?"
• MySQL completes the task and replies: "All done!"
• PHP goes back to the server: "It's saved!"

Now that the introductions are done, we can explore how a database is actually structured. This next part is crucial — understanding how databases are organized is key.

The Structure of a Database

With databases, we need to speak the right language. Luckily, we're going to use a visual metaphor that makes everything easier: think of a filing cabinet. Pay close attention, and feel free to re-read if needed.

Picture this:

• The filing cabinet is the database. It's the big piece of furniture where secretaries stash all the office paperwork.
• Inside the cabinet are drawers. In SQL, these are called tables. Each drawer holds different kinds of info. For example, one drawer might contain usernames and visitor info, another might store forum posts.

And what's inside each drawer? That's the data itself, laid out in a grid. In this grid:

• Columns are called fields
• Rows are called entries

So, a table looks like a grid. Here's an example of what a "visitors" table might look like:

This table is what the inside of a drawer looks like.

In this example, the fields are: "ID", "Username", "Email", and "Age". Each row is an entry. We've shown 5 entries here, but a table can easily have 100, 1,000 — or even 100,000! (May your site be that popular!)

Most of the time, we create an "ID" field (also called a primary key). It's super handy for identifying each entry — kind of like how PHP arrays are indexed.

A database can contain as many tables as you like. Each table is like a spreadsheet: columns = fields, rows = entries.

Some typical examples of tables you might create for your site:

• news: all the homepage news articles
• chat: all the messages posted in the chat
• forum: all forum messages
• newsletter: email addresses of everyone subscribed to the newsletter

Now you're starting to see why you'll need a database on your site, right? ;)

Where is this data actually stored?

Before we wrap up this chapter, here's a question almost everyone has when they first hear about databases:

Excellent question. Totally normal to wonder. I remember being completely stumped by this back when I started PHP, simply because I couldn't picture what a database really was.

So let me clear things up! :)

The answer: MySQL stores the data in files. Yup, like everyone else, it writes to your hard drive.

All that talk about tables and drawers? That's just a way to visualize how things work. It helps us understand, but behind the scenes, it's all files.

Now, the exact file format and structure depend on the DBMS. But they all do the same thing: store the data in files somewhere on your disk.

For instance, if you're using MySQL with Uniform Server on Windows, you'll probably find the data files under /core/mysql/data/.

In practice, you'll never touch these files yourself. You'll just send commands to MySQL like: "Please store this," or "Go fetch that," and it'll handle the messy stuff for you.

And that's the real magic of databases — you don't have to worry about the chaos of manually organizing files. You ask MySQL to fetch all your news from December to July, and it does the digging for you.

If you've understood the diagrams and ideas so far, you're in great shape. Still feels a bit abstract? That's totally normal. But don't worry — we'll get our hands dirty in the next chapter, and everything will start to click. :)

Enjoyed this PHP & MySQL course?

If you liked this lesson, you can find the book "How to Build a Website in HTML and CSS" from the same authors, available on SiteRaw, in bookstores and in online libraries in either digital or paperback format. You will find a complete PHP & MySQL workshop with many exclusive bonus chapters.

More information