Web Development Lesson 7 - Databases

Tables

In this activity you will use a new tool to get a good look at the structure of a database.

There is nothing to set up in this exercise.

There are many databases available that all meet our needs, but we'll be using MySQL, which is one of the most popular for use with PHP.
To simplify access, we'll begin using PHPMyAdmin to administer the database. This tool also allows us to look at the contents of our database and to update information.
Navigate to phpmyadmin and login using the password stored on your computer.
You'll see a list of all the available databases on your left. Select the one belonging to your class.

You should now see a list of tables in the main pane of the site.
Click on the table called 'Marvel'.
Now you can see why it's called a table. It contains information that is presented in the form of a table, with column headings at the top and many rows of data below with each field containing data corresponding to its column.
Here you can edit, copy and delete data.
Above the table, you can see a menu of tabs. You're currently in the 'Browse' tab where you can see the data.
Below that is a yellow row showing the number of rows in the result and how long it took to process. At this stage you don't need to worry about the processing time, but that can be very important for optimising complex queries.
Below the yellow row is some text that says SELECT * FROM `marvel` . This is the SQL command that extracts the data. We'll spend more time looking at queries in a minute.

Click on the 'Structure' tab.
Here we see a different table. It shows how the data table is structured.
Each row corresponds to a 'field', which is a piece of data. Each column in the 'Browse' view is a 'field'.
Two of the fields have type 'varchar' which means text (a variable number of characters). The number in brackets is the number of characters that are allowed.
'first_appeared' is of type integer with length 4 for storing the year the character first appeared in Marvel comics.
'hero_villain' is of type 'set' which means only specific values will be accepted. In this case, the values are 'Hero' and 'Villain'.
All columns have a 'No' value for 'Null' which means they must have a value. In some cases, you might be happy not to have any information for a certain field, in which case this would be marked 'Yes'.
Below this table, you'll see a section called 'Indexes'. An index is way of sorting the information (like the letters on library books), and every table must have at least one index, called the 'Primary' index that is unique. Imagine having two rows for 'Iron Man' and updating each of them with different information. You'd never know which one was correct.
In this table, the primary index is the 'character_name' field.

Click on 'Browse' to see the data again.
Now take another look at the 'query' (just below the yellow row at the top).
When you clicked on the 'heroes' table, PHPMyAdmin ran this query for you automatically.
It says 'show me everything in the heroes table'. Let's take a closer look.

SELECT * FROM `heroes`

A query is a command to the database for information or to take an action.
The query you see here is the basic form of every SELECT query.
Queries beginning with SELECT are asking for information and is immediately followed by the list of fields wanted.
* means 'all fields'.
FROM tells the database that the following items are the data sources. In this case, it's the table 'heroes'.

This data set is currently very small, but imagine if it had every character from every Marvel film listed. It would get quite long, and we may not want to see the whole list.
Perhaps we're only interested in the characters with magic.
Click on the 'SQL' tab. Here we can write our own queries.
There's a query already written for you.

SELECT * FROM `heroes` WHERE 1

This is very similar to the query in the 'Browse' tab except for the WHERE 1 at the end.
WHERE is always followed by conditions, and the results will only include rows that meet those conditions.
In this case the condition is 1 which is the definition of 'true', so it will show everything.
Change the query to the following.

SELECT * FROM `heroes` WHERE power='Magic'

Click the checkbox 'Retain query box' so that we don't lose our query, then click 'Go'.
You can now see the table with only rows where our condition is met.
We can further limit our results by using AND. Try the following query.

SELECT * FROM `heroes` WHERE power='Magic' AND hero_villain='Hero'

Only one character is returned in the results now because only one character meets both conditions.
We can also do inclusive results using OR.

SELECT * FROM `heroes` WHERE power='Magic' OR power='Technology'

If you run this query, you should see a longer list of characters, but still not all. Now those with powers based on technology and magic are shown, but those with skill or biological powers are not shown.

The next useful addition to SELECT queries is being able to sort results.
Imagine you're building a shopping site and want to be able to see which of your users has spent the most money so you can give them some incentives or a bonus. Sorting makes this much easier. Try the following query.

SELECT * FROM `heroes` WHERE power='Magic' OR power='Technology' ORDER BY first_appeared

We're almost there, but if we want the highest number first, we'll need to reverse the order.

SELECT * FROM `heroes` WHERE power='Magic' OR power='Technology' ORDER BY first_appeared DESC

The last part of common SELECT queries is limiting results to a certain number. In the previous example, we wanted to reward top spenders, but we'd probably only want to do it for, say, the top ten, so there's no need to see more than ten rows.
We don't have ten rows in our result, so you won't see the effect of this unless we use a lower number. Let's try LIMIT 1 to see the most recent character.

SELECT * FROM `heroes` WHERE power='Magic' OR power='Technology' ORDER BY first_appeared DESC LIMIT 1

This should give you a good understanding of tables and basic SELECT queries. In the next activity we'll look at combining results from multiple tables.