Advanced PHP Web Application Development

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 development of our web application,0005

incorporating what we learned in our last lesson on running SELECT queries from within a PHP script.0008

The new version of the web application that we are going to be working with today is version 15.0.0015

And what we are going to be doing is making use of SELECT queries within our DatabaseAccess class,0020

which is a class we created in our last version of the web application that encapsulates all of our access to a MySQL database.0027

We are going to add functionality to that class, so that we are going to be able to run SELECT statements to load items from the database.0034

In our last web application, we learned how to insert items into the database from our admin page.0041

And now, we are going to run some SELECT queries to be able to load items from the database.0046

In our DatabaseAccess class, we have added three public methods.0052

One is called getItem: you pass it an item ID, and it is going to load an item from the MySQL database.0057

It is the same functionality as getItem that we had in fileLIB.php, except that it is loading from a database, rather than items.txt.0064

Similarly, we have getItems, which is going to load an array of all of the items in the database.0072

It is going to run a SELECT query on our Items table, and then create an Item object for each row in that table.0077

Again, that models the behavior that we have had in our getItems function in fileLIB.php.0084

And then, we are also creating an itemExists function, which tests whether an item exists or not in the particular database.0090

You pass it an item ID, and what it is going to do is run a SELECT query.0098

If the query returns a result based on the itemID, then we are going to know that the item exists in our database.0102

One of the things that we are going to make use of in getItem and getItems is...we have also created, or added,0112

a private helper function to this DatabaseAccess class, called buildItemFromRow.0120

And what we are going to be doing is: we learned in our last lesson: when we run a SELECT query,0125

we can call the fetch_object method on the result set object that is returned from running the query.0131

And that is going to take the current row and put it into an object form.0137

It makes it an object of (if you remember) stdClass, which is a basic object in PHP.0144

What we are going to do is: when we fetch a row from our Items database,0152

we are going to be passing that row object to this buildItemFromRow function.0157

And what it is going to do is just build up our Item object.0162

We have an Item object that we have been using all throughout the web application, that has all the data of an item:0165

its ID, its name, its description, and so on.0171

And this is just going to be a function that is going to transfer from a row in the database to an actual Item object.0174

Let's take a look at what our new functions look like.0183

If we look at our DatabaseAccess class in version 15.0, we can see that we have added three new methods: getItem, getItems, and itemExists.0189

I'll blow this up a little more.0198

If we look at our getItem method, we can see that what it does is takes an item ID, and then uses that itemID to create a SELECT query.0200

And we are saying, "Select all of the columns from the Items table where the itemID equals the item ID that we have passed in."0214

And then, we have a debug message that is just going to log our query, to help us debug problems with it.0221

And then, we go ahead and run the SELECT query.0227

And as we learned in our last lesson, this is going to return an object, assuming that there are rows that will be returned, of the MySQLi_Result type.0230

It is going to be a result set object, which is going to get stored in this Result variable.0239

We know that that MySQLi result set object has a property called num_rows, which says the number of rows that were returned as a part of that query.0244

We are going to check to see if the number of rows is, in this case, not equal to 1.0254

If it is not equal to 1, that means that either it was 0, or there was an error--0260

meaning that we have multiple items with that ID in our database, which shouldn't happen, because it is a primary key.0265

If it is not equal to 1, it is going to be 0; we are going to return false.0271

This function is either going to return a built-up item, or it is going to return false if that item doesn't exist in the database.0275

If it does exist, meaning that the number of rows was equal to 1, then we are going to call0281

that buildItemFromRow function that we just talked about in the last slide.0286

And what we are doing is passing it the output of Result (fetch_object).0292

There is only one row in our result set; we are calling the fetch_object method on that result set object,0297

which is going to return a stdClass object, which is going to have all of the columns in our table mapped to properties in that object.0303

And then, we pass it to this buildItemFromRow class.0313

And we won't be able to see that here in the documentation, because it only documents public methods.0316

But if we look at the source code for the file, we will be able to see the private method.0321

And what buildItemFromRow does is simply takes that row object, which, again, is that stdClass that maps each column to a property0327

in that class, and then we just access all of the different properties of that row object0338

and build up an Item object, using the Items constructor.0343

Let's see, that is how our getItem class works.0349

It runs this query that has a WHERE clause that will only select a particular item that is requested from the database.0357

And then, it will run that query and build up an Item object from the row that is returned.0364

The other method that we have is getItems, which is similar, except that we are going to be loading0370

all of the items from the database--all of the items in our Items table.0375

One of the places that we use that is on our addItemToDepartment form; we have a dropdown box0379

that lists all of the different items to add to a department.0383

Well, that dropdown box uses this getItems method to load all of the items from the database.0385

So, we are going to create a query called SELECT* FROM Items, which is going to say, "Give me all of the columns for all of the items in the Items table."0391

And then, we go ahead and run that query.0401

And we perform a test to see if any rows were returned.0403

Maybe we have an Items table that has no items in it so far.0407

So, if items row is not greater than 0, that means it is equal to 0; we are going to return false, just to let0410

whoever calls this method know that there are no items in the table.0418

If it is greater than 0, that means that some rows were returned.0421

What we are going to do is create this indexed array called Items; and we are going to loop over the result set object.0424

And for each row that is returned, we are going to build an Item object and add it to this Items array.0433

So here, we have the construct that we saw in our last lesson, where we have a while loop0439

that calls this fetch_object method continuously until it returns null.0444

And it returns null once it has reached the end of the result set.0449

We store that object temporarily in this variable row.0452

And then, just as we did in the getItem function, we are going to build an Item object from that row.0456

And then, we are going to store it in our Items array.0462

And then, we simply return that Items array to our user.0464

And then finally, the last function, or method, that we have added to this class is an itemExists function.0469

And that makes use of a SELECT query, as well.0476

It makes use of the same SELECT query that we used in getItem; we are saying, "Give me all the columns for an item in the Items table,0478

where the item ID equals" this item ID that was passed in.0491

If we run that query, we are not concerned with the data it returns; we don't care about the name, the price, and all that information.0495

All we want to know is if it returned a result, which means that that item does exist in the database.0501

So, we run that query, and then we just test the number of rows.0506

If the number of rows it returns equals 1, which means that that item was in the database, and it returned one row,0510

we return true; otherwise, we return false.0517

And so, that is how we test for an item in our database.0520

Now, these new functions that we have added--we make use of them in a couple of different files within our web application.0525

Because right now, we haven't set up our departments, but we had our items database stored previously in a flat file called items.txt,0531

and we had our departments information stored in a file called departments.txt,0543

we haven't talked about how to model the departments.txt in our database yet.0547

We aren't implementing any SELECT statements yet for loading departments from the database.0554

So, what we have here is: these are all the scripts that make use of loading items, that load items from the database.0560

The couple of scripts we have are department.php, which, if we look at the department page on our web application,0566

calls getItem when it builds up this table; the Department object for this particular page is going to have an array of item ID's.0579

And then, it is going to go ahead and call this getItem method to load each of these items up.0587

That is how it will be used in Department.php.0591

Item.php--obviously, we are going to be using the getItem method to pull0594

all the information from the database for a particular item, so that we can output it.0598

viewCart.php: when we add items to a cart, it lists them all in the shopping cart table that we have on that page.0603

That is going to make use of the getItem method to be able to load each item, to output it as a row.0612

And then, we also make use of it in output.php, in that we are going to make use of one of these functions in output.php.0619

And in this particular case, in output.php, we have an outputItemSelector, which, if we look at our admin site--0626

on our addItemToDepartment script, we can see that we have this Select box that allows us to select an item in the database.0636

Well, the way that that is built up is by calling the getItems method, that new method in our DatabaseAccess object,0643

which is going to query the database for all of the items.0653

It is going to return an array of those items, so that we can build up this Select box.0655

That is how it is used in output.php, because output.php defines the static method that creates that dropdown box.0661

In addition, on this page addItemToDept.php, if we go and look at the source code for it, we can see0672

that it is going to make use of testing whether an item exists or not.0683

So, it makes use of the itemExists method, as well.0687

Let's take a look at all of these different files that have changed, starting with Item.php.0691

Before, Item.php, when it would try to load an item to view, would call the itemExists method, which was in our fileLIB.php library.0698

And then, if it was a valid item, it would try to load the item, using our getItem method.0707

Well, we have these two replacement methods now in our DatabaseAccess class.0713

So, we are just going to replace these method calls with our new method calls.0717

In our new version of Item.php, we can see that itemExists is called on dba, which is a variable that we created0721

at the beginning of the file, that creates that new DatabaseAccess object that is going to give us access to the MySQL database.0730

And then, we use it in the same way down here, to call the getItem method to load the item from the database.0738

As you can see, up here, we have called the constructor for DatabaseAccess within a try/catch block,0744

because it can throw an exception if there is an error connecting to the database.0751

And if there is an error, we simply log it, and then exit from the script.0755

And this isn't a very graceful way to manage this exception, but it would have added a lot to the code to build a more robust error processing system.0760

So, in order to keep from obscuring what we are trying to learn from the code (which is how to use this database object),0773

we are just going to have it exit; but in the production environment, just so you know,0778

you would want to do something more graceful than simply exiting from the script.0782

But that is what we do with all of these different files that are making use of this DatabaseAccess object.0785

If we look at Department.php, when it loads the table of all the items in a particular department, it calls the getItem method on each item in the particular department.0791

Well, in our new version, at the beginning of the file, we create our DatabaseAccess object.0804

And then, down in the HTML output section, we make use of that DatabaseAccess object0810

stored in the variable dba to call the getItem method to now load our item's information from the database, rather than the text file.0816

Similarly, in viewCart, we have a very similar change.0823

This is the old version of viewCart, and you can see that we call the getItem method, as the getItem method in fileLIB.php.0830

Well, in our new version, we create our DatabaseAccess object, and then we simply call getItem on that DatabaseAccess object to load it from the database.0839

That is how we are making use of these new getItem and itemExists functions.0852

In our addItemToDepartment script, you can see that, when we attempt to add an item to a department,0856

one of the methods that it calls is a test if an item exists or not.0864

And if it does, and it is able to be added, then it loads that item up, calling getItem.0868

Well, we are going to replace that again.0875

We set up our MySQL database; we have a DatabaseAccess constructor call.0877

Now, here we are not going to exit from the script, if it calls it as an error, because we have built in some error processing0885

into our admin scripts, if you remember, that is able to handle exceptions.0891

So, it is already within a try block.0895

Our itemExists function changes to this new dba function, and that is how we are going to test the existence of the item.0898

And we call getItem, using that new getItem method, as well, on our DatabaseAccess object.0906

The one other thing to note is our output class.0914

In our outputItemSelector, we can see now that...actually, let's go to the previous version.0923

We can see that it would load the items up, calling the getItems method in our fileLIB.0936

Well, now what it does is: it is going to create a new DatabaseAccess object to get access to that database.0942

It does so within a try/catch block.0948

In this particular case, if it has an error connecting, it is going to throw the exception,0950

which will be able to be caught in the addItemToDepartment script.0955

And then, it calls our getItems method that we created that is going to load that array of items from our database.0960

If we look back at addItem.php down in the output section, where we call that outputItemSelector method,0966

we simply have a try/catch block that is going to output a message if there is an error accessing the database.0974

Again, this isn't the most graceful method for handling an error.0982

But it simplifies our code to get at what we are trying to learn in this particular lesson.0987

The only other thing to mention about the changes here is that you will notice that this isn't in version 14.0, the last version:0994

we had the functions getItem, getItems, and itemExists in our file library.1002

Well, since we have moved them into our DatabaseAccess class, they no longer exist.1008

And so, we are slowly migrating from a file-based database system into our MySQL database system.1012

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