Rank by multiple fields

craygo · April 14, 2015

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.

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.

Psycho · April 14, 2015

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.

craygo · April 14, 2015

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

craygo · April 14, 2015

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.

Barand · April 14, 2015

Now we see why you're using arrays - your data is stored like a spreadsheet. Does "normalization" mean anything to you?

Psycho · April 14, 2015

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 April 14, 2015 by Psycho

craygo · April 14, 2015

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

Psycho · April 14, 2015

So just trying to work with what I got.

What I posted will work with what you have currenlty

craygo · April 15, 2015

Thanks Psycho,

It's so simple I just didn't even think of it.

Appreciate your help.

Ray

Sign In

Rank by multiple fields

Recommended Posts

craygo

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

craygo

Link to comment

Share on other sites

craygo

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

craygo

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

craygo

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information