本文共 9995 字，大约阅读时间需要 33 分钟。

PHP Data Objects (PDO)

本节主要讲用PHP来实现数据库的基本操作。

• php编程实现数据库的读取
• php编程实现数据库的插入
• php编程实现数据库的更新
• php编程实现数据库的删除
• php编程实现数据库的搜索

下述实验用的数据库名为：ebookingsdb。用户名为：root。密码为空。

内置三个表：

Lab9.1 - Read Records using PDO

读取数据库数据

            
                           
   query($sql);        $records = $statement->fetchAll();        print_r($records);                $sql = 'select * from invitations';        $statement = $conn->query($sql);        $records = $statement->fetchAll();        print_r($records);                $sql = 'select * from users';        $statement = $conn->query($sql);        $records = $statement->fetchAll();        print_r($records);        ?>    

Lab9.2 - Insert Records using PDO

往数据库里插入数据

            
                           
   prepare($sql);        $success = $statement->execute($values);        if ($success) {               echo"Insert booking record successed!";        } else {               echo"Insert booking record failed!";        }        echo '
';        //Insert invitations        $values = [27, 'whh', '454578962', 'whh.jpg',2];        $sql = "insert into invitations (invitation_id,name,mobile,filename,booking_id)values(?,?,?,?,?)";        $statement = $conn->prepare($sql);        $success = $statement->execute($values);        if ($success) {               echo"Insert invitations record successed!";        } else {               echo"Insert invitations record failed!";        }        echo '
';        //Insert users        $values = [3,  'Smith', 'penny@yahoo.com', 'password'];        $sql = "insert into users (user_id,username,email,password)values(?,?,?,?)";        $statement = $conn->prepare($sql);        $success = $statement->execute($values);        if ($success) {               echo"Insert users record successed!";        } else {               echo"Insert users record failed!";        }        echo '
';        ?>    

Lab9.3 - Update Records using PDO

更新数据库数据

            
                           
   prepare($sql);        $success = $statement->execute($values);        if ($success) {               echo "Update booking record successed!";        } else {               echo "Update booking record failed!";        }        echo "
";        //Update invitations        $invitation_id = 1;        $values = ['whh', '457412356', 'lll.jpg'];        $sql = "update invitations set name = ?,mobile = ?,filename = ?where invitation_id = $invitation_id";        $statement = $conn->prepare($sql);        $success = $statement->execute($values);        if ($success) {               echo "Update invitations record successed!";        } else {               echo "Update invitations record failed!";        }        echo "
";        //Update users        $user_id = 1;        $values = ['whh', 'whh@yahoo.com', '1111'];        $sql = "update users set username = ?,email = ?,password = ?where user_id = $user_id";        $statement = $conn->prepare($sql);        $success = $statement->execute($values);        if ($success) {               echo "Update users record successed!";        } else {               echo "Update users record failed!";        }        echo "
";        ?>    

Lab9.4 - Delete Records using PDO

删除数据库数据

            
                           
   query($sql);        $success = $statement->execute();        if ($success) {               echo "Delete booking record successed!";        } else {               echo "Delete booking record failed!";        }        echo "
";                // delete any related records in the Invitations table that belong to the booking record deleted.           $sql = "delete from invitations where booking_id = $booking_id";        $statement = $conn->query($sql);        $success = $statement->execute();        if ($success) {               echo "Delete invitations record successed!";        } else {               echo "Delete invitations record failed!";        }        echo "
";        ?>    

Lab9.5 - Search Records using PDO

查询数据库数据

            
                           
    $value) {                   echo "$key : $value 
";            }        }        echo "
   
";        //seach mouse in  invitations         $conn = mysqli_connect('localhost', 'root', '', 'ebookingsdb');         $keyword = "Mouse";        $res = mysqli_query($conn, "select * from invitations where name like '%$keyword%' or filename like '%$keyword%' ") or die(mysqli_error($conn));        while ($row = mysqli_fetch_assoc($res)) {               foreach ($row as $key => $value) {                   echo "$key : $value 
";            }        }        ?>    

Lab9.6 - Progress Activity

综合练习：

以table的形式输出数据库中的内容：

   query("select * from bookings order by booking_id");        $statement->setFetchMode(PDO::FETCH_ASSOC);        $records = [];        while ($row = $statement->fetch()) {               $records[] = $row;        }        //display records in bootstrap table        include_once("includes/viewBookings.php");    } else if ($action == 'addBooking') {           $first_name = '';        $last_name = '';        $email = '';        $booking_date = '';        $booking_time = '';        $num_people = '';        $errors = [];        if (isset($_POST['submit'])) {               //validation            if (isset($_POST['first_name'])) {   //设定first_name的输入规范                $first_name = $_POST['first_name'];                if (strlen($first_name) == 0) {                       $errors['first_name'] = 'First Name is missing input';                } elseif (!ctype_alpha($first_name)) {                       $errors['first_name'] = 'Enter a valid First Name';                }            }            if (isset($_POST['last_name'])) {   //设定last_name的输入规范                $last_name = $_POST['last_name'];                if (strlen($last_name) == 0) {                       $errors['last_name'] = 'Last Name is missing input';                } elseif (!ctype_alpha($last_name)) {                       $errors['last_name'] = 'Enter a valid Last Name';                }            }            if (isset($_POST['email'])) {   //设定email的输入规范                $email = trim($_POST['email']);                if (strlen($email) == 0) {                       $errors['email'] = "Email address is missing input";                } else if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {                       $errors['email'] = "Enter a valid Email Address";                }            }            if (isset($_POST['booking_date'])) {                   $temp = $_POST['booking_date'];                $booking_date = reformat($_POST['booking_date']);                if ($temp == "dd/mm/yyyy") {                       $errors['booking_date'] = 'Booking date is missing input';                } elseif (!validateDate($temp)) {                       $errors['booking_date'] = 'Booking date is not valid';                }            }            if (isset($_POST['booking_time'])) {                   $booking_time = $_POST['booking_time'];                if (strlen($booking_time) == 0) {                       $errors['booking_time'] = 'booking_time is missing input';                }            }            if (isset($_POST['num_people'])) {                   $num_people = $_POST['num_people'];                if (strlen($num_people) == 0) {                       $errors['num_people'] = 'Number of people is missing input';                }            }            if (count($errors) == 0) {                   //insert booking record code here                $values = [$first_name, $last_name, $email, $booking_date, $booking_time, $num_people];                $dsn = 'mysql:host=localhost;dbname=ebookingsdb';                $username = 'root';                $password = '';                $conn = new PDO($dsn, $username, $password);                $sql = "insert into bookings (first_name,last_name,email,booking_date,booking_time,num_people)values(?,?,?,?,?,?)";                $statement = $conn->prepare($sql);                $success = $statement->execute($values);                if ($success) {                       include_once 'index.php'; //如果无错误就返回主页                    echo"Insert booking record successed!";                } else {                       include_once 'index.php';                    echo"Insert booking record failed!";                }            } else {                   include_once 'includes/addBookingForm.php'; //有不符合规范的就返回form并提示            }        } else {               include_once 'includes/addBookingForm.php';        }    } else {           include_once('includes/content.php');    }}include_once('includes/footer.php');

viewBookings.php:

   
    
    
        
     
        
     
      View Bookings:
      results
     
        
     
    
    
    
    
        
     
        
     
            
      
                       
        
          
Booking ID
         
First Name
         
Last Name
         
Email
         
Booking Date
         
Booking Time
         
People
         
Manage
         
                                                    
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
 Edit   Delete 
         
                                           
      
        
     
        
     
    
    
   

addBookingForm.php:

   
    
    
        
     
        
     
                  Add Booking        
     
        
     
    
    
    
    
        
     
        
     
            
      
                
       
                    
         First Name  
                                
                        
       
                
       
                    
         Last Name                     
         
                              
                        
       
                                    
       
                    
         Email                     
         
                              
                        
       
                  
       
                    
         Booking Date                     
         
                              
                        
       
                
       
                    
         Booking Time                     
         
                              
                         
       
                
       
                    
         Number of People