You can query the scorehistory table and the predictions table. They contain the match predictions and the calculated scores for all users. Because both tables are linked to a user_id instead of a name, you can use the get_userdata function to retrieve the name, or use the public Football_Pool_Pool->user_name method from the plugin.
Hi AntoneH, i almost finish creating the sql query but the problem i have is that i list matches in the same row:
I need this:
/////////////////Game One////////////////////Game Two
Name///Home////Visitor////Score////Home////Visitor///Score
a //////////2 //////////0 //////////10 //////////0 //////////1 //////////0
b //////////0 //////////1 ///////////0 ///////////0 //////////1 //////////0
c //////////1 //////////0 //////////15 //////////0 //////////1 //////////0
and what im getting with sql is like this
Nam////Home////Visitor/////Score
a //////////2 //////////0 //////////10
b //////////0 //////////1 //////////0
c //////////1 //////////0 //////////15
a //////////1 //////////0 //////////0
b //////////1 //////////0 //////////0
c //////////1 //////////0 //////////0
this is the sql query
SELECT wus.display_name as Name , pre.home_score as Home1 , pre.away_score as Away1 FROM pool_wp_league_users lus Left JOIN wp_users wus
ON lus.user_id=wus.id Left JOIN pool_wp_predictions pre ON lus.user_id= pre.user_id Left JOIN pool_wp_matches mat ON pre.match_id= mat.id where pre.match_id=163
union all
SELECT wus.display_name as Name2 , pre.home_score as Home2 , pre.away_score as Away2 FROM pool_wp_league_users lus Left JOIN wp_users wus
ON lus.user_id=wus.id Left JOIN pool_wp_predictions pre ON lus.user_id= pre.user_id Left JOIN pool_wp_matches mat ON pre.match_id= mat.id where pre.match_id=164
I don’t know how mysql optimizes your query internally, but instead of using a UNION I would just use one SELECT to get all match info. Like so (not tested though):
SELECT wus.display_name as Name , pre.home_score as Home1 , pre.away_score as Away1 FROM pool_wp_league_users lus
Left JOIN wp_users wus
ON lus.user_id=wus.id
Left JOIN pool_wp_predictions pre
ON lus.user_id= pre.user_id
Left JOIN pool_wp_matches mat
ON pre.match_id= mat.id
where pre.match_id IN (163, 164)
If you loop through the query results, you can build a multidimensional array with the match results and group match info for one user. In pseudo code:
array of users (
user 1 ( array of match results )
user 2 ( array of match results )
)
You can then use this array to display the results.
If you want the query result to exactly match your output, then I think the only option is to use something that is called ‘transposing’. If you Google on ‘MySQL transpose’ you can find lots of examples of this technique.
