User Tools

Site Tools


playlists_sql_examples

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
Last revision Both sides next revision
playlists_sql_examples [2018/07/27 22:23]
A User Not Logged in
playlists_sql_examples [2019/06/18 12:55]
pinupadmin
Line 4: Line 4:
  
 Here are some sample of Playlists and the SQL you can set to use them: Here are some sample of Playlists and the SQL you can set to use them:
 +
 +some threads:
 +
 +[[https://​vpinball.com/​forums/​topic/​sql-playlists/​|https://​vpinball.com/​forums/​topic/​sql-playlists/​]]
 +
 +[[https://​vpinball.com/​forums/​topic/​sql-helper-for-pinballx-users-going-to-pinup-menu/​|https://​vpinball.com/​forums/​topic/​sql-helper-for-pinballx-users-going-to-pinup-menu/​]]
 +
 +Here are the some columns for Games Table.
 +
 +<​code>​
 +    GameID ​         INTEGER
 +    EMUID           ​INTEGER,​
 +    GameName ​       VARCHAR (200)
 +    GameFileName ​   VARCHAR (250)
 +    GameDisplay ​    ​VARCHAR (200)
 +    UseEmuDefaults ​ INTEGER,
 +    Visible ​        ​INTEGER ​      ​DEFAULT (1),
 +    Notes           TEXT,
 +    DateAdded ​      ​DATETIME,​
 +    GameYear ​       INTEGER,
 +    ROM             ​VARCHAR (100),
 +    Manufact ​       VARCHAR (200),
 +    NumPlayers ​     INTEGER,
 +    ResolutionX ​    ​INTEGER,​
 +    ResolutionY ​    ​INTEGER,​
 +    OutputScreen ​   INTEGER,
 +    ThemeColor ​     INTEGER,
 +    GameType ​       VARCHAR (50),
 +    TAGS            VARCHAR (200),
 +    Category ​       VARCHAR (200),
 +    Author ​         VARCHAR (200),
 +    LaunchCustomVar VARCHAR (200),
 +    GKeepDisplays ​  ​VARCHAR (50),
 +    GameTheme ​      ​VARCHAR (100),
 +    GameRating ​     INTEGER,
 +    Special ​        TEXT,
 +    sysVolume ​      ​INTEGER,​
 +    DOFStuff ​       VARCHAR (250),
 +    MediaSearch ​    ​VARCHAR (100),
 +    AudioChannels ​  ​VARCHAR (50),
 +    CUSTOM2 ​        ​VARCHAR (100),
 +    CUSTOM3 ​        ​VARCHAR (100),
 +    GAMEVER ​        ​VARCHAR (100),
 +    ALTEXE ​         VARCHAR (250),
 +    IPDBNum ​        ​VARCHAR (100),
 +    DateUpdated ​    ​DATETIME,​
 +    DateFileUpdated DATETIME,
 +    AutoRecFlag ​    ​INTEGER ​       DEFAULT 0,
 +    AltRunMode ​     VARCHAR (250),
 +    WebLinkURL ​     VARCHAR (1000),
 +    DesignedBy ​     VARCHAR (200)
 +</​code>​
  
 - Based on GameType, list all DMD (and color DMD) games: - Based on GameType, list all DMD (and color DMD) games:
  
-SELECT * FROM Games JOIN Emulators on Games.EmuID = emulators.EmuID ​ WHERE  (GameType= "​DMDc"​ or GameType= "​DMD"​) ​ ORDER BY GameDisplay+<​code>​ 
 +SELECT * FROM Games JOIN Emulators on Games.EmuID = emulators.EmuID WHERE (GameType= "​DMDc"​ or GameType= "​DMD"​) ORDER BY GameDisplay 
 +</​code>​
  
 - Using the TAG "​ignore"​ remove certain games from any list: - Using the TAG "​ignore"​ remove certain games from any list:
  
-SELECT * FROM Games JOIN Emulators on Games.EmuID = emulators.EmuID ​ WHERE  (GameType= "​DMDc"​ or GameType= "​DMD"​) ​  ​and tags not like "​%ignore%"​ ORDER BY GameDisplay+<​code>​ 
 +SELECT * FROM Games JOIN Emulators on Games.EmuID = emulators.EmuID WHERE (GameType= "​DMDc"​ or GameType= "​DMD"​) and tags not like "​%ignore%"​ ORDER BY GameDisplay 
 +</​code>​
  
-​​​​​​​- Playlist for Pinsound tables (using field LaunchCustomVar)+- Playlist for Pinsound tables (using field LaunchCustomVar)
  
-SELECT * FROM Games JOIN Emulators on Games.EmuID = emulators.EmuID ​ WHERE ( LaunchCustomVar LIKE "​%pinsound%" ​ or  LaunchCustomVar LIKE "​%altsound%"​) and TAGS NOT LIKE "​%ignore%" ​ ORDER BY "​GameDisplay"​+<​code>​ 
 +SELECT * FROM Games JOIN Emulators on Games.EmuID = emulators.EmuID WHERE ( LaunchCustomVar LIKE "​%pinsound%"​ or LaunchCustomVar LIKE "​%altsound%"​) and TAGS NOT LIKE "​%ignore%"​ ORDER BY "​GameDisplay"​ 
 +</​code>​
  
 - Playlist for adult games: - Playlist for adult games:
  
-select * from games JOIN Emulators on Games.EmuID = emulators.EmuID ​ where TAGS LIKE "​%adult%"​ and TAGS NOT LIKE "​%ignore%" ​  ​ORDER BY "​GameDisplay"​+<​code>​ 
 +select * from games JOIN Emulators on Games.EmuID = emulators.EmuID where TAGS LIKE "​%adult%"​ and TAGS NOT LIKE "​%ignore%"​ ORDER BY "​GameDisplay"​ 
 +</​code>​
  
  
playlists_sql_examples.txt · Last modified: 2020/03/27 13:48 by pinupadmin