Search Postgresql Archives

Re: Saving score of 3 players into a table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 26/10/11 08:32, Alexander Farber wrote:
Hello,

I'm trying to save results of card game with 3 players into a table.

It is bad enough, that I had to introduce
3 columns for user ids: id0, id1, id2 and
3 columns for their scores: money0, money1, money2 -

         create table pref_results (
                 id0 varchar(32) references pref_users,
                 id1 varchar(32) references pref_users,
                 id2 varchar(32) references pref_users,
                 money0 integer not null,
                 money1 integer not null,
                 money2 integer not null,
                 rounds integer not null,
                 finished timestamp default current_timestamp
         );

But now I've also realized, that I don't know,
how to join that table with the pref_users,
so that I get first_name for each of 3 players -

         $sth = $db->prepare("
         select
              id0,
              id1,
              id2,
              money0,
              money1,
              money2,
              rounds,
              to_char(finished,'DD.MM.YYYY') as day
         from pref_results
         where finished>  now() - interval '1 week'
              and (id0=? or id1=? or id2=?)
        ");
         $sth->execute(array($id, $id, $id));

         while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
                # XXX print the table with day, first_names and money
         }

I'm probably doing something wrong here?

Thank you
Alex

Hi,

I agree with the othet replies that you should have the results in separate tables, but I have tested out something similar to what you want below.

First, couple of points to note:
(1) the interval logic you have, selects finish times within one week of the current date and time. So if you run it at 11 am, then you miss records at 10 am 7 days ago, but pick up records that finish at 11:30am on that day! (2) I changed the format of the date since numerically there is sometimes ambiguity between dd.mm.yyyy and mm.dd.yyyy as American use the latter (9/11 is November 9th to me, but to an American it is September 11th)
(3) I have used an explicity money type
(4) I suggest that date/times should be stored in the database in GMT, so that the dates can be dislayed appropriately in any l,ocale, hence the use of 'timstamptz' (timestamp with timezone).

CREATE TABLE player
(
    id          int PRIMARY KEY,
    first_name  text NOT NULL,
    last_name   text NOT NULL,
    UNIQUE (first_name, last_name)
);


CREATE TABLE pref_results
(
    id          int PRIMARY KEY,
    rounds      int NOT NULL,
    finished    timestamptz NOT NULL,
    player0_id  int NOT NULL REFERENCES player(id),
    money0      money NOT NULL,
    player1_id  int NOT NULL REFERENCES player(id),
    money1      money NOT NULL,
    player2_id  int NOT NULL REFERENCES player(id),
    money2      money NOT NULL,
    CONSTRAINT player0_player1_same CHECK (player0_id != player1_id),
    CONSTRAINT player1_player2_same CHECK (player1_id != player2_id),
    CONSTRAINT player2_player0_same CHECK (player2_id != player0_id)

);


SELECT
(SELECT first_name FROM player WHERE player.id = pr.player0_id) AS player0, (SELECT first_name FROM player WHERE player.id = pr.player1_id) AS player1, (SELECT first_name FROM player WHERE player.id = pr.player2_id) AS player2,
    pr.money0,
    pr.money1,
    pr.money2,
    pr.rounds,
    pr.finished,
    to_char(pr.finished,'DD-MON-YYYY') as day
FROM
    pref_results pr
WHERE
    pr.finished > now() - interval '1 week'
ORDER BY
    pr.finished,
    pr.rounds;


Cheers,
Gavin

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux