‫All right guys.
‫So now we're going to set up our database.
‫And if you didn't watch the first part of the course and you don't know how to set up MySQL, just go
‫back to the database section.
‫I think the first first couple lessons and you'll get set up and also install MySQL workbench.
‫If you want to use something different you can.
‫I chose workbench because it's cross-platform and it's free and it's fairly easy to use.
‫Okay.
‫So let's start off by creating a new database or creating a schema.
‫So over here you'll see your schema is on the side.
‫We have the blog one from the last lesson we did.
‫So I'm going to just going to right click and say create schema.
‫And I'm going to call this.
‫Work Opia.
‫And we'll click apply.
‫So whenever we click apply, it's going to show us the query that's going to run.
‫And then we click apply again.
‫And you can see that that was successful.
‫So now over on the side here we have where we can close that tab.
‫And if we if we look at down here you'll see tables views stored procedures and functions.
‫So we want to create two tables one for listings and one for users.
‫And then we want to create a relationship between the two tables because we need to know which user
‫owns which listing.
‫So let's start off with the listings.
‫I'm going to right click and say Create Table.
‫And I know the UI is really small.
‫I apologize for that, but I'll try.
‫I'll explain everything I'm doing.
‫So in the name we're going to call it listings plural.
‫And we could just click apply now, but we can also add our columns at the same time.
‫So for the first column let's double click here and say ID which is going to be data type int.
‫So we can leave that.
‫We want it to be primary key.
‫So make sure you check that PK and the not null and also the I which will auto increment it okay.
‫So we have ID.
‫Next we're going to have user underscore ID.
‫And this is going to be the field that maps to an ID on the users table.
‫So that we can have that relationship that foreign key.
‫So that's going to be an int as well.
‫And not null.
‫And that's all we have to do here.
‫We'll create the relationship after.
‫So next field is going to be the title for the listing which will do varchar.
‫And we can I guess we could leave that at 45 or we'll raise it actually.
‫So that's just the max characters.
‫Let's do 255 for that and also not null.
‫And then we have the description.
‫The description can be longer.
‫So we're not going to use varchar.
‫We're going to use long text for that.
‫Which is right here.
‫Long text.
‫Okay, then we're going to have a salary.
‫And salary can be varchar 45.
‫That's fine.
‫And in your theme, just in case you forgot or missed that part, let me just open up the theme.
‫Here.
‫You have this listing data dot PHP, and this has all the fields except for the ID and the createdat.
‫And it also has dummy data.
‫So at the end of this video you can add this stuff in through workbench if you want.
‫That way you have some data to work with.
‫So salary then we have the tags.
‫So basically a comma separated list of tags which I'll change the.
‫We'll change that to 255.
‫After tags will do company.
‫So this will be the company name.
‫We can leave that at 45 I guess.
‫And then let's see we have the address.
‫Which will change that to 255.
‫And I only put the address.
‫And we're also going to put the city and state in case you want to add like some map functionality or
‫something like that.
‫So next we'll do city.
‫Keep that at Varchar or Varchar 45.
‫Then we'll do state.
‫And I realize that not every country has states.
‫I'm doing this from kind of from a US perspective.
‫If you want to add different types of fields like provinces or whatever, that's fine as well.
‫But I would suggest sticking to the same columns just to just to avoid confusion.
‫So next we have phone.
‫We'll keep that at Varchar 45.
‫Then we have email.
‫Okay, so that's just the contact phone and email for the listing or for the company.
‫Then we have requirements and requirements.
‫I'm going to allow long text for that.
‫So long text.
‫And then we'll also have benefits and we'll make that long text as well.
‫Let's see.
‫Where is it?
‫Long text.
‫All right.
‫And then after benefits, we have the last field which is created underscore at.
‫And we want that to be a timestamp.
‫So we're going to select timestamp.
‫And we're going to remove those parentheses okay.
‫And then for the default we're going to say current underscore.
‫Timestamp, and that'll automatically put the date and time where the listing was was entered, where
‫the record was entered or added.
‫All right.
‫So that should do it.
‫Now if we click apply and make sure again that you have PK and I checked for the ID click apply.
‫It's going to give us the query.
‫It's creating the table adding all the fields adding the primary key.
‫Click apply again.
‫And that was successful.
‫So now you should see under tables you should see listings.
‫Okay.
‫Now this tab here if you ever want to add come back to this.
‫Like if you close it you can click on the second icon the little wrench here.
‫And that'll open it back up.
‫Okay.
‫If you want to look at the data in the table you can click this last icon the little spreadsheet looking
‫thing.
‫Or you can right click sorry right click the table and say select rows.
‫So it basically just runs a Select query.
‫Now what we want to do is create the users table.
‫So let's go to Tables Create Table.
‫And we're going to give it a name of users.
‫And same thing.
‫We're going to add an ID that is the primary key and also autoincrement.
‫Then we're going to have the name of the user.
‫Then we're going to have the email.
‫And we'll keep those at 45.
‫That should be fine, actually.
‫The email.
‫Let's make that 255.
‫I guess the name as well, just in case there's a long name.
‫Okay.
‫And for email I'm going to select not null.
‫And then this one, this next one is going to be the password.
‫And obviously when it's entered it will be encrypted.
‫We definitely want to change this to 255.
‫Because the way that we're going to encrypt the password, it's going to make it really long.
‫So it's longer than 45 I believe.
‫So you want to keep that at 255 and don't check primary key for that.
‫Okay.
‫So email password.
‫And I'm also going to do the city and state.
‫So yeah, we'll just leave that at that state.
‫So the reason I added that is just in case you want to match that up with the listings, because the
‫city and state for the listings as well.
‫When you create an application, you should always think of features that you can add later, even if
‫you don't plan on doing it now, even if you don't plan on ever doing it, at least, at least you give
‫some thought to it and open that up for possibility.
‫All right.
‫So now we have created at and let's make that a timestamp.
‫Get rid of the parentheses.
‫And we want current timestamp.
‫For that.
‫And yeah, I think that looks good.
‫So let's click apply.
‫It's going to run the query.
‫And now we have our users table.
‫Okay, so now we want to add a relationship between the two tables.
‫And we can do that by adding by making the the user ID field a foreign key.
‫So we want to go to listings and click the little wrench icon and open this screen.
‫And down here you'll see on these tabs you'll see foreign keys.
‫So here we can give the foreign key a name.
‫So we're going to call it f k for foreign key underscore listings underscore users.
‫So basically the two the two table names.
‫And then the reference table is going to be users because we want to reference users.
‫And here the foreign key details here we want to choose the we want to choose the user ID.
‫And in the reference table the reference column is ID.
‫So we want to match those two right.
‫The user ID in the listings table to the ID and the users table.
‫And then over here on update and on delete you can choose what you want to happen.
‫For instance, if you delete a user and they have listings, do you want to keep those listings there
‫that could screw up your application?
‫So what I'm going to do is for both of these I'm going to choose Cascade, which means that the delete
‫will cascade.
‫If you delete the users then it'll delete their listings as well.
‫So that's it.
‫We'll click apply and apply again.
‫And now we have that foreign key set.
‫And we have a relationship between those tables.
‫Next thing we want to do is add some data.
‫So I'm going to go to users first.
‫And if we look at the data you can either click the spreadsheet looking icon or select rows.
‫And here's where you'll see your data.
‫Now you could write a query and insert if you want, but you can also use over here the form editor.
‫So that's what we're going to do.
‫Don't fill in the ID that's that's autoincrement.
‫Don't fill in the createdat.
‫But for the name I'm going to say John or John Doe.
‫And then for the email.
‫For the emails I'm just going to use like user one, user to user three.
‫So let's say user one at gmail.com.
‫And let's see.
‫Password I'm just going to do one through six.
‫And this is not going to be encrypted yet.
‫And that's fine.
‫We're going to obviously we do that through our application city I'll say Boston and state I'll say
‫mass.
‫And then we'll click apply.
‫All right.
‫So that will create a user.
‫And if we want to see that user this query right here select all from users.
‫We can click the lightning bolt to run it.
‫Okay.
‫Why is it not displaying?
‫That's weird.
‫Huh?
‫I'm not sure why it wasn't displaying, but here you can see the ID is one, right?
‫And then we have the stuff we filled in and the createdat was also created.
‫So let's add a couple more.
‫I'm going to go to form editor and then click this little blue arrow and create another user.
‫Let's call this one Jane Doe.
‫Email will be user to.
‫At gmail.com.
‫So password I'll just use the same password.
‫City will say.
‫San Francisco.
‫And state.
‫We'll say CA.
‫All right.
‫So let's click apply.
‫Ply again close.
‫And I'm going to create one more user I guess.
‫So we can see the users here.
‫So John Doe Jane Doe.
‫And then let's go back to the form editor I'm going to click the arrow.
‫Go to click it until it's blank.
‫And let's create I don't know we'll say Steve Smith.
‫And then email will be user three at gmail.
‫And then.
‫Password.
‫City.
‫Um, city, I'll say.
‫Chicago.
‫State IL and then apply.
‫All right.
‫So we have three users.
‫And notice the ID is we have one two and three.
‫So when we fill in a listing we can add these IDs.
‫So we'll close that up.
‫And then let's go to listings and click the little spreadsheet.
‫So we have no listings obviously.
‫And we can create.
‫Let's go to the form.
‫And for user ID I'm going to put in user one.
‫And then for the title, and I'm going to use the dummy data that we have in this listing data PHP file.
‫So software engineer.
‫Now, I'm sure you guys don't want to sit here and watch me put in, you know, 4 or 5 listings.
‫So I'm going to pause the video and do this.
‫You guys can do the same and then I'll come back.
‫Okay.
‫So I created my listings.
‫And before I go over this, just one thing I want to mention.
‫If you had issues with the form editor and scrolling, because you can see if I use my mouse wheel,
‫it doesn't do anything and there's no scroll bar, so you just have to move it a little bit.
‫So grab it right here, move it.
‫And then the scroll bar shows up.
‫So just in case any of you had that that issue.
‫All right.
‫But as far as the listings I added five.
‫And you can see the user IDs I used.
‫So one for the first 1 to 3 and then one for the fourth and two for the fifth.
‫Because a user can have more than one listing and then the title description, salary, no commas in
‫the salary tags.
‫Make sure you don't put the quotes.
‫Make sure you don't put any of the quotes from the array from the dummy data.
‫Now that I think of it, maybe I should have just wrote it, wrote the sample data out instead of putting
‫it into an array company address, city, state, phone, email requirements, benefits and created
‫at which is created on its own.
‫Now you're going to want to make sure you have a user to connect with.
‫We did this back in the database section.
‫But if you didn't, if you didn't watch that part of the course, you can go to administration over
‫here and then Users and Privileges and you can add an account.
‫So right here add account and call it what you want I'll just call it sample.
‫And you can leave the password blank if you want.
‫I mean it is local but it's good to have one.
‫It does have to be a strong password.
‫So I'm just going to.
‫Put this in.
‫All right.
‫And then for administrative roles, you can just check everything, essentially making it a super user
‫schema privileges.
‫You can click Add entry and you can choose the specific schema of work or just all schemas.
‫So I'll just choose all schemas okay.
‫And then all these options you can click select all and then apply.
‫And then that user is created.
‫And then you can try to log in.
‫Just test it out with your terminal.
‫So I'll say my SQL.
‫And dash.
‫You use the sample user dash P, it'll ask for the password.
‫And there we go.
‫So I'm in, I can say show databases.
‫There we go.
‫We have our work database in there.
‫So I can now when we get to connecting with our application, I should be able to connect, no problem
‫with that user or any of these other users that have the correct permissions.
‫All right.
‫So that's it.
‫In the next video we'll set up our application and PDO to connect to our database.