SELECT N.id_elv ,SUM(N.note) as somme_Notes ,COUNT(N.note) nb_Notes ,SUM(N.note)/COUNT(N.note) as Moyenne FROM notes N WHERE eval = '".$class."' GROUP BY N.id_elv ORDER BY Moyenne DESC, N.id_elv DESC
//la function qui va permettre le classement
function classement(){
//if(isset($_GET['id'])) $id = urldecode($_GET['id']);
if(isset($_GET['mat'])) $mat = urldecode($_GET['mat']);
if(isset($_GET['class'])) $class = urldecode($_GET['class']);
$results = array();
$sql = "SELECT N.id_elv
,id_matiere
,SUM(N.note) as somme_Notes
,COUNT(N.note) nb_Notes
,SUM(E.note)/COUNT(N.note) as Moyenne
FROM notes N
WHERE eval = '".$class."'
GROUP BY N.id_elv
ORDER BY Moyenne DESC, N.id_elv DESC
";
$query = mysql_query($sql) or die("error");
while($row = mysql_fetch_assoc($query)){
$results[] = $row;
}
return $results;
}
?>
SELECT N.id_elv ,SUM(N.note) as somme_Notes ,COUNT(N.note) nb_Notes ,SUM(N.note)/COUNT(N.note) as Moyenne FROM notes N WHERE eval = '".$class."' GROUP BY N.id_elv ORDER BY Moyenne DESC, N.id_elv DESC
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question$sql = "SELECT N.id_elv
,N.id_matiere
,SUM(N.note) as somme_Notes
,COUNT(N.note) nb_Notes
,SUM(E.note)/COUNT(N.note) as Moyenne
,I.id_inscri
,I.matricule
,I.nom
,I.prenom
,I.sexe
,I.date_naiss
FROM notes N ,inscription I,matieres M
WHERE eval = '".$class."'
AND M.id = N.id_matiere
AND N.id_elv = I.id_inscri
AND M.matiere = '".$matiere."'
GROUP BY N.id_elv
ORDER BY Moyenne DESC, N.id_elv DESC
";
SUM(E.note)N et non E.
$sql = "SELECT N.id_elv
,N.id_matiere
,SUM(N.note) as somme_Notes
,COUNT(N.note) nb_Notes
,SUM(N.note)/COUNT(N.note) as Moyenne
,I.id_inscri
,I.matricule
,I.nom
,I.prenom
,I.sexe
,I.date_naiss
FROM notes N ,inscription I,matieres M
WHERE eval = '".$class."'
AND M.id = N.id_matiere
AND N.id_elv = I.id_inscri
AND M.matiere = '".$matiere."'
GROUP BY N.id_elv
ORDER BY Moyenne DESC, N.id_elv DESC
";
<?php
//la function qui va permettre le classement
function classement(){
//http://www.1keydata.com/fr/sql/sql-rang.php
//if(isset($_GET['id'])) $id = urldecode($_GET['id']);
if(isset($_GET['matiere'])) $matiere = urldecode($_GET['matiere']);
if(isset($_GET['class'])) $class = urldecode($_GET['class']);
$results = array();
$sql = "SELECT N.id_elv
,N.id_matiere
,SUM(N.note) as somme_Notes
,COUNT(N.note) nb_Notes
,SUM(N.note)/COUNT(N.note) as Moyenne
,I.id_inscri
,I.matricule
,I.nom
,I.prenom
,I.sexe
,I.date_naiss
FROM notes N ,inscription I,matieres M
WHERE eval = '".$class."'
AND M.id = N.id_matiere
AND N.id_elv = I.id_inscri
AND M.matiere = '".$matiere."'
GROUP BY N.id_elv
ORDER BY Moyenne DESC, N.id_elv DESC
";
$query = mysql_query($sql) or die("error");
while($row = mysql_fetch_assoc($query)){
$results[] = $row;
}
return $results;
}
?>
<table width="914" border="0" >
<tr style="font-family:Georgia, 'Times New Roman', Times, serif; font-size:14px; background-color:#408080; color:#FFF">
<td width="48" align="center">N°</td>
<td width="114" align="center">MATRICULE</td>
<td width="200" align='left'>NOM</td>
<td width="307" align='left'>PRENOMS</td>
<td width="38" align="center">S</td>
<td width="54" align="center">AGE</td>
<td width="46" align="center">MOY </td>
<td width="73" align="center">RANG</td>
</tr>
<?php
//$elevPrec = "";
$classements = classement();
foreach($classements as $classement){
?>
<tr style="font-family:Georgia, 'Times New Roman', Times, serif; font-size:14px; background-color:#CDCDCD; color:#000">
<td width="73" align="center"><?php echo $classement[""]; ?></td>
<td width="48" align="center"><?php echo $classement["matricule"]; ?></td>
<td width="114" align="center"><?php echo $classement["nom"]; ?></td>
<td width="200" align='left'><?php echo $classement["prenom"]; ?></td>
<td width="307" align='left'><?php echo $classement["sexe"]; ?></td>
<td width="38" align="center"><?php echo $classement["date_naiss"]; ?></td>
<td width="54" align="center"><?php echo $classement["Moyenne"]; ?></td>
<td width="46" align="center"><?php echo $classement["id_elv"]; ?> </td>
</tr>
<?php
}
?>
</table>
$rang=0; foreach($classements as $classement){ $rang++; ?> <tr style="font-family:Georgia, 'Times New Roman', Times, serif; font-size:14px; background-color:#CDCDCD; color:#000"> <td width="73" align="center"><?php echo $rang; ?></td> <td width="48" align="center"><?php echo $classement["matricule"]; ?></td> <td width="114" align="center"><?php echo $classement["nom"]; ?></td> <td width="200" align='left'><?php echo $classement["prenom"]; ?></td> <td width="307" align='left'><?php echo $classement["sexe"]; ?></td> <td width="38" align="center"><?php echo $classement["date_naiss"]; ?></td> <td width="54" align="center"><?php echo $classement["Moyenne"]; ?></td> <td width="46" align="center"><?php echo $classement["id_elv"]; ?> </td> </tr> <?php }
$rang=0; foreach($classements as $classement){ $rang++; ?> <tr style="font-family:Georgia, 'Times New Roman', Times, serif; font-size:14px; background-color:#CDCDCD; color:#000"> <td width="73" align="center"></td> <td width="48" align="center"><?php echo $classement["matricule"]; ?></td> <td width="114" align="center"><?php echo $classement["nom"]; ?></td> <td width="200" align='left'><?php echo $classement["prenom"]; ?></td> <td width="307" align='left'><?php echo $classement["sexe"]; ?></td> <td width="38" align="center"><?php echo $classement["date_naiss"]; ?></td> <td width="54" align="center"><?php echo $classement["Moyenne"]; ?></td> <td width="46" align="center"><?php echo $rang; ?> </td> </tr> <?php }
<?php
//recuperation du groupe
$sql_m0 = ('SELECT groupe
FROM matieres
GROUP BY groupe
');
$q_m0 = mysql_query($sql_m0) or die('Erreur SQL !<br>'.$sql_m0.'<br>'.mysql_error());
$n_m0 = mysql_num_rows($q_m0);
while ($r_m0 = mysql_fetch_assoc($q_m0)) { //echo $r_m0['groupe'];
//recuperation des matières
$sql_m = ('SELECT *
FROM matieres
WHERE groupe = "'.$r_m0['groupe'].'"
');
$q_m = mysql_query($sql_m) or die('Erreur SQL !<br>'.$sql_m.'<br>'.mysql_error());
$n_m = mysql_num_rows($q_m);
while ($r_m = mysql_fetch_assoc($q_m)) {
//partie maoyenne
//echo $r_m['matiere'];
$sql = "SELECT N.id_elv
,N.id_matiere
,SUM(N.note) as somme_Notes
,COUNT(N.note) nb_Notes
,SUM(N.note)/COUNT(N.note) as Moyenne
,I.id_inscri
,I.matricule
,I.nom
,I.prenom
,I.sexe
,I.date_naiss
FROM notes N ,inscription I,matieres M
WHERE eval = '".$class."'
AND M.id = N.id_matiere
AND N.id_elv = I.id_inscri
AND M.matiere = '".$r_m['matiere']."'
AND N.id_elv = '".$id."'
AND (MONTH(N.date_enrg) BETWEEN '".$debut."' AND '".$fin."')
GROUP BY N.id_elv
ORDER BY Moyenne DESC, N.id_elv DESC
";
$query = mysql_query($sql) or die("error");
$rr = mysql_fetch_assoc($query);
//calcul de moyenne
$MOY0 = $rr['Moyenne'];
$MOY = number_format($MOY0, 2, ',', ' ');
//calcul de moyenne avec coefficient
$MOY_COEFF = $MOY * $r_m['coeff'];
//RANG
echo'<tr>
<td>'.$r_m['matiere'].'</td>
<td align="center">'.$MOY.'</td>
<td align="center">'.$r_m['coeff'].'</td>
<td align="center">'.$MOY_COEFF.'</td>
';
?>
<td>
<?php
echo $rr["id_elv"];
?>
</td>
<?php
echo'
<td>'.$rr['pseudo'].'</td>
<td></td>
</tr>
<tr>
';
}
echo ' <tr align="center">
<td colspan="7"><font color="red"><b> Bilan en '.$r_m0['groupe'].'</font></b></td>
</tr>';
}
?>