Advanced PHP SQL Command: SELECT

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

In today's lesson, we are going to learn how to retrieve data from our database using the SQL SELECT command.0005

We are going to go over the syntax of the SELECT command, and then we are going to talk about two different clauses that you can add,0011

which are additions you can add to the SELECT command.0018

The WHERE clause will allow you to restrict what rows from a table you want to retrieve.0023

And the ORDER BY clause is going to allow us to specify, when we retrieve rows from a table using the SELECT command, how we want the results ordered.0031

The SELECT command (or query, as it is often called) is used to retrieve rows from a database that match specified criteria that we choose.0044

Now, it is sometimes called a SELECT command, sometimes called a SELECT statement, sometimes called a SELECT query or just a query.0056

I will use those terms interchangeably; mostly, I will be using the term 'query.'0062

So, if I say, "We are executing a query on the database," that is going to be saying0065

that we are executing a SELECT command on the database, which is going to retrieve data from the database.0070

What the syntax of the SELECT command is: you have the SELECT keyword, and then you specify the names of the columns0077

that you want to retrieve data for, separated by commas.0086

We are saying, "Give me the values for column 1 and column 2" and then the FROM keyword--"from table name" and then you specify the table name.0091

So, this would say, "Give me all the values for column 1 and all the values for column 2 from the table named by Table Name."0101

And what happens is: the SELECT query returns its results in a tabular format ordered by how the columns are listed in the query.0113

For example, if we ran this query, we would get what is called a result set.0125

It is a table of data, and it would have two columns in it, because we specified two columns that we want to retrieve data for.0131

And then, it would have the values of each row in the table Table Name.0147

So, if we have a table called (in this case) Table Name, this query would retrieve the values0155

for the column1 name column and the column2 name column for every row in Table Name table.0162

And it would list them in a tabular format.0169

The order in which these columns are going to appear is the order in which we specify them here.0171

If we would switch it around, then these would be reversed in the result set.0174

And again, the term result set is used to refer to the table of data that is returned when you execute a SELECT query.0179

If you want to retrieve all of the columns from the table (which is something you often want to do),0188

you can use the wildcard in place of the list of column names.0192

So, we could type a query (and this is something that we talked about in the homework example for the last lesson,0196

and when inserting items, we used the database to verify that they were inserted, which we didn't go over).0201

But we said SELECT * from Items, and what that is saying is, "Give me all of the values for all of the columns for all of the rows in the Items table."0207

And what it is going to do is return a table, like this, a result set that is going to have values for all of the columns for all of the different rows in the table.0221

The thing to note is that the order of these columns in the result set is going to be ordered by how the table is defined.0232

For example, when we did an INSERT statement where we just specified values and didn't specify column names,0239

we had to specify the values in the order in which the columns were defined for that particular table.0244

That is how that SELECT * statement is going to work, as well.0249

It is going to return the data and have the columns ordered by how they were defined in the table definition.0252

If we go and log into our MySQL server as phpuser (I'm going to run the MySQL Monitor program), I'm going to go ahead0260

and have it so that we are already using the advanced_php database.0279

And then, what I am going to do is: if I run DESCRIBE Items again, it is going to show us the format of this Items table.0288

It has the columns ordered in this order here: itemID, name, price, description, imageFileExt.0295

I'm going to run a SELECT query that is going to retrieve all of the information from the table.0301

But I am going to do it by specifying each of the column names individually.0309

And as happened in the last lesson, because the descriptions are longer, it is going to run off the page.0321

But I am just going to do it to show you an example of how this works, just specifying the columns.0325

We are saying, "Select all of these different columns from the table Items."0329

Oops, it looks like I have an error in that statement.0341

And we can see here: it kind of runs off the page, but it returns all of the different columns that we are looking for.0347

You can see: one thing to note is that it also says 6 rows and set, which means that there were 6 rows that were returned by this query.0351

The same way--we could run the same exact query in a similar format by using the star.0359

We could say, "Select * from Items," and we are going to get the exact same results.0365

Now, as you saw me do in the last lesson (which I didn't really explain), you can select just certain columns from the particular table.0369

Let's say we just want the item ID, name, and price of each item in the database.0376

Well, we could say, "Give me the item ID, the name, and the price for all the rows in the Items table."0381

And you can see that it returns 6 rows, and it contains the item ID, the name, and the price.0390

And so, this is your result set; and notice that the result set columns are ordered in the way that you specified them.0394

We could alternatively have specified them in a different order--for example, price, name--and now they are switched.0402

And this becomes a little important when we get to interfacing with this database using PHP,0413

because we are going to be accessing these result sets, and so the order0419

that these columns are ordered in when results are returned is going to matter.0422

So, that is why we are talking about it now.0427

The commands that we just ran select all of the rows from a particular table.0433

Typically, when you are running a query on a table, you are going to want to be getting a subset of that particular data.0438

For example, you might want to look up a particular item in the database.0443

Maybe, for example, when we have Item.php, which is our item page on our store application,0446

when we load that page up, when we convert it over to using a MySQL database,0453

we are going to be loading up that item's information from a database.0458

So, we are going to be selecting a row from the Items table that corresponds to the particular item that we want to use.0460

And we are going to use the item ID to look it up; and the item ID is the primary key of that table0466

(which is why we have the primary key--so we can uniquely look up that row).0471

The way you do that in a SELECT query is by adding what is known as a WHERE clause.0475

And a WHERE clause contains what is called a where condition, which is a boolean expression that,0480

if it evaluates true for a particular row, will return that row from the database.0486

For example, here we have a SELECT query with a WHERE statement added.0492

So, we are saying, "Give me all of the columns from the Items table for all of the rows where this where condition is true-- where itemID equals 1001."0495

And we know that itemID is a primary key, so this should only return one result--one row from our table.0506

And so, that is going to allow us to just return information about item 1001.0515

So, if we run SELECT, and I'm just going to return name and price, so that it is easier to see, from Items;0521

what you do is, after the FROM and then the table name, you specify the WHERE keyword, and then you specify your where condition.0533

And in this case, it is a comparison operation; and what it is saying is, "where itemID equals the number 1001."0541

Now, in PHP, in our code, this would actually be a double equal sign--this is a comparison operator.0549

But in SQL, it just uses a single equal sign.0553

We are saying, "Give me the item ID, the name, and the price for all the rows in the Items table where the item ID equals 1001."0557

And when we run this query, we are going to see that we only get one row, and that is because 1001 is a primary key.0569

And so, there is only one row in the table that has that, because by definition, that is what a primary key is.0580

Now, these WHERE conditions can contain both logical and comparison operators.0585

We saw a comparison operator there, where we were saying, "The itemID column is equal to this."0588

The thing to note here, also, is that this item ID here in the WHERE condition refers to a column.0595

So, you are specifying conditions on columns in the table.0602

So, we are saying "where column itemID equals 1001."0604

Something else you could do, for example: there are other comparison operators--all the typical ones you would expect:0608

greater than, less than, less than or equal to, not equal to.0613

For example, we could say, "Give us all of the ones where the price column is greater than 15--all of the items where price is greater than $15."0616

You can also use logical operators, such as or, and, and not, to say, "Give me where itemID equals 1001 or 1002."0625

And you can combine these into savings, just as you can within PHP.0635

For example, let's say we want to get the information for itemID 1001 or itemID 1002.0641

We can use this or logical operator, and say itemID equals 1002.0651

Oops, that is the equal sign.0662

Now, we can see: we get two rows, because this statement says, "Give me the item ID, the name, and the price0665

for all rows and items where this condition is true: itemID equals 1001 or itemID equals 1002."0671

That is only true for two rows in the table, so we get two rows; and these are the values that returned.0678

Now, alternatively, we could say...let's say we did item 1007.0685

We are only going to get one row back, because itemID is never equal to 1007 in the current status of our table.0692

So, we only get one row set; that is another thing to note--if the condition doesn't match anything, you will not get any rows back.0699

But we could have just said "where itemID equals 1007," and we are going to get an empty set.0710

We are going to get a result set with 0 rows in it, because no rows in the table have an itemID equal to 1007.0714

Now, let's try another comparison operator.0722

We will say, "Give me all the items in the table..."0726

Well, first of all, let's take a look at all the items in the table, their names, their prices, and their item ID's.0728

And let's say we want to get all of the items that have a price greater than $15.0736

We are going to say, "Select the itemID, the name, and the price from Items,0740

where price (which is the name of the column) is greater than 15 (which is a literal value)."0745

When we hit Enter, it is going to show that 1002 through 1006--all of those items come back.0753

Another thing we could do is: let's also have it return image file extension.0760

These are the values for the itemID, the name, the price, and the image file extension from Items.0775

And what we are going to do is show how to combine some of these operations to have a more complex query,0781

where we are going to return all of the items, for example, that are JPEG's, that have a jpg image extension, that are greater than $15.0787

Before we do that, I just want to talk about how to specify literal values within WHERE clauses.0797

And it works just like you do when we use our INSERT statement.0806

If you are running a WHERE condition on a column that is a string data type,0810

you have to include the string that you are searching for in quotation marks, because it is a string.0818

For example, let's query it, saying, "Give me all of the columns from the Items table for all of the rows where imageFileExt equals jpg."0824

One thing to note is that, as with our INSERT statement, if you are searching for string values0834

that have the quotation marks as part of the string, you need to escape them with the backslash.0842

The other thing to note that we just saw was: for numeric literals, just as in the INSERT statement,0849

you don't enclose them in quotation marks; you simply write the numeral as is.0854

For example, let's pull the itemID, the name, the price, and the imageFileExt from Items.0859

And let's do it where imageFileExt equals jpg; and notice, we are enclosing 'jpg' in single quotation marks.0868

We are going to see that we get four results, because there are four items in our table that have jpg as the imageFileExt.0883

Well, now let's make this query even a little more restrictive.0889

And we are going to add an and condition to this where condition.0895

And we are going to say "where the imageFileExt equals jpg, and the price is greater than $15."0900

We have two conditions that each row has to meet in order to show up in our result set.0907

And if we look at the result set we just got from where imageFileExt equals jpg, there are four rows, but only three of them have a price greater than $15.0910

So, when we run this query, we should get three rows; and, in fact, we do.0918

We get the three rows where the jpg is the image file extension and the price is greater than $15.0922

We learned how to select columns from a table; we have learned how to specify particular rows that we want to pull from a table.0932

We also can specify how we want the results ordered.0938

For example, when these queries are run, they just go through the table, one row at a time, and pull each row that meets these particular criteria.0942

In this case, they are all being ordered by itemID, because they are going the way they were entered into the table.0952

So, because we entered itemID 1004 before itemID 1005, when we run this query, it shows up with item 1004 listed before item 1005.0960

Well, we could change that; let's say we wanted to order everything by the price.0972

Let's say...sometimes you go to websites, and you can sort the items in a particular department by price.0976

Well, what we could do is run a query on a database that says, "Give me all the items0984

in a particular department, and order them from highest to lowest price."0986

And then, what would happen is: you can create an SQL query that is going to order them by price, in the order that you want.0990

That is what the ORDER BY clause allows us to do.0997

For example, you specify it after the from-the-table-name part of the SELECT command.1000

You specify the keywords ORDER BY, and then you specify the column name, and again, this is the column name that you want to order things by.1011

So, if you want to order things by price, we would specify SELECT * FROM Items, ORDER BY price.1018

For example (and I don't want to pull all of the information up, because it would go off the screen),1025

we are going to pull up the item ID, name, and price from the Items table, and we are going to order it1032

(we are not going to include a WHERE clause here) by price.1038

Now, we can see that they are all ordered (as opposed to 1001, 1002, 1003) in ascending order according to price.1044

One of the other things that you can do, when you specify an ORDER BY clause: after the name of the column that you are ordering by,1052

you can also specify DESC for descending, which means to order them in descending order.1058

Now, the default is ascending, which you can also explicitly specify with ASC.1064

But let's go ahead and do this in descending order by price.1069

I'm going to do it in descending order, and now we can see that they are organized in descending order by price.1075

The other thing that you can do is order by multiple columns.1082

For example, let's say we wanted to have all of the items that have the image file extension jpg show up first,1086

and then have all of those items (after they are ordered according to image file extension) ordered by price.1093

This would say, "Give us all of the columns for all of the rows and items in the Items table;1100

order them in ascending order by imageFileExt" (because imageFileExt is a string, it is going to do them in alphabetical order);1106

"and then, after they are ordered according to image file extension, then order them by price."1112

If we run this command now, SELECT itemID, name, and we add imageFileExt to our list of columns that we are going to be returning,1118

and put ORDER BY on its own line--we are going to order by imageFileExt, and then we are going to order that by price.1134

Now, what we can see is that first, it has been sorted according to the image file extension.1147

Here, g is before j in the alphabet, so all of the gif items are listed first.1152

And then, within the gif items, they are sorted according to price.1158

And within the jpg items, they are sorted according to price (15, 34.99, 59.99, and 79, and so forth).1162

Again, the default sort order is ascending; and so, that is why gif is listed before jpg, and 15 is listed before 79.99.1168

In these more complex ORDER BY queries, where we are ordering by multiple columns, we can also specify descending order1177

for each of the different columns we are ordering by, by specifying the descending modifier after each column name.1184

Let's say we want to order them both in descending order.1192

We could specify descending twice; and you have to include it directly after the name of the column that you want to specify it by.1196

And here, if I hadn't explicitly said it, you want to separate the columns that you are ordering by, by a comma.1203

And now, when we run this query, we can see that JPEG's come first, and then they are listed in descending order of price.1209

And so, there are a lot of different combinations that you can do with this to get the data in the way that you want.1217

We have a way to select columns from rows in a table.1224

We have a way to specify what rows we want from a table.1228

And then, we have a way to specify the order.1231

We can put this all together by using SELECT queries that have both WHERE and ORDER BY clauses.1234

Let's say we want the itemID, the name, the price, and the imageFileExt from Items, and we are going to specify a WHERE clause1240

where price is greater than $15, and then we simply, after that,1251

to include an ORDER BY clause, include a space, and then ORDER BY, price, DESC.1258

We have combined both a WHERE condition and an ORDER BY clause to get this result set.1265

This is going to say, "Give me the values for itemID, name, price, and imageFileExt for all rows in the Items table1270

where their price column has a value greater than 15, and then order those results (those rows) in descending order by price."1277

Now, when we run it, we can see that we have all of our items, which is 5 items out of 6, that have a price greater than $15.1286

And they are ordered in descending order by price.1292

And so, as you can see, we can get some really complex queries, and it is an extremely powerful statement1295

that we can use to do all sorts of different things for getting whatever information we want out of our database,1301

which is a lot of power--the SELECT statement is really the biggest of all SQL statements.1308

That is where a lot of the power lies in our relational databases.1315

For the homework challenge, I just want you to practice running some SELECT queries.1320

You will log into the MySQL database using MySQL Monitor, log in as the phpuser, and then run a couple of these SELECT queries.1323

And what I have done is defined in this list...described the kind of queries I want you to write.1331

And then, what you ought to do is determine whether to add a WHERE clause, whether to add an ORDER BY clause,1339

whether to use a star, whether to specify the columns individually, and so forth.1345

And then, what I have done is: after each bullet point (representing a separate query I want you to run),1351

I have specified the number of rows that you should get back.1356

In this case, these first two should both return 6 rows, assuming you have the default setup that we did in the last lesson,1359

which has the default 6 items in our store.1365

And then, that way, you can have a check to make sure that you are writing the query right--1369

at least a partial check to make sure that you are writing the right query and that you are getting the appropriate number of results.1373

For example, for the second one, I want you to get the itemID, the name, and the price values for each row in the table.1379

And in this case, because it is for each row, we are not going to specify a WHERE condition.1387

So, this one would just be a SELECT statement that would say to select itemID, name, price from Items.1391

And I told you how to answer that one, and it is going to return 6 rows back.1397

Then, we are going to do a couple of different things.1402

You are going to get practice using the OR operator: have a WHERE clause that has an or--where itemID equals 1001 or 1004.1404

You are going to use the greater than symbol.1414

We are going to combine some of these; so you are going to get to use the AND condition of a WHERE condition.1415

You are going to say, "If a price is greater than a certain amount, and the image file extension equals a certain amount..."1420

Remember to include strings in quotations.1425

And then, you will get practice with the ORDER BY clause.1428

You are going to have to order by price, order by price in descending order...1430

And then, you are just going to get more and more complex queries, just to give you practice with writing these queries,1436

because they can get really long and complex, and they are going to be prone to errors.1441

And you are going to make errors, and that is OK.1443

And I encourage you, as much as possible, to do all of these homework examples.1445

Really get your hands dirty with it, because it is something you really just need to do to get a feel for it and to become comfortable with it.1451

And I encourage you to experiment with any queries on your own.1457

Maybe make up a query: say, "I want to get the name and file extension of all items that have an item ID greater than 1002," for example.1459

And then, maybe you will specify, "I want to order them according to name in descending order."1476

So, they will be in descending alphabetical order by name; and so forth.1481

And feel free to play around with it as much as you want; you are not going to break the database.1484

And it will give you practice writing these queries, and so forth.1487

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