Advanced PHP Using PHP from MySQL

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

In today's lesson, we are going to learn how to connect to our MySQL database from within a PHP script.0005

Specifically, we are going to briefly talk about how the PHP MySQL architecture works--how those two pieces of software interact.0011

We are going to talk about something known as the MySQLi, or MySQL Improved, extension,0021

that is provided and built into PHP, that allows us to connect to a MySQL database from PHP.0027

We are going to talk about the steps you need to complete to interact with a MySQL database from PHP,0033

which is going to include these four steps here that we are going to talk about in detail,0042

which would be connecting to the database, running your SQL statement,0046

processing any results from running the statement, and then closing your connection to the database.0050

Quickly, I just want to mention about the PHP and MySQL architecture:0057

What we have been doing so far is using our MySQL Monitor, which is a program running in our Windows Command Prompt,0063

to connect to our MySQL database (sometimes...this is not the best art...it is drawn like that),0073

which we know runs as a server on our localhost.0081

What we do is make a connection from the MySQL Monitor to the MySQL database, and then run our commands,0084

such as we practiced where we learned about INSERT commands and SELECT commands.0092

Well, we need to have a way to issue those commands to MySQL from PHP, as well.0097

So, what we have is: instead of this MySQL Monitor, we are going to have a PHP script that is going to do something to access MySQL.0102

And it has methods that are going to be used to connect to the MySQL database.0124

What happens when the user requests a web page that accesses a MySQL database:0129

you have a web client, like Firefox, which sends an HTTP request (as we know) to a web server (in this case, in XAMPP, Apache).0135

Apache will see that it is a PHP file, and it will go ahead and invoke the PHP Interpreter.0149

And the PHP Interpreter is going to understand these commands that access MySQL.0156

And behind the scenes, what it is going to be doing is going out and connecting to the MySQL database,0162

just as we did over here with the MySQL Monitor.0166

And so, the way it is set up is: we have added another piece to this web architecture that we have.0170

Instead of just a browser in Apache and PHP, we have added this MySQL back end that is directly communicated with by PHP.0175

And the way that we are going to communicate with our MySQL database in PHP is using an extension that is built into PHP by default.0187

It is called MySQLi, which stands for MySQL-improved.0195

It is an improved library for interacting with the MySQL database.0198

And it is an extension that, in XAMPP, is enabled by default.0202

And the way you would enable it (if it wasn't, just so you know) is: php.ini has configuration directives to install extensions.0207

And some of the extensions we have been using (such as mail, for example)0219

have already been enabled in our php.ini file, and we haven't specifically talked about them.0223

But just so you know, they have an extension word with = , and then you list the name, in this case, of a DLL file,0228

because it is a Windows operating system, that tells PHP, when it load the PHP configuration, to also load this extension.0237

So, this extension here, php_sql.ini.dll, contains the software that is needed to connect to a MySQL database from PHP.0246

The extension provides both an object-oriented and procedural interface.0258

What that means is that you can use just simple function calls to connect to a database.0263

For example, in our fileLIB.php, we have defined just general user-defined functions that we call without having to specify an object instance.0267

For example, we have the getItem function that we call in our code, just as such.0278

Now, if that was part of an object, we might call it like this, assuming it was a non-static method.0286

This extension provides a way to use either method; you can use just straight function calls, or you can use the object-oriented method.0297

In this class, we are going to be using the object-oriented interface.0305

And we are going to be making use of two classes that are provided by this MySQL extension to use this object-oriented interface.0308

The first one is called MySQLi; that is the name of the class, and that is going to represent a connection to the MySQL database.0315

And we are going to use that to execute queries.0322

There is also a class that we are going to be using called MySQLi_Results,0325

which represents results you get back from running, for example, a SELECT query on a database.0330

In today's lesson, we are not going to be talking about this MySQLi class.0337

We are going to talk about that in a lesson when we learn about how to run SELECT statements from PHP.0343

But today, we are going to be introduced into the MySQLi class.0348

There are four essential steps to connecting to a MySQL database from PHP0355

that all involve different function calls you have to make.0361

The first step that you do is: you need to connect to the MySQL database, which is just like we did from MySQL Monitor,0365

where we would type MySQL , and then we would provide our username and password and the database you want to select.0371

We are going to do that same thing.0379

We are going to do that using this MySQLi object, however.0380

So, the first thing you do is connect to the database.0388

Then, you run an SQL statement; you send the SQL statement to the MySQL server, which returns the results.0391

Then, you process any results from that particular statement.0397

For example, if you are inserting an item, you will be able to access the ID that was generated for a table that has an AUTO_INCREMENT column.0401

For a SELECT statement, it will return back a result set that allows you to see all the different rows that met your search criteria.0411

After you are done processing the results, then you just close the database connection.0419

You have open and close function calls that are two steps in the process.0423

And then, you run your SQL command, and then process the results.0430

Now, step 3, which is the processing of the results, is going to vary depending on whether using a SELECT query,0438

or a non-SELECT query (such as INSERT), because when you have a SELECT query, it is going to return results to you.0445

And as mentioned briefly (we are going to talk about this in our future lesson), the MySQLi result class is what is returned0451

when you execute a SELECT query that allows you to parse through all the rows that returned from a particular query.0461

And that process of doing that is a little bit different than when you have an INSERT query, for example,0470

that doesn't actually return rows; it just lets you know whether the INSERT succeeded, and possibly whether an ID was generated.0475

In this lesson, we are just going to be talking about how to do an INSERT statement,0483

and then how to process the results from that statement.0488

To connect to a MySQL database, the way you do it is: you simply create an instance of the MySQL object.0494

And you do that with its constructor.0501

Its constructor can take a number of parameters, the first four of which are the hostname of the MySQL server you are trying to connect to,0503

the username you would like to connect as, the password associated with that user account0510

(to enable you to log in), and then optionally the database name for the database you are connecting to.0515

The way the code looks is just like this: we are creating a variable, in this case, just called db to represent database connection.0521

We are creating a new MySQL object, and then in the constructor, we provide four arguments.0529

In this case, they are underlined, because these would be strings that you would insert on your own.0535

So, for host, you might provide (in our case) localhost as the string.0541

For username, you might provide phpuser, which is the account we set up.0548

You provide whatever password you set up on your computer.0553

And then, for our web store application, we can pass it the database name advanced_php.0557

When you run this constructor, or you create an instance of this MySQL object,0569

what it does is: it is going to try to connect to the MySQL server at localhost using the username and password you provided.0577

And try to use the database that you requested.0584

And assuming that that successfully works, then that MySQLi object is what you are going to use0587

to access and run queries on that MySQL database that you have just connected to.0594

One of the things that you will notice here is that there is the @ sign, the error suppression operator, before database,0600

because this will throw an error if there is a problem connecting to the database.0607

For example, maybe you provided the wrong username; you provided phpuser1, and it is not allowing you to connect.0610

This will suppress the error, and what it will allow you to do is handle it gracefully.0617

Typically, after you try to connect to the MySQL database by calling this constructor, you will test for connection errors.0621

And the way that you can do that is this MySQL object that was created in this step here.0629

You can test it; it has two properties: conn_errno and conn_err, and these are properties of this MySQL object0634

that provide status information about whether the connection was successful or not.0646

If the conn_errno is equal to 0, that means there were no problems connecting to the database.0651

conn_err is a message that is provided if there was an error; and if there is no error, it ends up being the empty string.0658

A typical test that you might perform would be: you would use the instance of the MySQLi object.0668

You would access, for example, the conn_errno property of that.0675

And if it is not equal to 0 (which implies that there is an error), then you might do some error processing code.0680

Maybe you will log an error message to a file or throw an exception.0684

The typical process with connecting to the database is two steps.0690

You create a new instance of the MySQLi object, and then you perform a connection error test0693

to make sure that your connection was successful--because, if you don't successfully connect to the database,0701

then the rest of the steps that we are going to need (such as trying to run queries and get result sets)0706

are not going to work, because you do not have a valid connection to the database.0711

After you connect to the database, the second step that you are going to be performing is running an SQL statement.0716

For example, for this particular lesson, we are going to talk about running an INSERT statement, which we learned about a few lessons ago.0724

The way you do that is: there is an object method called query that is a part of the MySQLi class.0729

And what you do is pass it a string that represents a valid SQL query.0740

And then, it goes ahead and executes that query on the server, and will return our results.0746

If you are running non-select queries (for example, an INSERT statement, like in this lesson),0752

if the query is successful (meaning the INSERT succeeded), the method will return true.0759

If you are running a SELECT query (as we will see in an upcoming lesson) and the query was successful,0765

it will return this MySQLi result object that is going to allow you to access all of the rows that were returned from the SELECT query.0770

And then, if there is an error with any of the different queries, it is going to return false.0778

One thing to note is that, when we are using MySQLi Monitor, for example, we always had to terminate our queries with a semicolon, like this.0783

Well, when we are creating a string to pass to the query method of the MySQLi object,0800

we don't actually need to include the semicolon; it handles that automatically for us.0806

For example, if we wanted to run an INSERT query--let's say we have a random table here; it could be any table name.0810

Let's say we want to insert...it has one column to which we are going to insert a value; maybe it's just the primary key, so it's just an integer, for example.0819

Well, we create a query that would say INSERT INTO, for example, People.0826

And maybe the value we would insert would be Joe Smith, for example.0832

We would create this query string, making sure, especially on these quotation marks (and actually, it should be in double quotes here)0839

that we should have this string value (if it's a string value) enclosed in single quotes.0849

Remember that string values, when part of a SQL query, have to be enclosed in quotation marks.0855

We would create this query string; and then, the way we run it is:0860

we access the instance of the MySQLi object; we run the query method on it and pass it this query string that was defined here.0865

Or, you could even explicitly, instead of using this intermediate variable, just define it directly within the parentheses.0875

It is an option that is up to you.0881

And then, we store the results of this in the variable called results.0884

Now, for an INSERT query, we are going to either get true or false; it was either successful, or it wasn't successful.0888

And as mentioned, for a SELECT query, we are going to get a MySQL Results object.0893

One thing I want to note, also, is that I am referring to an SQL command, such as a SELECT command,0899

as both a statement, a query, a command...they are all sort of synonyms for the same thing.0907

Whenever you hear me say "a SQL command" or "a SQL statement" or "a SQL query,"0912

it is referring to any of the SQL statements that we have learned about--0920

for example, the INSERT and SELECT that we have learned about.0924

So, even though SELECT is technically what a query is--it is querying the database for information--I will also use the term 'query' to refer to INSERT.0927

So, I might say we executed an INSERT query on the database.0937

I just wanted to mention that, so you understand the nomenclature, as far as when I am referring to these different things.0940

And that is just commonplace in the database world, as well.0948

You say, "I executed this query," "I executed this statement," and so forth.0952

Command is probably the least-used of all of them.0955

After we have opened a connection to the database, we have generated a query string and run the query on the database,0960

and we have received our results back, we want to process the results.0966

As mentioned, for non-SELECT queries like INSERT, no result set is returned, meaning no rows are returned from the database.0971

Instead, we just get true or false.0977

How do we process the results of an INSERT statement that was successful?0980

Well, one of the things that the MySQLi object provides is: it has a property called affected_rows0988

that gets updated any time that you run a non-SELECT query.0993

And what it does is tells you the number of rows that were affected by the last SQL query that you ran.0998

For example, let's say that we just ran that INSERT query that we saw in the last slide.1006

Well, we could, immediately afterwards, access the instance of our MySQLi object, access the affected_rows property value, and test if it is equal to 0.1011

Now, because we expect our INSERT statement to insert one line, we would expect that the number of rows affected would not be 0.1022

For an INSERT statement, the number of affected rows is how many rows were inserted.1032

So, we could test that, if it is equal to 0, then you could do some error processing code that would say there was an error.1036

Maybe you will exit your program.1042

Now, note that we also could have just tested Results to see if that returned false, to see if there is a failure with the query.1044

But maybe...it is not something we are going to do in this course, but one thing you might do is1053

have multiple queries, multiple INSERT statements, that you would be submitting.1057

And it might affect multiple rows; maybe one of them doesn't work, but the other one does.1061

And then, there are just other times when you want to know the number of rows that were added to the table.1065

And so, we are going to be using this just to give you experience with using the affected_rows property, because it is something you will see in code.1072

And when we learn about how to update rows in a database and delete rows in a database, we are going to be using that affected_rows property, as well.1080

One other thing specific to INSERT statements is: if you are inserting a row into a table with an AUTO_INCREMENT column1088

(for example, in our Items table, we have the itemID as an AUTO_INCREMENT column,1095

so every time we insert a row into the database, it increments the last itemID generated and generates that for that row),1098

well, what we can do is: if we have run an INSERT statement with the table with the AUTO_INCREMENT column,1107

we can access that INSERT ID that was generated.1112

For example, when we insert an item to the database, we are going to want to know what itemID it was given.1115

The way you can do that is: there is another property that is part of the MySQLi object, and it is called insert_id.1122

And that can be used to access whatever ID was automatically generated for the last INSERT statement that was executed.1129

So, after we run our INSERT statement, and we have checked and verified that at least one row was inserted,1137

then immediately afterwards, we can go and access the MySQLi object instance,1145

and access the insert_id property (and maybe we store it in a variable called rowID).1152

Now, rowID is going to hold the ID that was generated for the last row that we inserted into the table.1157

For INSERT statements (non-SELECT statements), processing the results is typically going to involve1163

testing whether the appropriate number of rows were inserted, or as we will learn, updated or deleted,1171

and then also, for INSERT statements, gathering the insert_id that was generated for a new row inserted into a table.1176

We have opened the database; we have run a query; we have been able to process the results to it.1186

The final step in accessing MySQL from PHP is to close the connection to the database.1192

After any queries have been run and the results processed using a MySQLi object, we are going to close the connection.1199

And the way that you do that is: there is an object method in the MySQLi class called, not surprisingly, close.1209

And it is a method that just closes that connection to the database, which is something you should always do,1217

when you have an open connection, to help save resources.1223

You don't want to have numerous open connections to a database.1226

What happens is: when you want to close the connection, you simply access the MySQL instance object.1230

You call the close method on it, and it is going to return true or false, just letting you know whether or not the connection was successfully closed.1238

A common thing you can do is to test to see if the connection was closed successfully, by testing, in this case, this variable close.1246

And then, do whatever error processing you might want to do, such as logging errors,1253

saying, "There was a problem closing a connection to the database," and so forth.1256

One important thing to note is that, to access the affected_rows and insert_id properties of this MySQLi object,1262

you have to do that before the close method is called.1271

So, if we call db, arrow, close, and then we try to find out the number of rows1273

that were affected by the last query, we are not going to be able to do that.1279

That is an important thing to notice.1282

We have talked about the four steps; let's see what they all look like in a real piece of PHP code.1286

One thing that I have already done is: I have opened up a command prompt, so that we can look at the MySQL Monitor,1294

so that we can see...what we are going to be doing in this example script is inserting an item into our Items table.1303

So, I have the MySQL Monitor up so that we can see the items in the database, and verify that the item that we tried to add was added to the database.1311

For example, if I run the SELECT query from the Items table as it is right now, before we have run any scripts,1320

and I am just pulling up the itemID, name, and price, because that is all that fits on the screen;1328

we can see that there are six default items in the database currently.1334

What we can do is: the example script that we have is called insertItem.php,1338

and what it is going to do is go through those four steps that we just went over1346

to insert an item into our Items table of our advanced_php database.1349

The first step (step 1) that we learned about was to open a connection to the database.1355

Here, we are creating a new instance of the MySQL class.1359

We pass it the hostname, the username, password, and database that we want to use.1364

And notice here that this password is in plain text, which may be typically something you wouldn't want to do in the real world.1371

But just for the purpose of this example, to not get into the details of that, we are just going to put it as plain text.1379

So, we have Educator as our password.1385

The other thing is that we notice we have the error suppression operator here.1389

So, if this throws an error, it won't get output, and we can handle it gracefully.1392

We have a test to see if a connection error has occurred; we test the conn_errno property on our MySQLi object.1397

And if it is not equal to 0, which means an error has occurred, then we are going to output an error message and exit the script.1406

In this particular case, as we learned, the error message, when an error occurs, is stored in the property conn_err.1412

So, if an error does occur, we are just going to output the message stored in that class property.1420

Assuming we open our connection to the database and everything is successful, then we are going to run our SQL statement.1427

We create an INSERT query that is saying, "insert into the Items table a new row."1433

We are passing it null, so that, as we know, that will trigger MySQL to auto-generate an ID for this new item.1440

We are giving it the name newItem; we are giving it a price $24.99; we are giving it a simple item description;1448

and then we are giving it image file extension jpg.1455

Notice, another thing to re-emphasize is that any string data that we include in SQL queries needs to be enclosed in quotation marks.1458

So, here we have enclosed the name, the description, and the image file extension, all within single quotes.1467

And so, because we are including the single quotes here, we have included the whole query string within double quotes,1475

because in PHP, as we know, that allows you to include single quotes without having to escape them.1481

And then, what we are going to do is run this INSERT query that we have generated.1486

And we do that using the query method.1489

Now, if you will notice here, we haven't stored the results of the query in this particular case.1493

Because it is an INSERT, even though that is something you can do, we are not going to be testing to see if it returned true or false.1502

We are going to be using the number of affected rows to test whether it was true or false.1509

So, in this case, we are not storing the result.1513

Now, when we run a SELECT statement, we are definitely going to be doing that, because that is how we are going to get our result set returned to us.1517

Here, we run the query, and then we are going to process (as step 3) the query results.1523

What we test is this affected_rows property of the MySQLi class that is going to have the number of affected rows from the last query that was run.1529

We are going to test to see that only one row was inserted; we have only had one INSERT statement that we did.1540

And if it is not equal to 1, then we are going to output an error message saying, "OK, there was an error inserting the item," and exit the script.1546

If that all goes well, what we are going to do (assuming that it was one row that was inserted) is:1553

we are going to output a message that says, "The item added was assigned the itemID" and then1560

we are going to access this insert_id property of our MySQLi class1565

to get that insert_id that was generated for the new row in the Items table.1570

And we are just going to output that to the user.1574

As step 4, we are going to close the database connection.1577

You notice that this echo statement actually is up here, because in order to access this insert_id class property, as mentioned,1581

it needs to be accessed before the database is closed.1589

Down here, we have our final step in accessing the MySQL database, which is calling the close method on our MySQLi object.1593

And then, we simply test to make sure whether the connection was successfully closed or not, and output an error message if it wasn't.1602

If we go to the script in our browser called insertItem.php, and we click on the script as is,1610

we are going to see that the item was added to the Items table successfully, and it was assigned the item ID 1007.1618

If we go and run our SELECT query again from MySQL Monitor, we can see that, in fact, a new item was added,1625

and it was given the ID 1007, as it had stated.1632

Now, just to show you how some of these error mechanisms work, let's say that, for example,1637

we misspelled our username; we tried to log in as phpuser1.1642

Well, what that is going to do is generate a connection error; we are going to suppress that error.1648

But we are not going to be able to access if an error occurred by testing the conn_errno property of the MySQLi class.1653

And then, we are going to output an error message.1662

If we go ahead and save this and reload the page, we are going to get the error message that was generated by MySQL,1664

which is saying, "Access was denied for php1user at localhost."1672

And that makes sense, because as mentioned, we had an incorrect username.1676

Let's say we correct the username, but then maybe we have an error in our query.1682

Let's say we misspelled values; and then, what is going to happen is:1686

the number of affected rows is not going to be equal to 1, because the query is going to fail.1691

So, we should be able to get this message: There was an error inserting the item.1694

Now, if we refresh the page, there was an error inserting the item.1699

So, even though there are four main steps that are in the process (open a connection, run the query, process the results,1703

and close the connection), there are little substeps that go along the way that are important for good application development,1710

which involves testing things along the way--testing to make sure that your connection was achieved;1717

checking to make sure that your query was run successfully; and then also, checking to make sure that the connection was closed.1723

This is a script that sums up all of those four steps for running an INSERT statement from PHP.1731

The homework challenge for today is going to be: I am going to have you mimic what we did in class today.1742

I am going to have you create a new table, called People, in the advanced_php database.1746

And there are a couple of reasons for this.1752

One is that it will allow us to run this example; two is that it will give you a little more practice using the CREATE TABLE command.1754

I want you to create a table called People that contains three columns: one called personID,1764

one called name, and age, that represent--personID is going to be a primary key,1769

which is going to be an auto-incremented primary key column; name is going to represent the full name of a person.1773

This database is just going to hold people that have names and ages.1782

And then, age is going to be an unsigned integer that holds the age of the person.1785

For the name, I want you to use a variable-length string data type, which (you should remember) uses VARCHAR data type.1790

And then, you can set some maximum limit on it, as to whatever you think would be reasonable for the full name.1801

I will leave that up to you.1807

Then, what you need to do is create a web form called insertPerson.html1809

that has two input text boxes: one where you can enter the person's name, and one where you can enter the age.1813

And then, have this HTML form submit that information.1820

You can do POST or GET--whatever you want--to insertPerson.php.1823

Now, there is a typo here; this should say insertPerson...1830

In this insertPerson.php script, what I want you to do is: the script itself is going to try and insert this Person into this People table in our database.1835

The way you are going to do that is: you are going to have it create an INSERT statement from the form data that was provided by either GET or POST.1846

And make sure it is a well-formed INSERT statement; make sure that any strings you have within your SQL statement are properly enclosed within quotation marks.1854

And just generate a valid INSERT statement.1868

Then, run the INSERT query, which is going to give you...you are going to have to use the query method of our MySQLi object.1872

The other thing to mention is that the first thing you are going to have to do is:1878

you are also going to have to create that MySQLi object to get the connection of the database.1882

So, you are going to need to provide the hostname, the username, the password, and the database (advanced_php).1887

Then, I want you to check the affected_rows property to make sure that one row is inserted into the database,1894

to test and make sure that the query works--that you didn't have any errors in your query.1900

Then, output the insert_id that was generated for that new row; and that is going to end up1905

being the personID of this new Person object that you have added to the database.1910

And so, that will just give you practice accessing the insert_id property.1915

And then, go ahead and close the connection to the database, using the close method.1921

A couple things: make sure that you test for a successful connection initially, using either conn_errno1927

to test if it is not equal to 0, or you can use conn_err to test for an empty string.1933

If that error is equal to the empty string, that means that no error was encountered.1939

And just do some proper error checking.1942

And then also, just check that close properly occurred, and output an error message if the close operation failed.1945

And then, as mentioned, make sure you properly quote any string values within your INSERT query.1952

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