Title: Table
Last modified: August 22, 2016

---

# Table

 *  Resolved [igadgets](https://wordpress.org/support/users/igadgets/)
 * (@igadgets)
 * [11 years, 2 months ago](https://wordpress.org/support/topic/table-10/)
 * How can i create a view to see this layout:
 * Player Name | Mach 1 predictions | Points| Mach 2 predictions | points| Total
   Score sum
 * [https://wordpress.org/plugins/football-pool/](https://wordpress.org/plugins/football-pool/)

Viewing 3 replies - 1 through 3 (of 3 total)

 *  Plugin Author [AntoineH](https://wordpress.org/support/users/antoineh/)
 * (@antoineh)
 * [11 years, 2 months ago](https://wordpress.org/support/topic/table-10/#post-5844983)
 * 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](http://codex.wordpress.org/Function_Reference/get_userdata)
   function to retrieve the name, or use the public `Football_Pool_Pool->user_name`
   method from the plugin.
 *  Thread Starter [igadgets](https://wordpress.org/support/users/igadgets/)
 * (@igadgets)
 * [10 years, 9 months ago](https://wordpress.org/support/topic/table-10/#post-5845099)
 * 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
 *  Plugin Author [AntoineH](https://wordpress.org/support/users/antoineh/)
 * (@antoineh)
 * [10 years, 9 months ago](https://wordpress.org/support/topic/table-10/#post-5845100)
 * 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.

Viewing 3 replies - 1 through 3 (of 3 total)

The topic ‘Table’ is closed to new replies.

 * ![](https://ps.w.org/football-pool/assets/icon-256x256.png?rev=983880)
 * [Football Pool](https://wordpress.org/plugins/football-pool/)
 * [Frequently Asked Questions](https://wordpress.org/plugins/football-pool/#faq)
 * [Support Threads](https://wordpress.org/support/plugin/football-pool/)
 * [Active Topics](https://wordpress.org/support/plugin/football-pool/active/)
 * [Unresolved Topics](https://wordpress.org/support/plugin/football-pool/unresolved/)
 * [Reviews](https://wordpress.org/support/plugin/football-pool/reviews/)

 * 3 replies
 * 2 participants
 * Last reply from: [AntoineH](https://wordpress.org/support/users/antoineh/)
 * Last activity: [10 years, 9 months ago](https://wordpress.org/support/topic/table-10/#post-5845100)
 * Status: resolved