Advanced PHP Introduction to MySQL

Hello again, and welcome back to Educator.com's Advanced PHP with MySQL course.0000

In today's lesson, we are going to be continuing our discussion of databases by getting to learn more about the details of the MySQL database system.0005

Specifically, we are going to be talking about what MySQL is; it is a relational database management system.0013

We are going to talk about some of the properties and features of MySQL.0019

We are going to talk about a program that comes with a MySQL distribution called the MySQL Monitor,0023

which is a way of connecting to a MySQL database and issuing SQL commands on it.0028

We are going to go over the steps that it takes to connect to a MySQL server.0034

And then, we are going to show how to be able to do that, using this MySQL Monitor program we are talking about.0039

And then, we are going to learn about the GRANT command, which is a way to create users on a MySQL server.0045

And users are a means of the database restricting access to particular parts of the database.0051

We are going to learn about how to create users using the GRANT command.0057

And then, we are going to walk through the setup of a user account for our PHP scripts,0061

so that our PHP scripts will be able to access our store database.0066

And then, we are going to go over a required homework, and it is a little bit different from your usual homework.0070

It is going to be a required homework, because the steps that you will be going through--you will need to go through these steps0075

in order to set up your MySQL database, so that when we continue web application development,0082

you will be able to connect to a database that is properly set up.0089

What is MySQL? Well, it is an open-source relational database management system.0095

At the back end, it is a relational database, and what it does is (because it is a relational database management system):0102

it provides some features and an interface on top of the database in order to properly use it.0108

It is commonly used with PHP applications; that is probably one of the reasons why you are taking this course:0116

PHP and MySQL are used together very often.0122

One of the things about MySQL is that, like most relational database management systems, in operates using a client-server model.0125

The database runs as a server, and then you have clients that connect to that server and then issue commands.0134

And what MySQL does is contains multiple databases; a MySQL installation can contain multiple databases,0143

each of which can contain multiple tables; and we learned about tables in our last lesson.0155

That is the way to store data in a relational database.0160

A MySQL server can have multiple databases; for example, you might have a couple of different web projects,0165

and you might create a different database (which is just a logical structure for a bunch of tables)0169

which might contain multiple tables; so you can have multiple databases with multiple tables,0174

each related to different projects, and each of the tables containing different information.0179

One thing you will notice is that I have been referring to it, in a way, as a MySQL server.0186

Because it runs in this client-server model, you will typically hear the terms MySQL database and MySQL server used interchangeably.0191

And that is what we are going to be using in this course, also.0199

Sometimes I will refer to it as the server, sometimes as the database.0202

But essentially, when I say that, I am just referring to the MySQL relational database management system as a whole.0205

So, when you are connecting to a MySQL database, you are connecting to this database management system0212

that runs as a server, that gives you access to the relational database.0217

One of the features that MySQL provides is a user privilege system, which controls access to that MySQL server.0222

It allows you to set up permissions for different user accounts to restrict access to certain databases and to certain tables.0229

For example, you could set it up so that a particular user can only access one particular database.0237

That is something that might be common on a web hosting.0243

If you have hosted on a web host before, you might only have access to a specific database.0247

So, they might tell you that, when you connect to a MySQL server (which we are going to learn more about in this lesson),0252

you have to use a particular database set up for your account.0258

And it is a way to restrict access to the database, and then also to different operations on the database.0261

For example, you might create a user account that can only run searches, or queries, on the database--0267

whereas another user account might be able to add information to the database.0273

When MySQL is first installed (as is the case in the XAMPP installation), it contains a root user account.0280

And it contains that account with no password initially.0287

And what that allows you to do is to initially log in to the server, and then create additional user accounts.0290

And then, what you do is build up the security of the system from there.0296

What we are going to be doing is logging in as the root user in order to create our PHP user account,0300

which is an account that we are going to give privileges to,0307

so that we can use that account to access our MySQL database from our PHP scripts.0310

As mentioned, MySQL runs in a client-server model, and the database itself acts as the server.0318

And what you do is connect to it using clients.0324

A PHP script, for example, would fall under the category of a client.0327

It is something that can connect to the database and run queries on it, which is the ultimate goal of this course:0330

we are going to be learning how to use a PHP script as a client to connect and run queries on the database.0335

Well, there is also a program included with the MySQL installation by default, called the MySQL Monitor.0342

And it is just a command-line program that allows you to connect to a running MySQL server, and then issue SQL commands on it.0349

In XAMPP, the MySQL program is called mysql.exe, and it is located in the bin subdirectory of the mysql directory of the xampp root directory.0360

For example, if we were to go to our xampp directory (which for this course, we have set up in our Windows User directory),0374

and we go to xampp, and the mysql directory, and then the bin directory, you can see a bunch of different files,0383

a lot of which are executables, and you can see the mysql.exe program.0390

What that is: that is that client (and we are going to be learning how to use that) that allows to connect to a MySQL database.0395

The way you use the MySQL Monitor program is by issuing a command that looks like this.0404

You specify MySQL, which is the name of the program, mysql.exe (you don't have to include the .exe, however).0410

And then, it has a couple of parameters, or options, that you can pass to it0416

that describe how you should connect to the particular database.0422

In that command that we just saw, there were a couple of command options.0428

The first one is specified by -h, followed by hostname in single quotes.0432

What that does is specifies the hostname, or it can be an IP address, of the MySQL server that you are trying to connect to.0438

The MySQL server is running on a computer somewhere.0446

For example, if you host your website at a web host, it might be running on a server somewhere on the network of your web host.0450

In our particular example with XAMPP, we are going to be running the server on our localhost, so we will be running on the same machine.0458

So, when we try to connect to MySQL, we are going to be specifying hostname as localhost.0464

And again, it is a hostname that represents a particular computer that contains the MySQL server that you are trying to connect to.0472

The -u command option was followed up by the username, or the user account that you want to connect to.0485

So, when you issue that command, you replace that with the username of a valid user account that has been set up on that MySQL server.0493

One of the things--for example, when you get set up with a web host, if you are using a shared hosting plan,0502

they might tell you the hostname of the MySQL server (maybe it's mysql1.hostexample.com--maybe that is the hostname of the server).0507

And you would specify that with -h.0526

And they might tell you that your username to connect to (they have set you up a user account) maybe is your account number.0527

Maybe it's 78967; and what -u does is: that is a command option that allows you to specify what user you want to connect to the server as.0533

Host specifies which server you want to connect to, and then -p is an option that tells the program that,0547

when you try to connect to the server, you want it to request a password from you.0554

After you specify the -p option and you run the command, it is going to ask you to enter your password,0558

which you can do, and it will be masked, like the password forms on web pages,0563

so that anybody looking over your shoulder can't see the characters that you are typing.0570

One thing to note is: if you leave out the -h option when you run this command, by default it uses the value localhost.0574

Because we are going to be running this on our local machine from a Windows command prompt, we will be able to leave out that -h option.0583

How do we go ahead and connect to a MySQL server with XAMPP, using this MySQL Monitor?0595

Well, the first thing that we do is (actually, I'll stop it, because it has been running) open up the XAMPP control panel.0600

And underneath the section for starting and running Apache, there is one for MySQL.0607

And you can go ahead and click the Start button, and what that does is gets that MySQL server up and running on your local computer.0614

And then, what we do is: because it is a command-line program, we are going to be using the Windows command prompt,0624

which some of you may or may not have experience using.0630

We are just going to use some basic command-line commands to be able to use our MySQL program.0633

Most of the commands that we are going to be learning are commands you issue once in the program.0643

You open a Windows Command Prompt, and for those of you that don't know how to do that,0649

on Windows 7, when you click on the Start menu, you can go and type in the search box0653

(and I am doing this off to the side; you won't be able to see it) cmd.exe.0658

And what is going to happen is: Windows will show you the cmd.exe program that you can select and start.0670

And when you click on it, it is going to open up a window like this that shows a command prompt.0679

It shows you the current directory that you are in.0683

Well, in order to be able to run the MySQL Monitor program, we have to switch to the directory that that program is in,0686

so that when we run this MySQL command here, the command prompt knows where to find that program at.0693

The first thing we do is change the directory to this bin directory of the mysql directory of our XAMPP installation.0701

And we change that directory; and the way you do that is using a command called CD, which stands for Change Directory.0708

And we are going to use an absolute path, so in our Windows Command Prompt, you type CD, and then a space,0713

and then the name of the directory that we are trying to switch to.0720

And in Windows, remember that directories are separated by back slashes.0724

In our case, we go to our user directory, which is in Users/(your username) in Windows 7.0729

We are going to go to the xampp installation folder, the mysql folder, and the bin folder of that.0738

And that is going to give us access to this MySQL program.0746

And now, what we want to do is connect to the server, using this root account that we said is set up by default.0748

And as mentioned, the root user does not initially have a password, so when we are prompted for a password,0754

by issuing this command here, we just hit Enter.0759

And what it is going to do is start the program.0762

So, we can type mysql, which is a command to run the program, and then we pass it some command-line parameters.0764

We are going to go ahead here (even though we mentioned we don't need to) and specify that we want to connect0771

to the MySQL server running on the hostname specified by localhost, which is the local computer.0776

We are going to specify the username, using the -u command.0782

So, we are saying -u, and then space, and then the username we want to connect as.0786

We want to connect as root, and then we want it to prompt us for a password, so we can authenticate ourselves.0789

So, we enter -p, and when we hit Enter, it is going to prompt us to enter a password.0795

Because root doesn't have a password, we can simply hit Enter as a blank password.0801

It is going to connect, and now we are connected to the MySQL Monitor.0804

And what you can see is that it outputs a little information.0808

It says, "Welcome to MySQL Monitor," talks about the version of the MySQL server you are connected to...0811

And then, down here, it has the MySQL Command Prompt, and it is kind of like its own command prompt that allows you to run the SQL commands.0818

As far as using this MySQL Monitor, when you are at the MySQL Command Prompt0831

(which is the word mysql followed by a greater than sign), you can enter in different commands.0836

You can enter in different SQL commands.0841

One thing to note is: like statements in PHP, you must terminate your statements with a semicolon.0843

One other thing that you can do is have commands that span multiple lines.0851

One of the things that we will see is that, as we start learning more about databases,0854

we are going to see that we are going to be entering commands that are quite long.0859

So, it is often helpful to space them on different lines to make them easier to read.0861

The way you can do that is to simply hit Enter.0865

For example, if I wanted to type a fake command on multiple lines, I just hit Enter.0867

And what you can see is that it adds a little dash, greater than sign.0874

And that is a signal to you, the user, that it is still expecting you to enter some more of the command.0879

So, I might type 'rest of command,' and then to execute the command, you finish it with a semicolon and simply hit Enter.0885

Now, when I do this, we are going to get an error, because it is not a valid command.0892

But that is how you run a command, and how you have it span multiple lines.0895

And then, when you are done using the MySQL Monitor, you just type exit, and that is going to exit you out of the program.0900

And it brings you back to the command prompt.0906

As mentioned, there is a privilege system that is built into MySQL that allows you to restrict access to parts of the database.0911

And there is a GRANT SQL command that is used to grant privileges to users.0918

And it can be used to create a database user, as well as grant them privileges.0925

And the command has the format that follows this format here.0929

It has a couple of keywords, GRANT, and TO, and IDENTIFIED, and BY.0937

And those are parts of the command that you have to specify.0942

Typically, in SQL, oftentimes you will see, in an SQL command, that any special keywords are required by a command0947

(for example, GRANT) are specified in all capital letters; though it is not necessary to do that, that is often the convention.0956

With this GRANT command, we have a couple pieces of information we need to provide.0964

We need to provide the types of privileges we want to grant.0968

And we specify (we are going to learn more about these in a second) what we want to grant these privileges on.0973

For example, we might say we want to allow this user that we are creating to have only query or select privileges on table Users,0979

which means they can only run select queries (which we are going to learn more about) on a particular table called Users.0989

We are saying, "Grant privileges on these particular database objects."0995

And here is where we specify our user: we are saying, "Create a user account with the username username" (enclosed in single quotes).0999

And that is where you would substitute what username you want to create.1008

And then, you say @, and then a hostname provided in single quotes.1012

And what that does is says that that is the hostname that that user is allowed to connect from.1016

So, for example, one of the things that we are going to be doing is creating a PHP user account.1021

I'm not sure why that is not erasing.1032

And the way that we specify that would be like this.1038

And what that is saying is, "Create an account" on the server that we are connected to, that you are issuing these commands to,1045

"called PHP user," and that PHP user is going to be able to connect to this server only from the localhost computer.1051

You could specify something else, like maybe a specific IP address that says1059

that the PHP user can only connect to this server from a computer with IP address 192.68.1.2, for example.1071

We are going to be specifying localhost, because all of our scripts are going to be running on the same machine as our MySQL server.1079

And then, after you specify the username and the host that that user can connect from,1084

you can specified the IDENTIFIED BY keywords, and then include the password1088

that you want that user to log in with, within quotation marks.1093

The underlined values that are in this command here are values that you must provide to the command, and refer to some different things.1099

The privileges are the type of privileges that you want to grant to the user.1109

It might be...for example, we might grant all privileges, which gives them access to do everything they want to in the database.1114

It might be that they can just view contents of the database; they can just insert contents in the database.1120

The privilege system is an advanced topic, and it is sort of beyond the scope of what we are going to get into in this course.1127

But you can definitely look at the MySQL documentation, if you are curious about that,1133

and find out more information about the different types of privileges that you can grant.1136

The objects parameter of this SQL command specifies the database objects on which you can grant particular privileges.1141

And we will see on the next slide that you can...let's say we were going to use GRANT all1152

(and all is a word that you can use, that says, "Give all privileges to this particular user.").1158

You might grant all privileges on all of the tables in a particular database.1165

Or you might give all privileges to the user for all tables and all databases.1169

The other parts of the GRANT command that were underlined are username1179

(which is the username we want to create for the account that you are creating);1184

hostname specifies the host from which the user is allowed to connect (so it is the host they have to connect from;1189

it might be a particular IP address; it might be a hostname);1197

and then, password is just the password that you want to create for the account.1200

Now, the objects, as mentioned, specifies database objects that privileges are going to work on.1206

So, if we say GRANT all (which means grant all privileges) ON, and then the form that this objects parameter takes is shown here...1211

Basically, you provide a database name, followed by a period, followed by a table name.1227

And so, what that allows you to do is: if you specify a particular database, you can say, for example...1233

maybe we have a database called PHP, and we have a table in it called Users; this is saying,1240

"Grant all privileges on the Users table in the PHP database," and down here we would create the rest of the command:1246

TO so-and-so user, with a password.1254

Now, what you can do is use wildcard characters.1257

Instead of having PHP.Users here, we could say PHP.* (in this case, if * is to the right of the period, it means all tables).1259

So in this case, we are saying, "Grant all privileges to this user on all tables in the PHP database."1269

And it can work the other way, as well; you can do *.*, which says, "Grant all privileges to" whatever user you select "on all tables in all databases."1276

And so, that is the format that it uses: it has the database name and a period,1292

followed by the database table, or wildcard characters, if you want to specify those.1296

Now, what we are going to do is walk through running a GRANT command that is going to create a PHP user account1303

that we could use to connect to MySQL from our PHP scripts.1309

We are going to be creating a username called phpuser that is going to be able to connect to the MySQL server from the localhost.1314

And the reason it is from the localhost is because our script, our web server, is running on the same server as our MySQL server-1321

at least, in this XAMPP distribution that we are using.1328

We are going to be issuing this command here, and we are saying, "Grant all privileges."1332

And 'all' gives you full permission to do whatever you want in the database.1339

That is not necessarily a secure way of doing things: you want to be as restrictive as possible.1344

But for educational purposes, we are going to grant all privileges.1351

So, that is going to allow us to experiment with different SQL commands.1355

But that is definitely not something you would want to do in a production environment.1357

And so, we are going to grant all privileges to our PHP user that is going to connect from the localhost.1362

And we are going to give them all privileges on all databases and all tables in all the databases.1369

Again, that is something you probably wouldn't want to do--to give access to a user to all databases.1375

For example, you might just want to give them access to all the tables in the PHP database.1380

So, this is, again, just for educational purposes that we are going to grant all privileges on all tables,1386

so that we can experiment and learn about SQL.1391

And then, we are going to specify that it is going to be identified by (it is going to have the password) xxx,1395

which--you can put in whatever password that you want there.1400

So, we are going to have you, as part of the homework, run this command on your home computer.1404

And you go ahead and create this phpuser account, and then specify whatever password you would like to have for the phpuser.1409

Again, all is going to grant all privileges to the user, and then *.* is going to specify that it grants privileges on all tables in all the databases.1417

If we go back to our command prompt (and we logged out of the MySQL Monitor program), and we are going to log back in,1429

this time I am not going to specify the -h localhost parameter, because as mentioned, by default it will default to that.1437

I'm just going to type -u and specify that we want to connect to the root user, and we want to be prompted for a password.1445

Just hit Enter when it asks for a password, because again, we don't have a password for the root account currently.1453

And then, what we are going to do is run this GRANT command.1459

We are going to run GRANT; we are going to type GRANT all, so grant all privileges.1461

And I'm going to do it over several lines; as mentioned, just by hitting Enter, it allows you to continue a command on a separate line.1466

We are saying, "Grant all privileges on all tables on all databases to the user we are creating (phpuser)."1473

And we are saying that that phpuser will be connecting from the localhost.1483

And then, we are going to say it is going to be identified by (let's spell it right)...in this case, I am just going to pick the password Educator.1489

And then, finish the command with a semicolon.1500

What we are saying is, "Grant all privileges on all tables in all databases to a new user called phpuser1503

that will be connecting from the localhost that will be identified by (that will have the password) Educator."1510

And so, when we hit Enter, it is going to give you a result that says "query OK," which means that the query went through--1515

or it has called the query, that the command went through, and that it ran OK.1521

And so now, what I want to do is exit out of the MySQL Monitor program1525

and then just verify that the account works by trying to log back in as this new user.1530

What we are going to do is specify MySQL; this time, I'll specify the host name, because we did create it, so we are connecting from the localhost.1535

Well, this localhost here actually refers to the MySQL server, so you can just ignore that.1548

And then, we are going to specify that we are going to connect as phpuser, which is the name of the new user account we created.1554

We are going to say -p, which says we want to be prompted by a password.1560

We hit Enter; now, we are going to be prompted by a password, and we have a password, which in this case was Educator.1566

So, when I type that, now we are logged into the MySQL server, as before.1571

And we are logged in as phpuser, which has all privileges on all tables in all databases.1575

And so, now we know that that account is set up and worked; and that is what we are going to be using from our PHP script.1581

The required homework for this is to go through and set up what we just did,1590

which is to create that phpuser account, because for the demo web store application that we are creating,1595

in order to be able to run future versions of that, you are going to have to have the database set up.1602

And so, we are going to have you set it up by issuing this GRANT command.1606

The steps, again, are just: start the MySQL database from the XAMPP control panel;1610

you open a Windows Command Prompt, and you switch to the MySQL bin directory, using the CD command, using an absolute path.1616

You go ahead and connect to the MySQL database, using the MySQL Monitor program as the root user.1626

And then, just issue a GRANT command that is going to create this phpuser account1632

that can connect from the localhost, has a password that you choose, and should have all privileges on all tables in all databases.1635

Then, type exit to quit the MySQL Monitor.1645

And then, verify that you have successfully created the account.1650

1) You should see a 'query OK' symbol, assuming the syntax and the command was written in correctly.1652

And it may give you an error, for example, if you leave out a single quote, or there is an error anywhere in your syntax.1658

And it will let you know; so if you get query OK, that lets you know the query went through all right.1663

And then, just to double-check to make sure that everything worked, log out and then log back in1668

as this new phpuser that you created, using the password that you created.1674

And if all goes well, you will be presented with a MySQL prompt that will allow you to begin issuing commands to the database.1679

That ends today's lesson; thank you for watching Educator.com--I look forward to seeing you next time.1687