PHP + Mysql显示一行中多行的值

Question

Hello i have always found here the solution to my troubles but this time i really don't know how to search for what i need, then i will post here hope (but i'm sure) some one can help me make this query working.

I'm the developer of real rally (A richard Burns Rally Mod bla bla bla). We need now an automated system to display the final standing for each championship.

I have acquired all the necessary info from 16 querys and stored all them into a temp db.

this mean i have for each championship (Montecarlo, Sweden, Mexico and so on) 15 results inside the temp table. 15 for montecarlo 15 for sweden and so on

then i run this query

SELECT Id, totale, num_prove, IdPilota, Nome, Cognome, Team, Nazionalita, Plate, Classe, VetturaUsata, Penalita, PaPenalty, PuntiCarriera, PuntiCarrieraP,
PuntiMon, PuntiSve, PuntiMes, PuntiArg, PuntiPor, PuntiIta, PuntiPol, PuntiFin, PuntiGer, PuntiCin, PuntiFra, PuntiSpa, PuntiGrb, PuntiAus, RallyCorso,
SUM(PuntiMon) + SUM(PuntiSve) + SUM(PuntiMes) + SUM(PuntiArg) + SUM(PuntiPor) + SUM(PuntiIta) + SUM(PuntiPol) + SUM(PuntiFin) + SUM(PuntiGer) + SUM(PuntiCin) + SUM(PuntiFra) + SUM(PuntiSpa) + SUM(PuntiGrb) + SUM(PuntiAus) AS Punti
FROM temp_table_5975a9e73a835
GROUP BY IdPilota
ORDER BY Punti DESC;

now the whats wrong. This query work but i need to display for every IdPilota wich point he reach in every championship.

atm the displayed data show 43 0 0 0 0 0 due to group  by IdPilota:

but i need to disaplay the right point gained in every championship

There is a way to display what i need?

43 <- from (Montecarlo)
33 <- from (Sweden)
43 <- from (Mexico)
43 <- from (Argentina)
43 33 43 43

Hope to have explained what i need!!!

EDIT: This is the working query atm:

    SELECT Id, totale, num_prove, IdPilota, Nome, Cognome, Team, Nazionalita, Plate, Classe, VetturaUsata, Penalita, PaPenalty, PuntiCarriera, PuntiCarrieraP,
SUM(PuntiMon), SUM(PuntiSve), SUM(PuntiMes), SUM(PuntiArg), SUM(PuntiPor), SUM(PuntiIta), SUM(PuntiPol), SUM(PuntiFin), SUM(PuntiGer), SUM(PuntiCin), SUM(PuntiFra), SUM(PuntiSpa), SUM(PuntiGrb), SUM(PuntiAus), RallyCorso,
SUM(PuntiMon) + SUM(PuntiSve) + SUM(PuntiMes) + SUM(PuntiArg) + SUM(PuntiPor) + SUM(PuntiIta) + SUM(PuntiPol) + SUM(PuntiFin) + SUM(PuntiGer) + SUM(PuntiCin) + SUM(PuntiFra) + SUM(PuntiSpa) + SUM(PuntiGrb) + SUM(PuntiAus) AS Punti
FROM temp_table_5975a9e73a835
GROUP BY IdPilota
ORDER BY Punti DESC;

Just to know if this is a correct way to display the data i need

Answer 1

I do not know if you have specific restrictions or limitations but I do not usually mix business logic with presentation logic. In this case i would prefer don't create a temp table (maybe a view is better) because you use it in order to show a table on the presentation layer. I assume i have a data model like this (i repeat: i don't know if it is your case)

TABLE "PILOTI"

+--------+-------------+
|  ID    | NOME        |
+--------+-------------+
|   1    | Colin McRea |
+--------+-------------+
|   2    | Carlos Sainz|
+--------+-------------+

TABLE "GARE"

+--------+-------------+
|  ID    | NOME        |
+--------+-------------+
|   1    | Montecarlo  |
+--------+-------------+
|   2    | Argentina   |
+--------+-------------+
|   3    | Svezia      |
+--------+-------------+

TABLE "CAMPIONATO" (n-n => relation table between GARE and PILOTI)

+--------+-------------+-------------+-------------+
|  ID    | ID_PILOTA   |  ID_GARA    | PUNTEGGIO   |
+--------+-------------+-------------+-------------+
|   1    |    1        |    1        |     43      |
+--------+-------------+-------------+-------------+
|   2    |    1        |    2        |     33      |
+--------+-------------+-------------+-------------+
|   3    |    2        |    1        |     15      |
+--------+-------------+-------------+-------------+
|   4    |    2        |    2        |     18      |
+--------+-------------+-------------+-------------+

With this query, i retrieve for each driver, the points reached every championship

SELECT P.NOME, G.NOME, C.PUNTEGGIO
FROM CAMPIONATO C
JOIN PILOTI P ON P.ID = C.ID_PILOTA
JOIN GARE   G ON G.ID = C.ID_GARA

You can add a "WHERE" condition in order to filter for a single driver (or single championship). For example, if you have 15 drivers, you can retrieve the points reached to each driver (for each championship) with only 15 query adding

WHERE P.ID = :id_pilota

to the prevoious query.

You may also create a view with this query and calculate the total points but i prefer to do this in PHP and not in MySQL. I.E. (PSEUDO-CODE)

foreach($driver){
 queryToGetPoints();
 foreach($championship){
   $totalPoints+=$points;
 }
 printHtmlRow();
}

However, With MySql the ordering is more simply

SELECT P.NOME,SUM(C.PUNTEGGIO) AS TOTALE
FROM CLASSIFICA C
JOIN  PILOTI P ON P.ID = C.ID_PILOTA
GROUP BY C.ID_PILOTA 
ORDER BY TOTALE ASC

In order to update the points, after a championship is closed, you can insert in "CAMPIONATO" table, for each driver, a new row whit his own ID, the ID of the championship and the points reached.

I.E.

INSERT INTO CAMPIONATO (ID_PILOTA, ID_GARA, PUNTEGGIO) VALUES (1,3,18)

=> McRea reached 18 points in Svezia

You may also create a table "CLASSIFICA" whit two fields: ID_PILOTA e TOTALE then create a trigger on "CAMPIONATO" table "after insert" in order to update the CAMPIONATO.TOTALE field with the sum of the old value + the points inserted.

So, you can see there is more than one way in order to do what you want. My suggestion is: if one more query help you to get your code more simply, use it even if there is a computational overhead (multiple queries in nested loop in this case). Hope this help you. Good luck!