Top 10 counts problems [MySQL5][PHP5.3]

[XRayden]

MySQL 5.1

PHP 5.3

 

Explaination:

We have a stockcar website, and everybody wanted some stats for the shows, so I added an option to enter events/pilots/results.

 

That was for last year, they only required total points, total $ win, and qualifications points for each event, and global.

 

Now they ask us to "count" the number of "win" "top5" and "top10" that a pilot do.

 

I tought i had done it, but at the FIRST event, a problem arrived: a tie

 

so the system i did gave 10 top10's but there was 11 pilots in the top 10!

 

Ok, here's my DB schematics

 

(my names are in french, i'll tell you what they do)

 

courses_evenement (Table for events) (only need to know that it 1 record by events)

 

courses_pilotes is the pilot info tables.

CREATE TABLE IF NOT EXISTS `courses_pilotes` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Numero` varchar(4) NOT NULL COMMENT 'Pilot Number',
  `Nom` varchar(150) NOT NULL COMMENT 'Pilot name',
  `Ville` varchar(100) NOT NULL COMMENT 'Pilot city',
  `PageFR` int(10) unsigned NOT NULL COMMENT 'Pilot page # (fr)',
  `PageEn` int(10) unsigned DEFAULT NULL COMMENT 'Pilot page # (en)',
  `s2011` int(11) unsigned DEFAULT '0' COMMENT 'participation in Season 2011',
  `s2012` int(11) DEFAULT '0' COMMENT 'participation in Season 2012',
  `datemod` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

 

courses_resultats is the raw event/result per pilots data

CREATE TABLE IF NOT EXISTS `courses_resultats` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `event_id` int(10) unsigned NOT NULL COMMENT 'Event ID',
  `pilote_id` int(10) unsigned NOT NULL COMMENT 'Pilot ID',
  `Qualif1` int(11) NOT NULL COMMENT '1st Qualification points',
  `Qualif2` int(11) NOT NULL COMMENT '2nd Qualification points',
  `Finale` int(11) NOT NULL COMMENT 'Final Points',
  `Bonus` int(11) NOT NULL COMMENT 'Bonus Points',
  `tours` int(11) NOT NULL COMMENT 'Number of laps',
  `temps` varchar(15) NOT NULL COMMENT 'Best timelap',
  `gagner` int(11) NOT NULL COMMENT 'Is the winner (not really used)',
  `bilan` text NOT NULL COMMENT 'Text about this entry',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

 

courses_pilotes_points is the compile data of the season for each pilots

CREATE TABLE IF NOT EXISTS `courses_pilotes_points` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `PID` int(10) unsigned NOT NULL COMMENT 'Pilot ID',
  `saison` varchar(4) NOT NULL COMMENT 'Season ',
  `pointsFinale` int(11) NOT NULL COMMENT 'Finals points',
  `pointsQualifs` int(11) NOT NULL COMMENT 'Qualifications points',
  `recru` tinyint(1) NOT NULL COMMENT 'Rookie flag',
  `n_courses` int(11) NOT NULL COMMENT 'number of event',
  `n_victoires` int(11) NOT NULL COMMENT 'Number of wins',
  `n_top5` int(11) NOT NULL COMMENT 'Number of top 5',
  `n_top10` int(11) NOT NULL COMMENT 'Number of top 10',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

 

Right now while "compiling" data for each event of the season for 1 pilot, I use this PHP/MYSQL code:

 

function check_top($quantite,$pid,$saison) {
$top = 0;
$qi = 'SELECT `ID` FROM `courses_evenement` WHERE `saison` = \''.$saison."'";
if(!$de = mysql_query($qi)) { die($qi.'<br>'.mysql_error()); }
while($re = mysql_fetch_assoc($de)) { 
 $cd = 'SELECT `event_id` FROM `courses_resultats` WHERE `event_id` = '.$re['ID'].' AND `pilote_id` = '.$pid.' AND `pilote_id` IN 
     (select * from (SELECT `pilote_id` FROM `courses_resultats` WHERE `event_id` = '.$re['ID'].' ORDER BY  (`Finale`+`Bonus`) DESC LIMIT '.$quantite.')  alias)';
 if(!$cq = mysql_query($cd)) {
	die(mysql_error().'<br>'.$cd);
 } else {
 if(mysql_fetch_assoc($cq)) {
	 $top++;
 }
}
}
return $top;
}

 

As you can see, the MYSQL ask for

each events that is the event that this pilote is in the "quantity" requires... exemple of MYSQL:

SELECT `event_id` FROM `courses_resultats` 
WHERE `event_id` = 1 AND `pilote_id` = 2 AND `pilote_id` IN 
     (select * from (SELECT `pilote_id` FROM `courses_resultats` WHERE `event_id` = 1 ORDER BY  (`Finale`+`Bonus`) DESC LIMIT 10)  alias)

MYSQL will only give me 10... even if there is 2 row with the same number...

 

I HAVE ABSOLUTLY no idea about this... it's already 2 weeks in the season and everybody is bothering me with this bug!

[awjudd]

Add a DISTINCT to it.

 

SELECT `event_id` FROM `courses_resultats` 
WHERE `event_id` = 1 AND `pilote_id` = 2 AND `pilote_id` IN 
     (select * from (SELECT DISTINCT `pilote_id` FROM `courses_resultats` WHERE `event_id` = 1 ORDER BY  (`Finale`+`Bonus`) DESC LIMIT 10)  alias)

 

That will remove the duplicate from being returned.

 

~awjudd

[XRayden]

Thanks awjudd, but that's not my problem

 

I do not have a pilot problem, but a point problems, here's a exemple:

 

Position	#	Name				Points
1		66	Simon Roussin			99
2		10	Michaël Lavoie			94
3		40	Sylvain Labbé			97
4		25	Pier-Luc Labbé			90
5		2	Dany Poulin			92	
6		27	Marco Savoie			84
7		07	Jonathan Côté			87	
8		7	Réjean Blanchet			81
9		77	Stéphane Roy			77
10		45	Steve Malouin McKibbin		74
11		17	Jimmy Nadeau			74

 

All of them should have gotten a "top 10" mention, but there is a tie.... and mysql will only give me 10 ppl.

[fenway]

Then you want more than 10.

[XRayden]

So.. yes,

 

is there a way to change the limit if 2 values are the same?

 

i dont think so, but ask anyway

[XRayden]

oh... I think i just tought of something good...

 

what if...

 

I first ask what is the "points" of the #10 THEN ask (without limits) who is under this points?

 

like in my exemple, if I ask for "everybody under 74" i'll lget 11 results :\ but I think it'll only work with a tie in the 10th place...

[Illusion]

LIMITING the records by count doesn't help...

 

set @previous_high_score=0;
set @rank=10;

select event_id from (

SELECT `pilote_id` , `event_id`,if( (
`Finale` + `Bonus`
) < @previous_high_score , @rank := @rank -1, @rank ) as rank, @previous_high_score := ( `Finale` + `Bonus` ) AS points
FROM `courses_resultats`
WHERE `event_id` =1
AND @rank >0
ORDER BY (
`Finale` + `Bonus`
) DESC ) a where a.pilote_id=2

[Illusion]

I dont understand why you are selecting event_id in select query when you have where clause to filter the records with event_id =1  as it fetches the same value.

[fenway]

So.. yes,

 

is there a way to change the limit if 2 values are the same?

 

i dont think so, but ask anyway

Do this in 2 parts -- grab the top 10 scores, and then join back to grab anyone with said scores.