View original (French)

Order responses

danielos77 Posted messages 117 Registration date   Status Member Last intervention   -  
jee pee Posted messages 31945 Registration date   Status Moderator Last intervention   -

Hello,

I have a piece of code that works very well but I would like, if possible, to order the display sequence President/Secretary/Treasurer/Activity Manager A / Activity Manager B... knowing that the same person can have several roles (hence the explode).

<?php //enable PHP error display error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); //include the database connection file require_once ""; //prepare the query based on the chosen unit $sth="SELECT Nom, Prenom, Tph, Mail, Unite, Fonction FROM Fonctions where Unite = :unite ORDER BY Nom ASC" ; try{ //prepare query $req =$bdd->prepare($sth); //execute $req->bindValue(':Unite',$nomUnite); $req->execute(); //fetch results $result = $req->FetchAll(); } catch(Exception $e){ // on error: echo " <br>Erreur ! ".$e->getMessage(); echo " <br>Les datas : " ; print_r($datas); } /* echo'<pre>'; var_dump($result); die(); echo'</pre>'; */ if (!empty($result)){ foreach ($result as $ligne) { //retrieve info $Nom = $ligne['Nom']; $Prenom = $ligne['Prenom']; $Mail = $ligne['Mail']; $Tph = $ligne['Tph']; $Unite = $ligne['Unite']; $Fonction = $ligne['Fonction']; $fonction = explode(", ", $Fonction); foreach($fonction as $key => $fonction) { echo' <font size="4"> <ul><li>'.htmlspecialchars($fonction).' :<br> &emsp;&emsp;'.htmlspecialchars($Prenom).' '.htmlspecialchars($Nom).'<br> &emsp;&emsp;Tph : '.htmlspecialchars($Tph).' <br> &emsp;&emsp;Mail : '.htmlspecialchars($Mail).' </li></ul> '; } } }else{ echo' <p style="margin-left: 40px;"> <font size="5"> Cette unité ne semble pas active... Etes-vous sûr de vouloir la choisir quand même ? </font><br> <i>(Pour en changer, vous pouvez consulter la carte des activités dans votre espace personnel et en changer.)</i><br> '; } ?>

J had thought to put an if ($fonction="Président")[echo'....';}elsif($fonction="Secrétaire"){echo'...';} etc... between the "if (!empty($result)){ and the foreach.." but that doesn't seem to be the solution. I would welcome a tip. 
 

Thanks in advance,
Daniel

1 answer

  1.
    jee pee Posted messages 31945 Registration date   Status Moderator Last intervention   10 003
     
    Hello,

    One idea could be to generate the order directly in the select via a calculated field based on the Fonction field.

    With Oracle I used decode(); in MySQL there is CASE, which is similar, or perhaps IFNULL(ELT(FIELD(...)) as suggested at https://stackoverflow.com/questions/4706100/mysql-equivalent-of-decode-function-in-oracle

    It could be:

    SELECT Nom, Prenom, Tph, Mail, Unite, Fonction, CASE Fonction WHEN 'Président' THEN 1 WHEN 'Secrétaire' THEN 2 WHEN 'Tésorier' THEN 3 WHEN '...' THEN 9 ELSE 99 END AS Tri FROM Fonctions ORDER BY Tri, Nom

    If a person can have 2 functions, your DB model is not ideal. It would be preferable to have a Person table and a Function table (PersonneId, Fonction).
    0