Table

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.