je suis face à un probleme pour l'affichage d'un classement.
Je m'explique. j'ai une table "tours" qui regroupe les temps de chaque pilote ( le champ total_tour enregistre l'ecart de temps entre le depart et l'arrivée)
Code : Tout sélectionner
CREATE TABLE IF NOT EXISTS `tours` (
`id_tour` int(11) NOT NULL AUTO_INCREMENT,
`id_course_tour` int(11) NOT NULL,
`id_pilote_tour` int(11) NOT NULL,
`nb_tour` int(11) NOT NULL,
`depart_tour` time NOT NULL,
`arrivee_tour` time NOT NULL,
`total_tour` time NOT NULL,
PRIMARY KEY (`id_tour`),
KEY `id_course_tour` (`id_course_tour`,`id_pilote_tour`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=159 ;J'aimerai obtenir un classement du plus petit au temps le plus long
j'ai ceci comme classement mais je n'affiche pas dans l'ordre voulu
Code : Tout sélectionner
<table>
<thead>
<tr>
<th>Dossard</th>
<th>Nom</th>
<th>Prenom</th>
<th>Tour-1</th>
<th>Tour-2</th>
<th>Tour-3</th>
<th>Tour-4</th>
<th>Tour-5</th>
<th>Tour-6</th>
<th>Tour-7</th>
<th>Tour-8</th>
<th>Tour-9</th>
<th>Tour-10</th>
<th>Total</th>
</tr>
</thead>
<tbody>
<?php
if(isset($_GET['id_course']))
{
$id_course = $_GET['id_course'];
$sql = $connection->query('SELECT * FROM `tours`, `pilotes` WHERE id_course_tour = '.$id_course.' AND id_pilote_tour = numero_pilote GROUP BY id_pilote_tour');
$sql->setFetchMode(PDO::FETCH_OBJ);
while( $data_pilote = $sql->fetch() )
{?>
<tr class="surbri_actif">
<td><?php echo $data_pilote->numero_pilote;?></td>
<td><?php echo $data_pilote->nom_pilote;?></td>
<td><?php echo $data_pilote->prenom_pilote;?></td>
<td><?php
$numero_pilote_tour = $data_pilote->numero_pilote;
$sqltour = $connection->query('SELECT * FROM `tours`, `pilotes` WHERE id_course_tour = '.$id_course.' AND id_pilote_tour = '.$numero_pilote_tour.' AND nb_tour = 1 AND id_pilote_tour = numero_pilote');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
if($data_tour){
$sqltour = $connection->query('SELECT * FROM `tours` WHERE id_course_tour = '.$id_course.' AND nb_tour = 1 AND id_pilote_tour = '.$numero_pilote_tour.'');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
$tour1 = substr($data_tour->total_tour,1);
echo $tour1;
$_SESSION['tour1'] = $tour1;
}
elseif($data_tour == NULL){
echo 'Attente' ;
}
;?></td>
<td><?php
$numero_pilote_tour = $data_pilote->numero_pilote;
$sqltour = $connection->query('SELECT * FROM `tours`, `pilotes` WHERE id_course_tour = '.$id_course.' AND id_pilote_tour = '.$numero_pilote_tour.' AND nb_tour = 2 AND id_pilote_tour = numero_pilote');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
if($data_tour){
$sqltour = $connection->query('SELECT * FROM `tours` WHERE id_course_tour = '.$id_course.' AND nb_tour = 2 AND id_pilote_tour = '.$numero_pilote_tour.'');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
$tour2 = substr($data_tour->total_tour,1);
echo $tour2;
$_SESSION['tour2'] = $tour2;
}
elseif($data_tour == NULL){
echo 'Attente' ;
}
;?></td>
<td><?php
$numero_pilote_tour = $data_pilote->numero_pilote;
$sqltour = $connection->query('SELECT * FROM `tours`, `pilotes` WHERE id_course_tour = '.$id_course.' AND id_pilote_tour = '.$numero_pilote_tour.' AND nb_tour = 3 AND id_pilote_tour = numero_pilote');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
if($data_tour){
$sqltour = $connection->query('SELECT * FROM `tours` WHERE id_course_tour = '.$id_course.' AND nb_tour = 3 AND id_pilote_tour = '.$numero_pilote_tour.'');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
$tour3 = substr($data_tour->total_tour,1);
echo $tour3;
$_SESSION['tour3'] = $tour3;
}
elseif($data_tour == NULL){
echo 'Attente' ;
}
;?></td>
<td><?php
$numero_pilote_tour = $data_pilote->numero_pilote;
$sqltour = $connection->query('SELECT * FROM `tours`, `pilotes` WHERE id_course_tour = '.$id_course.' AND id_pilote_tour = '.$numero_pilote_tour.' AND nb_tour = 4 AND id_pilote_tour = numero_pilote');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
if($data_tour){
$sqltour = $connection->query('SELECT * FROM `tours` WHERE id_course_tour = '.$id_course.' AND nb_tour = 4 AND id_pilote_tour = '.$numero_pilote_tour.'');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
$tour4 = substr($data_tour->total_tour,1);
echo $tour4;
$_SESSION['tour4'] = $tour4;
}
elseif($data_tour == NULL){
echo 'Attente' ;
}
;?></td>
<td><?php
$numero_pilote_tour = $data_pilote->numero_pilote;
$sqltour = $connection->query('SELECT * FROM `tours`, `pilotes` WHERE id_course_tour = '.$id_course.' AND id_pilote_tour = '.$numero_pilote_tour.' AND nb_tour = 5 AND id_pilote_tour = numero_pilote');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
if($data_tour){
$sqltour = $connection->query('SELECT * FROM `tours` WHERE id_course_tour = '.$id_course.' AND nb_tour = 5 AND id_pilote_tour = '.$numero_pilote_tour.'');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
$tour5 = substr($data_tour->total_tour,1);
echo $tour5;
$_SESSION['tour5'] = $tour5;
}
elseif($data_tour == NULL){
echo 'Attente' ;
}
;?></td>
<td><?php
$numero_pilote_tour = $data_pilote->numero_pilote;
$sqltour = $connection->query('SELECT * FROM `tours`, `pilotes` WHERE id_course_tour = '.$id_course.' AND id_pilote_tour = '.$numero_pilote_tour.' AND nb_tour = 6 AND id_pilote_tour = numero_pilote');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
if($data_tour){
$sqltour = $connection->query('SELECT * FROM `tours` WHERE id_course_tour = '.$id_course.' AND nb_tour = 6 AND id_pilote_tour = '.$numero_pilote_tour.'');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
$tour6 = substr($data_tour->total_tour,1);
echo $tour6;
$_SESSION['tour6'] = $tour6;
}
elseif($data_tour == NULL){
echo 'Attente' ;
}
;?></td>
<td><?php
$numero_pilote_tour = $data_pilote->numero_pilote;
$sqltour = $connection->query('SELECT * FROM `tours`, `pilotes` WHERE id_course_tour = '.$id_course.' AND id_pilote_tour = '.$numero_pilote_tour.' AND nb_tour = 7 AND id_pilote_tour = numero_pilote');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
if($data_tour){
$sqltour = $connection->query('SELECT * FROM `tours` WHERE id_course_tour = '.$id_course.' AND nb_tour = 7 AND id_pilote_tour = '.$numero_pilote_tour.'');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
$tour7 = substr($data_tour->total_tour,1);
echo $tour7;
$_SESSION['tour7'] = $tour7;
}
elseif($data_tour == NULL){
echo 'Attente' ;
}
;?></td>
<td><?php
$numero_pilote_tour = $data_pilote->numero_pilote;
$sqltour = $connection->query('SELECT * FROM `tours`, `pilotes` WHERE id_course_tour = '.$id_course.' AND id_pilote_tour = '.$numero_pilote_tour.' AND nb_tour = 8 AND id_pilote_tour = numero_pilote');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
if($data_tour){
$sqltour = $connection->query('SELECT * FROM `tours` WHERE id_course_tour = '.$id_course.' AND nb_tour = 8 AND id_pilote_tour = '.$numero_pilote_tour.'');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
$tour8 = substr($data_tour->total_tour,1);
echo $tour8;
$_SESSION['tour8'] = $tour8;
}
elseif($data_tour == NULL){
echo 'Attente' ;
}
;?></td>
<td><?php
$numero_pilote_tour = $data_pilote->numero_pilote;
$sqltour = $connection->query('SELECT * FROM `tours`, `pilotes` WHERE id_course_tour = '.$id_course.' AND id_pilote_tour = '.$numero_pilote_tour.' AND nb_tour = 9 AND id_pilote_tour = numero_pilote');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
if($data_tour){
$sqltour = $connection->query('SELECT * FROM `tours` WHERE id_course_tour = '.$id_course.' AND nb_tour = 9 AND id_pilote_tour = '.$numero_pilote_tour.'');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
$tour9 = substr($data_tour->total_tour,1);
echo $tour9;
$_SESSION['tour9'] = $tour9;
}
elseif($data_tour == NULL){
echo 'Attente' ;
}
;?></td>
<td><?php
$numero_pilote_tour = $data_pilote->numero_pilote;
$sqltour = $connection->query('SELECT * FROM `tours`, `pilotes` WHERE id_course_tour = '.$id_course.' AND id_pilote_tour = '.$numero_pilote_tour.' AND nb_tour = 10 AND id_pilote_tour = numero_pilote');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
if($data_tour){
$sqltour = $connection->query('SELECT * FROM `tours` WHERE id_course_tour = '.$id_course.' AND nb_tour = 10 AND id_pilote_tour = '.$numero_pilote_tour.'');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
$tour10 = substr($data_tour->total_tour,1);
echo $tour10;
$_SESSION['tour10'] = $tour10;
}
elseif($data_tour == NULL){
echo 'Attente' ;
}
;?></td>
<td class="total">
<?php
$numero_pilote_tour = $data_pilote->numero_pilote;
$sqltour = $connection->query('SELECT SEC_TO_TIME( SUM( TIME_TO_SEC(total_tour) ) ) AS total FROM `tours`, `pilotes` WHERE id_course_tour = '.$id_course.' AND id_pilote_tour = '.$numero_pilote_tour.' AND id_pilote_tour = numero_pilote GROUP BY id_pilote_tour ORDER BY total DESC');
$sqltour->setFetchMode(PDO::FETCH_OBJ);
$data_tour = $sqltour->fetch();
$resultat_total = substr($data_tour->total,1);
echo $resultat_total;
?>
</td>
</tr>
<?php
}
}
?>
</tbody>
</table>