Show drivers page are showing drivers several times

[avs-code]

show_drivers.php http://prem.hol.es/prem/?page=show_drivers
It is showing drivers as many times as teams have or had.
If a pilot have 2 teams the pilot is showed two times.
Ideal solution will show the pilot only one time GROUP BY driver.id, with the COUNT of all podiums from all their teams (including historical teams in his career).

[avs-code]

@inguni @stmeissner Can any of you modify array to resolve this issue?

I've been a week and I have not got it, I even tried with:

$sql_first_position = "SELECT `team_driver`, COUNT(`position`) FROM race_driver WHERE `position` = 1";
$sql_second_position = "SELECT `team_driver`, COUNT(`position`) FROM race_driver WHERE `position` = 2";
$sql_third_position = "SELECT `team_driver`, COUNT(`position`) FROM race_driver WHERE `position` = 3";

[stmeissner]

@arv187 could you please provide some sample data to reproduce this issue? Thanks

[avs-code]

Of course!
u275158832_prem_.zip

[stmeissner]

@arv187 I think I know what you are after; try adding this to the end of the queries:
GROUP BY team_driver
Does this help?

[avs-code]

Not, GROUP BY driver.id on second sql query its a solution for repetitions, but if you see the page on Munchkin driver for example, you see two entries:

_______________1st_____2nd_____3rd  (Times he finished in those positions)
1. Munchkin 	2 	3 	1  (When he was in Kunos ART (Ferrari) team).
2. Munchkin 	1 	1 	0  (When he was in Bullseye Honda (F3) team).

When I make "group by" I lost the second entry. So first you have to do a count by position, I think something like this or another better option with arrays.

$sql_first_position = "SELECT team_driver, COUNT(position) FROM race_driver WHERE position = 1";
$sql_second_position = "SELECT team_driver, COUNT(position) FROM race_driver WHERE position = 2";
$sql_third_position = "SELECT team_driver, COUNT(position) FROM race_driver WHERE position = 3";

And you should get:

_______________1st_____2nd_____3rd  (Times he finished in those positions)
1. Munchkin 	3 	4 	1

I think that actual array its bad to achieve this goal, see 4-7 and 42-51 lines.

[stmeissner]

@arv187 this can be done on SQL level.
First I created a view over all race results while counting their top 3 finishers identified by their team driver ID:

CREATE VIEW team_driver_top3 AS
SELECT team_driver,
  sum(case when position = 1 then 1 else 0 end) as position_1_count,
  sum(case when position = 2 then 1 else 0 end) as position_2_count,
  sum(case when position = 3 then 1 else 0 end) as position_3_count
from race_driver
group by team_driver
order by position_1_count desc

and this is its content:

team_driver	position_1_count	position_2_count	position_3_count
75	3	3	1
105	2	2	1
86	2	3	1
104	2	1	1
82	2	0	1
102	1	1	1
118	1	1	0
93	0	0	1
91	0	0	1
76	0	0	0
112	0	1	0
83	0	0	2
119	0	0	0
80	0	1	0
92	0	0	0
117	0	0	0
87	0	0	0
111	0	0	3

That view still contains separate rows for drivers running for more than one team.
To accumulate their achivements I made another SQL query for the driver name on this view based on the driver ID and summing up the number of each position for drivers with duplicate entries:

SELECT name,
    SUM(position_1_count) AS pos_1,
    SUM(position_2_count) AS pos_2,
    SUM(position_3_count) AS pos_3 
FROM team_driver, team_driver_top3, driver 
WHERE (team_driver.id = team_driver_top3.team_driver AND team_driver.driver = driver.id) 
GROUP BY driver
ORDER BY pos_1 DESC, pos_2 DESC, pos_3 DESC;

leading to this result:

name	pos_1	pos_2	pos_3
InGuNi	5	5	2
Munchkin	3	4	1
Spark	2	1	3
Flame	2	0	1
martinwrx	1	2	1
Chacal	0	1	0
Luft	0	0	4
Occam	0	0	1
flipe2001	0	0	0
HAMMER	0	0	0
Fonsooo	0	0	0

Is this what you want to see?
If so I'd look on the weekend where to embed this best in the PHP scripts.

[avs-code]

@stmeissner YES!!!!! that is, thank you very much!!!
With my ignorance I could not do it. xD

Once I got it with the sql console, but I was not able to put it together in php with the team_driver table and driver (which contains the names and image of the pilot when it is registered). I was getting lost with the array and php.

Again, thank you so much for your help.

[avs-code]

Fixed by @stmeissner in pull request #44 Thank you!!