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 about JOINS.0008

We are going to go through two different versions of the web application in this lesson.0014

We are going to have a version 19.0 and a 19.1, which are going to do a couple of different things.0017

Before we get into those, we are going to cover the topic of inserting orders in our database,0023

because as we learned in our last lesson, and having done the required homework to set up your database to be able to handle orders,0029

we have information on orders spread over multiple tables: Orders, the Customers table, and orders_items table.0037

Because, when we create an order, we have information for that order stored in multiple tables,0047

we have to have multiple INSERT statements in order to do that.0051

For example, when we create an order, a user submits all the information about all the items in their cart, and the quantities.0057

And then, they submit customer information; and then, in our Orders table, we store a date/time stamp, and so forth.0064

Well, the first INSERT we are going to do is an INSERT into the Customers table.0072

What we are going to be doing is creating a DatabaseAccess from another method called insertOrder,0081

which is going to perform the all the INSERTs that are needed to update all of the tables for a particular order.0086

The first one it is going to do is INSERT Customers: the reason for that is that it needs to be able to generate a custID0095

for a particular customer, because then, when we go ahead and...the second INSERT we are going to do is into the Orders table0101

(and that is because our Orders table is structured: orderID, dateTimeOrdered, taxRate, and then custID);0111

so, in order to input all of this information, we need to have an INSERT0127

into the Customers table first, to figure out the ID of a particular customer.0132

Then, we are going to insert information about the order, which is going to include the date/time stamp,0137

which we are going to generate using the PHP date function.0140

Tax rate is something that is going to be set--that is going to be a static value.0144

And then, once we have done that, then we need to add all of the items associated with the order into the orders_items table.0149

So then, we are going to perform one or more INSERTs into the orders_items linking table,0155

which, again, links an orderID with an itemID, and then has columns for the quantity of that item in that order,0165

and the price of that item at the time the order was made.0175

We are going to have one INSERT statement for the Customers table.0182

We are going to have one INSERT statement for our Orders table.0185

And then, we are going to have one to many INSERTs into the orders_items table, depending on how many items.0189

And there will be an INSERT here for each item in the order.0198

So, if there were three items in the order, we would have three INSERTs into our orders_items linking table.0201

And so, that is the process that is going to be involved for ordering an item.0214

As mentioned, in version 19.0, we are going to be adding a method called insertOrder into our DatabaseAccess class.0228

And what that is going to do is replace, in checkout.php...whereas before, we were calling an emailOrder function0236

to email the order's information to the store administrator, now we are going to be storing it in the database.0244

So, we are going to replace that call to emailOrder with a call to this insertOrder function.0248

And if we take a quick look at just the function prototype for the insertOrder function0258

(actually, it's better to look at it here), we can see that, for insertOrder, we are passing it three different variables.0269

We are going to pass it a cart variable, which is our shopping cart.0277

And that is going to be the status of the user's shopping cart when they are ready to check out.0280

So, it is going to contain an array of all of the CartItem objects, which contain the quantity of each item, and then information about each item.0286

We are going to pass it a Customer object, which is something that, in checkout.php, we have already been building up0295

from the information the user submitted on the checkout.php form.0302

And then, we are also going to be passing it the tax rate, which would be...0305

we are just going to be passing it the constant sales tax rate that we have set up.0309

But that may change over time; maybe we change our configuration file to (right now it's at 10%) change the tax rate to 9%.0313

Well, that way, when we call this method, we can set the tax rate.0321

So, when we insert an order into the database, not only is it going to have the customer data;0324

it is going to have information about all the items and their prices at the current time;0328

but it is also going to have the tax rate that was used at the time of that order.0331

And so, that way, we can recalculate that total down the road, when we want to look up orders in the database.0336

And what it is going to do is: this insertOrder method is going to build up an Order object,0341

which is just like a Customer object, or our Item, or our Department object.0347

It is an object that represents the data of an order.0351

So, if we take a look at the Order class, we can see that it has a couple of properties.0355

It has an orderID property, which is the unique primary key ID of a particular order.0364

It is going to have a string that is going to represent the date and timestamp that an order was made.0370

It is going to have a float field, which is going to be the tax rate associated with the order.0375

It is going to have a custID string, which is going to be that foreign key that is going to link an order to a customer in the Customers table.0382

And then, it is also going to have an indexed array of what we are going to call OrderItem objects.0391

And what that is: there is going to be one OrderItem object created in this array property of the Order class for each item that is in the cart.0397

What an OrderItem is: it is kind of like the CartItem object that we had been using before.0410

An OrderItem represents a row in the orders_items table; so it has the orderID that this OrderItem object is referring to;0418

it has the itemID that it is referring to; so between those two, it is going to say, "This is the item, and this is the particular order that it is in."0432

And then, in our orders_items table, we also have a price and quantity associated with the item in that order.0440

So, that is going to have fields: a float field for price, and then an integer field for quantity.0444

So, that is going to represent all of those rows in our Items table.0449

That Order object is going to contain all of the information in the Orders table as fields.0454

It is going to contain the custID, which is going to be the ID of the customer that made the order.0459

And then, it is going to contain an array of all the different order items.0468

What we are going to do in our insertOrder method is build up this object.0470

After we have inserted the order information into these three different tables, we return that Order object,0475

assuming there is no error, in which case we would return false.0481

If we go and look at our insertOrder method, we can see what we had learned about on the last page:0486

that inserting an order into the database is going to involve multiple INSERT statements.0497

We can see that it takes a shopping Cart object, a Customer object, and a tax rate.0504

And the first thing it does, as we just learned, is: it is going to insert a row into the Customers database.0510

So, it is going to take this Customer object that was passed in, and it is going to insert it into the Customers database.0516

The way that it is done is: we have created another method within this DatabaseAccess class, called insertCustomer,0522

which works just like our insertItem method: it just takes that Customer object,0527

and then forms an INSERT query and puts it in the insert table.0534

So, it is the same standard procedure we have been doing for inserting, for example, an item into the Items table,0538

except now we are going to be inserting a Customer object into the Customer table.0546

And when it is successful, we return the updated Customer object with the custID that was generated by the AUTO_INCREMENT column of that particular table.0550

The first step we do is insert the customer; assuming that goes OK, we move on to the next part,0560

which is going to be our second INSERT, which is going to be an insert into the Orders table.0564

And so, what we are doing is: we start off by building up an Order object that we had just talked about.0570

And because its first property is an orderID, we set that equal to null, because we don't know the orderID until we have inserted it.0578

And you can see, here--well, let's look at the order constructor: the order constructor takes these parameters in this order.0587

It takes the orderID, a string for the date and time ordered, the tax rate, a string for the custID for the customer, and then the array of the ordered items.0598

We can see here that this second one is a call to the date function.0608

And what that is going to do is generate our timestamp for this particular order.0611

So, when this Order object is created, this is going to create the timestamp.0616

We have created a constant in this DatabaseAccess class called DATE_FORMAT.0619

And if we look at the constant summary for this particular class, we can see that this is a DATE_FORMAT string.0624

And what that does is allows us to properly format the string for MySQL to be able to interpret it when we try to insert it.0634

This signifies a 4-digit year, 2-digit month, 2-digit day, 2-digit hour, 2-digit minute, and 2-digit seconds.0641

And so, that way, when we go to insert it in the database, MySQL will be able to understand that string.0649

So then, after we have built up this Order object, we are going to create an INSERT statement for the Orders table.0656

And what we are going to do is just take information that we have included in this new Order object0662

and use the sprintf function to create that Order statement, and go ahead and insert that into the table.0669

Assuming there are no problems, we go ahead and update the Order object with the insert_id that was generated for it.0674

And so, now we have our customer in the Customers table; we have our row in the Orders table.0680

Now, all we need to do is loop over all of the different items that were in the order and create rows in the orders_items table.0686

Well, we have passed a shopping cart object to this particular method.0693

And so, what we are going to do is loop over all of the items in that particular cart,0699

and then extract the information we need to create an OrderItem object.0704

And we are going to build up this orders_items array.0708

And so, what we are extracting is: for each OrderItem, we are including the orderID that we had just generated0711

for the order row that we had just added to the Orders table.0718

For the itemID part of the OrderItem, we are going to pull the itemID from the CartItem object that is in our shopping cart.0721

Likewise, we are going to pull the quantity for that particular item out of the CartItem object.0730

And then, we are going to pull the price for the item out of the Item object that is in the CartItem object that is in the cart.0734

So, it gets a little bit complicated, with objects referring to objects, and so forth.0741

What we are doing is building up this OrderItem object that contains a reference to the Order, the orderID,0746

the item that is in the order, and then the price and quantity of that item when the order was made.0753

We add that for each item in our cart, so we are going to loop over the cart, and we build up that orders_items array.0759

Then, what we are going to do is loop over that array and run an INSERT statement into the orders_items table, representing each of those OrderItem objects.0767

So, for every item that we have in our shopping cart, there is going to be an INSERT into the orders_items table.0778

So, we loop over that orders_items array.0783

Assuming we don't have any problems, we go ahead and update our Order object with that array of ordered items.0786

And then, we return it to the user, so that they can pull information that they might need from that.0792

And so, the place that uses this is checkout.php, as mentioned.0799

If we go and look at checkout.php, in our previous version of checkout.php, when the user would submit0803

a form with the completeOrder action, it would build up a Customer object.0812

It would validate the Customer, and then it would call this emailOrder function, which would pass along the shopping cart,0816

which contains all of the information about all of the items and the prices and the quantities,0821

and then the Customer object, which contains information about the customer.0824

And that would return the date/time stamp.0828

Well, now what we are doing is: we are going to be replacing that.0830

We still build up a Customer object; we still validate the Customer, except now, we are going to be calling on our DatabaseAccess object.0833

We are going to be calling this insertOrder function that we just learned about.0841

We are going to pass it the shopping cart, with all of the information in the cart that is trying to be completed.0844

We are going to pass it the Customer information.0849

And we are also going to pass it the sales tax rate used for this order, which, in this particular case, is our constant sales tax rate.0851

As we just learned, insertOrder returns a built-up Order object, assuming it was successful.0859

And so, what we can do is: on that object, we can pull the dateTimeOrdered property out of that object,0866

which is that date/time stamp that we generated using the date function in PHP.0871

And we can set our order date and time equal to that.0877

And so, the rest of this script is going to work the same way.0880

Here, we had an order date and time that was set from emailOrder.0883

And then, what it does is goes ahead and processes the rest of the script, and outputs a thank-you message0887

that shows the current date and time, the current total, and so forth.0895

Now, one other thing to note about our Order and OrderItem classes is: like Cart and CartItem,0901

there is a way to calculate the total for a particular order, because, as we had mentioned in the last lesson,0910

when we have an order in the database, we don't include a column for total for the order, because that is something that we can calculate.0916

And so, if we already have all of that information in the database, there is no need to store it in there, because we can calculate it.0922

It wastes space; so the way we set up the method that has been set up to do that is:0927

each OrderItem object has a calcOrderItem total, just like we had calcCartItem total.0932

And it just multiplies the quantity of the item in the cart times its price to give you the price due to that quantity.0939

And then, in our Order class, it simply loops over all of the OrderItem objects that are in its orders_items array.0948

And in calcOrderTotal, it loops over those OrderItems, adds up a subtotal by calling that calcOrderItemTotal method0955

on each OrderItem object, and then simply multiplies that, using 1+the tax rate to get the total including tax.0962

It rounds it off, and then it returns that.0971

And that is the way that we are going to be able (in the future, and we are going to see that in a little bit)0973

to calculate an order total in the future, when we look up an order in the database.0978

These are the methods that we are going to use to do that.0983

If we look at version 19.0, which is our current version of the web store, and we go ahead and create a shopping cart,0987

and add a couple of items to it--maybe we'll add 2 T-shirts, and maybe we are going to add a DVD player to the cart;1005

now, when we go to check out, our new checkout.php script is going to run.1016

So, instead of emailing the order, it is going to insert it into the database.1020

If we go ahead and submit this order, and click Complete Order, it is going to insert it into the database.1023

We are going to see this timestamp that we were generated; it is going to tell us the order, like before.1041

It is going to be able to echo the customer information.1045

And now, if we go to our MySQL Monitor, we can look up the information in this Orders table, orders_items table,1048

and Customers table to verify, and see that it was actually added to our database.1056

Now, if we run SELECT * on our Orders table (which we created in the last required homework),1062

we can see that there is one order in the table, which is the order we just created.1070

It has orderID 1; this is the date and time stamp of when it was ordered.1074

The tax rate at the time was 10%, and it was associated with custID #1.1078

If we go and look up our information in our Customers table, we can see that (let me restrict that a little bit)1084

there is one row in our Customer table; it is Joe Smith, and has customer ID 1.1112

And we can see that that is what links this row in our Orders table to the row in the Customers table.1118

Now, we added two items to our particular cart, so we should have two rows in our orders_items linking table.1125

So, if we see all the rows in our orders_items table, we can see that there are two entries.1133

It says that item 1001 was associated with orderID 1; it had a quantity, 2;1147

the price of the item when that order was placed was $15.1153

And then, we can also see that associated with the order 1 was the item 1005, which was the DVD player.1157

We only added one of those, and its price was $79.99.1162

As you can see, this insertOrder method has gone ahead and updated our Orders table, our Customers table, and our orders_items table1166

to have all of that information in our database, via one method.1175

For example, what we are going to be showing in the next version is how we are going to view these orders.1181

And what we had learned in our last lesson was about JOINS.1189

So, let's say we want to pull, for our order, the date and time of the order, and the customer name.1192

Well, we can use a JOINS query to do that (that we learned about in our last lesson).1204

We are saying, "Select the orderID from the Orders table, the first name and last name from the Customers table,1215

from the Orders table with an alias of o, and we are going to have that table inner joined with the Customers table with an alias of c."1220

And the condition is where the custID of our Orders table equals the custID of our Customers table.1239

And we can see that, via this JOINS query, we can pull information about this order and find out that,1252

"OK, Joe Smith was the one that ordered orderID number 1."1257

So, that is how we are going to make use of our inner join query in our next version of the application, which we are going to talk about now.1260

In 19.1, we have already implemented the method to insert an order to a table.1271

What 19.1 does is adds an admin page, or two admin pages, to our administrative interface.1276

It is going to allow us to view all of the orders that have been added to the store (orders that have been made).1282

It is going to do that by a couple of different functions that we are going to add to our DatabaseAccess class.1289

We have a getOrder function, which is going to allow us to pull all of the information from those three tables1293

to build up an Order object, to extract all of that information.1299

We have a getOrders function that is going to allow us to pull an array of all of the orders in the database.1305

And there is also a getCustomer function that we are going to be creating to use in these methods, to pull information from the Customer table.1311

Our getOrder function, because it is pulling information from our Orders table and our Customers table,1320

as we just saw--it is going to make use of an inner join query.1326

And that is going to allow it to receive information from both Orders and Customers in a single result set.1329

That is the key method that we have added here, getOrders, so let's take a look at that.1336

Oops, I'm in the wrong version; that is why I couldn't find it.1358

So, if we look at our DatabaseAccess class in version 19.1, we can see the getOrder function.1371

And if we go and take a look at that, it is like our getItem or getDepartment function.1379

It takes an ID of a particular order; it takes an orderID to look it up.1383

And what you can see here is: we have a SELECT query that is an inner joined query.1388

It is a join query that joins the Orders table and the Customers table.1393

And what we are pulling up is the ID of the order, the date and the time it was ordered, and the tax rate.1397

We are pulling that from the Orders table.1403

We are pulling all of the information from the Customers table, and we do that with c.*.1407

And c is an alias for our Customers table; * says to pull all of the columns from the Customers table.1411

And we are saying, "From the Orders table," alias o, "joined with the Customers table," who has an alias of c...1417

and we are joining them on the condition; so we join those two tables--it is going to be a combination of all those rows;1425

but we are saying, "Only give us the rows where the customer ID of the Orders table matches the customer ID of the Customers table,1430

and"--because we don't just want the join of all of the different rows that match up; we also want it for a specific order,1437

we are saying, "also where the orderID of our Orders table equals the orderID passed in."1446

So, that is going to give us the result set that is going to have all of these different columns and allow us to build up an Order object.1452

So, we go ahead and run this SELECT query; we save the row using the fetch_object method from the result set that is orderCustRow.1461

And then, what we do: assuming that there wasn't an error in returning the rows, we should receive one row back.1472

Then, what we need to do is: we also need (to build up an Order object) to get all of the information from our orders_items table.1483

We have another SELECT query that is going to pull all of the columns from the orders_items table1490

that have an orderID equal to that which was passed into here.1493

And what we do is: we run that query and generate a result set.1498

And then, we are going to return an Order object that is built using this buildOrderFromRows.1502

It is a private helper function that takes the row returned from our original SELECT query,1509

that was a JOINS query from the Customers and Orders tables, and then also the result set1514

that is going to contain all of the rows from orders_items for a particular order.1519

And it is going to build up that object and return it.1523

And if we look at the source code, we can see that private method.1525

What it does is loops over all of the results from the orders_items query, builds up an OrderItem for each one of those, and adds it to an array.1534

And then, we simply build up an Order object containing information obtained from the original SELECT join query.1544

It is contained in this orderCustRow; and then, we simply add to that constructor this Items array1553

that we have built up of all of the different OrderItems.1560

And so, what that is going to allow us to do is: we are going to have a script that we are going to see in a minute called viewOrder.1563

It is going to call this getOrder function, and it is going to return this Order object, and we are going to be able to pull information about the order from that.1567

Now, we also have the getOrders function; and simply, what that does is runs a SELECT query on the Orders table,1576

and says, "Give me the orderID for all of the orders in the table."1583

Assuming that there is at least one order in the table, what it does is just builds up an array of Order objects1587

by running that getOrder function again for each order.1592

Let's take a look at what this looks like in our administrative website.1601

We can see that we have all the same links as before; we have added a new link called View Orders.1609

And actually, before I do this, let me add a little more data to our database; let's add another order.1614

So now, we are going to have two orders in our database.1640

When we go to our administrator website, we can click on this View Orders link.1644

And what that is going to do is call that getOrders function.1648

And we can see that what this script does is: after it calls getOrders, it is able parse all of the order information in there1652

and output this table that says, "These are the orders that are currently in our database."1659

There is one order that was placed at this time; this was its total.1663

And it calculates that total using the Order object calcOrderTotal function that we had talked about.1668

And we could see that there was another order placed here.1675

And then, the actual orderID links to a detail page about the particular order.1678

Before we look at that, we can look at viewOrders.php.1683

What it does, up here in the beginning section, in the data processing section: it loads all of the orders in the database, using the getOrders function.1689

And then, simply, it generates a table in the output section where it loops over each of the different orders,1696

and then outputs a link to the viewOrder page for that particular order, outputs the date and time that it was ordered,1701

and then outputs the total, which is calculated from the calcOrderTotal function.1709

Now, our viewOrder class--when we actually click on one of these links...for example, let's click on order 2; it provides details about that order.1714

We can see, orderID 2 was ordered on this date; this was its total; and it contained item 1006, quantity 1, at price $34.99.1722

Likewise, if we do it for order 1, where we had two items, we can see that item 1001 was contained in it twice.1732

The price, when it was ordered, was $15; and this one was $79.99.1738

And our viewOrder class gets passed an orderID of the order to view.1743

It loads it up from the database, using the getOrder function.1748

And then, it simply just outputs a table that contains all of that information that we just saw about the particular order.1751

And so, that is how the viewOrders and viewOrder scripts work.1757

So now, we are able to add orders to our database, and then, from the administrative side, go ahead and look at the different orders that are available.1761

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