Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
We've learned enough PHP by now that we can build dynamic websites. We can build pages that link to other pages, web forms that submit data, and even access cookies to remember user data from page to page. You can build a pretty good website using only these tools. You can even have features like user log ins. But as your site complexity increases, you are going to reach the limits of what you can do with just PHP alone. And quickly, you'll discover that adding a database makes a lot of sense. A database is going to allow you to both read and write data. Mostly with PHP so far, we have been reading data that we had coded ahead of time.
Writing data can be done with just PHP alone, but the process is more complicated than it is to just set up a database. Databases also let you store more data, keep it better-organized, they're faster to access the data. And it's much easier to manipulate the data, especially if we want to manipulate a lot of data all at the same time. And perhaps most importantly, databases allow us to relate data to other data. That's why we often refer to them as relational databases. It's an important aspect, the fact that we have relationships, and we can work with our data in complex ways. And the database that we'll be using is going to be MySQL. MySQL is open source and free just like PHP.
You can use other databases with PHP, almost any database you want. You just have to change the way that you connect to the database. But we're going to be using MySQL because it's easy to use. It's very popular and you're going to find lot's of support for it out there if you run into problems. And it also provides a good introduction to many common database concepts that you're going to find in any database that you use. If you've never worked with a database before chances are that you have worked with a spreadsheet like Microsoft Excel. And you've seen people put data in there, that's not just numbers that they're adding up. They will put columns like first name and last name and so on. A database is similar to this in that it has columns and rows that are populated with data.
A spreadsheet page is what we would call a database table. And you can have multiple tables in the same way many spreadsheets often lets you have different worksheets that you can switch between. The spreadsheet columns are the table columns and those are what define what data will be stored. For example first name, last name, city and so on. The rows are individual records. So if I have a database table that has 20 customers in it, then I have 20 rows. Each row has data in each of the columns. Now, while this can be a useful analogy, databases are not spreadsheets. A database can define and traverse relationships between tables. That's that relational aspect again.
It's very powerful. Spreadsheets don't give you that. The other big difference is that, when working with databases, we're going to be issuing commands in order to interact with the database. With the spreadsheet, we have it all laid out in front of us in a visual medium where we can see all of those tables and rows and columns. Databases, we're not going to have that. We're going to be working with subsets of the data all the time. And we can ask it to show us some of that information. But we're going to be issuing commands to pull back bits of the data or issuing commands to manipulate parts of the data. And perhaps, it goes without saying, that spreadsheets are optimized for adding numbers, that's what they do best.
Databases are optimized for working with data and that's what they do best. Let's review some of the common database terms that we're going to be using. So that you'll recognize them when I use them and make sure we're all on the same page about their meaning. The first, of course, is just database. We can have several databases running in MySQL at the same time. A database is a set of tables. And each database will contain its own set of tables. And different databases don't interrelate. So we'll typically have one database for one application. So we build our web application. It's going to connect to one and only one database.
And we'll be building a CMS for a fictional company called Widget Corp. And access permissions to our data are typically granted at the database level. Next, we have our table. A database is a set of tables, so a table is going to be a set of columns and rows, just like we saw when we used the spreadsheet metaphor. Now, each table is going to contain one type of information. That type is a single building block of our web application. And it's going to be a plural noun. So for example products, customers, orders, countries, students, books, transactions, those are good examples of nouns that would be in our application.
And notice that they're all plural because our table is going to be a container holding many of these things. And the nouns don't have to be concrete, they can represent more abstract ideas like favorites, or settings. And where with our database is they don't interrelate, our tables very much will interrelate. We're going to want to create relationships between our tables. Next we have column. A column is a set of data of a single simple type. We saw that in the spreadsheet example. So we have first name, last name, email password, and columns have types so we have certain kinds of data that goes in certain kinds of columns.
So strings go into the strings columns, integers go into the integers column and so on. We also have rows, that's a single record of data. So for example, I might have a row that's Kevin, Skoglund, email@example.com and secret that corresponds to column types that list in the example bar. And then last of all, we have a field. And a field is the intersection of a column and a row. So for example, in the first name column, in the field for the user Kevin, I have the data, Kevin. So the field is actually the intersection between the two. Now, field is often used interchangeably with column. And you'll very often hear me flip the two around. Don't let that throw you but technically speaking a field is an intersection between a row and a column.
The next important term to know about is index. Index is going to be a data structure on a table that is going to increase the speed of look ups in that table. It's part of what makes data bases really suited to work with data is the fact we have something like this that can speed up our access. It works a lot like the index at the back of a book. You thumb to the index, you look up the reference that you're looking for, it tells you what page, and go directly to that page. Indexes work the same way. And then we have this idea of a foreign key. And that's going to be a table column whose values are going to reference the rows that are in another table. So this is where we're going to create our relationships. This is how we'll create relational databases is by using foreign keys. We'll get to this a little more when we talk about creating relational tables. I wanted to at least introduce the term to you here. And then last of all, I want us to make sure that we understand what CRUD means. CRUD is an acronym that stands for the four most basic operations that we do with databases.
So we have create, read, update, and delete. And together, these are the four most basic operations that we perform with databases. We will create new rows in our database tables, we'll read back data, we'll update the data, and we'll delete data from it. So if you hear me say, we're going to take care of the CRUD now, what I mean is that we're going to write the PHP code that is going to allow us to perform these four basic operations. Then we're going to learn how to do all four of those but first we need to go to MySQL and create our database.
Get unlimited access to all courses for just $25/month.Become a member