Titanium SDK: Selecting Data from Multiple Tables in SQLite with JOIN

Assumptions

For this example, I’m assuming we get our user_ID populated from a web service when the app starts.

Create your tables

There’re 3 tables, one for game text info, one for game icon graphics and one that stores a list of my favorite games.

 
CREATE TABLE MyGames (
      game_ID INTEGER PRIMARY KEY,
      game_name TEXT,
      num_players INTEGER,
      game_rules TEXT
);   

CREATE TABLE MyGameIcons (
      icon_ID INTEGER PRIMARY KEY,
      game_ID INTEGER,
      icon_ImgPth TEXT,
      detail_ImgPth TEXT
); 

CREATE TABLE MyFaveGames (
	   fave_ID INTEGER PRIMARY KEY,
	   game_ID INTEGER, 
	   user_ID INTEGER 
	)

Add some dummy data to them using SQLite Manager plugin for Firefox or a command line tool. Once you have some data, you can move on to trying out the JOIN clause.

Use a basic INSERT to save a game to the MyFaveGames table:

 
INSERT INTO MyFaveGames(user_ID, game_ID) VALUES(1823, 9)

To use this statement in Titanium SDK 2.x app, you can do this:

 
db.execute( 'INSERT INTO MyFaveGames(user_ID, game_ID) VALUES(?, ?)', varUserID, varGameID );

Assuming varUserID and varGameID are pre-populated in your app and db is defined earlier as your database connection object ( db = Ti.Database.open(myConn); )

Use SQL’s implicit JOIN clause to pull records from more than one table using one query

Pull the game info & icon graphics for games saved to My Favorite Games. Order them by the game names.

 
SELECT MyGames.game_ID, game_name, num_players, game_rules, icon_ImgPth, detail_ImgPth FROM MyGames, MyGameIcons, MyFaveGames WHERE MyFaveGames.game_ID=MyGames.game_ID AND MyFaveGames.game_ID=MyGameIcons.game_ID ORDER BY game_name

In Titanium SDK 2.x app, it can look like this:

 
db.execute('SELECT MyGames.game_ID, game_name, num_players, game_rules, icon_ImgPth, detail_ImgPth FROM MyGames, MyGameIcons, MyFaveGames WHERE MyFaveGames.game_ID=MyGames.game_ID AND MyFaveGames.game_ID=MyGameIcons.game_ID ORDER BY game_name');

The above would return a recordset, which you can loop through.

Creating a Temporary Table for Immediate Use

 
		var myDBconn = getDB();

		var temp = myDBconn.execute('CREATE TEMP VIEW MyTempTabl AS SELECT fave_ID, game_ID, user_ID FROM MyFaveGames WHERE user_ID=' + varUserID);
		var rows = myDBconn.execute('SELECT MyGames.game_ID, game_name, num_players, equip_items, fld_type, classics, fancies, game_instructns, fld_Icon, equip_Icon, composit_Icon, fave_ID, user_ID FROM MyGames LEFT OUTER JOIN MyGameIcons on MyGames.game_ID = MyGameIcons.game_ID LEFT OUTER JOIN MyTempTabl ON MyGames.game_ID = MyTempTabl.game_ID WHERE classics = "TRUE" ORDER BY game_name'); 

SQLite3: CREATE Table w/ DATE data type, INSERT Rows Using date() Function

Let’s say our UI gives the user a choice between 4 options. They can select option 1, 2, 3 or 4.

You can grab the SQLite Manager Add On for Firefox, use sqlite3 via command line or use commercial software.

The userID can be obtained locally, or in a real life situation, most likely populated from a web service after you log in. The details of how to populate userID are beyond the scope of this note, but are easily searchable online.

Create your TABLE

It’ll consist of these 4 fields:

• Options ID INTEGER primary key
• User ID INTEGER
• Entry Date INTEGER – stored in YYYY-MM-DD format
• Choice Type: 1, 2, 3, 4 (1 = option 1, etc) INTEGER

 
CREATE TABLE Choices (
      options_ID INTEGER PRIMARY KEY,
      choice_type INTEGER,
      user_ID INTEGER,
      entry_Date DATE DEFAULT CURRENT_DATE
   );   

The entry_Date field gets auto populated with today’s date if you fail to provide a value in the INSERT statement.

Save today’s choice

Sample data includes ‘4’ for the chosen option and ‘1823’ as a mock userID. Today’s date is inserted using SQLite’s built-in date() function.

    
INSERT INTO Choices(choice_type, user_ID, entry_Date) VALUES( 4, 1823, date('now','localtime'))

Save yesterday’s choice

Yesterday’s date is grabbed using SQLite’s built-in date() function plus the NNN days modifier.

    
INSERT INTO Choices(choice_type, user_ID, entry_Date) VALUES( 1, 1823, date('now','localtime', '-1 days')) 

Get yesterday’s choice

Get the option chosen for YESTERDAY’s date for user ID ‘1823’

    
SELECT choice_type FROM Choices WHERE Choices.user_ID=1823 AND Choices.entry_Date=date('now','localtime', '-1 days')