Jump to content

Rank by multiple fields


craygo
Go to solution Solved by Psycho,

Recommended Posts

I am writing a golf league management system and one request is to give points for lowest net score. Simple enough. Now when there is a tie I need to go back starting at the first hole and get the lowest score and if a tie move to the next hole and so on. Also there can be more than one division so I have the division seperated.

 

Here is the array containing the players that have tied. In the example below A is the division the next is the Players unique id and ppoints is what they would have gotten if there was no tie. Not all this information is needed but have it in the array until the script is complete. The s_id is needed to update the database which has already been populated with the scores.

Array
(
    [A] => Array
        (
            [193] => Array
                (
                    [s_id] => 9
                    [pid] => 193
                    [ppoints] => 6
                    [net] => 31
                    [tie] => yes
                )

            [199] => Array
                (
                    [s_id] => 10
                    [pid] => 199
                    [ppoints] => 7
                    [net] => 31
                    [tie] => yes
                )

            [191] => Array
                (
                    [s_id] => 5
                    [pid] => 191
                    [ppoints] => 5
                    [net] => 31
                    [tie] => yes
                )

        )

    [B] => Array
        (
            [202] => Array
                (
                    [s_id] => 8
                    [pid] => 202
                    [ppoints] => 6
                    [net] => 25
                    [tie] => yes
                )

            [194] => Array
                (
                    [s_id] => 11
                    [pid] => 194
                    [ppoints] => 7
                    [net] => 25
                    [tie] => yes
                )

        )

)

This array is the strokes achieved by each golfer. So you have the unique id and then h1 is hole1 and so on.

Array
(
    [189] => Array
        (
            [h1] => 4
            [h2] => 4
            [h3] => 5
            [h4] => 4
            [h5] => 5
            [h6] => 4
            [h7] => 3
            [h8] => 5
            [h9] => 5
        )

    [203] => Array
        (
            [h1] => 4
            [h2] => 4
            [h3] => 5
            [h4] => 5
            [h5] => 5
            [h6] => 5
            [h7] => 4
            [h8] => 5
            [h9] => 5
        )

    [190] => Array
        (
            [h1] => 4
            [h2] => 4
            [h3] => 4
            [h4] => 4
            [h5] => 4
            [h6] => 4
            [h7] => 4
            [h8] => 4
            [h9] => 4
        )

    [204] => Array
        (
            [h1] => 5
            [h2] => 4
            [h3] => 4
            [h4] => 4
            [h5] => 5
            [h6] => 5
            [h7] => 5
            [h8] => 4
            [h9] => 6
        )

    [191] => Array
        (
            [h1] => 4
            [h2] => 4
            [h3] => 4
            [h4] => 3
            [h5] => 4
            [h6] => 4
            [h7] => 4
            [h8] => 4
            [h9] => 5
        )

    [201] => Array
        (
            [h1] => 4
            [h2] => 4
            [h3] => 4
            [h4] => 4
            [h5] => 4
            [h6] => 4
            [h7] => 4
            [h8] => 4
            [h9] => 4
        )

    [192] => Array
        (
            [h1] => 4
            [h2] => 4
            [h3] => 4
            [h4] => 4
            [h5] => 4
            [h6] => 4
            [h7] => 4
            [h8] => 4
            [h9] => 4
        )

    [202] => Array
        (
            [h1] => 4
            [h2] => 4
            [h3] => 4
            [h4] => 5
            [h5] => 4
            [h6] => 4
            [h7] => 4
            [h8] => 4
            [h9] => 4
        )

    [193] => Array
        (
            [h1] => 4
            [h2] => 5
            [h3] => 4
            [h4] => 4
            [h5] => 4
            [h6] => 4
            [h7] => 4
            [h8] => 4
            [h9] => 4
        )

    [199] => Array
        (
            [h1] => 5
            [h2] => 4
            [h3] => 4
            [h4] => 4
            [h5] => 4
            [h6] => 4
            [h7] => 4
            [h8] => 4
            [h9] => 4
        )

    [194] => Array
        (
            [h1] => 4
            [h2] => 4
            [h3] => 5
            [h4] => 4
            [h5] => 4
            [h6] => 4
            [h7] => 4
            [h8] => 4
            [h9] => 4
        )

    [200] => Array
        (
            [h1] => 4
            [h2] => 4
            [h3] => 4
            [h4] => 4
            [h5] => 4
            [h6] => 4
            [h7] => 4
            [h8] => 4
            [h9] => 4
        )

    [195] => Array
        (
            [h1] => 5
            [h2] => 5
            [h3] => 5
            [h4] => 5
            [h5] => 5
            [h6] => 5
            [h7] => 5
            [h8] => 5
            [h9] => 5
        )

    [197] => Array
        (
            [h1] => 5
            [h2] => 5
            [h3] => 5
            [h4] => 5
            [h5] => 5
            [h6] => 5
            [h7] => 5
            [h8] => 5
            [h9] => 6
        )

    [196] => Array
        (
            [h1] => 5
            [h2] => 5
            [h3] => 5
            [h4] => 5
            [h5] => 5
            [h6] => 5
            [h7] => 5
            [h8] => 5
            [h9] => 5
        )

    [198] => Array
        (
            [h1] => 5
            [h2] => 5
            [h3] => 5
            [h4] => 5
            [h5] => 5
            [h6] => 5
            [h7] => 5
            [h8] => 5
            [h9] => 5
        )

)

So here's what needs to happen. Those 3 in the first division who have ties need to compare scores. So first hole 193 and 191 got 4 sonot they move to the second hole. 191 got a 4 so he ranks first so 193 would rank second and 199 would be third.

 

I need a loop to compare all thrugh the 9 holes just in case it goes till the end.

 

Hope I explained it well enough.

 

Thanks in advance for the help.

Link to comment
Share on other sites

Isn't this data in a database? If so, you should be sorting the data when you retrieve it from the database (i.e. the SELECT query). This can be done via an array, but I don't want to waste time on a solution to sort the array if this is, in fact, derived from a database.

Link to comment
Share on other sites

All this data starts off as arrays from the scorcard(form). The data gets inserted into the database earlier in the script. I am pretty good at queries but haven't been able to think of a way to get this one to work. I figured since I had the data already why not try using the arrays.

 

If you have an idea on a query that would work I'm all ears. I can definatly go back to the database to get the data.

 

Thanks

 

Ray

Link to comment
Share on other sites

Forgot here is a dump of the table.

DROP TABLE IF EXISTS `scorecards`;
CREATE TABLE IF NOT EXISTS `scorecards` (
  `sc_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `dateid` int(11) NOT NULL DEFAULT '0',
  `playid` int(11) NOT NULL DEFAULT '0',
  `h1` int(2) NOT NULL DEFAULT '0',
  `h1p` float(2,1) NOT NULL DEFAULT '0.0',
  `h2` int(2) NOT NULL DEFAULT '0',
  `h2p` float(2,1) NOT NULL DEFAULT '0.0',
  `h3` int(2) NOT NULL DEFAULT '0',
  `h3p` float(2,1) NOT NULL DEFAULT '0.0',
  `h4` int(2) NOT NULL DEFAULT '0',
  `h4p` float(2,1) NOT NULL DEFAULT '0.0',
  `h5` int(2) NOT NULL DEFAULT '0',
  `h5p` float(2,1) NOT NULL DEFAULT '0.0',
  `h6` int(2) NOT NULL,
  `h6p` float(2,1) NOT NULL DEFAULT '0.0',
  `h7` int(2) NOT NULL,
  `h7p` float(2,1) NOT NULL DEFAULT '0.0',
  `h8` int(2) NOT NULL,
  `h8p` float(2,1) NOT NULL DEFAULT '0.0',
  `h9` int(2) NOT NULL,
  `h9p` float(2,1) NOT NULL DEFAULT '0.0',
  `gross` int(2) NOT NULL DEFAULT '0',
  `net` int(2) NOT NULL DEFAULT '0',
  `adj_gross` int(2) NOT NULL DEFAULT '0',
  `tpoints` float(4,1) NOT NULL DEFAULT '0.0',
  `ppoints` float(4,1) NOT NULL,
  `schandicap` float(5,2) NOT NULL DEFAULT '0.00',
  `fill_in` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sc_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;

--
-- Dumping data for table `scorecards`
--

INSERT INTO `scorecards` (`sc_id`, `dateid`, `playid`, `h1`, `h1p`, `h2`, `h2p`, `h3`, `h3p`, `h4`, `h4p`, `h5`, `h5p`, `h6`, `h6p`, `h7`, `h7p`, `h8`, `h8p`, `h9`, `h9p`, `gross`, `net`, `adj_gross`, `tpoints`, `ppoints`, `schandicap`, `fill_in`) VALUES
(1, 1, 189, 4, 0.5, 4, 0.0, 5, 0.0, 4, 1.0, 5, 0.5, 4, 1.0, 3, 1.0, 5, 0.5, 5, 0.5, 39, 34, 39, 5.0, 0.0, 5.00, 0),
(2, 1, 203, 4, 0.5, 4, 1.0, 5, 1.0, 5, 0.0, 5, 0.5, 5, 0.0, 4, 0.0, 5, 0.5, 5, 0.5, 42, 35, 42, 4.0, 0.0, 7.00, 0),
(3, 1, 190, 4, 0.5, 4, 0.0, 4, 0.0, 4, 0.5, 4, 0.5, 4, 0.5, 4, 0.5, 4, 0.0, 4, 1.0, 36, 30, 36, 3.5, 0.0, 6.00, 0),
(4, 1, 204, 5, 0.5, 4, 1.0, 4, 1.0, 4, 0.5, 5, 0.5, 5, 0.5, 5, 0.5, 4, 1.0, 6, 0.0, 42, 28, 42, 5.5, 0.0, 14.00, 0),
(5, 1, 191, 4, 0.5, 4, 0.0, 4, 0.0, 3, 1.0, 4, 0.5, 4, 0.5, 4, 0.5, 4, 0.5, 5, 0.0, 36, 31, 36, 3.5, 0.0, 5.00, 0),
(6, 1, 201, 4, 0.5, 4, 1.0, 4, 1.0, 4, 0.0, 4, 0.5, 4, 0.5, 4, 0.5, 4, 0.5, 4, 1.0, 36, 29, 36, 5.5, 0.0, 7.00, 0),
(7, 1, 192, 4, 0.5, 4, 0.0, 4, 0.0, 4, 1.0, 4, 0.5, 4, 0.5, 4, 0.5, 4, 0.5, 4, 0.5, 36, 26, 36, 4.0, 0.0, 10.00, 0),
(8, 1, 202, 4, 0.5, 4, 1.0, 4, 1.0, 5, 0.0, 4, 0.5, 4, 0.5, 4, 0.5, 4, 0.5, 4, 0.5, 37, 25, 37, 5.0, 0.0, 12.00, 0),
(9, 1, 193, 4, 1.0, 5, 0.0, 4, 0.5, 4, 0.5, 4, 0.5, 4, 0.5, 4, 0.5, 4, 0.5, 4, 0.5, 37, 31, 37, 4.5, 0.0, 6.00, 0),
(10, 1, 199, 5, 0.0, 4, 1.0, 4, 0.5, 4, 0.5, 4, 0.5, 4, 0.5, 4, 0.5, 4, 0.5, 4, 0.5, 37, 31, 37, 4.5, 0.0, 6.00, 0),
(11, 1, 194, 4, 0.5, 4, 0.0, 5, 0.0, 4, 0.5, 4, 0.5, 4, 0.0, 4, 0.5, 4, 0.5, 4, 0.0, 37, 25, 37, 2.5, 0.0, 12.00, 0),
(12, 1, 200, 4, 0.5, 4, 1.0, 4, 1.0, 4, 0.5, 4, 0.5, 4, 1.0, 4, 0.5, 4, 0.5, 4, 1.0, 36, 20, 36, 6.5, 0.0, 16.00, 0),
(13, 1, 195, 5, 0.5, 5, 0.5, 5, 0.5, 5, 0.5, 5, 0.5, 5, 0.5, 5, 0.5, 5, 0.5, 5, 1.0, 45, 37, 45, 5.0, 0.0, 8.00, 0),
(14, 1, 197, 5, 0.5, 5, 0.5, 5, 0.5, 5, 0.5, 5, 0.5, 5, 0.5, 5, 0.5, 5, 0.5, 6, 0.0, 46, 38, 46, 4.0, 0.0, 8.00, 0),
(15, 1, 196, 5, 0.5, 5, 0.0, 5, 0.0, 5, 0.5, 5, 0.5, 5, 0.0, 5, 0.5, 5, 0.5, 5, 0.0, 45, 31, 45, 2.5, 0.0, 14.00, 0),
(16, 1, 198, 5, 0.5, 5, 1.0, 5, 1.0, 5, 0.5, 5, 0.5, 5, 1.0, 5, 0.5, 5, 0.5, 5, 1.0, 45, 27, 45, 6.5, 0.0, 18.00, 0);

h1, h2, etc are the strokes for each hole.

Link to comment
Share on other sites

  • Solution

Typically, you shouldn't have calculated values in your tables (e.g. gross & net). you should calculate in your queries. Lots of reasons why that I won't go into. Plus, the "shots" would make more sense in a separate table with an individual record for each shot - instead of separate columns.

 

I don't see a value for division in the table, so I didn't add any logic for that. The following query will do what you want. If first sorts on the 'net' score, then it sorts on h1, h2, etc.

SELECT sc_id, playid, ppoints, net,
       h1, h2, h3, h4, h5, h6, h7, h8, h9 -- This line can be removed if not needed
 
FROM scorecards
 
ORDER BY net, h1, h2, h3, h4, h5, h6, h7, h8, h9
Edited by Psycho
Link to comment
Share on other sites

Yes i have heard of normalization and use it quite extensively.

 

At the time this seemed like a decent way to hold the data, which was quite a while ago and since it has been in use for over 4 years I don't really feel like reinventing the wheel. Everything worked fine up until the new scoring piece was implemented. So just trying to work with what I got.

 

Ray

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.