Re: Saving score of 3 players into a table

On Oct 25, 2011, at 15: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?

Likely. Are you only ever going to have three players per table? Seems unlikely.

Without knowing anything else about your application, I suspect you need more tables:
a games table
a games_players table with each row associating a single player with a game. A three-player game has three rows per game. A four-player game would have four.

Depending on your app, you might also have finished_games and game_player_results tables.

Michael Glaesemann
grzm seespotcode net