How do you find a specific hand in the database?

[WhiteRider]

Ok, I am trying to ferret out ways of identifying the hand other than by hand_no. Which BTW, I was surprised to see hand_no is only found in one 'holdem%' table. Don't know why but I just was expecting to see it more.

hand_no is the "real world" reference number for the hand as generated by the site, but PT3 assigns its own unique reference to each hand - id_hand.

Anyway, something else that surprised me as I am beginning to get better familiar with the data. In the holdem_hand_details table there are two columns named [ holecards_1 ] and [ holecards_2 ]. I full expected this to return one individual card for holecards_1 and one individual card for holecards_2. What I am seeing is two complete hole card sets. In this case A8o & A4o respectively. Don't know exactly what this is supposed to be telling me?

holdem_hand_details isn't a table in the PT3 database. Have you used HoldemLuck, or any other application that writes additional tables to your database? Having two sets of holecards would match an EV function which compares them.

[doco]

Guess I should have said holdem_hand_player_detail instead

I have used HoldemLuck and TourneyLuck.

Apparently, individual 'hole cards' are being parsed from the [ history ] field at run time? Haven't been able to find those in the data. Also, there must be carriage return/line break embedded in the text as well? Pasting the contents of the [ history ] field into Notepad gave a well ordered and readable result. I kind of expected to see one long string. Don't suppose there is a generic parser available on the net somewhere ( VB, .NET, C#, JAVA, ?? ).

[WhiteRider]

holdem_hand_player_detail.holecard_1 and _2 are the IDs of the single holecards.
holdem_hand_player_statistics.id_holecard is the pair of holecards.

See the Holdem Cash Hand stats "Hand" and "Hole 1" & "Hole 2".

[doco]

My apologies to the OP but my curiosity is up now.

Code: Select all

select
    id_hand,
    id_player,
    holecard_1,
    holecard_2,
    ( select c.hole_cards
      from lookup_hole_cards c
      where c.id_holecard = holecard_1 ) as hole_card_1,
    ( select c.hole_cards
      from lookup_hole_cards c
      where c.id_holecard = holecard_2 ) as hole_card_2
from
    holdem_hand_player_detail
where
    id_hand = 1 and id_player = 6;


The script above returns



If I understand correctly, the hole_card_x fields are representative of a range? And not necessarily any cards that are actually held?

Code: Select all

select
    ps.id_hand,
    ps.id_player,
    ps.id_holecard,
    hc.hole_cards
from
    holdem_hand_player_statistics ps INNER JOIN
    lookup_hole_cards hc ON
    ps.id_holecard = hc.id_holecard
where
    id_hand = 1 --  and id_player = 6;


Returns



Which are all of the cards shown real time as displayed below
PokerStars No-Limit Hold'em, $0.05 BB (9 handed) - Poker-Stars Converter Tool from FlopTurnRiver.com

MP3 ($2.93)
CO ($7.86)
Button ($5.61)
SB ($9.03)
Hero (BB) ($10)
Villain1 (UTG) ($7.90)
UTG+1 ($5.92)
Villain2 (MP1) ($1.81)
MP2 ($4.76)

Preflop: Hero is BB with A, 9
Villain1 bets $0.20, 1 fold, Villain2 calls $0.20, 4 folds, SB calls $0.18, 1 fold

Flop: ($0.65) 8, 7, 5 (3 players)
SB checks, Villain1 bets $0.70, Villain2 raises to $1.61 (All-In), 1 fold, Villain1 calls $0.91

Turn: ($3.87) 8 (2 players, 1 all-in)

River: ($3.87) 6 (2 players, 1 all-in)

Total pot: $3.87 | Rake: $0.15

Results:
Villain1 had Q, Q (two pair, Queens and eights).
Villain2 had 7, 7 (full house, sevens over eights).
Outcome: Villain2 won $3.72

I don't see where the 'hole cards' are ever displayed in data separately. Are they parsed for display individually at run time in the UI?

[WhiteRider]

Everything stored in the database is read at import.
Have a look at how "Hand" and "Hole1" stats are formatted - they use different lookups. One looks up a cardpair and one looks up a card.

[doco]

I couldn't find a lookup for card_x. So I made one.

Code: Select all

CREATE TABLE lookup_card(
    card_id integer primary key not null,
    card varchar(2) not null );
--  ---------------------------------------------------------------------------
--  CLUBS
--  ---------------------------------------------------------------------------
INSERT INTO lookup_card( card_id, card )
VALUES( 1, '2c' );
INSERT INTO lookup_card( card_id, card )
VALUES( 2, '3c' );
INSERT INTO lookup_card( card_id, card )
VALUES( 3, '4c' );
INSERT INTO lookup_card( card_id, card )
VALUES( 4, '5c' );
INSERT INTO lookup_card( card_id, card )
VALUES( 5, '6c' );
INSERT INTO lookup_card( card_id, card )
VALUES( 6, '7c' );
INSERT INTO lookup_card( card_id, card )
VALUES( 7, '8c' );
INSERT INTO lookup_card( card_id, card )
VALUES( 8, '9c' );
INSERT INTO lookup_card( card_id, card )
VALUES( 9, 'Tc' );
INSERT INTO lookup_card( card_id, card )
VALUES( 10, 'Jc' );
INSERT INTO lookup_card( card_id, card )
VALUES( 11, 'Qc' );
INSERT INTO lookup_card( card_id, card )
VALUES( 12, 'Kc' );
INSERT INTO lookup_card( card_id, card )
VALUES( 13, 'Ac' );
--  ---------------------------------------------------------------------------
--  DIAMONDS
--  ---------------------------------------------------------------------------
INSERT INTO lookup_card( card_id, card )
VALUES( 14, '2d' );
INSERT INTO lookup_card( card_id, card )
VALUES( 15, '3d' );
INSERT INTO lookup_card( card_id, card )
VALUES( 16, '4d' );
INSERT INTO lookup_card( card_id, card )
VALUES( 17, '5d' );
INSERT INTO lookup_card( card_id, card )
VALUES( 18, '6d' );
INSERT INTO lookup_card( card_id, card )
VALUES( 19, '7d' );
INSERT INTO lookup_card( card_id, card )
VALUES( 20, '8d' );
INSERT INTO lookup_card( card_id, card )
VALUES( 21, '9d' );
INSERT INTO lookup_card( card_id, card )
VALUES( 22, 'Td' );
INSERT INTO lookup_card( card_id, card )
VALUES( 23, 'Jd' );
INSERT INTO lookup_card( card_id, card )
VALUES( 24, 'Kd' );
INSERT INTO lookup_card( card_id, card )
VALUES( 25, 'Qd' );
INSERT INTO lookup_card( card_id, card )
VALUES( 26, 'Ad' );
--  ---------------------------------------------------------------------------
--  HEARTS
--  ---------------------------------------------------------------------------
INSERT INTO lookup_card( card_id, card )
VALUES( 27, '2h' );
INSERT INTO lookup_card( card_id, card )
VALUES( 28, '3h' );
INSERT INTO lookup_card( card_id, card )
VALUES( 29, '4h' );
INSERT INTO lookup_card( card_id, card )
VALUES( 30, '5h' );
INSERT INTO lookup_card( card_id, card )
VALUES( 31, '6h' );
INSERT INTO lookup_card( card_id, card )
VALUES( 32, '7h' );
INSERT INTO lookup_card( card_id, card )
VALUES( 33, '8h' );
INSERT INTO lookup_card( card_id, card )
VALUES( 34, '9h' );
INSERT INTO lookup_card( card_id, card )
VALUES( 35, 'Th' );
INSERT INTO lookup_card( card_id, card )
VALUES( 36, 'Jh' );
INSERT INTO lookup_card( card_id, card )
VALUES( 37, 'Qh' );
INSERT INTO lookup_card( card_id, card )
VALUES( 38, 'Kh' );
INSERT INTO lookup_card( card_id, card )
VALUES( 39, 'Ah' );
--  ---------------------------------------------------------------------------
--  SPADES
--  ---------------------------------------------------------------------------
INSERT INTO lookup_card( card_id, card )
VALUES( 40, '2s' );
INSERT INTO lookup_card( card_id, card )
VALUES( 41, '3s' );
INSERT INTO lookup_card( card_id, card )
VALUES( 42, '4s' );
INSERT INTO lookup_card( card_id, card )
VALUES( 43, '5s' );
INSERT INTO lookup_card( card_id, card )
VALUES( 44, '6s' );
INSERT INTO lookup_card( card_id, card )
VALUES( 45, '7s' );
INSERT INTO lookup_card( card_id, card )
VALUES( 46, '8s' );
INSERT INTO lookup_card( card_id, card )
VALUES( 47, '9s' );
INSERT INTO lookup_card( card_id, card )
VALUES( 48, 'Ts' );
INSERT INTO lookup_card( card_id, card )
VALUES( 49, 'Js' );
INSERT INTO lookup_card( card_id, card )
VALUES( 50, 'Qs' );
INSERT INTO lookup_card( card_id, card )
VALUES( 51, 'Ks' );
INSERT INTO lookup_card( card_id, card )
VALUES( 52, 'As' );
--  ---------------------------------------------------------------------------
select * from lookup_card;


Took a little trial and error until I had the orders and suits right - but it works. Good practice anyway.

Code: Select all

select
    s.id_hand,
    s.hand_no,
    s.date_played,
    ( select card from lookup_card where card_id = s.card_1 ) as card_1,
    ( select card from lookup_card where card_id = s.card_2 ) as card_2,
    ( select card from lookup_card where card_id = s.card_3 ) as card_3,
    ( select card from lookup_card where card_id = s.card_4 ) as card_4,
    ( select card from lookup_card where card_id = s.card_5 ) as card_5
from
    holdem_hand_summary s
where
    id_hand = 1


Results



Looks like the hand history file shows anyway. OK, I'm done - thanks

I think OP will need to learn a bit of SQL and how to use the PGAdmin III utility to find some things and help in creation of report ideas. Using this approach is the only way I could have found certain aspects of a hand from only knowing the card room hand_no

[doco]

I think in the interest of the OP - FINALLY. I have played around with a script that will give some basic information. If you only know the poker site hand_no and would familiarize yourself with the pgAdmin III utility that came when you downloaded Postgres. Paste the script below into the query editor and run.

Code: Select all

SELECT
    s.id_hand,
    s.hand_no,
    s.date_played,
    ( select site.site_name from lookup_sites site where site.id_site = tsum.id_site ) as site,
    t.table_name,
    ( select l.limit_name from holdem_limit l where id_limit = hps.id_limit) as limit,
    hps.position,
    ( select pos.description
      from lookup_positions pos
      where pos.position = hps.position and pos.cnt_players = hps.cnt_players ) as pos_desc,
    ( select p.player_name from player p where p.id_player = hps.id_player ) as player,
    ( select hc.hole_cards from lookup_hole_cards hc where hps.id_holecard = hc.id_holecard ) as saw_crds
FROM
    holdem_hand_summary s INNER JOIN
    holdem_hand_player_statistics hps ON
    s.id_hand = hps.id_hand
        and hps.date_played = s.date_played INNER JOIN
    holdem_table_session_summary tsum ON
    hps.id_player = tsum.id_player
        and hps.id_session = tsum.id_session INNER JOIN
    holdem_table t ON
    tsum.id_table = t.id_table
WHERE
    s.hand_no = 34457318254
--and hps.id_player = ( select id_player from player where player_name = 'astrodon' )
ORDER BY hps.position


Results



There is a bit of redundancy if you just use hand_no as a filter. If you should also know the screen_name then change those in the script and run. Anyway, you should be able to find your hand by filtering from PT3 using screen_name search then filter by limit and site. Then look for your table name, start date, hands, position, etc from PT3 UI.
HTH

[GrassNinja]

I appreciate everyone's input and help. Unfortunately, I wouldn't even know where to begin with SQL, so not really an option for me. I guess there is no "easy" way to find it. Maybe some sort of simple search function can be added to the next update?...hint hint lol.

[WhiteRider]

Otherwise you'll need to create a Custom Report and include the Hand # stat, or add a custom filter in your report to that hand number.
Tutorial - Custom Reports and Statistics

You can create a custom report in the Holdem Cash Hand section and add stats to help you find the session it was in, like Date and Limit.
Then add a filter for:
#Hand ## = 1234
(obviously replace 1234 with the hand number.)

See the tutorial I linked for more information.

[doco]

Actually pretty simple build but one question: where are the saved reports stored?