Advanced PHP SELECT QUERIES from PHP

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

In the last few lessons, we have been working with learning how to run INSERT statements from within our PHP code.0005

In today's lesson, we are going to learn how to run SELECT queries from within PHP.0010

We are going to talk a little bit about SELECT queries and how they work, as far as returning results.0016

We are going to talk about something we mentioned in our lesson where we first introduced accessing MySQL from PHP,0023

which is the MySQLi_Result class, which is part of the MySQLi extension.0030

And it is a class that is used to represent results returned from a SELECT query.0036

We are going to talk about the fetch_assoc method, which is part of this Result class0042

that is going to allow us to access information from the results of the SELECT query.0047

We are going to talk about something called the stdClass objects, which is like a basic object that is built into PHP.0054

We are going to talk about an alternative to fetch_assoc, which is fetch_object, which is another way to allow us0061

to access information in the results returned from a SELECT query.0067

And then, we are going to talk briefly about freeing up result set memory, which means freeing up the memory0071

that is occupied by results that are returned from a SELECT query.0078

SELECT queries return a table of data, and the data that it returns is known as a result set.0084

And you hear me use that term; I have probably used it before.0091

When you run a SELECT query, it returns a table of data that contains rows; it is called a result set.0094

And if I start on MySQL Monitor, and I run a SELECT statement on our Items table,0103

we can see that this is a result set--this is a table of data that it returns.0114

It has, in this case, 6 different rows with 3 different columns.0118

And what we do is: we are going to be able to loop over this result set, processing each row, one at a time, extracting any information we need to know.0124

For example, we might want to output the name and price of every item returned by this SELECT query.0132

The result set that we get returned needs to be processed to extract the data, as we had just mentioned--to extract whatever data we want.0139

And then, the memory that that result set occupies should be freed.0146

For example, maybe we run a SELECT query that returns 1,000 items from our database.0151

Let's say you have this huge store; well, that occupies memory on the server, so you want to be able to free that memory up.0155

And we are going to talk a little bit about that in the last slide of the lesson.0162

SELECT queries are run in the same way that we learned how to run our INSERT queries.0167

You simply run them using the query method of our MySQLi object.0173

The difference is that, when you run a SELECT query, it is going to return (assuming that the query was successful;0178

if it is not, it returns false) a result set that is in the form of this new object that we are going to learn about, called MySQLi_Result,0183

which is an object that encapsulates the result set returned from a SELECT query.0192

It represents, as just mentioned, the result set from a query.0201

And as an object, it has properties and methods.0205

One of the properties that it has is num_rows, and what that tells you is the number of rows that this particular result set returns.0208

If we look back at this SELECT query we had run in MySQL Monitor, the result set had 6 rows.0218

So, if we ran this through PHP, this result set object would have the num_rows property be equal to 6.0223

And so, that is one of the things we can learn about: how many rows were returned from a query.0233

And then, it also has the following object methods for processing a result set.0240

There is one called fetch_assoc, which allows you to fetch a row from this result set, one row at a time.0243

And it returns that information from each row as an associative array.0252

It has an analog called fetch_object, which does the same thing, except that, instead of returning the data from a particular row0258

in the result set as an associative array, it returns it as an object.0266

And in particular, it returns it as an object of a class type known as StdClass,0271

which is a built-in class type in PHP that we are going to talk about in a little bit.0277

The way the two fetch methods work is that you use them to loop over the rows of a result set.0284

We saw that our result set for our SELECT query that we just looked at had 6 rows in it.0289

Well, what you do is continually call this fetch_assoc or fetch_object method on your MySQLi_Result object.0294

And every time you do that, it is going to return either an associative array or an object representing that particular row.0304

And it will continue to return rows, until it reaches the end of the result set--there are no more rows--in which case, it will return null.0309

The way that it works...well, first we are going to talk about the fetch_assoc method, which, again, returns an associative array;0320

and what it does is returns an associative array where the keys of the array are the names of the particular columns.0328

For example, if this was our particular result set, if we got the first row of this result set using fetch_assoc,0336

it would return an associative array of length 3.0345

It would have one key called itemID with the value set to 1001.0349

It would have a key called name, set to the value '100% Cotton T-Shirt.'0353

And it would have a key named price, set to $15.0358

And so, that is the way you would access the different information.0362

Let's say we wanted to output the item ID and name of every item in our items database.0368

Well, we could create a query that says "SELECT * FROM Items," which is going to pull all of the column data from Items.0374

And then, what we do is call this query method.0381

This is assuming, like in our last lesson, that this db variable is a valid MySQLi object,0384

which means we have already opened a connection to the database.0392

Assuming that is the case, we have verified that the connection is open.0395

We can run the query method on it, pass it this SELECT query, and then this time, we are going to be storing the results in a variable called Results.0399

And assuming that the query is successful, it is going to return Results.0407

And Results is going to be an object of the type MySQLi_Result.0413

And as mentioned, that class, MySQLi_Result, has a method fetch_assoc as part of it that allows us to fetch each row of the result set as an associative array.0423

What we can do is create a while loop to loop over the result set.0435

And what it does is: on each iteration of this loop, it sets a variable we have called row0439

equal to the associative array that is returned next by the fetch_assoc function.0447

And if you will notice, this fetch_assoc function is called on this Results variable,0454

because fetch_assoc is a method of the MySQLi_Result class.0461

And that is key to note, because a common mistake at first sometimes would be to run it on, for example, our MySQLi object,0469

and say db/fetch_assoc; so that is one important thing to note: we call it on that actual MySQLi_Result object.0477

And then, for each iteration through this loop--for each row that we get--0491

we are going to output the item ID of that particular row and the name of that particular item in that particular row.0496

And as you notice here, we are accessing it as an associative array.0504

And this is within double-quoted strings, which is why we don't have the single quotes around these.0509

And so, the row comes back as an associative array; it has itemID as one of the keys, and it has name as one of the keys; and we are going to output that.0514

Now, this loop is going to continue for each row that is returned in our result set, until there are no more results,0523

because what is going to happen: as we learned, when fetch-assoc is called, and all the rows have been returned from a result set,0528

it is going to return null; when this evaluates to null, it is going to evaluate to false, and it is going to kick out of the loop.0535

Let's take a look at a script I created called fetchAssoc.0541

And at the beginning of the script, it goes through the four steps that we learned about, accessing MySQL from PHP.0546

You open a connection to the database; you run a query; you process the results; and then you close the connection.0553

Here, in step 1, this is the same step as we had before in our last example, when we used an INSERT query.0559

We have created a new MySQLi object; we test if any errors occurred; and if not, we go ahead and continue.0567

And then, we are going to run an SQL statement.0574

In this case, we are running a SELECT statement, and it is going to ask to get the itemID and the name for all of the rows in the table Items.0577

And then, we are going to run this query using the query method, just as we had done before with our INSERT query.0586

This time, we are going to store the results of this query method (which, assuming that there is no error with the SELECT query,will be a MySQLi_Result object).0592

And then, this is where things will be different--in our processing of the query results.0604

In our last example, where we worked on running an INSERT query from PHP code, we would look at the affected_rows property and the insert_id property.0610

Well, now we are actually getting back data, in the form of this result set object.0624

We are going to do a little more processing here, so this is where step 3 differs a little bit from our INSERT query.0627

The first thing we do is check to see if the number of rows is greater than 0.0635

We access this num_rows property that we had mentioned on the slide, that is a property of that MySQLi_Result object.0641

As long as that is greater than 0 (which means that there were some rows returned in our result set),0649

then we are going to loop over the rows of the result set, using this fetch_assoc method.0654

And then, we are going to output the itemID and the name of each item in that table.0658

If no rows were returned, then we are going to output a statement that says no rows were returned for the specified query.0666

Finally, as we had in our lesson on using an INSERT statement from PHP, we complete step 4, which is to close the database connection.0674

And we output an error if there is a problem closing the database connection.0683

As we can see, we currently have 6 default items in our Items table.0688

If we go and run this fetch_assoc.php script, it is going to run that SELECT query that is going to say,0695

"Give me the itemID and name of every item in the Items table, and then output them."0703

And so, when we run it, we can see that that is exactly what it has done.0708

It has output ID 1001, which is named 100% Cotton T-Shirt, and so on for the rest of the items in that particular table.0711

Now, just to show you how the error message would work, let's say that we added a WHERE clause to this query.0723

Let's say we only want to return the item ID and name from the Items table where itemID equals 1007.0731

And we just looked at the output of that SELECT statement on the table, and we saw that there is no item 1007.0739

This is going to return 0 rows.0745

So, when we get down here, and we access the num_rows property on our result set object, it is not going to be greater than 0.0748

And so, we are going to output that no results were returned for this specific query, just to show you how that works.0755

And we refresh the page; it is going to show that no results were returned for the query.0760

Now, I just want to talk about a built-in class in PHP that is called stdClass.0769

It is kind of like a built-in function.0772

It is a class that is used to just hold basic data; it is a basic data object.0775

And it has all of its properties that are publicly available.0779

Essentially, you could create a variable, if you wanted to create an instance of the class; and we do so like this.0783

And then, you can just set properties on it, and it will automatically set them.0794

For example, we have this new object, a, that has no properties;0799

well, we could create a new property on it, simply by setting a property name equal to Joe.0803

And then, we could access that property, simply using the arrow syntax, as we normally would on an object, name.0810

And that would output the name that we have set.0820

What we are going to be using in our next example is: we are going to be using the fetch_object method of our result set class.0822

And what that is going to do is: that is going to return the information in a particular row of the result set as an object.0833

And it is going to return it as an object of the stdClass type.0839

This slide here is just to explain what a standard class is.0845

It is an empty object that you can populate with data values, and then you can access them in the same way that you would a regular data object.0848

So, fetch_object, as mentioned, is going to return the next row of the result set each time it is called.0860

And it is going to return it as a stdClass object.0866

And the key is that the stdClass object is going to have a property for each column in the result set.0870

The property names of the stdClass object that is returned are going to match the properties of our table.0876

For example, when we run a SELECT query on our Items table, if we were to run this query here, and we would get this result set back,0886

when we would call fetch_object for the first time on this result set, it would return a stdClass object0895

that would have a property called itemID whose value would be set to 1001.0900

It would have a property named name whose value would be set to 100% Cotton T-Shirt.0904

It would have a property named price that would be set to $15.0908

Here we are again: we set up a query that selects all the rows in the Items table.0915

We run the query, and we get our result set back as a MySQLi_Result object.0920

And now, the difference here is that, when we loop over the result set,0926

we are going to be calling this fetch_object method instead, and storing that in the row.0931

So now, whereas before when we would echo information about a row, we were echoing it using an array syntax,0935

because it was returning an associative array, now we return it as an object, so we access it using object syntax,0946

which involves curly braces and then the arrow operator; and then you mention the name of the property you want.0953

This itemID would refer to the column itemID that is returned.0959

name would refer to the value associated with the column name that was returned in this particular row.0964

This is going to loop over our result set, until the end of the result set is reached,0971

in which case fetch_object, like fetch_assoc, will return null.0975

These are two ways of doing the same thing; it just depends whether you prefer to use an associative array to access your information, or an object.0980

And part of the reason I mention both of them is that you will see both of these used in PHP applications out there.0989

So, this will give you experience seeing both of them.0995

The preference that we are going to be using in this course is to use the fetch_object method and keep things in an object-oriented manner.0998

We have also created a fetchObject.php script, which is exactly the same as our last script, fetchAssoc.php.1006

We create a SELECT query that says, "Get the item ID and name from every row in the Items table."1015

We test if the number of rows is greater than 0.1020

And then here, instead of calling fetch_assoc in our while condition, we set row equal to the return value from fetch_object,1023

which is going to be that stdClass object, with properties that are named according to the columns in the result set.1033

And then, here we are just outputting the item ID and name of each item, using the syntax for accessing object properties.1041

We are going to access the itemID property of this row variable,1052

which is going to be the itemID property of the stdClass variable, which was created by fetch_object.1057

Here, we are going to output the name; and so, it should output the same information that we had done before.1062

If no rows are returned, it is going to output a message, and then it simply closes the connection, as before.1067

If we go back and run this new script fetchObject, we can see that it does exactly the same thing that we had done before.1072

Just to show you how it works dynamically as the database goes, and to show you that it is actually working when we add new items,1080

I'm just going to add a new item to our database, using the admin site that we created in our last version of the web application.1088

It is going to say item 1007 was added.1105

And so now, if we refresh our page that loads all of the items from our Items table,1109

we can see that it shows up as 'item 1007 has been added,' as well.1115

The last thing I want to talk about in this lesson is a method called the free method, which is a method of the MySQLi_Result class, or result set object class.1123

And what it does, as mentioned: it frees memory associated with the result set.1133

As we mentioned at the beginning of the lesson, result sets occupy memory on the server.1138

And so, when you are done using them, you want to de-allocate that memory so that it can be used for something else,1144

so you are not consuming all of the server's resources.1150

The way you do that is: you call the free method on your MySQLi_Result object when you are done using it; and that frees the memory.1154

And you should always call this after you are done processing a result set, just for the reason of freeing your resources.1163

For example, this is the same section of code from our last slide on fetch_object,1171

except now, at the end, we have added this method call free.1177

And the method is called on a MySQLi_Result object.1182

And so again, just to make sure that you don't get confused: we wouldn't be calling it1188

on our database object, for example--our MySQLi object, which might be a common mistake at first.1195

It is called on the result set object.1200

Here, we just have a script that has been created called free.php.1203

It is the exact same thing as our fetchAssoc script, except that, in addition, it adds this method call that is going to free up the memory from the result set.1206

If we look at our fetchAssoc, we can see that after the else loop, it goes straight into step 4, which closes the database.1221

Here, after our else loop, it goes to this free method, and then closes the database.1236

It doesn't affect the output of our script, but it does affect how that memory is going to be freed on the server.1240

Just to show you how it works: when we go ahead and run free.php, it is going to do the same thing as the other scripts.1246

It is going to request all of the items from the database, and then output their item ID and item name.1251

And so, that is the process for running a SELECT query.1258

And this is an additional step that is also not part of when we ran an INSERT statement.1263

Our INSERT statements don't generate a result set, so you don't need to free memory for them.1269

But you do for SELECT queries.1273

To run SELECT queries, our steps are: we open a connection to the database by creating a new MySQLi object;1275

we run our SELECT query, using the query method; then, we process the result set that is returned1282

by using either fetch_assoc or fetch_object.1288

And we can also test for the number of rows that were returned by accessing the num_rows property of that MySQLi_Result object.1292

After we are done processing all of the result set, then we can call, as part of the result processing step...1300

You could say, "We are going to call the free method to free up those resources."1309

And then, the fourth step for this SELECT statement is: just as we did with an INSERT statement,1313

we close our connection to the database, using the close method on our db object.1317

For today's homework challenge, I would like you to create a script called peopleAssoc.php.1329

And it is going to run a SELECT query on that People table that you should have created in the last homework challenge.1336

So, if you haven't done that, go ahead and create that People table and populate it with some data.1346

What the SELECT query should do is: it just returns all of the rows from the People table.1352

And the People table has a personID column, a name column, and an age column.1361

And so, that is the information that is going to be returned in your result set.1369

So, use the num_rows property...1373

Go ahead and run that SELECT query; you are going to open a connection to the database; you are going to run the query.1375

Then, I want you to output the number of rows that were returned, assuming there is at least one row in the database,1380

so you will at least have one row to output.1385

If there are rows that have been returned from the SELECT query, then go ahead and process the result set1388

by looping over it, using the fetch_assoc method.1394

And output the name and age of each person that is a part of that result set,1396

just in the same way that we had outputted the itemID and item name of each item in our Items table.1402

Then, I want you to do the same thing in a new script called peopleObject.php, which is going to use,1407

instead of fetch_assoc, the fetch_object method, which is going to give you practice1414

using the object-oriented approach to accessing result set data.1418

Make sure that, when you are done using the result set, you go ahead and free the memory1423

used by the result sets, by calling that free method on the result set object.1429

And one thing that I did mention, that is important, is that you don't want to call that free method1433

until you are completely done processing your result set data, because once you call free, it eliminates that data from the memory,1438

and you are not going to be able to access that anymore.1446

This is the last step that will be called after you are done accessing all of your result set data, and before you close the connection to the database.1448

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