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');