Part 4. We've setup a database to store all of our data and we've created 3 tables to store our Leaderboard score and our Usernames. Now we'll learn some of the basics of SQL which allows us to add data to our tables (INSERT) and how to read data from them (SELECT). For this part we'll focus solely on the basics of SQL and go through a handful of different queries that let's us read/write data to our database. In the next part we'll deal with HOW to communicate with our database from the outside and we'll use PHP to do so. Our PHP code will contain SQL queries so I wanted to split the SQL/PHP parts up instead of just throwing everything out at once and hoping it sticks in your brains. 🙂
Lets open up phpMyAdmin again and click on our Usernames table on the left-hand side and then navigate into the SQL tab at the top of the screen. Once again, we're presented with a blank box that let's us run SQL queries inside of it. We did this in part 3 when we created our Challenge table using SQL code instead of using the interface. If you look underneath the input box you'll notice a bunch of buttons; SELECT *, SELECT, INSERT, etc. These are different statements that we can run on our SQL tables. SELECT * means "select all", INSERT means "add" and UPDATE lets us change existing data in our tables. If you click on INSERT a default example will appear in the input box that looks like the following:
Let's pick this example apart before we modify it ourselves just to keep everyone on the same page.
- You'll notice INSERT INTO is the first thing we see followed by 'Usernames' in backticks. This is pretty self explanatory, "insert this data into our Usernames table."
- Next, we have 'username' and 'uniqueid' inside the first set of parenthesis. These are the names of the columns where we're going to insert the data. If you remember when we created our Usernames table we have 1 column named username and another named uniqueid. These names MUST match the exact name of the column. If we put username1 instead of username we would get a "Unknown column 'username1'..." error.
- Finally, we have the VALUES statement. The number of arguments in the parenthesis must match the number of arguments in our INSERT INTO statement parenthesis. Since we want to add a username and uniqueid into Usernames, we need to supply 2 values that correspond to those arguments.
Let's fill out the full query and run it to see what happens. Replace the [value-1] and [value-2] values with data of your liking. The first argument, [value-1], corresponds to our username argument so let's change [value-1] to 'jeff' (make sure to add the single quotes around it). Our second argument relates to uniqueid so let's add a random unique id just for the purpose of this article: 'abc123'. The final query ends up looking like this:
INSERT INTO Usernames(username, uniqueid) VALUES ('jeff','abc123')
Press Go in the bottom-right corner and the result of the query will be posted above the input box. If it succeeds it should say something similar to "1 row inserted." Let's navigate to the Browse tab at the top so we can look at our newly created data in the Usernames table. NOTE: notice that 'Usernames' and the INSERT INTO variables (username and uniqueid) in the example code that is generated have backticks around them, while our VALUES (jeff and abc123) have single quotes around them. Backticks are used in table names and column identifiers and that is to avoid issues with reserved keywords (like if you wanted to name your table 'null' for some reason). I don't use backticks so they won't be included past this point, they're just included so you know to look out for them in the future. Single quotes are used around string variables and those will be used anytime we're entering values. You should see the new entry near the middle of the screen:
You'll also that notice near the top of the screen it shows the current SQL query that was run to display the current results:
LIMIT 0 , 30
It's selecting all the data from the Usernames table and returning results 0-30.
Now that we've added a user to our Usernames table we can add data to either of our leaderboard tables; Arcade and Challenge. Remember when we setup the Foreign Keys to attach the username column in our leaderboard tables to the username column in our Usernames table? We had to insert a user into our Usernames table before we could add data to our leaderboard tables. If we tried to add data to our leaderboard table and we specified a username that doesn't exist in our Usernames table we would get an error similar to "Cannot add or update a child row: a foreign key constraint fails" and this is because we must reference an existing username in our Usernames table. This might seem like added complexity (if you didn't want to support Users changing their names then it would be) but for our needs it's necessary.
Let's open up our Arcade table and insert some scores into it. Navigate to the SQL tab like before and click on INSERT underneath the input box. We'll get a familiar looking INSERT INTO statement but this one has 4 arguments instead of 2; id, username, score and timestamp. Lets fill in the values with some actual data:
INSERT INTO Arcade(id, username, score, timestamp) VALUES (null, 'jeff', '465', DEFAULT)
For the id argument we pass in null. This is because it's setup to auto-increment automatically without us expressing a unique index ourselves. For the username argument we must pass in an existing username (from the Usernames table) so I'll pass in 'jeff' since that's what I used beforehand. The score argument can be any integer value that has a length of 10 or less so I just used '465'. Finally, the timestamp argument is setup to use the CURRENT_TIMESTAMP as the default and we'll use that which is signified by writing DEFAULT. In the future we won't specify the id and timestamp arguments since they will default to acceptable values, so let's add another score to our Arcade leaderboard without using those arguments:
INSERT INTO Arcade(username, score) VALUES ('jeff', '247')
Add 3 more random scores into our Arcade table so we have a total of 5. After you add those scores click on the Browse tab to verify that we have 5 scores saved in our table.
Now that we've learned how to insert some data into our tables, how can we read the data? We can use the SELECT statement to choose which data we want returned in our query. Let's go back into the SQL tab for our Arcade table. If we want to view all of the data in the table we can use the following query:
SELECT * FROM Arcade
Which reads as "SELECT everything FROM our Arcade table". If you haven't caught on yet, after the FROM statement we always specify the name of a table in our database. In our case we'll always have either Arcade, Challenge or Usernames after any FROM statement. What if we just want to see how many rows (i.e. scores) are in our Arcade table? We could use the following query:
SELECT COUNT(*) FROM Arcade
This returns an integer with the number of entries we have, so in our case it should return 5. What if we wanted to return scores that are greater than a certain value?
SELECT * FROM Arcade WHERE score > 300
This will return all scores that are greater than 300. The WHERE statement is an extra condition that must be met to the returned data. We'll use this one in almost all of our queries so we can get only the data we want. There's no reason to return all the data in our table then sort through it later using PHP (or whatever language you're using) because that's just a waste of resources and bandwidth. What if we want to order our scores from lowest to highest?
SELECT * FROM Arcade ORDER BY score ASC
This will order scores ASCENDING in value, so from the lowest values to the highest. Finally, how do we return a certain number of scores or return a certain page of scores? Pagination is something we'll do when we want to display our score because we don't want to display all the scores at once, we just want to show scores 10 at a time, for example, like 1-10, 11-20, etc.
SELECT * FROM Arcade LIMIT 0, 3
This will start at row 0 and display 3 scores. If we wanted to show our bottom 2 scores we could use LIMIT 3, 2 (start at row 3 and display 2 scores).
That's it for our quick SQL lesson! There's quite a bit more complicated SQL queries that we can run and we'll tackle those as we come across them in the next part where we'll start using PHP so we can communicate with our database from the outside.