将PHP示例代码从mysqli转换为PDO

Question

I hope that someone would be willing to help me out here - since I know it shouldn't be too hard.

I found and tutorial example of something that I wish to do. Unfortunately the sample code make use of the old MYSQLi and I have no idea how to go about to change it over to PDO.

Here is a link to the full tutorial

 $connect = mysqli_connect("localhost", "root", "", "testing");  
 $query = "SELECT * FROM tbl_employee ORDER BY id desc";  
 $result = mysqli_query($connect, $query);

Then further down:

       <div class="container" style="width:800px;">  
            <h2 align="center">PHP AJAX Jquery - Load Dynamic Content in Bootstrap Popover</h2>  
            <h3 align="center">Employee Data</h3>                 
            <br /><br />  
            <div class="table-responsive">  
                 <table class="table table-bordered">  
                      <tr>  
                           <th width="20%">ID</th>  
                           <th width="80%">Name</th>  
                      </tr>  
                      <?php  
                      while($row = mysqli_fetch_array($result))  
                      {  
                      ?>  
                      <tr>  
                           <td><?php echo $row["id"]; ?></td>  
                           <td><a href="#" class="hover" id="<?php echo $row["id"]; ?>"><?php echo $row["name"]; ?></a></td>  
                      </tr>  
                      <?php  
                      }  
                      ?>  
                 </table>  
            </div>  
       </div>  

Answer 1

This is how you would do this query in PDO

database connection:

$host = 'localhost';
$db   = '';
$user = '';
$pass = '';
$charset = 'utf8';

$dsn = "mysql:host={$host};dbname={$db};charset={$charset}";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new PDO($dsn, $user, $pass, $opt);

Notice we set some extra parameters here, namely ATTR_EMULATE_PREPARES, which makes allows PDO to do proper prepared statements instead of emulated ones, which makes it safer. (If we didn't do this, it would basically be just as safe as string concatenation) Note, in your query you wrote, this actually doesn't matter much as there is nothing to prepare, but it will be useful later when you have some statements that needs data to be passed.

Query:

$stmt = $pdo->query("SELECT * FROM tbl_employee ORDER BY id desc")->fetchAll();

Loop:

if(!empty($stmt)) {
    foreach($stmt as $row) {  
        ?>  
            <tr>  
               <td><?php echo $row["id"]; ?></td>  
               <td><a href="#" class="hover" id="<?php echo $row["id"]; ?>"><?php echo $row["name"]; ?></a></td>  
            </tr>
        <?
    }
} else {
    //show error, no results
}

---

I would like to mention that the MySQLi_* interface is not really "old", it's not deprecated or unused by far. I prefer PDO over MySQLi because it seems easier to me, but both syntax's are fully valid and supported. The problem arises when you are not using Prepared Statements using either interface.