There’s more to SELECT statements than just getting data. In addition to join clauses (discussed briefly in Chapter 4), you can order and group data as well as provide intermediate tables for doing what, in effect, are subselect operations. If you are used to procedural programming where you describe each step of a process (do this . . . do that, . . . if this is true do the other thing or else do that thing . . .), you may notice that SQL and relational databases encourage you to think in declarative programming ways (get me everything that is X, do something to all the Xs . . .).

When you are working in the world of declarative programming, the more you can put into the declarations the better off you’ll be. Translated into English, this means all of the following:

• Instead of writing code to test for valid data, attempt to enforce the validation rules in the database or framework. (The Xcode Core Data model editor takes this to a graphical limit.)
• Rather than looping through each item in a set of data and testing a value to determine which procedural branch to take, get all the Xs and do something to each of them. (In effect, you move the if statement into the SELECT statement.)

This chapter examines a number of SQL clauses that can improve your code. Bear in mind that in effect you are writing some functional code, but in a declarative way, so that you can get to the declarative goal of doing the same thing to every item in a group (or in a SELECT result).

You’ll also see how to use variables in SQLite statements. This can dramatically improve your SQLite performance. The concept is implemented in PHP, Android, and Core Data not to mention in many common databases.

Looking at the Test Data

In this chapter, we use some test data in the examples. It’s actually some data generated in testing the Score app in Chapter 12. The table that is used is Score, and the values that are used here are rowid (a primary key), userid (a user number and foreign key), and score (a specific score). A given user can have multiple scores, but each user and rowid is unique.

Following is the basic data used:

sqlite> select rowid, userid, score from Score;
1|1|10
2|2|20
3|3|30
4|1|99
5|1|99
6|1|9
7|1|99
8|1|99
9|1|17
10|1|31
11|1|23
12|1|50
24|2|16
25|3|8
26|2|99
27|1|3
sqlite>

Ordering Data Makes It Easier to Use

The row numbers are in order, but to look at the data in a meaningful way, it makes sense to order it as in the following:

sqlite> select rowid, userid, score from Score order by userid;
1|1|10
4|1|99
5|1|99
6|1|9
7|1|99
8|1|99
9|1|17
10|1|31
11|1|23
12|1|50
27|1|3
2|2|20
24|2|16
26|2|99
3|3|30
25|3|8
sqlite>

Grouping Data Can Consolidate It

When you group data, you consolidate it into a single item for each value of the group. If you want to find the maximum value of score, you can use the following code:

sqlite> select max (score) from Score;
99
sqlite>

If you group the data, you can get the maximum value for each userid as in the following:

sqlite> select userid, max(score) from Score group by userid;
1|99
2|99
3|30
sqlite>

Life isn’t just about the biggest and smallest: SQLite helps you find the middle of road (the average). If these scores represent teams rather than users, the winning team based on average scores would be Team 1:

sqlite> select userid, avg (score) from Score group by userid;
1|49.0
2|45.0
3|19.0
sqlite>

Remember group by the next time you’re tempted to write a loop to calculate an average or find the maximum or minimum value. In fact, it you’re using a relational database such as SQLite, which supports SQL (even one more basic and less powerful than SQLite), try to catch yourself the next time you start to write a loop to process the results of a query, and ask yourself: “Can I do this inside the query itself?”

Using Variables in Queries

In Chapter 6, you’ll see how to use SQLite with the PHP data object (PDO). It’s a singleton object in your PHP code, but it is definitely an object with its own methods. As you’ll see in the next chapter, the process of querying the database consists of using four methods.

• new PDO creates the PDO and connects it to your SQLite database.
• prepare takes as its parameter your SQLite query
• execute performs the query
• $query->fetchAll retrieves the data from the query

There is also a simple query () call that creates and performs the query all at once. Many databases support this type of process—creating a query that can be used and reused as well as creating a query to run once. The reason for exposing the step-by-step query create, prepare, and execute process becomes clear when you realize that you can create variables inside the query.

Thus, the basic query that can be used to get all the names from a user table can be

$query = $sqlite->prepare ("select * from users;");

The name to retrieve the record for a record with a specific value for name could be

$query = $sqlite->prepare ("select * from users where Name = "Rex";");

At this point it may seem that it would be easier to use the one-step call which does the “prepare” and “execute” together.

$query = sqlite->query ("select * from users where Name = "Rex");

However, the power and efficiency of the separate statements become clear if you use a variable. Here’s an example. Begin with new PDO and prepare but, inside your query, use a variable such as :name.

$sqlite = new PDO(’sqlite:sqlitephp.sqlite’);
$query = $sqlite->prepare ("select * from users where name = :name;") ;

You don’t have to use $query right away, but whenever you do decide to use it, fill in the value for :id with code such as the following:

$query->bindValue (’:name’, "Rex");

or, if you want to use a variable,

$theName = "Rex";
$query->bindValue(’:name’, $theName);

Then execute the query:

$query>execute()

You can come back later and change the bound value and re-execute the query:

$query->bindValue (’:name’, "Anni");
$query>execute()

This process saves some memory and is much more efficient. The query can be prepared with the missing data not yet present so that when you come back to execute it, there’s much less work to do.

Summary

In this chapter you’ve seen some of the extra tweaks you can use with SELECT statements in SQLite. Everything that you can move into the query means a little less code for you to write and debug.

With this background, it’s time to move into using SQLite with PHP, Android, and OpenDoc. Each of them does the same thing, but their approaches are a little different. What remains the same is SQLite.