SQL Playlist Examples and Ideas

[Iain]

SQL in playlists is an awesome feature of Pinup Popper. I’m a programmer by day but i’ve never actually been able to mess around with SQL so I’d love to know if there are any cool tricks and ideas that some users out there know with SQL?

I’ve got the absolute basic knowledge of it in practice, and I’ve got a few playlists I’ve setup and I’ll share the SQL here for anyone interested.

I honestly recommend using SQL over making your own playlists, even some more specific playlists because you’ll *never* have to maintain lists if you do it right.

So, first a tiny bit of basic SQL knowledge (this is a very very very basic overview and skips *a lot* of stuff)….

All SQL is in the form of something like….

SELECT blah
FROM blah
WHERE blah
AND blah
OR blah
ORDER BY blah

Now. Most of these are optional, all you *actually* need is SELECT and FROM.

In terms of Pinup Popper, you’re almost always going to start with….

“SELECT * FROM Games”

SELECT * basically means, “Select everything” because the * is the wildcard so will get ALL games.

FROM Games means you’re picking items from the “Games” database, this is the main database of games inside Pinup Popper.

The rest are all optional.

WHERE is followed by some equality check. Maybe WHERE Theme=’something’ or WHERE Rating>3 where Theme and Rating are values set when you add games in the Game Manager.

Now, this is where its a little fiddly, the input values in the GameSetupForm don’t match exactly the name of the value in the database.

Currently the easist way I found to get the names of the values I need was to go the Popper Setup menu and click DBEditor. Don’t worry, you won’t be editing anything.

In this the basic “Select * from Games” will be shown, so just hit Execute and Open Results, this will show you the *raw* table data and you can see the name of the columns, these are the fields you can use in your SQL.

AND and OR basically do what they say, they let you combine other equality checks. So maybe you want all themes of 3 rating at least, so you’d do “SELECT * FROM Games WHERE GameTheme=”blah” AND GameRating>=3″

ORDER BY is how you want the list ordered after being built. If you don’t have ORDER BY, it will return the results in the order they are in the database, which is probably the order you added them in! Most of the time you want them in alphabetical order, so “ORDER BY GameDisplay” is what you want (note. NOT GameName, as thats the name of the vpx file itself, not the display name you see in the menu).

So a simple, get all games that have rating at least 3 in alphabetical order might be….

“SELECT * FROM Games WHERE GameRating>=3 ORDER BY GameDisplay”.

And if I wanted them in *reverse* order for no apparant reason, pop a “DESC” on the end.

“SELECT * FROM Games WHERE GameRating>=3 ORDER BY GameDisplay DESC”.

Now, tbh, this is the limits of my SQL knowledge as I haven’t even looked much at SQL for nearly 10 years. So I’m really interested to see if there are any cool tricks or ideas of there for more advanced SQL searching. For now though, these are the playlists I’ve got in my setup….

 

TLDR; Here are my playlists…

Visual Pinball X – Displays all games that are VPX tables…
SELECT * FROM Games WHERE EMUID= ‘1’ ORDER BY GameDisplay;

(This highlights the issue between database and how you input values, the emulator field is EMUID and they all have ids, so VPX is 1, PBFX2 is 2 and PBFX3 is 3, I haven’t checked the others but I assume FP is 4 and ‘PC’ is 5, then any others you add will be 6,7,8, etc)

 

 

Pinball FX Tables – Displays all PBFX2 and PBFX3 tables (I have both installed as PBFX2 still has 4 tables not in PBFX3)
SELECT * FROM Games WHERE EMUID= ‘2’ Or EMUID=’3′ ORDER BY GameDisplay;

(Note – this uses OR and not AND, it might seem intutive to put AND as you want FX2 AND FX3, but no table is *both*, they are one or the other, so what you actually are asking for is FX2 OR FX3)

 

 

MARVEL – These are all Marvel tables. I set the Theme in the game manager to be “Marvel”.
SELECT * FROM Games WHERE GameTheme=’Marvel’ ORDER BY GameDisplay;

STAR WARS – These are all Star Wars tables. I set the Theme in the game manager to be “Star Wars”
SELECT * FROM Games WHERE GameTheme=’Star Wars’ ORDER BY GameDisplay;

 

 

Stern Pinball – This is an odd one. Basically I wanted a “modern stern” playlist, but I have all modern and old stern tables as the same manufacturer. I could have used a Theme tbh but instead I’ve been more hacky and just use the date 1999 as the point Modern Stern tables ‘started’
SELECT * FROM Games WHERE Tags = ‘Stern’ AND GameYear >= ‘1999’ ORDER BY GameDisplay;

(Now we DO use the AND as we want both things to be true, Stern AND from 1999 onwards. Note also, Manufacturer in the game manager is saved to the Tags field…this threw me at first!)

 

 

I then have a “Decades” playlist which contains a playlist for each “Decade”. So each one is like this…

2000s+ – SELECT * FROM Games WHERE GameYear >= 2000 ORDER BY GameDisplay;
1990s – SELECT * FROM Games WHERE GameYear >= 1990 AND GameYear < 2000 ORDER BY GameDisplay;
1980s – SELECT * FROM Games WHERE GameYear >= 1980 AND GameYear < 1990 ORDER BY GameDisplay;
1970s – SELECT * FROM Games WHERE GameYear >= 1970 AND GameYear < 1980 ORDER BY GameDisplay;
1960s – SELECT * FROM Games WHERE GameYear < 1970 ORDER BY GameDisplay;

Yes – 2000s is actually everything after 2000 and 1960s is everything before 1970, but its good enough for me :P

You could also actually order them chornologically instead, so have something like “Order By GameYear” on the end instead, and can have it go new to old with “Desc” on the end of that.

Thats all I’ve got for now, I don’t have ratings set for games but if I did I’d go with something like a “top 25” playlist or similar with

SELECT * FROM Games ORDER BY GameRating DESC LIMIT (25)

This introduces a new thing, LIMIT which basically limits the result to the amount in the brackets.

 

So. Now if I were to add a new modern stern Star Wars table (ha), then it will automagically get added to the 2000s playlist, the modern stern playlist and if I set the theme correctly, to the Star Wars playlist and the Visual Pinball X playlist!

You need to login in order to like this post: click here

[Iain]

SQL in playlists is an awesome feature of Pinup Popper. I’m a programmer by day but I’ve never actually been able to mess around with SQL so I’d love to know if there are any cool tricks and ideas that some users out there know with SQL?

I’ve got the absolute basic knowledge of it in practice, and I’ve got a few playlists I’ve setup and I’ll share the SQL here for anyone interested.

I honestly recommend using SQL over making your own playlists, even some more specific playlists because you’ll *never* have to maintain lists if you do it right.

So, first a tiny bit of basic SQL knowledge (this is a very very very basic overview and skips *a lot* of stuff)….

All SQL is in the form of something like….

SELECT blah
FROM blah
WHERE blah
AND blah
OR blah
ORDER BY blah

Now. Most of these are optional, all you *actually* need is SELECT and FROM.

In terms of Pinup Popper, you’re almost always going to start with….

“SELECT * FROM Games”

SELECT * basically means, “Select everything” because the * is the wildcard so will get ALL games.

FROM Games means you’re picking items from the “Games” database, this is the main database of games inside Pinup Popper.

The rest are all optional.

WHERE is followed by some equality check. Maybe WHERE Theme=’something’ or WHERE Rating>3 where Theme and Rating are values set when you add games in the Game Manager.
Now, this is where its a little fiddly, but the input values in the GameSetupForm don’t match exactly the name of the value in the database.

Currently the easiest way I found to get the names of the values I need was to go the Popper Setup menu and click DBEditor. Don’t worry, you won’t be editing anything.

In this the basic “Select * from Games” will be shown, so just hit Execute and Open Results, this will show you the *raw* table data and you can see the name of the columns,these are the fields you can use in your SQL.

AND and OR basically do what they say, they let you combine other equality checks. So maybe you want all themes of 3 rating at least, so you’d do “SELECT * FROM Games WHERE GameTheme=”blah” AND GameRating>=3″

ORDER BY is how you want the list ordered after being built. If you don’t have ORDER BY, it will return the results in the order they are in the database, which is probably the order you added them in! Most of the time you want them in alphabetical order, so “ORDER BY GameDisplay” is what you want (note. NOT GameName, as that the name of the vpx file itself, not the display name you see in the menu).

So a simple, get all games that have rating at least 3 in alphabetical order might be….

“SELECT * FROM Games WHERE GameRating>=3 ORDER BY GameDisplay”.

And if I wanted them in *reverse* order for no apparent reason, pop a “DESC” on the end.

“SELECT * FROM Games WHERE GameRating>=3 ORDER BY GameDisplay DESC”.

Now, tbh, this is the limits of my SQL knowledge as I haven’t even looked much at SQL for nearly 10 years. So I’m really interested to see if there are any cool tricks or ideas of there for more advanced SQL searching. For now though, these are the playlists I’ve got in my setup….

TLDR; Here’s my playlists…

Visual Pinball X – Displays all games that are VPX tables…
SELECT * FROM Games WHERE EMUID= ‘1’ ORDER BY GameDisplay;

(This highlights the issue between database and how you input values, the emulator field is EMUID and they all have ids, so VPX is 1)

 

Pinball FX Tables – Displays all PBFX2 and PBFX3 tables (I have both installed as PBFX2 still has 4 tables not in PBFX3)
SELECT * FROM Games WHERE EMUID= ‘2’ Or EMUID=’3′ ORDER BY GameDisplay;

(Note – this uses OR and not AND, it might seem intuitive to put AND as you want FX2 AND FX3, but no table is *both*, they are one or the other, so what you actually are asking for is FX2 OR FX3)

MARVEL – These are all Marvel tables. I set the Theme in the game manager to be “Marvel”.
SELECT * FROM Games WHERE GameTheme=’Marvel’ ORDER BY GameDisplay;

STAR WARS – These are all Star Wars tables. I set the Theme in the game manager to be “Star Wars”
SELECT * FROM Games WHERE GameTheme=’Star Wars’ ORDER BY GameDisplay;

 

 

Stern Pinball – This is an odd one. Basically I wanted a “modern stern” playlist, but I have all modern and old stern tables as the same manufacturer. I could have used a Theme tbh but instead I’ve been more hacky and just use the date 1999 as the point Modern Stern tables ‘started’
SELECT * FROM Games WHERE Tags = ‘Stern’ AND GameYear >= ‘1999’ ORDER BY GameDisplay;

(Now we DO use the AND as we want both things to be true, Stern AND from 1999 onwards. Note also, Manufacturer in the game manager is saved to the Tags field…this threw me at first!)

 

I then have a “Decades” playlist which contains a playlist for each “Decade”. So each one is like this…

2000s+ – SELECT * FROM Games WHERE GameYear >= 2000 ORDER BY GameDisplay;
1990s – SELECT * FROM Games WHERE GameYear >= 1990 AND GameYear < 2000 ORDER BY GameDisplay;
1980s – SELECT * FROM Games WHERE GameYear >= 1980 AND GameYear < 1990 ORDER BY GameDisplay;
1970s – SELECT * FROM Games WHERE GameYear >= 1970 AND GameYear < 1980 ORDER BY GameDisplay;
1960s – SELECT * FROM Games WHERE GameYear < 1970 ORDER BY GameDisplay;

Yes – 2000s is actually everything after 2000 and 1960s is everything before 1970, but its good enough for me :P

You could also actually order them chronologically instead, so have something like “ORDER BY GameYear” on the end instead, and can have it go new to old with “DESC” on the end of that.

 

Thats all I’ve got for now, I don’t have ratings set for games but if I did I’d go with something like a “top 25” playlist or similar with

SELECT * FROM Games ORDER BY GameRating DESC LIMIT (25)

This introduces a new thing, LIMIT which basically limits the result to the amount in the brackets.

You need to login in order to like this post: click here

[Author]

Posts