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
>
You may want to cosider:
finished > CURRENT_DATE - interval '1 week'