Displaying PHP + Mysql value from multiple lines in one line
Hi, I have always found a solution to my problems here, 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) someone can help me this query works.
I'm a real rally developer (A richard Burns Rally Mod bla bla bla). We now need an automated system to display the final position for each championship.
I got all the information I needed from 16 requests and saved them all to db temp.
This means I have, for each championship (Montecarlo, Sweden, Mexico, etc.), 15 results inside the temp table. 15 for montecarlo 15 for Swedish etc.
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 something is wrong. This query works, but I need to display for every IdPilota it reaches 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 correct moment obtained in every championship
Is there a way to show what I need?
43 <- from (Montecarlo)
33 <- from (Sweden)
43 <- from (Mexico)
43 <- from (Argentina)
43 33 43 43
Hope to explain what I need.
EDIT: This is a working atm request:
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 the correct way to display the data that I need
source to share
I don't know if you have specific constraints or constraints, but I usually don't mix business logic with presentation logic. In this case, I would prefer not to create a temporary table (view may be better), because you are using it to show the table at the presentation level. I assume I have a data model like this (I repeat: I don’t know if this 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" (nn => relationship 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 request I get for each driver, points have reached each 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 clause to filter out one driver (or one championship). For example, if you have 15 drivers, you can get the points reached for each driver (for each championship) with only 15 adding the query
WHERE P.ID = :id_pilota
to the previous request.
You can also create a view with this query and calculate the total scores, but I prefer to do this in PHP rather than MySQL. I.E. (Pseudocode)
foreach($driver){
queryToGetPoints();
foreach($championship){
$totalPoints+=$points;
}
printHtmlRow();
}
However, with MySql the ordering is easier
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
To update the points, after the end of the championship you can insert the table "CAMPIONATO", for each driver, a new row with his own ID, the championship ID and the points achieved.
those.
INSERT INTO CAMPIONATO (ID_PILOTA, ID_GARA, PUNTEGGIO) VALUES (1,3,18)
=> McRea reached 18 points in Svesia
You can also create table "CLASSIFICA" with two fields: ID_PILOTA e TOTALE then create a trigger on table "CAMPIONATO" "after insert" to update the field CAMPIONATO.TOTALE with the sum of the old value + points.
So, you can see that there are several ways to do what you want. My suggestion: if another query helps you get your code more simple, use it even if there is computational overhead (in this case, multiple queries in a nested loop). Hope this helps you. Good luck!
source to share