I keep getting no results in php, but I can see my query is returning multiple rows in phpMyAdmin; the thing that looks strange to me is that phpMyAdmin says 'showing rows 0-0' in phpMyAdmin even when many rows are shown. My main need is to get the data of each row in php.
I'm working with poker data in tables 'metrics' and 'hands'.
metrics has one row per nickname with 3 relevant columns. Example... nickname, AggPct, numHands
john, 0.3276, 160
mike, 0.4752, 200
saul, 0.4781, 100
hands has one row per poker hand with 2 relevant columns. Example... opponentNickname, netSmallBets
john, 7
john, 4
john, 9
mike, 8
mike, 2
saul, 20
I want a new intermediate table made from hands and metrics to look like this: nickname, netSmallBets, AggPct, numHands
john, 20, 33, 160
mike, 10, 48, 200
saul, 20, 48, 100
Then I want a final table (the final results I need) to look like this:
ev, numHands, AggPct
0.125, 160, 33
0.1, 300, 48
...where 'ev' is the sum of netSmallBets for a group of same AggPct, divided by sum of numHands in that group. i.e. mike and saul (10+20)/(200+100) = 0.1, and john is in his own group 20/160 = 0.125
The query I have is this:
SELECT
SUM(h.netSmallBets)/SUM(m.numHands) ev,
SUM(m.numHands) numHands,
m.AggPct
FROM
(SELECT nickname, ROUND(100*AggPct) AggPct, numHands FROM metrics) m
LEFT OUTER JOIN
(SELECT opponentNickname, SUM(netSmallBets) netSmallBets FROM hands GROUP BY opponentNickname) h
ON m.nickname=h.opponentNickname
GROUP BY m.AggPct
ORDER BY m.AggPct ASC
It seems to work, except that my php document isn't getting any rows returned (I put a counter in the while loop that gets rows, and it never gets hit). When I run the exact same query in phpMyAdmin SQL prompt, it says 'Showing rows 0 - 0 (1 total...', but then it shows results like this...
ev, numHands, AggPct
0.60810810810811, 74, 9
1.6666666666667, 9, 17
1.6666666666667, 6, 19
2.8333333333333, 6, 20
0.74213836477987, 159, 21
-0.37777777777778, 45, 22
I need to be able to do some post-processing on each row individually in my php. Thanks for any help to be able to do this, I greatly appreciate it.
