实现全局函数以连接到数据库并在PHP中执行查询

Question

我正处于PHP的学习阶段。有人能帮我写几个函数吗？

• ConDB($host, $user, $pwd)通过PDO连接到MySQL数据库。
• RunSQL($sql)执行语句(如CREATE、INSERT、UPDATE、DELETE)。该函数将使用ConDB()函数连接到数据库并执行$sql，然后自动关闭连接。
• QuerySQL($sql)查询数据库。它应该返回一个记录集数组，我们可以进一步使用这些记录集在页面上作为表显示。

RunSQL()和QuerySQL()函数应该可以在任何.php页面上使用。

如果能做些更好的事情来实现这一点，对我来说将是一个很大的帮助。谢谢。

例如，我希望能够这样写：

$sql = “CREATE TABLE Persons(Person_ID int Auto_Incrememt PrimaryKey, Name varchar(255))”;
RunSQL($sql);

$sql = “SELECT * FROM Persons ORDER BY Person_ID ASC”;
$result = QuerySQL($sql)

然后，使用while循环，我们可以将数据提取到一个表中。我希望你能明白这个想法。

Answer 1

奈杰尔·任的评论是正确的，但由于我在几年前遇到了同样的问题，这里是我当时使用的链接。它也有使用示例。滚动您自己的PDO类

下面您将看到我根据这个示例创建的数据库类、在该类中使用的一些常量以及一个简单的用法示例。

define("DB_USER",           "yourUser");
define("DB_PASS",           "yourPassword");
define("DB_HOST",           "localhost");
define("DB_NAME",           "yourDBName");
define("DB_CHARSET",        "utf8mb4");

//create new database handler
$dbh = new Database();

//sample query
$dbh->query('SELECT var FROM table WHERE user_id = :id');
$dbh->bind(':id', $id );
$result = $dbh->resultsetAssoc(); // a two dimensional array is returned
foreach ($result as $values) {
    //do something with $values["var"]
}


class Database
{
  private $host = DB_HOST;
  private $dbname = DB_NAME;
  private $charset = DB_CHARSET;
  private $user = DB_USER;
  private $pass = DB_PASS;    

  private $dbh;
  private $error;

  private $stmt;

  public function __construct() {
     // Set DSN
    $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname . ';charset=' . $this->charset;
    // Set options
    $options = array(
        PDO::ATTR_PERSISTENT    => true,
        PDO::ATTR_ERRMODE       => PDO::ERRMODE_EXCEPTION
    );
    // Create a new PDO instanace
    try{
        $this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
    }
        catch(PDOException $e) {  
            $this->error = $e->getMessage();               
            file_put_contents('PDOErrors.txt', $this->error, FILE_APPEND);
            die ("Database Connection Error");
    }
  }    
  public function query($query) {
    $this->stmt = $this->dbh->prepare($query);        
  }
  public function bind($param, $value, $type = null){
  //determine type as input for bindValue
    if (is_null($type)) {
      switch (true) {
          case is_int($value):
              $type = PDO::PARAM_INT;
              break;
          case is_bool($value):
              $type = PDO::PARAM_BOOL;
              break;
          case is_null($value):
              $type = PDO::PARAM_NULL;
              break;
          default:
              $type = PDO::PARAM_STR;
        }
      }
      // PDO method bindValue (PDOStatement::bindValue)
      $this->stmt->bindValue($param, $value, $type);
  }
  public function execute(){
      return $this->stmt->execute();
  }

//    This is returned by resultset():
//    Multidimemnsional array with column labeled values
//    and numbered values
//    Array
//(
//    [0] => Array
//        (
//            [name] => pear
//            [0] => pear
//            [colour] => green
//            [1] => green
//        )
//
//    [1] => Array
//        (
//            [name] => watermelon
//            [0] => watermelon
//            [colour] => pink
//            [1] => pink
//        )
//
//)
  public function resultset(){
      $this->execute();
      return $this->stmt->fetchAll();
  }

//    This is returned by resultsetAssoc():
//    Multidimemnsional array with column labeled values
//    and NO numbered values
//    Array
//(
//    [0] => Array
//        (
//            [name] => pear
//            [colour] => green
//        )
//
//    [1] => Array
//        (
//            [name] => watermelon
//            [colour] => pink
//        )
//
//)
  public function resultsetAssoc(){
      $this->execute();
      return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
  }
//  With single():
//  The array has only one dimension!!!
  public function single(){
      $this->execute();
      return $this->stmt->fetch();
  }
//  With singleAssoc():
//  The array has only one dimension!!!
  public function singleAssoc(){
      $this->execute();
      return $this->stmt->fetch(PDO::FETCH_ASSOC);
  }
  public function rowCount(){
      return $this->stmt->rowCount();
  }
  public function lastInsertId(){
      return $this->dbh->lastInsertId();
  }
  public function beginTransaction(){
      return $this->dbh->beginTransaction();
  }
  public function endTransaction(){
      return $this->dbh->commit();
  }
  public function inTransaction(){
      return $this->dbh->inTransaction();
  }
  public function cancelTransaction(){
      return $this->dbh->rollBack();
  }
  public function debugDumpParams(){
      return $this->stmt->debugDumpParams();
  }
// array is returned: [0] SQLSTATE, [1] some useless error code [2] error message
  public function errorInfo() {
      return $this->stmt->errorInfo();
  }
}