me
Posts: 9
|
Post by me on Feb 17, 2022 15:54:36 GMT -5
I am trying to gauge the level of interest in a relational db for wheel of fortune. I know what you have currently. It has a lot of information, but it is very hard to find that information and has no real community update features.
I am thinking more like j-archive, but more sophisticated.
This would have a number of benefits.
-easier to add new data
-easier to edit
-greater depth
-uniform uri structure to find data
-ability to add any version and any language
-everything can be links (random puzzle on home page links to wherever it appeared)
-data layer is separated from styling so easy to change how it looks without affecting data
-portability: anyone can download db and use it for their own noncommercial purposes
-community updating with different levels of privilege. trusted users can directly update data while untrusted can only suggest changes to prevent spam and abuse.
you can see what I did so far at wofdb.com. It only has a few episodes. I have more, but I changed db structure and need to re-add them.
wofdb.com/browse/Wheel_of_Fortune_(syndicated)/38/134 is mostly complete and shows how it works. All data in db for s38e134 of syndication version is found there. Missing data would automatically end up in right place as long as version, season, and episode are set correctly. Once missing stuff is added, it would be in the right place because each episode has a flow attached (game_round_order or something in the db) that automatically puts them in show order for that game.
There is not a lot to see right now because I tore it all down recently. I need to test some stuff i have finished before uploading it. But it is an idea of what it can be. I am not trying to eliminate or replace what you have. Both can exist obviously. I more want to see if people are interested in something that is more user friendly.
|
|
Guint
Production Assistant 
Risen from the Dead
^ Top Notch Production Values...kinda
Posts: 2,746 
|
Post by Guint on Feb 18, 2022 0:54:33 GMT -5
It's certainly intriguing. I'll be the first to admit that the compendium is not the most user-friendly thing out there, but that's simply my limitations of knowing code. I can make html tables like a beast but that's about it! I have tried to look into more database driven compendium stuff than just a simple text box, but since this was really only a side project of mine, I didn't have the time to look into it further, especially since I was spending most of my time on it compiling hundreds of episodes. Luckily people have been able to mine the data in their own various unique ways, but I always wondered what it'd be like to have a more user-friendly option to viewing the information.
However, since your site is in really primitive form and you don't have much of a history yet on these boards, I'd just like to see it get fleshed out with a few more shows and some search functions (I see the search bar isn't working just yet) before I can really attest to its viability. But I like what you have so far, and I do encourage you to keep going.
Though I will warn you - if you're trying to get to the point where what you have in the database matches the compendium, you may want to consider ways to try and mine the data quicker from the tables...trying to get what is probably 40,000+ puzzles in there would be time consuming, even if you did start getting people to help fill them in.
Also, I would consider using the show's episode count numbers instead of, or perhaps in addition to, the season/episode format to display which show you're referring to. I know the compendium has been used in the past to find shows that contestants were on, and since the show's episode numbers are just the basic count (barring Season 37's weirdness), it'd be easier for them to find it that way.
But yeah, keep at it and I'm looking forward to seeing what you come up with.
|
|
me
Posts: 9
|
Post by me on Feb 21, 2022 11:41:01 GMT -5
basic sql is very easy to learn. There are a number of intros on youtube ("beginner database course" or "learn sql in x hours") that give you all the relevant information. I added a basic search, but I do not want to do anything more complex until everything is finished. You can look up any puzzle or part of a puzzle (3 character minimum). "green" will give you "green" and "evergreen", for example. I will eventually add more search options and a wild card search for puzzles ("_'m o_ m_ w_y" would find "i'm on my way"). Wild cards work now, but it looks anywhere in the puzzle so "t_e" finds "the" and "albert einstein." I have finished the category page. www.wofdb.com/categories lists all the category types I have. Clicking on a category takes you to all the puzzles under the category and a list of versions that have featured it. Clicking a version gives you only the puzzles in that category for the specific version. It can also limit by season by adding the season to the end of the uri, though this is not included in the UI yet. all 'event' puzzles in db: www.wofdb.com/categories/eventall 'event' puzzles for "Wheel of Fortune (syndication)" in db: www.wofdb.com/categories/event/wheel-of-fortune-syndicationall 'event' puzzles for "Wheel of Fortune (syndication)" season 38 in db: www.wofdb.com/categories/event/wheel-of-fortune-syndication/38The counts on the category page are wrong. It is listing number of categories with puzzles (14), not the number of puzzles in each category. I need to fix that. Only 14 categories have puzzles. the rest kick it back to the category list. I will update the page to make those sections collapsible. Data entry is easy. versions, categories, puzzles, etc can be uploaded as CSV files as long as the information is consistent with the information in the database. if it is not a little manipulation can fix it. For example, old puzzles might have "place" as category while new versions would use "on the map". These can be adjusted easily to change the category to the "classic" form. I could also change the table design a little and make category-puzzle relationship many to many so a puzzle can have its original category and new equivalent, but this would require constant updating to ensure that it is correct. place becomes on the map and fictional place becomes place. I think it would be a bad idea though. As for uri structure, this is the easiest way to cross-reference with other sources. Episode info is listed by season and episode number. The database has fields for airdate, vtr, and production code, so it would not be difficult to find the exact episode you want. The person table could also include people's names. I have an episode role for "celebrity contestant" and could add a "contestant" role, but I am not really sure that people want their full name made available. |
|
Guint
Production Assistant
Risen from the Dead
^ Top Notch Production Values...kinda
Posts: 2,746
|
Post by Guint on Feb 26, 2022 15:54:31 GMT -5
Oh it's not that I don't think I could learn it, it's just finding the time. I've been pretty much retired from the day-to-day entry of the compendium for years now, and I've only recently been helping add/enhance a few features. Not that I don't care about the compendium, it's just there's other things in my life I'd like to do.  But ultimately yeah, I like what you are doing, and please by all means keep it up. If you would like people to help you fill in things, by all means. I probably won't be interested in doing so, for the reasons I mentioned, but I'm sure you could find people who are willing to help. |
|
me
Posts: 9
|
Post by me on Feb 28, 2022 11:51:08 GMT -5
I improved the search wofdb.com/search. still not done, but more capable.
The or search has issues when categories and versions are added, but the standard and advanced searches work fine.
Advanced search has wildcard capabilities (and requires them as it is looking for exact matches).
The underscore (_) is any letter. It does not match spaces or punctuation. It would be useful to cheat in video games that have puzzles in db. For example, if "RSTLNE" resulted in "t__ ___e __ _____n____" in the bonus round for SNES version of Wheel of Fortune, the db would return "the duke of buckingham" so you could win that pretend trip.
The asterisk (*) stands in for any number (0,) of letters or punctuation, but not spaces. "*" would find any word. "the*" would find any works that begins with "the-" (the, them, there, they, thesaurus, etc). "* * *" would find all three world puzzles.
The percent sign (%) matches any number (0,) of characters, including spaces and punctuation. "a %" returns all puzzles that start with the word "a". "%-% finds any puzzle with an exclamation point.
Wildcards can be combined. "___ * %" returns all puzzles that start with a three letter word and have at least three words. "* ____ *" finds three world puzzles with a middle word that is four letters long. "b%%" meets three character search minimum and finds all puzzles starting with the letter b.
Category and version can be included to reduce result set. "%ing" with version as "Wheel of Fortune (syndication)" finds puzzles ending in "ing" from syndicated version only.
I need to finish up the add data function. They are mostly done, but I need to do more testing.
|
|
me
Posts: 9
|
Post by me on Jun 29, 2022 10:26:47 GMT -5
|
|
MarioGS
Made It and Played It VIP 
PhD in Wheeloffortunology
Posts: 14,931
Airdate: 03/25/2016
Winnings: $76,086
SPIN ID: RS1363545
|
Post by MarioGS on Jun 29, 2022 10:41:53 GMT -5
^ Nice work, but the example episode page you linked is missing the first Toss-Up.
|
|
me
Posts: 9
|
Post by me on Oct 3, 2022 17:32:57 GMT -5
The data does not really matter right now. It can be added at any time. I usually miss toss up puzzles so I do not have them. But, again, they can be dropped in easily. This stage is getting everything working. Data and design come later. This is really about showing how easily accessible the data can be with proper database. It will not look like this either. I plan to have non-tv versions as live lists that can add or substract categories as desired. I really need to get all the models as tight as possible first. Added people www.wofdb.com/peopleShows people affiliated with WOF. What to know the puzzles for the episode snoop dogg appeared in, but you do not know the season or episode number? Click his name and get a list of all one of his episodes. www.wofdb.com/people/snoop-doggI will add a hover info to the episodes at some point so it can show air date, vtr, production code, categories, and such things when you mouse over each episode. The people pages are designed to use colors to identify each show role. The host could be blue, for example, and the co-host could be green. This would easily allow people to find the episodes where Vanna hosted because they would be a different color. I need to update the CSS for that though. Still not ready to release the edit/add functions. More under the hood stuff first. Rewriting the search to make live searching possible and give more options. Still figuring out the best way to add notes and comments to episodes. |
|
|
|
Post by notexplosive on Nov 11, 2022 13:07:04 GMT -5
What I personally really want is either:
- an API so I can query the database directly (ie: "give me all puzzles and categories from 2015-2020, excluding toss-up rounds")
- or better yet a giant CSV file of every single puzzle.
I'm making a Wheel of Fortune inspired "competitive hangman" game and in looking for puzzles I came upon the Compendium. So far I've just been copy-pasting the HTML tables into excel and stitching them together, but that's pretty time consuming.
|
|
|
|
Post by woffan9821 on Nov 11, 2022 16:41:09 GMT -5
|
|
me
Posts: 9
|
Post by me on Dec 5, 2022 5:43:50 GMT -5
People wanted episode by number as aired. www.wofdb.com/browse/celebrity-wheel-of-fortune/episode-number/31www.wofdb.com/browse/wheel-of-fortune-syndication/episode-number/7600It works, but it will require a bit of rewriting as this was never an intended feature. The episode numbers are being created by database (just row_number'ing it). They should be correct as I think all episode are in the DB (though puzzles are not included yet). Other than that, it is mostly fixing problems and improving function. What I personally really want is either: - an API so I can query the database directly (ie: "give me all puzzles and categories from 2015-2020, excluding toss-up rounds") - or better yet a giant CSV file of every single puzzle. I'm making a Wheel of Fortune inspired "competitive hangman" game and in looking for puzzles I came upon the Compendium. So far I've just been copy-pasting the HTML tables into excel and stitching them together, but that's pretty time consuming. The intent is to make data for every page on the site available as JSON so information can easily be used for personal projects. This is only implemented after the site is mostly finalized though. |
|
me
Posts: 9
|
Post by me on Dec 27, 2022 14:18:36 GMT -5
creating an api for wheel of fortune database puzzle endpoint: GET wofdb.com/api/beta/puzzle currently should be functional parameters: | versionId (list or range of integers) | the id of version you want to search with. Syndication is 1. List through version endpoint (does not exit yet). Multiple IDs can be separated with pipe (1|3|5). No spaces are allowed. A range of IDs can be separated using a hyphen. Range and pipe can be used together: 1|4-5 or 1-3|9-14 | | versionSlug (string) | slug of version you want. example: wheel-of-fortune-syndication. Multiple slugs can be separated with pipes. No spaces. | | categoryId (list or range of integers) | the id of category desired. List would be found through category endpoint that does not exist yet. Multiple IDs can be separated with pipes. No spaces are allowed. A range of IDs can be separate using a hyphen. 1-2 or 1-3|5 | | categorySlug (string) | slug of category you want. example: wheel-of-fortune-syndication. Multiple slugs can be separated with pipe characters. No spaces. | | from (date yyyy-mm-dd) | start date of airdates to search. Most episodes are currently missing airdates so it is of limited use. | | until (date yyyy-mm-dd) | end date of airdates to search. | | minLength (integer) | Minimum number of letters in a puzzle. Does not include spaces or punctuation (or it should not at least). | | maxLength (integer) | Maximum number of letters in a puzzle. Does not include spaces or punctuation (or it should not at least). | | page (integer) | page you want to start with. Default is 1. | | rows (integer) | Maximum number of rows to return. Default is 50. Maximum is 250. |
example: www.wofdb.com/api/beta/puzzle?versionSlug=wheel-of-fortune-syndication&categorySlug=person|people|proper-name&maxLength=10&from=2022-03-01&until=2022-03-31Returns puzzles that: - appeared on Wheel of Fortune (Syndication)
- category is person, people, or proper name
- are no more than 10 letters long
- airdate of episode was between 01 March 2022 and 31 March 2022
these are what it has in db currently: {
"meta": {
"httpRequests": {
"versionSlug": [
"wheel-of-fortune-syndication"
],
"from": "2022-03-01",
"until": "2022-03-31",
"category": [
"person",
"people",
"proper-name"
],
"maxLength": "10"
},
"total": 4,
"currentPage": 1,
"maxPage": 1
},
"data": {
"0": {
"puzzle": "wallflower",
"category": {
"name": "person",
"description": "a person; not a proper name"
},
"episodeInfo": {
"season": "39",
"episode": "133",
"airdate": "2022-03-16"
},
"version": {
"name": "Wheel of Fortune (syndication)",
"type": "tv show"
}
},
"1": {
"puzzle": "juniors",
"category": {
"name": "people",
"description": "Plural of person. Not proper names."
},
"episodeInfo": {
"season": "39",
"episode": "136",
"airdate": "2022-03-21"
},
"version": {
"name": "Wheel of Fortune (syndication)",
"type": "tv show"
}
},
"2": {
"puzzle": "sophomores",
"category": {
"name": "people",
"description": "Plural of person. Not proper names."
},
"episodeInfo": {
"season": "39",
"episode": "136",
"airdate": "2022-03-21"
},
"version": {
"name": "Wheel of Fortune (syndication)",
"type": "tv show"
}
},
"3": {
"puzzle": "zendaya",
"category": {
"name": "proper name",
"description": "A specific person or place"
},
"episodeInfo": {
"season": "39",
"episode": "139",
"airdate": "2022-03-24"
},
"version": {
"name": "Wheel of Fortune (syndication)",
"type": "tv show"
}
}
}
}In situations with extra data, like a crossword clue, that is included in the response: "0": {
"puzzle": "bill boar rice life",
"category": {
"name": "crossword",
"description": "multiple words connected by shared letters; all words fit crossword style clue"
},
"clue": "wild _____",
"episodeInfo": {
"season": "39",
"episode": "143",
"airdate": "2022-03-30"
},
"version": {
"name": "Wheel of Fortune (syndication)",
"type": "tv show"
}
},other parameters not yet available until site is updated again: episode number (based on date aired), round, version type (tv show, video game, board game), exclude bonus round, exclude toss up, and sort order of results (order by airdate desc or puzzle asc or random). response is currently only json. any criticism or requests would be appreciated. |
|
me
Posts: 9
|
Post by me on Dec 30, 2022 9:52:35 GMT -5
small changes: length is now total letters. minLetters and maxLetters are length excluding punctuation and spaces. response types: xml (application/xml) and json (application/json). parameter list: keyword string. separated multiple keywords by pipe. SQL LIKE operator so finds result anywhere in puzzle "can" returns "can", "toucan", and "cannot". versionSlug string. separate multiple versions by pipe versionId INT or range of INT separated by dash or pipe separated INT or a combination of them season INT or range of INT separated by dash or pipe separated INT or a combination of them episode INT or range of INT separated by dash or pipe separated INT or a combination of them from DATE yyyy-mm-dd until DATE yyyy-mm-dd categorySlug string. separate multiple cats by pipe categoryId INT or range of INT separated by dash or pipe separated INT or a combination of them noBonus BOOLEAN true/false 1/0 noTossUp BOOLEAN true/false 1/0 noCrossword BOOLEAN true/false 1/0 minLetters INT maxLetters INT minLength INT maxLength INT minWords INT maxWords INT sort random/rand, date, maybe other stuff later (a random puzzle that is not bonus, crossword, or tossup from syndication since 2022-01-01: wofdb.com/api/beta/puzzle?rows=1&sort=random&noTossUp=1&noBonus=1&noCrossword=1&versionId=1&from=2022-1)sortOrder ASC/DESC rows INT page INT showDetails BOOLEAN true/false 1/0 language and country work as well (2 character iso codes), but the only data is US and EN right now so they do nothing. needs some more validation and error handling. |
|
me
Posts: 9
|
Post by me on Jan 13, 2023 12:21:21 GMT -5
category endpoint: parameters keyword string. separated multiple keywords by pipe. SQL LIKE operator so finds result anywhere in puzzle "can" returns "can", "toucan", and "cannot". categorySlug string. separate multiple versions by pipe categoryId INT or range of INT separated by dash or pipe separated INT or a combination of them versionSlug string. separate multiple versions by pipe versionId INT or range of INT separated by dash or pipe separated INT or a combination of them season INT or range of INT separated by dash or pipe separated INT or a combination of them. only valid for tv shows from DATE yyyy-mm-dd (only valid for tv shows right now.) until DATE yyyy-mm-dd (only valid for tv shows right now.) showDetails BOOLEAN true/false 1/0 includes more information, like category id, slug, and description, which would be useful for queries using other endpoints (categoryId for puzzle, for example). default false showStats BOOLEAN true/false 1/0 avg, min, max puzzle length and average words for each category. default false need to add rounds to this probably. example search: wofdb.com/api/beta/category?versionId=1&season=39&showStats=1syndicated season 39 categories with stats wofdb.com/api/beta/category?keyword=thing&showDetails=1categories containing the word "thing" |
|
|
|
Post by benb620 on Jun 26, 2023 10:37:44 GMT -5
Hey there cool stuff
I'm wondering if you, or anyone, knows of places that have collected details on guesses within a wheel of fortune game? For instance, what letters were guessed at various points or where the wheel landed.
|
|
