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?
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 )
Something like this, although I didn't really stop to think about good tables names:
puzzle
int id
varchar name
question
int id
int puzzle_id
varchar question
char 1 answer (t/f)
puzzle_person
int puzzle_id
int person_id
enum status (in progress, completed, etc)
choice
int question_id
int person_id
char 1 choice (t/f)
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.
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?
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.
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?
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.
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.
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.
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.
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:
1 | // void show_gallery(object gal) |
2 | // Draws a gallery of thumbnails from the given recordset. |
3 | function show_gallery($gal) |
4 | { |
5 | global $site, $cur_user; |
6 | |
7 | $this_row = 0; |
8 | |
9 | echo "<table border=\"1\" cellpadding=\"0\" cellspacing=\"3\"><tr>\n"; |
10 | $pic = $gal->fetchRow(DB_FETCHMODE_ASSOC); |
11 | while(isset($pic)) |
12 | { |
13 | echo "<td>\n"; |
14 | // Here I need to print out the status of the puzzle for $cur_user |
15 | var_dump($pic); |
16 | echo "</td>\n"; |
17 | $this_row++; |
18 | if($this_row == $site['picsperrow']) |
19 | { |
20 | echo "</tr><tr>\n"; |
21 | } |
22 | $pic = $gal->fetchRow(DB_FETCHMODE_ASSOC); |
23 | } |
24 | while($this_row < $site['picsperrow']) |
25 | { |
26 | echo "<td> </td>\n"; |
27 | $this_row++; |
28 | } |
29 | echo "</tr></table>\n"; |
30 | } |
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.
Can you tell me the query?
Also, how can I do something like:
SELECT `name` , ( SELECT `dir` FROM `ispy_serieses` WHERE `cat` =1 ) AS `series` FROM `ispy_puzzles`
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.
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
SERIESES (what it is, dunno):
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
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]
My second query selects the relationships:
This is your query without the series table
(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)
SELECT ispy_puzzles.id, ispy_serieses.dir
FROM ispy_puzzles, ispy_serieses
WHERE ispy_puzzles.series = ispy_serieses.id
AND ispy_serieses.id =1
Note that it is the same as WHERE ispy_puzzles.series = 1, no need to link both tables.
(Edited one last time)
If all tables are linked to each other (as I thought in my relation table), you can answer everything with one query.
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?
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.
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):
To select puzzles:
$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]
Oh, and I forgot to make this thread a question so.... NO R 4 U!