|
[php/mysql] Storing user's completed puzzles |
CGamesPlay
Member #2,559
July 2002
|
I'm writing a quick little web site to keeps users entertained for a while. they will be offered a list of puzzles, and they can choose to solve any one. I need to store which puzzles they've solved, however. Assuming each puzzle is a simple true or false, what would be the best way to store the information for this in a mysql table? -- Ryan Patterson - <http://cgamesplay.com/> |
ReyBrujo
Moderator
January 2001
|
Three tables for me. A table of users, a table of puzzles, and a relation table linking an user with a puzzle if solved. (Edited: This might be the normalized solution ) -- |
Matthew Leverton
Supreme Loser
January 1999
|
Something like this, although I didn't really stop to think about good tables names: puzzle
question
puzzle_person
choice
The puzzle_person table could hold some denormalized data, such as the score. All the tables could use more fields, like puzzle_create_time, but they are probably all obvious enough. |
CGamesPlay
Member #2,559
July 2002
|
It seems Matthew has given Rey's idea but with 2 more tables and a "status" field. Can you tell me what question, choice, and puzzle_person.status represent? -- Ryan Patterson - <http://cgamesplay.com/> |
Matthew Leverton
Supreme Loser
January 1999
|
choice = what the person chose as the answer. If you are not storing their results, then you wouldn't have the table. question = one to many relationship to the puzzle. it is the question they are being asked. puzzle_person.status is unnecessary if you are not storing results. Otherwise, it would tell you if a person has started a test/quiz/puzzle thingy, but not yet finished. |
CGamesPlay
Member #2,559
July 2002
|
The idea is to make an I Spy game where the user is given an image and must find an object in that image. If they click on the object, they solve the puzzle. Nothing fancy like multiple objects per puzzle or anything. Since this is so simplistic, I can just do with Rey's 3 tables? -- Ryan Patterson - <http://cgamesplay.com/> |
ReyBrujo
Moderator
January 2001
|
To get the current stage, you would need to query the relation table for the MAX value of puzzle id for a determined player. If the game will grow, you might want to add new fields and maybe an extra table as Matthew's one. -- |
CGamesPlay
Member #2,559
July 2002
|
I'm still not seeing why. What I'm trying to do is implement something like [edit: the light bulbs on] the recent threads page, except I only need to know if the user has ever clicked on the thread, not when they did or anything else. -- Ryan Patterson - <http://cgamesplay.com/> |
ReyBrujo
Moderator
January 2001
|
Sure. I mean, if you later want something more complex (in example, give the user three tries for each puzzle), you need a variable in the relation table (or somewhere else) saying how many times the user tried. -- |
Matthew Leverton
Supreme Loser
January 1999
|
Quote: Since this is so simplistic, I can just do with Rey's 3 tables? Yes. I was under the impression that there would be multiple things attached per puzzle. |
CGamesPlay
Member #2,559
July 2002
|
Ok, I'm to the point where I need to query this list now. My table setup is, practically, as follows: users * id puzzles * id * series status * user * puzzle serieses * id * dir Now, I have a recordset of puzzles, and a logged in user. I want to print out a gallery of puzzles, and for each one, print either an empty checkbox image for uncompleted ones, or a checked checkbox image for completed ones. For practical purposes, we can say that if the record (uid, pid) exists in the status table, then we print a full checkbox. What is the best way to query this? A separate query for each puzzle, or one query which returns the status for all the puzzles? My code:
-- Ryan Patterson - <http://cgamesplay.com/> |
ReyBrujo
Moderator
January 2001
|
CGamesPlay said: What is the best way to query this? A separate query for each puzzle, or one query which returns the status for all the puzzles? Ugh, please, if possible ALWAYS bring all the solution with one query. -- |
CGamesPlay
Member #2,559
July 2002
|
Can you tell me the query? Also, how can I do something like: As in, Get a field from a query and use that as a field in another query? I've updated my table layout above. I want to get the dir field from the serieses table where the id matches the puzzle's series field. -- Ryan Patterson - <http://cgamesplay.com/> |
ReyBrujo
Moderator
January 2001
|
If this is the relation: USERS STATUS PUZZLES SERIESES * id ------> * user * series ------> * id * puzzle ------> * id * dir
Then this should bring you the id of the user and all the dir from SELECT USERS.id, SERIESES.dir FROM USERS, STATUS, PUZZLES, SERIESES WHERE USERS.id = STATUS.user AND STATUS.puzzle = PUZZLES.id AND PUZZLES.series = SERIESES.id
-- |
CGamesPlay
Member #2,559
July 2002
|
Sorry, I think I've got you confused Thanks for that query, it answers my first question. Basically, I think I need two queries. The first query selects the puzzles in a category: // Relation ispy_puzzles ispy_serieses * id * id * series ----> * dir SELECT ispy_puzzles.id, ispy_serieses.dir FROM ispy_puzzles, ispy_serieses WHERE ispy_puzzles.series = ispy_serieses.id AND ispy_serieses.id =1 1 is a php variable. [edit] -- Ryan Patterson - <http://cgamesplay.com/> |
ReyBrujo
Moderator
January 2001
|
(Edited from my previous post) As for that SELECT, it is right. You can do it in different ways, like: SELECT table1.name, aux.age FROM table, (SELECT age FROM table2) AS aux SELECT table1.name, (SELECT age FROM table2) FROM table1 (Edited for your last post) Quote: SELECT ispy_puzzles.id, ispy_serieses.dir Note that it is the same as WHERE ispy_puzzles.series = 1, no need to link both tables. (Edited one last time) -- |
CGamesPlay
Member #2,559
July 2002
|
When you say "linked together" do you mean they have some configuration in mysql to link them? If so, can I manage this from phpmyadmin? -- Ryan Patterson - <http://cgamesplay.com/> |
ReyBrujo
Moderator
January 2001
|
No, no, no. This: USERS STATUS PUZZLES SERIESES * id ------> * user * series ------> * id * puzzle ------> * id * dir Each foreign key links one table with another. Everytime you use a foreign key in a query, the motor must create a temporary table linking the foreign keys in the tables. -- |
CGamesPlay
Member #2,559
July 2002
|
Alrighty, I got it all done and working now. Thanks for your help. For posterity's sake (I'm not quite sure what that word means, though): $q = $dbh->query('SELECT '.$db_config["prefix"].'puzzles.name, ' .$db_config["prefix"].'puzzles.id, '.$db_config["prefix"].'puzzles.desc, ' .$db_config["prefix"].'puzzles.file, '.$db_config["prefix"].'serieses.dir FROM ' .$db_config["prefix"].'puzzles, '.$db_config["prefix"].'serieses WHERE ' .$db_config["prefix"].'puzzles.series = '.$db_config["prefix"].'serieses.id LIMIT 0, ' .(int)$site['resultsperpage'].';'); To get relations: $tries = $dbh->getAssoc('SELECT '.$db_config["prefix"].'puzzles.id, ' .$db_config["prefix"].'status.tries FROM '.$db_config["prefix"].'status, ' .$db_config["prefix"].'puzzles WHERE '.$db_config["prefix"].'status.user ='.$cur_user['id'].' AND '.$db_config["prefix"].'status.puzzle = '.$db_config["prefix"].'puzzles.id'); if(isset($tries)) { if(isset($tries[$pic['id']])) { $check = $check_full; unset($tries[$pic['id']]); } }
[edit] -- Ryan Patterson - <http://cgamesplay.com/> |
|