Allegro.cc - Online Community

Allegro.cc Forums » Programming Questions » [php/mysql] Storing user's completed puzzles

This thread is locked; no one can reply to it. rss feed Print
[php/mysql] Storing user's completed puzzles
CGamesPlay
Member #2,559
July 2002
avatar

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?

--
Tomasu: Every time you read this: hugging!

Ryan Patterson - <http://cgamesplay.com/>

ReyBrujo
Moderator
January 2001
avatar

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 :))

--
RB
光子「あたしただ…奪う側に回ろうと思っただけよ」
Mitsuko's last words, Battle Royale

Matthew Leverton
Supreme Loser
January 1999
avatar

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.

CGamesPlay
Member #2,559
July 2002
avatar

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?

--
Tomasu: Every time you read this: hugging!

Ryan Patterson - <http://cgamesplay.com/>

Matthew Leverton
Supreme Loser
January 1999
avatar

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
avatar

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?

--
Tomasu: Every time you read this: hugging!

Ryan Patterson - <http://cgamesplay.com/>

ReyBrujo
Moderator
January 2001
avatar

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.

--
RB
光子「あたしただ…奪う側に回ろうと思っただけよ」
Mitsuko's last words, Battle Royale

CGamesPlay
Member #2,559
July 2002
avatar

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.

--
Tomasu: Every time you read this: hugging!

Ryan Patterson - <http://cgamesplay.com/>

ReyBrujo
Moderator
January 2001
avatar

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.

--
RB
光子「あたしただ…奪う側に回ろうと思っただけよ」
Mitsuko's last words, Battle Royale

Matthew Leverton
Supreme Loser
January 1999
avatar

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
avatar

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.
3function 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>&nbsp;</td>\n";
27 $this_row++;
28 }
29 echo "</tr></table>\n";
30}

--
Tomasu: Every time you read this: hugging!

Ryan Patterson - <http://cgamesplay.com/>

ReyBrujo
Moderator
January 2001
avatar

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.

--
RB
光子「あたしただ…奪う側に回ろうと思っただけよ」
Mitsuko's last words, Battle Royale

CGamesPlay
Member #2,559
July 2002
avatar

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.

--
Tomasu: Every time you read this: hugging!

Ryan Patterson - <http://cgamesplay.com/>

ReyBrujo
Moderator
January 2001
avatar

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

--
RB
光子「あたしただ…奪う側に回ろうと思っただけよ」
Mitsuko's last words, Battle Royale

CGamesPlay
Member #2,559
July 2002
avatar

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 :)

--
Tomasu: Every time you read this: hugging!

Ryan Patterson - <http://cgamesplay.com/>

ReyBrujo
Moderator
January 2001
avatar

(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
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.

--
RB
光子「あたしただ…奪う側に回ろうと思っただけよ」
Mitsuko's last words, Battle Royale

CGamesPlay
Member #2,559
July 2002
avatar

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?

--
Tomasu: Every time you read this: hugging!

Ryan Patterson - <http://cgamesplay.com/>

ReyBrujo
Moderator
January 2001
avatar

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.

--
RB
光子「あたしただ…奪う側に回ろうと思っただけよ」
Mitsuko's last words, Battle Royale

CGamesPlay
Member #2,559
July 2002
avatar

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!

--
Tomasu: Every time you read this: hugging!

Ryan Patterson - <http://cgamesplay.com/>

Go to: