LAMP開發精要(13)：PHP中使用mysql_stmt（預先處理語句）處理select查詢結果

    許多PHP指令碼通常都會執行除參數以外，其他部分完全相同的查詢語句，針對這種重複執行一個查詢，每次迭代使用不同的參數情況，MySQL 從4.1版本開始提供了一種名為預先處理語句（prepared statement）的機制。它可以將整個命令向MySQL伺服器發送一次，以後只有參數發生變化，MySQL伺服器只需對命令的結構做一次分析就夠了。這不僅大大減少了需要傳輸的資料量，還提高了命令的處理效率。可以用mysqli擴充模式中提供的mysqli_stmt類的對象，去定義和執行參數化的 SQL命令。以下是使用這種機制實現的一個查詢過程。

     例子1：

     <?php

       $db = new mysqli("localhost","user","password","testdb");

       if (mysqli_connect_errno()){
         printf("Error:%s/n",mysqli_connect_error());
         exit;
       }
       else
       {
         if ($stmt = $db->prepare("select id,name,author,price from book where name like ?"))
         {

           $stmt->bind_param('s',$n);
           $n = "%p%";

           $stmt->execute();

           $stmt->store_result();
           $stmt->bind_result($id,$name,$author,$price);
           while ($stmt->fetch())
           {
             printf("%s:%s,%s,%s<br/>",$id,$name,$author,$price);
           }
           $stmt->close();
         }
         $db->close();
       }
     ?>

    例子2. Object oriented style

    <?php
    $mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');

    /* check connection */
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s/n", mysqli_connect_error());
        exit();
    }

    $stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
    $stmt->bind_param('sssd', $code, $language, $official, $percent);

    $code = 'DEU';
    $language = 'Bavarian';
    $official = "F";
    $percent = 11.2;

    /* execute prepared statement */
    $stmt->execute();

    printf("%d Row inserted./n", $stmt->affected_rows);

    /* close statement and connection */
    $stmt->close();

    /* Clean up table CountryLanguage */
    $mysqli->query("DELETE FROM CountryLanguage WHERE Language='Bavarian'");
    printf("%d Row deleted./n", $mysqli->affected_rows);

    /* close connection */
    $mysqli->close();
    ?>

    例子3. Procedural style

    <?php
    $link = mysqli_connect('localhost', 'my_user', 'my_password', 'world');

    /* check connection */
    if (!$link) {
        printf("Connect failed: %s/n", mysqli_connect_error());
        exit();
    }

    $stmt = mysqli_prepare($link, "INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
    mysqli_stmt_bind_param($stmt, 'sssd', $code, $language, $official, $percent);

    $code = 'DEU';
    $language = 'Bavarian';
    $official = "F";
    $percent = 11.2;

    /* execute prepared statement */
    mysqi_stmt_execute($stmt);

    printf("%d Row inserted./n", mysqli_stmt_affected_rows($stmt));

    /* close statement and connection */
    mysqli_stmt_close($stmt);

    /* Clean up table CountryLanguage */
    mysqli_query($link, "DELETE FROM CountryLanguage WHERE Language='Bavarian'");
    printf("%d Row deleted./n", mysqli_affected_rows($link));

    /* close connection */
    mysqli_close($link);
    ?>

    二例將輸出：

    1 Row inserted.
    1 Row deleted.