Jump to content

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


XRayden

Recommended Posts

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! :(

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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


Link to comment
Share on other sites

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.

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.