Re: Saving score of 3 players into a table - Mailing list pgsql-general

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