PHP添加预处理语句到数据库

Question

我想实现准备好的语句到我的脚本，但真的不能让它工作。我已经有很多功能，所以我希望尽可能少的改变。

我认为最好有一个准备好的语句函数?所以当我得到用户输入时，我可以调用那个函数而不是query。

database.php类

class MySQLDB {
    var $connection; // The MySQL database connection
    /* Class constructor */
    function MySQLDB() {
        global $dbsystem;
        $this->connection = mysqli_connect ( DB_SERVER, DB_USER, DB_PASS,   DB_NAME ) or die ( 'Connection Failed (' . mysqli_connect_errno () . ') ' . mysqli_connect_error () );
    }

    /**
     * query - Performs the given query on the database and
     * returns the result, which may be false, true or a
     * resource identifier.
     */
    function query($query) {
        return mysqli_query ( $this->connection, $query );
    }
};
/* Create database connection */
$database = new MySQLDB ();

这是我如何从另一个类调用数据库。

    $q = "UPDATE users SET name = '$name', started = '$time' WHERE id = '$id';";
    $result = mysqli_query ( $database->connection, $q );

Answer 1

在您的例子中，我会做一些更简洁的事情，像这样:

<?php
class MySQLDB{
  private function openConnection(){
    // If you don't always use same credentials, pass them by params
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $database = "database";
    // Create connection
    $conn = new mysqli($servername, $username, $password, $database);
    // Check connection
    if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
    }
    // Assign conection object
    return $conn;
  }
  private function closeConnection($conn){
    $conn->close();
  }
  function updateUserById($id, $name, $startedTime){
    $conn = $this->openConnection();
    $sqlQuery = "UPDATE users SET name = ?, started = ? WHERE id = ?";
    if ($stmt = $conn->prepare($sqlQuery)) {
      // Bind parameters
      $stmt->bind_param("ssi", $name, $startedTime, $id);
      // Execute query
      $stmt->execute();
      if ($stmt->errno) {
        die ( "Update failed: " . $stmt->error);
      }
      $stmt->close();
      }
    $this->closeConnection($conn);
  }
} // Class end

现在，要使用它，你只需要这样做:

<?php
$myDBHandler = new MySQLDB;
$myDBHandler->updateUserById(3, "Mark", 1234);