Appunti sull'interfaccia di PHP con MySQL

§ 9. MySQL Select Data

Per recuperate dati da una o più tabelle si utilizza lo statement SELECT:

SELECT column_mame(s) FROM table_name

per recuperate tutte le colonne di una tabella possiamo usare il carattere "*":

SELECT * FROM table_name

§ 9.1. Select con MySQLi object-oriented

   <?php
      include 'infoConnessione.php'    
   // SQL per SELECT
   
      $sql = "SELECT id, firstname, lastname, email, reg_data FROM MyGuest_1";

   // Create connection 
   
      $conn = new mysqli($servename, $username, $password, $dbname);
      
   // Check connection
   
      if ($conn-> connect_error){
         die("Connection failed: " . $conn-> connect_error);
      }
      echo "<p class=phpoutput> Connessione mysqli object-oriented riuscita. </p>;
      
   // Excecute query
   
      $result = $conn->query($sql);

   // Check for resultset not empty.
   
      if ($result->num_rows > 0){
      
      // Loop through recordset   

         while($row = $result->fetch_assoc()){
            echo ". $row["id"]. " " . $row["firstname"] . " " .$row["lastname"]. " "
            .$row["email"]. " " .$row["reg_date"]";
         }
      } else {
         echo "<p class=phpoutput> 
              0 results </p>";
      }
      
   // Close connection
   
      $conn->close();
   ?>             

§ 9.2. Select con MySQLi procedural

   <?php
   include 'infoConnessione.php';
      
   // SQL per SELECT
   
      $sql = "SELECT id, firstname, lastname, email, reg_date FROM MyGuest_2";

   // Create connection 
   
      $conn = new mysqli($servename, $username, $password, $dbname);
      
   // Check connection
      if (!$conn){
         die("Connection failed: " . mysqli_connect_error);
      }
      echo "<p class=phpoutput> Connessione mysqli procedural riuscita. </p>;
      
   // Excecute query
   
      $result = mysqli_query($conn, $sql);

   // Check for resultset not empty.
   
      if (mysqli_num_rows($result) > 0) {
 
   // Loop through recordset   

         while($row = mysqli_fetch_assoc($result)){
            echo ". $row["id"]. " " . $row["firstname"] . " " .$row["lastname"]. " "
                  .$row["email"]. " " .$row["reg_date"]";
         }
      } else {
         echo "<p class=phpoutput> 
              0 results </p>";
      }
      
   // Close connection
   
      mysqlì_close($conn);        
   ?>             

§ 9.3. Select con PDO

In questo paragrafo ho fatto qualche modifica rispetto all'esempio di w3schools. Arriverò all'esempio di w3schools gradualmente.

§ 9.3.1 Select con PDO usando il metodo query()

   <?php
      include 'infoConnessione.php';
      $conn = null;
      
   // SQL per SELECT
   
      $sql = "SELECT id, firstname, lastname, email, reg_date FROM MyGuest_3";

      try{
   // Create connection
   
         $conn = new PDO("mysql:host=$servername; dbname=$dbname",
                 $username, $password);
                 
   // Set attribute
   
         $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
         
         echo "<p class=phpoutput> Connessione pdo riuscita </p>";
         
   // Execute query 
   
           $result = $conn->query($sql);         
         
   // Check for resultset not empty.  
   
         if (count($result)> 0){  
         
            // Loop through recordset   
            
            foreach($result as $row){
               echo "<p class=phpoutput>". $row["id"]. " " . $row["firstname"] . " "
                  .$row["lastname"]. " " .$row["email"]. " " .$row["reg_date"]. "<p>";
            }
         } else {
            echo "<p class=phpoutput> 0 results </p>";
         }      
     }
      catch(PDOException $e){
         echo "<p class=phpoutput >". $sql. "</p>";
         echo "<p class=phpoutput >". $e->getMessage(). "</p>";
      }
      
   // Close connection
   
      if ($conn != null){
         $conn = null;
      } 
   ?>                       

§ 9.3.2 Select con PDO usando uno statement precompilato

   <?php
      include 'infoConnessione.php';
      $conn = null;
      
   // SQL per SELECT
   
      $sql = "SELECT id, firstname, lastname, email, reg_date FROM MyGuest_3";

      try{
   // Create connection
   
         $conn = new PDO("mysql:host=$servername; dbname=$dbname",
                 $username, $password);
                 
   // Prepare statement  
   
         $stmt = $conn->prepare($sql);
         
   // Execute statement  
   
         $stmt->execute();
         
   // Set the resulting array to associative
   
         $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 
         
   // Load the resulting array
   
         $result = $stmt->fetchAll(); 
         
   // Check for resultset not empty.  
   
         if (count($result)> 0){  
         
            // Loop through recordset   
            
            foreach($result as $row){
               echo "<p class=phpoutput>". $row["id"]. " " . $row["firstname"] . " "
                  .$row["lastname"]. " " .$row["email"]. " " .$row["reg_date"]. "<p>";
            }
         } else {
            echo "<p class=phpoutput> 0 results </p>";
         }      
     }
      catch(PDOException $e){
         echo "<p class=phpoutput >". $sql. "</p>";
         echo "<p class=phpoutput >". $e->getMessage(). "</p>";
      }
      
   // Close connection
   
      if ($conn != null){
         $conn = null;
      } 
   ?>                       

§ 9.3.3 Select con PDO usando uno statement precompilato e presentando il resultset in una tabella

<?php

   echo "<table style='border: solid 1px black;'>";
   echo "<tr><th>Id</th><th>Firstname</th><th>Lastname</th></tr>";

   class TableRows extends RecursiveIteratorIterator { 
      function __construct($it) { 
         parent::__construct($it, self::LEAVES_ONLY); 
      }

      function current() {
         return "<td style='width:150px;border:1px solid black;'>" . parent::current(). "</td>";
      }

      function beginChildren() { 
         echo "<tr>"; 
      } 

      function endChildren() { 
         echo "</tr>" . "\n";
      } 
   } 
   
   include 'infoConnessione.php';
   $conn = null;
      
// SQL per SELECT
   
   $sql = "SELECT id, firstname, lastname, email, reg_date FROM MyGuest_3";

   try{
   // Create connection
   
      $conn = new PDO("mysql:host=$servername; dbname=$dbname",
         $username, $password);
                 
   // Prepare statement  
   
      $stmt = $conn->prepare($sql);
         
   // Execute statement  
   
      $stmt->execute();
         
   // Set the resulting array to associative
   
      $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 
   
      foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) { 
         echo $v;
      }
   }
   catch(PDOException $e){
      echo "<p class=phpoutput >". $sql. "</p>";
      echo "<p class=phpoutput >". $e->getMessage(). "</p>";
   }
      
   // Close connection
   
   if ($conn != null){
      $conn = null;
   } 
?>