• 20 hours
  • Medium

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 11/25/19

Read records

Log in or subscribe for free to enjoy all this course has to offer!

In the last chapter we created some records.  In this chapter we are going to read records!

When asking querying a database, you always get a list back.  It can be a list with no members in it which means nothing got returned or it can be a list with 1 or many members in it.  In the rest of the CRUD  operations ( which would be the RUD of CRUD), you can read, manipulate or eliminate records, more than one record at a time.

The first step for reading database records is kind of obvious - you have to tell it which records you want. In SQL this means you'd use the SELECT  statement.

Let's look at how phpMyAdmin creates the SQL to read records.

When I click on the apartment table in the left pane, we see a screen like this:

Viewing all the apartment records
Viewing all the apartment records

The SQL that phpMyAdmin created to view all the records in the apartment table is just below the neon green box.

SELECT * FROM `apartment`

The asterisk is a wildcard to select everything. And by everything, I mean both which columns to display and which rows to display.  This command is going to show everything in the table!

Choosing a single record

To select one specific record, you'd use  SELECT  on a field that contains unique content like the Primary Key. 

Click the search tab at the top of the screen.

You'll see a screen like this:

The search page
The search page

Put in an  apt_id  that you know don't have to see what search that returns nothing looks like, then try putting in an  apt_id  that you do have.

Notice that the default operator for a column with the datatype  INT  is  = .  You can see the other operators for integers in the pulldown menu.

Check out the SQL that appears on the screen after you click the Go button.

Check out the SQL in the red rectangle!
Check out the SQL in the red rectangle!

Now let's try a search on the  apt_number.  The  apt_number  is not the PK of the table, and it isn't an  INT . 

Click on the pulldown menu the currently says  LIKE  and select the  =  operator.  Then put in the value of one of your  apt_numbers  for an apartment that you have already created, then click Go.

In the green box on the following screen you'll see SQL like this:

SELECT * FROM `apartment` WHERE `apt_number` = '2A'

Choosing multiple records with a wildcard

Now, let's go back to the  cat  table example. In case you were wondering, I have five cats in my cat table.

 LIKE  allows you to make an SQL statement like this:

SELECT * FROM `cat` WHERE `name` LIKE 'Mand%'

The % sign is a wildcard used in LIKE statements.  The % sign will match any number of character.  The SQL statement above will return the cats named Mandy and Manheim.

This SQL will return all the cats that start with the letter M:

SELECT * FROM `cat` WHERE `name` LIKE 'M%'

This SQL will return all the cats with an 'a' in their name:

SELECT * FROM `cat` WHERE `name` LIKE '%a%'

This SQL will choose all the catID that are greater than 2:

SELECT * FROM `cat` where `cat_id` > 2;

This SQL will just return the name and the breed columns of the cat table for those cats whose cat_id is greater than 2:

SELECT `name`, `breed` FROM `cat` where `cat_id` > 2;

On the search page in phpMyAdmin (in the pulldown menu under = or LIKE), there is the list of all the operators that can be used in the SELECT statement.

Search with these operators
Search with these operators

Use the SQL window to try out these operators!

Click the word Options  at the bottom of the screen. 

Here you can select which columns you want to view when the rows are returned.  You can also choose how you want the list to be ordered.  Which brings us to the next topic:

Ordering a list

Look at either of the following SQL statements - ( they are the same except for the line breaks).

SELECT `catID`, `name`, `breed` FROM `cat` WHERE `catID` > 1 ORDER BY `catID` DESC;
SELECT `cat_id`, `name`, `breed` 
FROM `cat` 
WHERE `cat_id` > 1 
ORDER BY `cat_id` DESC;

 The new SQL keyword here is ORDER BY. Use it to choose which column you want to order the list by. 

Furthermore,  you can choose if you want the ordering to be in ascending values (a, b, e, j, r or 1, 2, 4, 6) or descending values with the DESC or ASC keyword. 

The SQL above is ordering using the DESC keyword ( for descending). Ascending values are the default - so often it isn't specified but when it is, it looks like this:

SELECT `cat_id`, `name`, `breed` 
FROM `cat` 
WHERE `cat_id` > 1 
ORDER BY `cat_id` ASC;

Guess what the SQL term is to limit the returned list to a certain number of records?

It's LIMIT , as in: 

SELECT `cat_id`, `name`, `breed` 
FROM `cat` 
WHERE `cat_id` > 1 
ORDER BY `cat_id` ASC
LIMIT 2;

Play with the SQL tab to put in some SQL statement that work and that don't work.  It's important to get used to spotting errors and to reading the error messages.  The cool thing is that SQL is very readable.

And now on to the U of CRUD!

Example of certificate of achievement
Example of certificate of achievement