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 how to access a MySQL database from PHP.0008

Before we go into the specifics of what we change about our web application in our new version,0016

which is going to be 14.0, we are going to introduce a function called sprintf, which is a function that is built into PHP.0021

that makes you find it easier to write INSERT statements using this function.0029

We are going to talk about how the function works and talk about how to use it in INSERT statements.0036

And then, we are going to go over the changes that we have made to this web application in version 14.0.0041

And specifically, what we have done is: we have just updated a small part of our web application.0047

We have updated the admin page, addItem.php, so that when we add an item to our web store,0051

instead of adding it to items.txt, it is now going to add it to our database.0057

Our application is not going to be fully functional anymore, in that it is going to have some different parts to it.0062

It will be reading from items.txt, but will be inserting items into the database.0066

And what we are going to gradually do, over the next few web application lessons, is eliminate all of the flat-file database that we have0070

and convert it all over to the MySQL database back end.0078

And while we do that, there are going to be some overlapping parts, where some things are in files and some things are in the database.0082

The sprintf function is used to do what is known as generating formatted strings.0090

And what it does is takes what it calls a formatted string (I think a better name for it is a string template).0097

You define a template for a string, and then you call this sprintf function;0106

you pass it this string template, and then values that you want inserted into that template.0111

For example, let's just look at the example; I think that is easiest.0117

Let's say that we want to write "My name is Joe."0124

And so, we have a variable called first, and it has the value Joe.0128

Well, we can create a formatted string (or a string template) that will be used by this sprintf function.0135

And this is what the template looks like: it is a normal string, containing string data;0141

but then, it has this special modifier in here--a percent sign, followed by an s,0144

which says that when you use this string with the sprintf function,0151

it is going to replace that %s with a string variable that you also provide to the method.0157

There are other things you can do: you can include float information; you can include all different data types.0166

Typically, what you use it for is to include strings' information.0173

We are going to see that that is going to be useful in the INSERT statement, because we need to include multiple string variables within a query string.0177

For example, we call sprintf and pass it this format (I'm just going to erase this so you can see it better).0185

And I don't know why this is still here; this is a typo.0192

We are only passing it two parameters; we are passing it the format string, and then first.0195

And what this does is: sprintf goes through this format string, and it outputs a string.0201

And any regular string data that it finds, it simply adds to this string that it is generating.0208

And then, any time it comes upon, for example, a %s, the first time it reaches it,0212

it replaces that with the value provided as the first parameter to this sprintf function.0217

So, sprintf takes a variable number of arguments.0225

For example, we could change this so...let's say this wasn't here, and we had another %s in this formatted string.0229

And let's say (the way I originally intended to write this) that we call sprintf; we pass it this format string here0239

that is going to be looking for two string variables to insert.0248

And then, we are going to pass it two variables: first and last.0252

So, let's say, up here, that we were to insert another variable declaration: last name Smith.0254

So now, what we do is: when sprintf is called, it generates a string.0263

It goes through this format string, adds to the string it is generating any regular string data--any regular character data;0267

the first %s it finds, it replaces with the value of the first parameter (in this case, it's a variable called first).0273

And then, the second one it encounters (the %s), it is going to replace with what is passed in as the second parameter to the sprintf function.0281

And you can do this, and so on, and so on, for as many variables as you want to include in this particular string.0292

What it is going to do: it is going to end up generating the string, so at the end of this str for this particular example0296

is going to end up being the string "My name is Joe Smith."0302

That is how the sprintf function works.0312

Well, let's take a look now at why we are introducing this function, because it is commonly used to generate INSERT queries when working with databases.0316

As we know, when we have INSERT queries, we have a query that says INSERT INTO, a table name, and then values.0327

And then, in parentheses, we have a comma-delimited list of a number of different values that we are trying to insert into the database.0335

For example, for our Item table, we include a null value for the item ID; we include a string that is for the name.0342

We include a number for the price, and then two strings for the description and the image file extension.0352

Well, if you want to include that all into one string in PHP (let's say you used, for example, double-quoted strings,0357

and let's say you had some variables); for example, when we are going to be using our INSERT statement,0368

we are going to be building our INSERT statement by pulling information about an item from an Item object.0374

If you wanted to build up an INSERT query (let's say we had INSERT INTO Items values, and we continue this on the next line down here),0382

and then we would write null, and then for the name, we would use our curly brace notation.0395

Let's say we had an Item object called Item; we would do name, and we would add the price.0405

And this would be surrounded by single quotes, and so on.0417

And so, you would get this really ugly-looking string.0421

So, what we can do (I am going to erase this now) to simplify this is use the sprintf function.0423

For example, let's say we have a database called People, which we have learned about in one of our homework problems,0434

that has two fields that we are going to be inserting: name and age.0441

And so, let's say we have a variable called name that is set to the value 'Joe Smith.'0446

We have an age variable that is set to 40.0452

Well, what we can do is use this sprintf function to generate this INSERT statement by creating a string template,0455

a string format, that has the string data INSERT INTO People values, and then it uses these %s symbols that we learned about0465

that the sprintf function is going to be able to insert values in place of.0474

Now, because our name Joe Smith in our database is a string (it is a VARCHAR), notice that we have to include single quotes here.0482

And this should be included in double quotes, because we have single quotes.0491

So, make sure that, when you generate these INSERT statements, you have appropriately-quoted string variables.0496

Age is going to be an integer, so we do not include that.0502

One thing to note is that SQL statements are always just strings.0506

When we have an SQL INSERT statement, it is just a string of characters.0511

Now, it may contain string data; it may contain number data.0514

The way we denote string data is by putting it within quotation marks.0517

But essentially, when you create an SQL statement, it is all going to be a string.0523

That is why, here, even though we might be including an age (which is an integer), we are still going to use the %s value:0529

because what it is going to do is convert that age integer into a string to add to this built-up INSERT INTO query.0537

So now, we are going to create a variable called query.0546

We are going to call sprintf on this format that we generated up here.0550

And then, we are going to pass it the values first (oh, it looks like I didn't update the example)...0557

We are going to pass it the variables name and age.0565

And what that is going to do is: when it is generating the string, and it reaches this first %s,0570

it is going to insert the value of the variable name.0577

When it reaches the second %s, it is going to insert the string value of the variable age.0581

And the reason age goes second and name goes first is just the order that they are specified, within this sprintf function.0590

So, now that we have talked about that, that gives us a background for what we are going to see in the details of this new version, which is version 14.0.0601

We are going to talk about the changes that we made.0609

The first thing that we did was: we updated the logger class to include a logDebug static method,0612

which is going to be use to log debug messages.0618

And this is something that you most likely will find useful.0623

And I have included it because it is a commonly-used coding practice to help debug your code.0628

What we can do is include logDebug function calls throughout our code to keep track of things as they go along, to make sure things are working.0634

For example, we can have a debug statement that will say, "OK, we have successfully connected to the database";0643

"OK, we are running this query"; "OK, we have successfully closed the database," and so forth.0650

If we go and look at this new class we have, Logger, what we can see is that there is just a new method, logDebug.0657

It is a static method, and it is the same as the error and warning messages,0671

except we prepend any debug statements with this debug flag, which is just DEBUG in all-capital letters.0675

And so, this is just a method we are going to be able to use to help us in debugging our code.0681

And hopefully you will find it a useful technique.0686

Besides the Logger class (which is sort of secondary--not the most important part of this web application lesson), we have created a class called DatabaseAccess.0691

And what that is going to do is: in our last lesson, we learned how to interact with the MySQL database from PHP, as those four steps:0702

connect to the database; run a query; process the results; close the connection;0710

well, we are going to be running multiple queries, INSERTs, SELECTs, updates, and deletes throughout our web application.0716

And all of these have the common function that they will be opening a connection to the database and closing a connection to the database.0723

So, any time we have things that would be used over and over again, we want to take advantage of code reuse.0730

So, what we are going to do is create this DatabaseAccess object that is going to encapsulate any queries that we are going to be running0736

(for example, a getItems query to load all of the items from the database).0743

This DatabaseAccess class is going to encapsulate all of those interactions.0747

And what it does is: it has a constructor that is going to, when it is called, set up a MySQLi instance,0753

which is going to represent, as we learned, a connection to our database.0764

And then, in the destructor, it is going to call the close method, so that0767

every time we are done using the class, it always closes our connection to the database.0770

If we go and take a look at this new class in version 14.0 (it is called DatabaseAccess),0776

let's look at the source code for it; we can see that it has (let's blow this up a little bit) a private property that we are just calling db.0784

And what that is going to do is be a MySQLi object instance, which represents a PHP connection to our MySQL database.0801

And then, what we have is a constructor here.0810

And our constructor serves, when we create a DatabaseAccess object, to go ahead and create that connection to the database0814

and store it in this db property that we have.0821

What we are saying is, "Store in the db property of this class a new MySQLi instance."0824

And it is going to create it using these constants that we have created.0832

And we have created a new configuration file for this particular version of the web application, called dbConfig.php.0838

And it contains these four constants (let's look at the source code) that define the hostname of our database we are connecting to,0849

the username we want to connect as (phpuser), our password, and then also the name of the database that we want to use.0856

So, we have this new dbConfig file, and we simply included it within our regular config file.0864

And we just separated it out to separate commonly-related constants in a separate file.0870

If we go back to DatabaseAccess, we can see that what it does is uses these constants to create a new MySQLi object,0876

which is a connection to a MySQLi database.0887

We store it in this private class property, db.0890

And then, we perform what we learned about in the last lesson:0895

we perform some error checking on whether the connection was successfully made or not.0898

In this case, we are checking for an error message.0903

We are accessing the MySQLi object, which is the db property of this class.0907

And then, we are accessing the conn_err property of that, which is going to be an error message.0913

If it is not empty, that means that an error has occurred; so we test to see if it is not empty.0918

If it is not empty, then what we are going to do is throw an exception that says "there was an error connecting to the database."0922

And it outputs the contents of that error message.0928

If everything was OK, then we are going to make use of our logDebug function that we had just talked about,0931

just logging a note to ourselves in our log file that says, "You are successfully connected to the database."0939

Now, these logDebug statements are something that you typically use in the development stage of your application.0945

In a real application, you wouldn't want to include these.0952

I have included them here, because we are developing an application, and it is a useful application development tool, to demonstrate that.0955

But in a production environment, you wouldn't include these logDebug statements,0961

because then, for example, these are writing to a data file on your server.0965

So, every time you have file I/O, it makes things go slower.0970

That is why you wouldn't have these in a production server.0974

I just wanted to mention that.0976

We have the destructor for this class, and what that does is takes responsibility0978

for closing the connection that is open to the MySQL database that this class represents.0982

What it does is calls the close method on our MySQLi object, stored in the db property variable of this particular class.0989

And then, we get the value that was returned, whether it was true or false.0998

And if there is an error closing the connection, we are not going to throw an exception, but we are going to log a warning message,1003

using the logWarning static method of our Logger class, and just saying, "There was an error closing your connection to the database."1011

That will at least give us some information.1019

If we are having problems with our application, we can look at the log and see what is going on.1020

And then, if it is successfully closed, we are adding just another debug statement that says, "You successfully closed your connection to the database."1024

The other method that we have included in this is called insertItem, and that is for inserting an item into the database.1031

When a user goes to addItem.php in our admin site, and it enters the name, price, description, and image file,1038

and it clicks Add Item, whereas before we would be adding the item information to our items.txt file,1052

we are now going to be inserting that item information into our Items table of our advanced_php database.1059

This insertItem function is going to take an Item object.1071

And then, what it is going to do is: from that Item object, it is going to build up an INSERT query to insert that into the database.1077

The Item object will have everything except for...it is not going to have the item ID,1083

because that is automatically generated when we insert it into the table.1088

So, we are going to build up our INSERT query, using this sprintf function we just learned about.1092

We are saying (and I hope this is dark enough that you can see), "Insert into Items table the values,"1096

and then we have five parameters that we are going to be filling in with our sprintf function.1105

Notice that the name parameter, which is the second column in our table, is enclosed in quotes,1112

as well as the last two, which are the description and the image file extension, because those are strings.1119

The first one, which is just going to be a null value, is left as just a string without quotes, because it is not going to be quoted.1125

It is just the null--that is all that is provided to MySQL.1132

And then, our price, since it is a number, is not a string; it is not enclosed in quotation marks.1137

We just have %s, without single quotes around it.1142

Then, we go ahead and generate the query; we pass it this string template.1148

For the first %s, we pass it the string null, so it is just going to include n-u-l-l within this string, without quotation marks around it.1153

And then, what we are going to do is pass it the name of the item, the price, the description, and the image file extension--1162

all that were entered by the user on our particular form.1167

Then, something that I commonly do when working with database applications is: I add a debug statement1171

to the log file that notes the query that I am running.1181

I am simply the query, this INSERT query that we just created, to our log file.1183

That way, if we are having problems with a query, and we are trying to figure out why an item isn't being inserted into the database,1194

if we look at our log file, we can see the query that was generated, that was passed to MySQL to be run.1201

And therefore, maybe we can see the errors; maybe it is an error in the query.1207

For example, maybe we left out a single quote, or something like that.1210

We logged the query, used the query method of our MySQLi class to run the query (which is step 2 in our process);1214

step 3 is to process the results--we are going to get the number of affected rows and store it in the numRows variable.1222

We are going to get the insert_id for the row that was inserted, because it has an AUTO_INCREMENT column,1229

and it is going to generate a new itemID; we are going to call it insert_id.1234

And then, we are going to test that the item was properly inserted into the database.1238

And the way we are going to check that is that we are going to make sure that the number of rows was equal to 1,1244

that only one row was inserted, and then also, we are going to check that the insert_id is not equal to the value 0.1249

And one thing to note is that the insert_id property...if you insert a row into a database and there is an error doing it,1257

insert_id will get set to 0, which is why we test here for if it's 0.1266

So, if either of these error conditions occurs, we are going to throw an exception and say that there was an error inserting an item into the database.1270

Assuming those error conditions don't occur, what we are going to do is update that Item object1276

that was passed into this particular function by setting its itemID equal to the insert_id that was generated for it.1282

So now, we have this Item object that is built up and has all of its properties.1289

It has its name, its price, its description and image file extension; and now it also has its itemID set.1293

And we are going to return that Item object.1300

And what we are going to find out is that our script that calls this insertItem function is going to be able to use that to output information.1302

For example, in addItem.php, when we add an item, it outputs the ID that was generated for a particular item.1309

We mentioned the creation of dbConfig.1321

But also, we have updated addItem.php; so let's take a look at the changes that we have made there.1326

In our addItem.php before, what we did was: we would build up an item from the information submitted on the addItem form.1332

And then, we validate the information submitted--the image data, and then also the data provided (the name, the description, and so forth).1351

And then, what we did was added the item to our flat file database, using the insertItem function.1360

And what we would do is pass it this Item object that we had built up.1366

And then, we would pass it the image data, which was something that, as we learned in our lesson on uploading files, is contained in this _FILES superglobal array.1370

And then, what insertItem would do is go and look up the last ID for the last item added to the database in lastAdded.txt.1381

It would create a new row in our items.txt file that would represent this new item.1393

It would update the lastAdded file with the ID of the new item that was generated.1401

And then, it would go ahead and move the uploaded image to the appropriate spot on the server.1407

Well, in our new addItem.php, we are not going to be making use of items.txt anymore.1413

We are not going to be doing those file operations.1418

What we are going to do is: we still create this Item object; we build it up from information provided on the form.1422

We validate the information provided, just as before.1430

One thing we do this time is: because we are going to be passing this Item object1435

to our DatabaseAccess class object method insertItem that we just talked about,1440

we want to have it have all of the information populated, except for the item ID.1448

So, we are going to set the image file extension on this Item object.1451

And the way we do that is: we are going to make use of this getFileExt method that we defined in our utilLIB library.1454

And that is going to set the file extension for this particular item.1461

And then, here is the key part: we are creating a new instance of our DatabaseAccess class that we just discussed.1467

And as we saw, what that does is: when you call this constructor, it creates a MySQLi object, which creates a connection to our database.1474

Then, we simply call on this (and we are calling the variable dba, for database access) insertItem method in this particular class instance.1483

And we pass it this new Item object variable, which is this Item object that we have been building up from the form data and from the image data.1493

And then, what that is going to return is an Item object that has been updated with the insert_id.1502

So, what we can do is: now that we have this fully-updated item returned, we can access the item ID that was generated.1508

And we can set that as our new item ID, which is a variable that we used in the script previously1516

to output, for example, at the bottom of the script, the new item ID--if the item was successfully added, we would output this new itemID.1521

So, we simply set that variable, newItemID, equal to this.1533

If newItemID is equal to 0, which would indicate an error, we are going to throw an exception.1537

And if not, then what we are going to do is go ahead and move the image that the user uploaded to the appropriate spot on the web server.1544

And so, what we have done is: all of this functionality, before, was included in the insertItem function that we had in fileLIB.php.1553

If we look at our last version of our web application, and we look at the insertItem function,1562

we can see that it did a number of different things.1573

It would load the ID of the last item added; it would update the Item object with this new item.1575

It would update the image file extension of the item.1580

And these are all things that we just saw had been moved to addItem.php.1583

It would go ahead and move the uploaded image, and then it would go ahead and insert the item into the database,1586

which is our items.txt file, and then update the lastAdded.txt file with a new ID.1593

What we have done is: we have eliminated this function, and we have taken all of the functionality...1602

Part of this was not only updating the database with the items' ID, but also moving the image.1608

And we have just moved that all into the beginning of the addItem.php script.1612

What we have done in our new version of our web application is: we have gotten rid of a couple of functions within our file library.1618

For example, we no longer need the insertItem function.1627

Because we are generating the ID for the new item, not by looking up the ID in the lastAdded.txt file--1630

it is being generated by MySQL--we can get rid of this getLastItemAdded function.1640

And we can also get rid of the updateLastItemAdded function, because that is all going to be handled by the database now.1644

If we go and look at our new version of the admin page, version 14.0--and let me go ahead and log in to our MySQL server1653

using MySQL Monitor, and let's take a look at the items that are currently in the database.1666

There are 6--our 6 default items are in the database right now.1670

And if we go ahead and run this new form--let's say we call it newItem, price $14.99...1674

and we are just going to say 'description' for the description...we are going to set its image equal to a basketball.1680

And then, what we are going to do...I'm just checking to make sure that something was set up right...1695

And then, when we click on Add Item, now, instead of adding it to items.txt, it is going to add it to our database.1700

We click Add Item; it is going to say, "The item was successfully added to the store; the item ID is 1007,"1705

which makes sense, because the last ID of an item in our store was 1006.1711

And so, if we go and re-run this SELECT statement, we can see that this new item has been added to the store, in fact.1716

And it has been given the ID 1007.1722

We can see that this is how our new addItem form works.1727

Now, also, we should note, just to verify, that an image...we can see that we have selected a basketball--1731

that item-1007.jpg has been added, and it is a basketball image.1740

The other thing is: let's take a look at the log files.1744

We had talked about adding these logDebug statements to see what it actually looks like.1746

If we click on the logs directory in version 14.0, and we click on log.txt and blow this up, we can see these debug statements.1751

We can see that there is a statement that says, "Successfully connected to the database."1760

We can see a debug statement that outputs the query that we ran, which was "Insert into Items these values here."1763

And that is the query that we ran; so we can verify, if there is an error, that that was what we tried to run.1770

And then, we can see that we successfully closed the connection to the database.1774

It makes sense that these appear in this order, because you open the database first.1778

You run the query second; and then you close the database after that is all done.1781

This shows what occurred when we ran the constructor for our DatabaseAccess class.1786

And then, when the script ends, our destructor is called on that DatabaseAccess object,1791

which is going to close the connection to the database.1795

And so, we can see our debug message saying it was successfully closed.1797

You can see, these debug statements can be useful; and you can add them wherever you need to, throughout your code,1801

to provide whatever information you want to help you try and figure out what is going on with your particular application.1805

And then, this last bullet point was just about removing the functions from fileLIB.php that were related to inserting an item to the database.1814

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