SQL Playlists

[blokenamedblake]

How can I find games I have on my system but have never played?

 

I thought this would work:

select * from Games where NumberPlays=0

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

[DBut73]

blokenamedblake wrote:

How can I find games I have on my system but have never played?

I thought this would work:

select * from Games where NumberPlays=0

Try this in a playlist

SELECT * FROM Games Gm WHERE Visible=1 AND NOT EXISTS(SELECT 1 FROM GamesStats WHERE GameID=Gm.GameID) ORDER BY GameName

Any games that have not been played should appear in the playlist. If you play a game you will have to back out of the playlist then go back in to update the list and remove any played games.

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

[Stevenoj2769]

Anyone know what SQL I can use to get a playlist that just shows tables that were added in the last 30 days.  I tried several things with no luck.

Here is one of the things I tried that did not work.

SELECT * FROM Games WHERE DateAdded >= DATEADD(day,-30, getdate()) ORDER BY DateAdded desc;

Thanks in advance!!!

What almost works if dates are not involved is this will get you just the last 30 tables you added.

Select * from Games order by dateadded desc limit 30;

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

[NailBuster]

fyi… documentation for SQL lite is on this site:  https://www.sqlite.org/lang_datefunc.html

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

1 user thanked author for this post.

Stevenoj2769

[Stevenoj2769]

…after NailBuster posted the link to SQL lite documentation I got what I needed to work to show just the tables added in the last 30 days.  Thanks NailBuster!

Here is what I came up with to get a playlist that is just the games added in the last 30 days.

Select * from Games where dateadded >= datetime(‘now’,’-30 day’) order by dateadded desc;

I hope this helps someone out.

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

[bschaberg21]

I’m trying to add a recently added playlist like the recent post above. I’m getting an error message when I test the activesql query.  Any thoughts or help would be or help would be appreciated.

 

.

BSchaberg21

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

[John William]

bschaberg21 wrote:

I’m trying to add a recently added playlist like the recent post above. I’m getting an error message when I test the activesql query. Any thoughts or help would be or help would be appreciated.

The single quotes look like they got swapped in copy/paste/encoding.. replace them with the single quote just to the left of the enter key if using a US keyboard.

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

[bschaberg21]

That worked.  Thanks so much!

BSchaberg21

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

[gride]

I’m trying to create a “recently played” playlist across playlists. I’ve tried the below.

select * from Games where GameID in (select GameID from GamesStats order by LastPlayed DESC limit 25)

This seems to give me the order backwards, but if I order ASC it’s incorrect.

Any thoughts on what I can change?

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

[superproxy]

help, please. how do i list most played according to total play time?

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

[DBut73]

superproxy wrote:

help, please. how do i list most played according to total play time?

Try this:

SELECT * FROM Games JOIN GamesStats ON Games.GameID=GamesStats.GameID AND Visible=1 ORDER BY GamesStats.TimePlayedSecs DESC LIMIT 5

This should display the top 5 tables played by time in seconds. Change ‘LIMIT 5’ to list a different amount of tables e.g. ‘LIMIT 10’ will display top 10.

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

1 user thanked author for this post.

superproxy

[superproxy]

DBut73 wrote:

superproxy wrote:

help, please. how do i list most played according to total play time?

Try this:

SELECT * FROM Games JOIN GamesStats ON Games.GameID=GamesStats.GameID AND Visible=1 ORDER BY GamesStats.TimePlayedSecs DESC LIMIT 5

This should display the top 5 tables played by time in seconds. Change ‘LIMIT 5’ to list a different amount of tables e.g. ‘LIMIT 10’ will display top 10.

Thanks a lot!! This worked like a charm! :)

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

[superproxy]

superproxy wrote:

DBut73 wrote:

superproxy wrote:

help, please. how do i list most played according to total play time?

Try this:

SELECT * FROM Games JOIN GamesStats ON Games.GameID=GamesStats.GameID AND Visible=1 ORDER BY GamesStats.TimePlayedSecs DESC LIMIT 5

This should display the top 5 tables played by time in seconds. Change ‘LIMIT 5’ to list a different amount of tables e.g. ‘LIMIT 10’ will display top 10.

Thanks a lot!! This worked like a charm! :)

Hi. Is there a way to take this a step further and say for example, most played according to total play time, for the last 30 days only?

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

[NailBuster]

superproxy wrote:

Hi. Is there a way to take this a step further and say for example, most played according to total play time, for the last 30 days only?

not possible as that would require to track playing time by date.  pinup just has a total time played field for each game… not a detail by date/time.

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

1 user thanked author for this post.

superproxy

[Tom]

I recently got into popper and started digging around.  This is a really nice front end.  I have watched a few videos on popper setup, is there a video tutorial that explains the stuff you are talking about here to us lay people or is this something that should be left to the professionals

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

[Rajo Joey]

Tom wrote:

I recently got into popper and started digging around. This is a really nice front end. I have watched a few videos on popper setup, is there a video tutorial that explains the stuff you are talking about here to us lay people or is this something that should be left to the professionals

The sql-commands are a great help. You don’t have to put tables in a playlist. It was all automatically done. Only add the table in the game manager and it’s in the playlist. If you want some, I can send you my sql-commands. It’s easy to edit them.

https://www.nailbuster.com/wikipinup/doku.php?id=playlists_sql_examples

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

[Tom]

what would be a command for top ten played tables, thanks

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

[blokenamedblake]

G’day all.

Nailbuster’s site shows the structure of the Games table, but can anyone share the structure of the GamesStats table.

Thanks in advance.

**EDIT**

Answered my own question, so here is it is in case it helps anyone else:

UniqueID INTEGER

GameID INTEGER,

LastPlayed DATETIME,

NumberPlays INTEGER,

TimePlayedSecs INTEGER,

HighScores TEXT

• This reply was modified 3 years, 2 months ago by blokenamedblake.

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

[blokenamedblake]

Sorry for the double post, but the reason for the above initial question was because I wanted to find a way to add the tick to the field “Add to Batch record” so when I do my weekly update/addition of tables, I can record the updated playfields.  So if you go in to PinUP Popper Setup > Popper Setup > DB Editor (advanced), I now paste the following in the sqltext and click Execute SQL.

UPDATE Games SET AutoRecFlag ='1' WHERE DateUpdated>= datetime('now','-7 day')

I then just run the PopperAutoRecord and after doing the brand new tables, I tick only add flagged Games, change the playfield and/or backglass dropdown to All/Overwrite, and then click Scan, then Start Auto-record

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

[KiwiBri]

NailBuster wrote:

always good practice to add to where clause ” and visible=1″

visible=2 are adult/hidden status and visible=0 are hidden. (visible is set in games manager on table).

thanks!  The example on the PinUp Wiki page for SQL Playlists is outdated/incorrect. (Example at the bottom of the page)

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

[Author]

Posts