9.1 Create a Users Table

One of the primary functions of the server is to process data. The data could be coming from the user, and a lot of times it is. But most of the time, if we're working with data, it resides either on the server itself or it resides on another server. Now, we refer to where we store our data as a data store. A data store could be files on the file system. And in fact, a lot of early PHP applications did just that, they stored all of their information on the file system. But most of the time, if you're storing data, you're going to use a database. And at the beginning of this course, I briefly talked about different types of databases. We have what are called relational databases. These are also called SQL databases, and there are many different types of SQL databases. There is MySQL, which is what we are going to be working with in this course. And it's also the standard database to use with PHP applications. There is also Microsoft SQL, Microsoft has their own SQL database which is also very good. Oracle has one. Now Oracle also owns MySQL, but don't confuse that with Oracle's SQL database. They are two separate products, in fact, Oracle bought MySQL. So you can say that Oracle owns MySQL, but they didn't create MySQL. There's also something called Postgres or something along those lines. That's also a SQL database. That's also a really cool database to use. But I digress, IBM has a SQL database. There are a ton of SQL databases and you can use all of them with your PHP applications. But most of the time, you're going to be using MySQL databases. So we have a relational databases, which are also SQL databases. Now, SQL stands for Structured Query Language. You could say SQL, but it's easier to just say SQL. And then there are what are called documents databases, and these are typically referred to as NoSQL. So, whenever we start talking about selecting, and inserting, and updating. We are going to be using SQL commands to do all of those things. And when it comes to document databases, there are no relationships between the data that you store. There's also no commands that you issue as far as selecting information or updating or inserting new information. So we group all of those different types of databases as NoSQL databases. So we are going to be using what's called the relational database. Now, a database is just a collection of different pieces of information. But it's a lot more than that, it's not just pieces of information, it's more like an entity. Let's say for example that we're going to write a blog application. So, we would create a database called blog. And then we would store all of the data for our blog inside of this database. But it is organized in two separate entities. We want to break things down into logical pieces. Like for example, when you create a blog, well, one of the things you need is to be able to keep track of the users. So that they can log in, they can create posts, edit posts, and delete posts. So you need to store the users that are going to have permission to do those things with your blog. You also need to be able to store the different posts, because that's how you're going to store all of the content for your website. Now, if you weren't going to use a third party tool for comments, you could also store your comments. And if you wanted a robust system of permissions, you would have a table for permissions, and so on and so forth. But then, these individual logical pieces, break down into smaller pieces of information. For example, our users would have several different pieces like a first name and a last name and an email address. A post would have a title, it would have the content of the post. And it would also have a reference to the user that created it. So we could also say that the posts would have user information. And then comments, well, we would have contents of the comment. Some comments, or at least some comment systems allow you to have a title for the comment. And comments are typically linked to posts, so we can have some information about the post that that comment is for. So what you end up with is a database that has what are called tables, and these tables are just organized pieces of data. And we call each one of these individual pieces of data as a column. So at the top, we have a database, a database contains tables, and a table contains columns. So we are going to be working with the MySQL database that was installed whenever we installed MAMP. And we can manage our database in a variety of different ways. Now, by default, nothing is given to us, if we just installed the MySQL database engine. We would have the engine, we might have a command line tool. And some database admins, would love to use the command line. But for us developers, we need something a little bit more easier to use. Because, well, databases can be a little cumbersome to work with, if you don't have a graphical tool. So if you go to MAMP and then, open start page, that's going to take you to the welcome page for MAMP. Go to Tools, and then there's this phpMyAdmin. Now, phpMyAdmin has been around for a very, very, very long time. And it is one of the ways that we can work with a MySQL database. Now, this was automatically installed for us with MAMP. It's already set up so that it refers to our MySQL installation. So we don't have to do anything as far as configuration is concerned. So we're going to create a blog database. And it's basically going to follow some of what we've done here. We're going to have a blog, we're going to have a user's table and then a post's table. So over on the left-hand side, we want to create a new database. So we will click on new, and then we want to specify the database name, blog, in this case. Now the collation, we're just going to use the defaults. Frankly, I have no idea what this does, I know it changes some of the way that data is stored within the database. But the default is going to work 99.999% of the time. So after you create the database, then you need to create a table. And that is right where it takes us. So we want to name this table. We're going to call it users, and we have the amount of columns that we need. Well, if we look at our list here, we have the first name, we have the last name, and we have the email. So we need at least three columns. But we actually need four, because there's something called a primary key. Now, every table needs a primary key. A primary key is something that you use to refer to an individual record within the table. And it's just a unique identifier that you can use to quickly find a record. And some developers think that not all tables need a primary key. But at some point in time, you will want a primary key. So it's just easier to add a primary key to your table whenever you create it. So, the primary key is an identifier, and we typically just use the column name of id, that is the identifier. The type of data that's in the id is going to be an integer. It's going to automatically increment every time we add a new user to our table. So that's going to be an INT. We don't need to set the length or the values, but we do need to scroll over. Because we want to check this A_I, that stands for auto increment. And if we click on this, then it's going to pop up this Add Index. So when you have a database table, you have what's called an index. An index allows the database server to find something quicker. So because we are setting up our primary key, our identifier is going to create an index on our identifier. So that we can quickly find a user with a given identifier. And we're just going to click on Go, we'll just take the defaults because that's really all that we need. And then we'll go on from there. So the next thing is going to be our first name. So let's have first_name, that is the convention with MySQL databases. If you have two names, or rather two words within the name, use an underscore. So first name, but in this case, it's not an integer, this is text data. But we don't want to use text because that is a ton of text, instead we want to use a VARCHAR. This allows us to choose the size of the data that we want to store, so that we aren't storing a ton of information. So, a first name, let's say that we want a limit of 25 characters, for our first name, and that should be fine. And if we scroll over, there is an option for Null. Now what Null means is we could have a column that could either have the value or it could be null. And that typically means that there is no value there. Now, in the case of our first name, we can say that every user needs to have a first name. In which case, we would leave Null unchecked. So we're going to do just that, and we're going to do the same thing for last name. This is going to be a VARCHAR, let's make this a length of 50, just in case. Because last names can be lengthier than first names, especially if you have hyphenated names or anything like that. And then we're going to have an email address. Just to be safe, we're going to make this a length of 25. Now, just in case I wasn't clear before, we're using a VARCHAR instead of a TEXT. Because whenever we create a new record within this table, it's going to allocate the maximum amount available for that column. So a TEXT is a huge chunk of text. And if we choose TEXT, then it is going to allocate a huge amount for the first name and we don't need that. We just need about 25 characters, so that's going to be fine. So for every new record we create, the first name can be 25 characters. It can be less than 25 characters, but it's going to allocate 25 characters for the first name. It's going to allocate 50 characters for the last name, and 75 for the email. Not more, not less. Now, if we wanted to add any comments, this table stores users, then that will be fine. We can save, and then that has created our user's table. And here we can see our table structure. So we have this little key next to id, that's our primary key. First name, last name and email. Well, in the next lesson, we are going to create another table, one for storing our posts. And we will set up the relationship between our post's table and our user's table.