MOD MySQL est remplacé par PostgreSQL (fini)

MOD #140

diff --git a/index.php b/index.php
index eb24013..e41a32e 100644 (file)
--- a/index.php
+++ b/index.php
@@ -28,7 +28,7 @@ $panel = new Panel($participant);
 
 if ($controller->nouvel_inscrit)
    $page = "bienvenue";
-
+   
 echo '<?xml version="1.0" encoding="UTF-8"?>';
 
 ?>

diff --git a/php/connexion.php b/php/connexion.php
index 49c68a1..0d337c1 100644 (file)
--- a/php/connexion.php
+++ b/php/connexion.php
@@ -52,11 +52,11 @@ class Connexion
          setcookie("COOKIE_INFO_PARTICIPANT", "", time() - 100); # 'efface' le cookie membre
          unset($_COOKIE["COOKIE_INFO_PARTICIPANT"]);
       }
-
+      
       if (isset($_POST['log'])) # le membre se logue
       {
          $this->participant = new Participant($_POST['pseudo'], $_POST['password']);
-         if ($this->participant->valide)
+         if ($this->participant->existe())
          {
             setcookie("COOKIE_INFO_PARTICIPANT", $this->participant->info->id, time() + 31104000);
             $this->message_utilisateur = "Loggation ok";

diff --git a/php/controller.php b/php/controller.php
index 0906650..f3fd468 100644 (file)
--- a/php/controller.php
+++ b/php/controller.php
@@ -28,25 +28,39 @@ class Controller
             $_POST['accord'] == "on"
          )
          {
-            mysql_query("BEGIN TRANSACTION");
-            mysql_query("
+            pg_query("BEGIN");
+            
+            // Cherche un id libre.
+            // Pour des questions de sécurité, les ids ne sont pas générés sequentiellement
+            // car ils sont mémorisé dans un cookie et permette l'authentification.
+            $id = 0;
+            do
+            {
+               $id = rand(1, PHP_INT_MAX);
+               $row = pg_fetch_row(pg_query_params("SELECT count(*) FROM participants WHERE id = $1", array($id)));
+            } while ($row[0] != 0);
+            
+            pg_query_params("
                INSERT INTO participants 
-               (pseudo, password, clan_nom, clan_tag, nom, prenom, age, e_mail, remarques)
-               VALUES (
-                  '".addslashes($_POST['pseudo'])."',
-                  '".addslashes($_POST['pass1'])."',
-                  '".addslashes($_POST['clan_nom'])."',
-                  '".addslashes($_POST['clan_tag'])."',
-                  '".addslashes($_POST['nom'])."',
-                  '".addslashes($_POST['prenom'])."',
-                  '".addslashes($_POST['age'])."',
-                  '".addslashes($_POST['e_mail'])."',
-                  '".addslashes($_POST['remarques'])."'
-               )"
+               (id, pseudo, password, clan_nom, clan_tag, nom, prenom, age, e_mail, remarques)
+               VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
+               ", 
+               array(
+                  $id,
+                  $_POST['pseudo'],
+                  $_POST['pass1'],
+                  $_POST['clan_nom'],
+                  $_POST['clan_tag'],
+                  $_POST['nom'],
+                  $_POST['prenom'],
+                  $_POST['age'],
+                  $_POST['e_mail'],
+                  $_POST['remarques'],
+               )
             );
-            $id = mysql_insert_id();
+            
             $this->set_periodes($id);
-            mysql_query("COMMIT");
+            pg_query("COMMIT");
          
             $this->participant = new participant($_POST['pseudo'], $_POST['pass1']);
             setcookie("COOKIE_INFO_PARTICIPANT", $this->participant->info->id, time() + 31104000);
@@ -54,7 +68,7 @@ class Controller
          }
       }
       # un participant modifie ses infos
-      else if(isset($_POST['modification_participant']) && $this->participant->valide)
+      else if(isset($_POST['modification_participant']) && $this->participant->existe())
       {   
          if ($this->config->get("inscription_terminees"))
             return;
@@ -62,52 +76,65 @@ class Controller
          $this->traiter_donnees_inscription();
          if (!$this->login_deja_pris() && $this->donnees_inscription_valides())
          {
-            mysql_query("BEGIN TRANSACTION");
-            mysql_query("UPDATE participants SET pseudo = '".addslashes($_POST['pseudo'])."' WHERE id = " . $this->participant->info->id);
-            mysql_query("UPDATE participants SET password = '".addslashes($_POST['pass1'])."' WHERE id = " . $this->participant->info->id);
-            mysql_query("UPDATE participants SET clan_nom = '".addslashes($_POST['clan_nom'])."' WHERE id = " . $this->participant->info->id);
-            mysql_query("UPDATE participants SET clan_tag = '".addslashes($_POST['clan_tag'])."' WHERE id = " . $this->participant->info->id);
-            mysql_query("UPDATE participants SET nom = '".addslashes($_POST['nom'])."' WHERE id = " . $this->participant->info->id);
-            mysql_query("UPDATE participants SET prenom = '".addslashes($_POST['prenom'])."' WHERE id = " . $this->participant->info->id);
-            mysql_query("UPDATE participants SET age = '".addslashes($_POST['age'])."' WHERE id = " . $this->participant->info->id);
-            mysql_query("UPDATE participants SET e_mail = '".addslashes($_POST['e_mail'])."' WHERE id = " . $this->participant->info->id);
-            mysql_query("UPDATE participants SET remarques = '".addslashes($_POST['remarques'])."' WHERE id = " . $this->participant->info->id);
+            pg_query("BEGIN");
+            pg_query_params("UPDATE participants SET pseudo = $1 WHERE id = $2", array($_POST['pseudo'], $this->participant->info->id));
+            pg_query_params("UPDATE participants SET password = $1 WHERE id = $2", array($_POST['pass1'], $this->participant->info->id));
+            pg_query_params("UPDATE participants SET clan_nom = $1 WHERE id = $2", array($_POST['clan_nom'], $this->participant->info->id));
+            pg_query_params("UPDATE participants SET clan_tag = $1 WHERE id = $2", array($_POST['clan_tag'], $this->participant->info->id));
+            pg_query_params("UPDATE participants SET nom = $1 WHERE id = $2", array($_POST['nom'], $this->participant->info->id));
+            pg_query_params("UPDATE participants SET prenom = $1 WHERE id = $2", array($_POST['prenom'], $this->participant->info->id));
+            pg_query_params("UPDATE participants SET age = $1 WHERE id = $2", array($_POST['age'], $this->participant->info->id));
+            pg_query_params("UPDATE participants SET e_mail = $1 WHERE id = $2", array($_POST['e_mail'], $this->participant->info->id));
+            pg_query_params("UPDATE participants SET remarques = $1 WHERE id = $2", array($_POST['remarques'], $this->participant->info->id));
             $this->set_periodes($this->participant->info->id);
-            mysql_query("COMMIT");
+            pg_query("COMMIT");
             //header("Location: /inscrits.html");
             $this->message_utilisateur = "Les modifications ont été enregistrées";
-            $page = "inscrits"; // TODO : moche car la page ne va plus correspondre à l'url
+            
+            // Puisque les données du participant ont changés il faut les re-charger.
+            $this->participant->chargerInfos();
          }
       }
       # vote pour des jeux (autorisé même lorsque les inscriptions sont terminées)
-      else if (isset($_POST['set_jeux_joues']) && $this->participant->valide)
+      else if (isset($_POST['set_jeux_joues']) && $this->participant->existe())
       {
          $votes = $_POST['votes'];
          if (!$votes)
             $votes = array();
          
-         mysql_query("BEGIN TRANSACTION");
+         pg_query("BEGIN");
          
          # l'utilisateur peut proposer le nom d'un jeu qui ne se trouve pas dans la liste
          $jeu = trim($_POST['jeu']);
          if ($jeu !== '')
          {
-            mysql_query("INSERT INTO jeux (nom) VALUES ('".addslashes($jeu)."')");
-            $id = mysql_insert_id();
-            if ($id != 0) # si le jeu se trouve déjà dans la liste alors $id == 0
-               array_unshift($votes, $id);
+            if(@pg_query_params("INSERT INTO jeux (nom) VALUES ($1)", array($jeu)))
+            {                      
+               $row = pg_fetch_row(pg_query("SELECT LASTVAL()"));
+               $id = $row[0];
+               if ($id != 0) # si le jeu se trouve déjà dans la liste alors $id == 0
+                  array_unshift($votes, $id);
+            }
+            else # Puisque le jeu existe déjà, on le recherche
+            {
+               pg_query("ROLLBACK");
+               pg_query("BEGIN");
+               $res = pg_query_params("SELECT id FROM jeux WHERE nom = $1", array($jeu));
+               if ($id = pg_fetch_object($res))
+                  array_unshift($votes, $id->id);
+            }
          }
          
          # suppression des anciens votes (remplacement par les nouveaux)
-         mysql_query("DELETE FROM jeux_choisis WHERE participant_id = " . $this->participant->info->id);
+         pg_query_params("DELETE FROM jeux_choisis WHERE participant_id = $1", array($this->participant->info->id));
 
          # traite les trois premiers votes
          for ($i = 0; $i < count($votes) && $i < $this->config->get('nb_votes_jeux'); $i++)
          {
-            mysql_query("INSERT INTO jeux_choisis (participant_id, jeu_id) VALUES (".$this->participant->info->id.", ".(int)$votes[$i].")");
+            pg_query_params("INSERT INTO jeux_choisis (participant_id, jeu_id) VALUES ($1, $2)", array($this->participant->info->id, (int)$votes[$i]));
          }
          
-         mysql_query("COMMIT");
+         pg_query("COMMIT");
       }
    }
    
@@ -121,10 +148,10 @@ class Controller
 
    private function login_deja_pris()
    {
-      if ($this->participant->valide && strtolower($this->participant->info->pseudo) === strtolower($_POST['pseudo'])) // le pseudo n'a pas changé
+      if ($this->participant->existe() && strtolower($this->participant->info->pseudo) === strtolower($_POST['pseudo'])) // le pseudo n'a pas changé
          return FALSE;
          
-      $loginDejaPris = mysql_fetch_array(mysql_query("SELECT count(*) FROM participants WHERE pseudo = '".addslashes($_POST['pseudo'])."'"));
+      $loginDejaPris = pg_fetch_array(pg_query_params("SELECT count(*) FROM participants WHERE pseudo = $1", array($_POST['pseudo'])));
       if ($loginDejaPris[0] > 0)
       {
          $this->message_utilisateur = "Le pseudo '".$_POST["pseudo"]."' est déjà pris";
@@ -159,13 +186,13 @@ class Controller
       if (!$periodes)
          $periodes = array();
       
-      mysql_query("DELETE FROM participations WHERE participant_id = " . (int)$id);
+      pg_query_params("DELETE FROM participations WHERE participant_id = $1", array($id));
       for ($i = 0; $i < count($periodes); $i++)
       {
-         mysql_query("
+         pg_query_params("
             INSERT INTO participations (participant_id, periode_id)
-            VALUES (".$id.", ".(int)$periodes[$i].")
-         ");
+            VALUES ($1, $2)", array($id, $periodes[$i])
+         );
       }
    }
 }

diff --git a/php/fonc_images.php b/php/fonc_images.php
index f001223..6c7e0ef 100644 (file)
--- a/php/fonc_images.php
+++ b/php/fonc_images.php
@@ -92,7 +92,7 @@ function image_lien_html($card)
                if(!@getimagesize($link->path().'DivXDB/images/image_tmp/'.$card->id.'.jpg'))
                {
                        #si l'image n'est pas valide (en tant que jpeg) alors l'efface de la BD
-                       mysql_query("update movie set image = '' where id = ".$card->id, $glob->get('_the_db_'));
+                       pg_query_params($glob->get('_the_db_'), "update movie set image = '' where id = $1", array($card->id));
                        unlink($link->path().'DivXDB/images/image_tmp/'.$card->id.'.jpg');
                }
        }

diff --git a/php/pages/inscription.php b/php/pages/inscription.php
index cfdd1e7..dd603ac 100644 (file)
--- a/php/pages/inscription.php
+++ b/php/pages/inscription.php
@@ -6,7 +6,7 @@ if ($config->get("inscription_terminees"))
    return;
 }
 
-if ($participant->valide)
+if ($participant->existe())
    echo '<p>Modification de mes infos</p>';
 else
    echo'<p><em>Les personnes inscrites s\'engagent à être présentes à la LAN et à payer la somme convenue.</em></p><p>Elles peuvent se désinscrirent en cas d\'empèchements majeurs.</p>';
@@ -14,7 +14,7 @@ else
 
 <form id="formulaireInscription" method="post" action="inscription.html">
 <?php
-   if($participant->valide)
+   if($participant->existe())
                echo '<p><input type="hidden" name="modification_participant" value="1" /></p>';
    else
       echo '<p><input type="hidden" name="inscription" value="1" /></p>';
@@ -29,7 +29,7 @@ else
          pseudo <span class="miniInfo">(login)</span>
       </td>
       <td>
-         <input type="text" maxlength="50" name="pseudo" value="<?=$participant->valide ? $participant->info->pseudo : $_POST["pseudo"]?>" />
+         <input type="text" maxlength="50" name="pseudo" value="<?=$participant->existe() ? $participant->info->pseudo : $_POST["pseudo"]?>" />
       </td>
    </tr>
    <tr>
@@ -37,8 +37,8 @@ else
          password <span class="miniInfo">(pour pouvoir par la suite modifier mes infos)</span>
       </td>
       <td>
-         <input type="password" size="10" maxlength="10" name="pass1" value="<?=$participant->valide ? $participant->info->password : $_POST["pass1"]?>" />
-         re: <input type="password" maxlength="10" size="10" name="pass2" value="<?=$participant->valide ? $participant->info->password : $_POST["pass2"]?>" /> 
+         <input type="password" size="10" maxlength="10" name="pass1" value="<?=$participant->existe() ? $participant->info->password : $_POST["pass1"]?>" />
+         re: <input type="password" maxlength="10" size="10" name="pass2" value="<?=$participant->existe() ? $participant->info->password : $_POST["pass2"]?>" /> 
       </td>
    </tr>
    <tr>
@@ -46,16 +46,14 @@ else
          clan name
       </td>
       <td>
-         <input type="text" maxlength="30" size="15" name="clan_nom" value="<?=$participant->valide ? $participant->info->clan_nom : $_POST["clan_nom"]?>" /> 
+         <input type="text" maxlength="30" size="15" name="clan_nom" value="<?=$participant->existe() ? $participant->info->clan_nom : $_POST["clan_nom"]?>" /> 
          <select id="clanChoix" name="clanChoix" size="1">
             <option value ="0" selected="selected">clans existants</option>
             <?php
-               $res = mysql_query("SELECT DISTINCT clan_nom, clan_tag FROM participants WHERE clan_nom != '' OR clan_tag != ''");
-               while($info_clan = mysql_fetch_object($res))
-               {
-                  echo '<option value = "', $info_clan->clan_nom, ';', $info_clan->clan_tag, '">', $info_clan->clan_nom != '' ? $info_clan->clan_nom : $info_clan->clan_tag, '</option>';
-               }
-            ?>
+                  $res = pg_query("SELECT DISTINCT clan_nom, clan_tag FROM participants WHERE clan_nom != '' OR clan_tag != ''");
+                  while($info_clan = pg_fetch_object($res))
+                     echo '<option value = "', $info_clan->clan_nom, ';', $info_clan->clan_tag, '">', $info_clan->clan_nom != '' ? $info_clan->clan_nom : $info_clan->clan_tag, '</option>';
+               ?>
          </select>
       </td>
    </tr> 
@@ -64,7 +62,7 @@ else
          clan tag
       </td>
       <td>
-         <input type="text" maxlength="10" name="clan_tag" value="<?=$participant->valide ? $participant->info->clan_tag : $_POST['clan_tag']?>" /> 
+         <input type="text" maxlength="10" name="clan_tag" value="<?=$participant->existe() ? $participant->info->clan_tag : $_POST['clan_tag']?>" /> 
       </td>
    </tr> 
    <tr>
@@ -72,7 +70,7 @@ else
          nom
       </td>
       <td>
-         <input type="text" maxlength="30" name="nom" value="<?=$participant->valide ? $participant->info->nom : $_POST['nom']?>" />
+         <input type="text" maxlength="30" name="nom" value="<?=$participant->existe() ? $participant->info->nom : $_POST['nom']?>" />
       </td>
    </tr>
    <tr>
@@ -80,7 +78,7 @@ else
          prénom
       </td>
       <td>
-         <input type="text" maxlength="30" name="prenom" value="<?=$participant->valide ? $participant->info->prenom : $_POST['prenom']?>" />
+         <input type="text" maxlength="30" name="prenom" value="<?=$participant->existe() ? $participant->info->prenom : $_POST['prenom']?>" />
       </td>
    </tr>
    <tr>
@@ -88,7 +86,7 @@ else
          age
       </td>
       <td>
-         <input type="text" maxlength="30" name="age" value="<?=$participant->valide ? $participant->info->age : $_POST['age']?>" />
+         <input type="text" maxlength="30" name="age" value="<?=$participant->existe() ? $participant->info->age : $_POST['age']?>" />
       </td>
    </tr>
    <tr>
@@ -96,7 +94,7 @@ else
          email <span class="miniInfo">(non-public)</span>
       </td>
       <td>
-         <input type="text" maxlength="30" name="e_mail" value="<?=$participant->valide ? $participant->info->e_mail : $_POST['e_mail']?>" />
+         <input type="text" maxlength="30" name="e_mail" value="<?=$participant->existe() ? $participant->info->e_mail : $_POST['e_mail']?>" />
       </td>
    </tr>
    <tr>
@@ -105,17 +103,15 @@ else
       </td>
       <td>
          <?php          
-            $res = mysql_query("
+            $res = pg_query_params("
                SELECT periodes.id, periodes.nom, participations.participant_id
                FROM periodes
                LEFT JOIN participations ON periodes.id = participations.periode_id
-                  AND participations.participant_id = ".($participant->valide ? $participant->info->id : "0")."
+                  AND participations.participant_id = $1
                ORDER BY periodes.id
-            ");
-            while($periode = mysql_fetch_object($res))
-            {
-               echo '<p><input name="periodes[]" value="'.$periode->id.'" '.((!$participant->valide && (!$_POST['periodes'] || in_array($periode->id, $_POST['periodes']))) || $periode->participant_id ? 'checked="checked"' : '').' id="periode'.$periode->id.'" type="checkbox" /><label for="periode'.$periode->id.'" />'.$periode->nom.'</label></p>';
-            }
+            ", array(($participant->existe() ? $participant->info->id : "0")));
+            while($periode = pg_fetch_object($res))
+               echo '<p><input name="periodes[]" value="'.$periode->id.'" '.((!$participant->existe() && (!$_POST['periodes'] || in_array($periode->id, $_POST['periodes']))) || $periode->participant_id ? 'checked="checked"' : '').' id="periode'.$periode->id.'" type="checkbox" /><label for="periode'.$periode->id.'" />'.$periode->nom.'</label></p>';
          ?>
       </td>
    </tr>
@@ -132,11 +128,11 @@ else
          remarques
       </td>
       <td>
-         <textarea cols="30" rows="5" name="remarques"><?=$participant->valide ? $participant->info->remarques : $_POST['remarques']?></textarea>
+         <textarea cols="30" rows="5" name="remarques"><?=$participant->existe() ? $participant->info->remarques : $_POST['remarques']?></textarea>
       </td>
    </tr>
    <?php
-      if (!$participant->valide)
+      if (!$participant->existe())
       echo'
          <tr>
             <td>

diff --git a/php/pages/inscrits.php b/php/pages/inscrits.php
index ad26645..a28efdb 100644 (file)
--- a/php/pages/inscrits.php
+++ b/php/pages/inscrits.php
@@ -2,20 +2,20 @@
 
 include_once("php/traitement_pre_affichage.php");
 
-$participants = mysql_query("
+$participants = pg_query("
    SELECT id, pseudo, nom, prenom, clan_nom, clan_tag, remarques
    FROM participants
-   ORDER by clan_nom, clan_tag, pseudo
+   ORDER by clan_nom NULLS FIRST, clan_tag NULLS FIRST, pseudo
 ");
 
-$periodes = mysql_query("SELECT nom_abrege FROM periodes ORDER BY id");
+$periodes = pg_query("SELECT nom_abrege FROM periodes ORDER BY id");
 $debut_table = '
  <tr>
   <th>pseudo</th>
   <th>nom</th>
   <th>prénom</th>';
 $nb_periode = 0;
-while($periode = mysql_fetch_object($periodes))
+while($periode = pg_fetch_object($periodes))
 {
    $debut_table .= '<th>'.$periode->nom_abrege.'</th>';
    $nb_periode++;
@@ -23,10 +23,10 @@ while($periode = mysql_fetch_object($periodes))
 $debut_table .= '<th>remarques</th>
  </tr>';
  
-$clan_courant = null;
+$clan_courant = "PHP c'est du CACA";
 
 echo '<table class="inscrits">';
-while($participant = mysql_fetch_object($participants))
+while($participant = pg_fetch_object($participants))
 {  
    if ($clan_courant !== $participant->clan_nom)
    {
@@ -34,18 +34,18 @@ while($participant = mysql_fetch_object($participants))
       $clan_courant = $participant->clan_nom;
    }
    
-   $participations = mysql_query("
+   $participations = pg_query_params("
       SELECT periodes.nom, participations.participant_id FROM periodes
-      LEFT JOIN participations ON periodes.id = participations.periode_id AND participations.participant_id = ".$participant->id."
+      LEFT JOIN participations ON periodes.id = participations.periode_id AND participations.participant_id = $1
       ORDER BY id
-   ");
+   ", array($participant->id));
    
    echo '<tr>';
    echo '<td>', htmlentities($participant->clan_tag, ENT_QUOTES, "UTF-8"), traitement_pre_affichage($participant->pseudo), '</td>';
    echo '<td>', traitement_pre_affichage($participant->nom), '</td>';
    echo '<td>', traitement_pre_affichage($participant->prenom), '</td>';
-   while ($participation = mysql_fetch_object($participations))
-     echo '<td '.($participation->participant_id == $participant->id ? 'class="participe"' : '').'></td>';
+   while ($participation = pg_fetch_object($participations))
+      echo '<td '.($participation->participant_id == $participant->id ? 'class="participe"' : '').'></td>';
    
    echo '<td>', traitement_pre_affichage($participant->remarques), '</td>';
    echo '</tr>';

diff --git a/php/pages/jeux_joues.php b/php/pages/jeux_joues.php
index 6bf932d..a05a388 100644 (file)
--- a/php/pages/jeux_joues.php
+++ b/php/pages/jeux_joues.php
@@ -2,7 +2,7 @@
 \r
 include_once("php/traitement_pre_affichage.php");\r
 \r
-if (!$participant->valide)\r
+if (!$participant->existe())\r
 {\r
    echo '<p><em>Remarque : </em>Il faut être inscrit pour pouvoir voter.</p>';\r
 }\r
@@ -12,30 +12,42 @@ echo '
 <form id="formulaireJeuxJoues" method="post" action="index.php?page=jeux_joues">\r
    <p><input type="hidden" name="set_jeux_joues" value="1" /></p>\r
    <table>\r
-      <tr>', ($participant->valide ? '<th></th>' : ''), '<th>Votes</th><th>Jeux</th></tr>';\r
-\r
-$jeux_query = mysql_query("\r
-   SELECT jeux.id, jeux.nom, jeux_choisis.participant_id, COUNT(*) + IF(participant_id is not null, 1, 0) - 1 AS nb_vote\r
-   FROM jeux LEFT JOIN jeux_choisis ON jeux.id = jeux_choisis.jeu_id\r
-   GROUP BY jeux.id\r
-   ORDER BY nb_vote DESC, nom \r
+      <tr>', ($participant->existe() ? '<th></th>' : ''), '<th>Votes</th><th>Jeux</th></tr>';\r
+
+# /!\ Attention, requête un poil tricky :
+# On souhait avoir une liste des jeux joués avec pour chaque tuple :
+#  - l'id du jeu et son nom
+#  - Un tableau des participants ayant voté pour ce jeu
+# De plus il faut que le résultat soit trié par le nombre de vote\r
+$jeux_query = pg_query("\r
+   SELECT 
+      jeux.id,
+      jeux.nom,
+      array_agg(jeux_choisis.participant_id) AS participants,
+      (COUNT(*) + (CASE WHEN bool_and(jeux_choisis.participant_id is null) THEN -1 ELSE 0 END)) as nb_vote
+   FROM 
+      jeux 
+      LEFT JOIN jeux_choisis ON jeux.id = jeux_choisis.jeu_id
+   GROUP BY jeux.id, jeux.nom  
+   ORDER BY nb_vote DESC, jeux.nom\r
 ");\r
    \r
-while ($jeu = mysql_fetch_object($jeux_query))\r
-{\r
+while ($jeu = pg_fetch_object($jeux_query))\r
+{
+   $a_vote = FALSE;
+      \r
    # est-ce que le participant courant à voté pour ce jeu ?\r
-   if ($participant->valide)\r
-   {\r
-      $a_vote = mysql_fetch_row(mysql_query("\r
-         SELECT COUNT(*) FROM jeux_choisis\r
-         WHERE participant_id = ".$participant->info->id." AND jeu_id = ".$jeu->id\r
-      )); $a_vote = $a_vote[0];\r
+   if ($participant->existe())\r
+   {
+      $participants = split(",", ereg_replace("[{-}]", "", $jeu->participants));
+      for($i = 0; $i < count($participants); $i++)
+         $participants[$i] = (int)$participants[$i];
+      
+      $a_vote = in_array($participant->info->id, $participants);\r
    }\r
-   else\r
-      $a_vote = FALSE;\r
    \r
    echo '<tr>',\r
-      $participant->valide ? '<td><input type="checkbox" name="votes[]" '. ($a_vote ? 'checked="checked"' : '') .' value="'.$jeu->id.'" /></td>' : '',\r
+      $participant->existe() ? '<td><input type="checkbox" name="votes[]" '. ($a_vote ? 'checked="checked"' : '') .' value="'.$jeu->id.'" /></td>' : '',\r
       '<td>' . $jeu->nb_vote . '</td>',\r
       '<td ' . ($a_vote ? 'class="aVote" ': '').'>' . traitement_pre_affichage($jeu->nom) . '</td></tr>';\r
 }\r
@@ -43,7 +55,7 @@ while ($jeu = mysql_fetch_object($jeux_query))
 echo '\r
    </table>';\r
 \r
-if ($participant->valide)\r
+if ($participant->existe())\r
    echo '\r
    <p>Autre : <input type="text" maxlength="50" name="jeu" /></p>\r
    <p><input type="submit" value="Voter" /></p>';\r
@@ -51,7 +63,7 @@ if ($participant->valide)
 echo '</form>';\r
 \r
 # affichage du nombre de vote restant\r
-if ($participant->valide)\r
+if ($participant->existe())\r
    echo '<p>Nombre de votes restant : ' . $participant->nb_vote_restant() . '</p>';\r
 \r
 ?>
\ No newline at end of file

diff --git a/php/pages/pizzas.php b/php/pages/pizzas.php
index be856bd..bb5de5e 100644 (file)
--- a/php/pages/pizzas.php
+++ b/php/pages/pizzas.php
@@ -6,7 +6,7 @@ function selection_pizzas()
 {\r
    global $config;\r
    global $participant;\r
-   $requ = mysql_query("select * from pizzas order by nom");\r
+   $requ = pg_query("select * from pizzas order by nom");\r
    echo '<h1>commande de pizza</h1>';\r
    \r
    if ($config->get('pizza_peut_commander'))\r
@@ -18,7 +18,7 @@ function selection_pizzas()
       echo '<table width ="100%" border="0" cellpadding="1" cellspacing="3">';\r
       echo '<tr><td class="pizzaHeader"></td><td class="pizzaHeader">Nom</td><td class="pizzaHeader">Composition</td><td class="pizzaHeader">Prix</td></tr>';\r
       echo '<tr><td><input type="radio" name="piz_choisie" value="-1" ', ($participant->info->pizza==null?'checked':''), '></td><td class="texte">', ($participant->info->pizza==null?'<b>':''), 'Aucune', ($participant->info->pizza==null?'</b>':''), '</td><td></td><td></td></tr>';\r
-      while($pizza = mysql_fetch_object($requ))\r
+      while($pizza = pg_fetch_object($requ))\r
          echo '<tr><td width="1" ',($participant->info->pizza==$pizza->id?'class="pizzaChoisie"':''),'><input type="radio" id="pizza_', $pizza->id ,'" name="piz_choisie" value="', $pizza->id, '"',($participant->info->pizza==$pizza->id?'checked':''), '></td><td class="',($participant->info->pizza==$pizza->id?'pizzaChoisie ':''),'texte"><label for="pizza_', $pizza->id ,'">', $pizza->nom, '</label></td><td class="',($participant->info->pizza==$pizza->id?'pizzaChoisie ':''),'texte"><label for="pizza_', $pizza->id ,'">', $pizza->composition, '</label></td><td class="',($participant->info->pizza==$pizza->id?'pizzaChoisie ':''),'texte"><label for="pizza_', $pizza->id ,'">', $pizza->prix, '.-</label></td></tr>';\r
       echo '</table><br><input type="submit" value="Commander"></form>';\r
    }\r
@@ -32,19 +32,19 @@ function selection_pizzas()
 // Affiche les statistique sur les pizza\r
 function stats()\r
 {\r
-   $requ_pizza = mysql_query("select * from pizzas order by nom");\r
-   $requ_participant = mysql_query("select * from participants");\r
+   $requ_pizza = pg_query("select * from pizzas order by nom");\r
+   $requ_participant = pg_query("select * from participants");\r
    $nb = array();\r
    $nb_tot = 0;\r
    $total = 0;\r
-   $nb_pizza = mysql_fetch_row(mysql_query("select count(*) from pizzas"));\r
+   $nb_pizza = pg_fetch_row(pg_query("select count(*) from pizzas"));\r
    \r
    // Initialise le tableau de comptage\r
    for ($i=0; $i < ($nb_pizza[0] + 3); $i++)  // on fait un '+3' parce que on ne sais jamais trop ce qui pourrait se passer ;-))\r
       $nb[$i] = 0;\r
      \r
    // Rempli le tableau de nombre de pizzas\r
-   while ($participant = mysql_fetch_object($requ_participant))\r
+   while ($participant = pg_fetch_object($requ_participant))\r
       if ($participant->pizza != null)\r
           $nb[$participant->pizza]++;\r
       \r
@@ -52,7 +52,7 @@ function stats()
    echo '<table width="100%" border="0" cellpadding="1" cellspacing="3">';\r
    echo '<tr><td class="pizzaHeader">Nom</td><td class="pizzaHeader">Nombre</td><td class="pizzaHeader">Prix unitaire</td><td class="pizzaHeader">Prix total</td></tr>';   \r
       \r
-   while($pizza = mysql_fetch_object($requ_pizza))\r
+   while($pizza = pg_fetch_object($requ_pizza))\r
    {\r
       if ($nb[$pizza->id] == 0)\r
          continue;      \r
@@ -67,8 +67,8 @@ function stats()
 // Retourne le nom de la pizza en fonction de son ID\r
 function pizza ($id)\r
 {\r
-   $requ = mysql_query("select * from pizzas where id = " . $id);\r
-   if ($pizza = mysql_fetch_object($requ))\r
+   $requ = pg_query_params("select * from pizzas where id = $1", array($id));\r
+   if ($pizza = pg_fetch_object($requ))\r
       return $pizza->nom;\r
    else\r
       return 'Pizza inexistante !';\r
@@ -79,18 +79,18 @@ function pizza ($id)
 function kiakoi()\r
 {\r
    global $participant;\r
-   $requ = mysql_query("select * from participants order by nom");\r
+   $requ = pg_query("select * from participants order by nom");\r
    \r
    \r
    echo '<h1>qui prend quoi</h1>';\r
    echo '<table width="100%" border="0" cellpadding="1" cellspacing="3">';\r
    echo '<tr><td class="pizzaHeader">Pseudo</td><td class="pizzaHeader">Nom</td><td class="pizzaHeader">Prix</td><td class="pizzaHeader">Paiement</td></tr>';\r
    \r
-   while ($participant = mysql_fetch_object($requ))\r
+   while ($participant = pg_fetch_object($requ))\r
    {\r
       if ($participant->pizza != null)\r
       {\r
-         $pizza = mysql_fetch_object(mysql_query("select * from pizzas where id = " . $participant->pizza));\r
+         $pizza = pg_fetch_object(pg_query_params("select * from pizzas where id = $1", array($participant->pizza));\r
          echo '<tr><td class="texte">', $participant->pseudo ,'</td><td class="texte">', $pizza->nom, '</td><td class="texte">', $pizza->prix, '.-</td><td class="texte">', ($participant->info->admin?'<a href="?page=pizzas&stats=1&paye=' . $participant->id . '">':''), ($participant->pizza_paye?'payé':'non payé !'), ($participant->info->admin?'</a>':'') ,'</td></tr>';\r
       }\r
       else\r
@@ -122,24 +122,24 @@ function liens()
 \r
 \r
 echo '<div id="pizzas">';\r
-if ($participant->valide) // le participant est loggé\r
+if ($participant->existe()) // le participant est loggé\r
 {\r
    // Si demande d'effacer les commande et que le gars est admin, le fait\r
    if(isset($_GET['reset']) && $participant->info->admin)\r
    {\r
-      mysql_query("update participants set pizza = null, pizza_paye = 0");\r
+      pg_query("update participants set pizza = null, pizza_paye = 0");\r
       $participant->info->pizza = -1; //mettre a jour le participants courant pour la beaute de l'affichage\r
    }\r
       \r
    if(isset($_GET['paye']) && $participant->info->admin)\r
    {  \r
       /////// methode d'inversion\r
-      // $gars = mysql_fetch_object(mysql_query("select * from participants where id = " . $paye));\r
-      // mysql_query("update participants set pizza_paye = " . (1 - $gars->pizza_paye) . " where id = " . $paye);\r
+      // $gars = pg_fetch_object(pg_query_params("select * from participants where id = $1", array($paye)));\r
+      // pg_query_object("update participants set pizza_paye = $1 where id = $2", array(1 - $gars->pizza_paye, $paye));\r
       ///////\r
       \r
       /////// methode d'un unique changement : non-paye -> paye\r
-      mysql_query("update participants set pizza_paye = 1 where id = " . $_GET['paye']);\r
+      pg_query_params("update participants set pizza_paye = 1 where id = $1", array($_GET['paye']));\r
       ///////\r
    }\r
       \r
@@ -158,7 +158,7 @@ if ($participant->valide) // le participant est loggé
             echo 'Aucune pizza commandée !'; // la pizza est '-1' donc pas de pizza commandée         \r
             $pizza_id = "NULL";\r
          }\r
-         mysql_query("update participants set pizza = " . $pizza_id . " where id = " . $participant->info->id);\r
+         pg_query_params("update participants set pizza = $1 where id = $2", array($pizza_id, $participant->info->id));\r
       }\r
       else\r
          selection_pizzas();\r

diff --git a/php/panel.php b/php/panel.php
index f65705f..54b1b53 100644 (file)
--- a/php/panel.php
+++ b/php/panel.php
@@ -25,16 +25,12 @@ class Panel
       # affichage des participants
       if (pg_num_rows($res_SQL) > 0)
          echo '<ul id="participants">';
-      for ($i=0; $i<pg_num_rows($res_SQL); $i++)
-      {
-         pg_data_seek($res_SQL, $i);
-         $le_participant_pseudo = pg_fetch_object($res_SQL);
-         echo '<li>', traitement_pre_affichage($le_participant_pseudo->pseudo, 8), '</li>';
-      }
+      while ($participant_pseudo = pg_fetch_object($res_SQL))
+         echo '<li>', traitement_pre_affichage($participant_pseudo->pseudo, 8), '</li>';
       if (pg_num_rows($res_SQL) > 0)
          echo '</ul>';
 
-      if($this->participant->valide)
+      if($this->participant->existe())
       {
          echo'
          <form method="post" action="index.php?';

diff --git a/php/participant.php b/php/participant.php
index 9f718b0..ef92a06 100644 (file)
--- a/php/participant.php
+++ b/php/participant.php
@@ -22,15 +22,18 @@ class Participant
       # Aucunes valeurs transmise => ce n'est pas un participant valide.
           if ($v1 == NULL && $v2 == NULL) 
          return;
-            
+
                if (is_string($v1) && is_string($v2)) # Aucun des arguments n'est vide alors c'est le pseudo et le password qui ont été transmis
+      {
                        $res = pg_query_params("SELECT id FROM participants WHERE pseudo = $1 AND password = $2", array($v1, $v2));
+      }
                else # Sinon c'est l'id
                        $res = pg_query_params("SELECT id FROM participants WHERE id = $1", array($v1));
    
-               if (pg_result_status($res) == PGSQL_COMMAND_OK && pg_num_rows($res) === 1)
+               if (pg_result_status($res) == PGSQL_TUPLES_OK && pg_num_rows($res) === 1)
       {
          $this->id = pg_fetch_object($res)->id;
+         $this->chargerInfos();
       }
        }
    
@@ -49,7 +52,7 @@ class Participant
          
       $res = pg_query_params("SELECT * FROM participants WHERE id = $1", array($this->id));
    
-      if (pg_result_status($res) == PGSQL_COMMAND_OK && pg_num_rows($res) === 1)
+      if (pg_result_status($res) == PGSQL_TUPLES_OK && pg_num_rows($res) === 1)
          $this->info = pg_fetch_object($res);
       else
          $this->id = 0;
@@ -74,7 +77,7 @@ class Participant
      */
    static function nombre_participant_max_atteint()
    {
-      return $this->nombre_place_restante() <= 0;
+      return Participant::nombre_place_restante() <= 0;
    }
    
    /**

diff --git a/php/participants.php b/php/participants.php
index 995752b..e71e59a 100644 (file)
--- a/php/participants.php
+++ b/php/participants.php
@@ -13,11 +13,10 @@ function ON($condition)
        return $condition ? 'Oui' : 'Non';
 }
 
-#connection à la base de données
-$lien_mysql = mysql_connect ($SQL_HOTE, $SQL_LOGIN, $SQL_PASS);
-mysql_select_db ($NOM_BASE);
+#connexion à la base de données
+$conn_bd = pg_connect(sprintf("host=%s dbname=%s user=%s password=%s", $SQL_HOTE, $NOM_BASE, $SQL_LOGIN, $SQL_PASS));
 
-$requ = mysql_query("select * from participants order by pseudo");
+$requ = pg_query("select * from participants order by pseudo");
 
 echo
 '<table width="100%" border="2" cellpadding="2" cellspacing="2"><tr>
@@ -34,7 +33,7 @@ echo
 </tr>
 ';
 
-while($participant = mysql_fetch_object($requ))
+while($participant = pg_fetch_object($requ))
 {
        echo '<tr>',
                '<td>',$participant->pseudo,'</td>',

diff --git a/php/update_db.php b/php/update_db.php
index 66a3216..be2c041 100644 (file)
--- a/php/update_db.php
+++ b/php/update_db.php
@@ -36,7 +36,7 @@ function creer_db()
    ");
    pg_query("
       CREATE TABLE participants (
-        id serial NOT NULL,
+        id int NOT NULL,
         pseudo varchar(50) DEFAULT NULL,
         clan_nom varchar(30) DEFAULT NULL,
         clan_tag varchar(10) DEFAULT NULL,
@@ -105,12 +105,12 @@ function update_db()
    # si la table 'config' n'existe pas alors on suppose qu'aucune table n'existe
    $version = 0;
    $res = @pg_fetch_object(pg_query("SELECT valeur FROM config WHERE nom = 'version'"));
-   if (pg_result_status($res) != PGSQL_COMMAND_OK)
+   if (pg_result_status($res) != PGSQL_TUPLES_OK)
       $version = $res->valeur;
       
    if($version == 0)
    {
-      echo "Création de la base de donnée, version = 1";
+      echo "Création de la base de donnée, version = 1\n";
       pg_query("BEGIN");
       creer_db();
       initialiser_db();