PHP - 开始准备语句但它们看起来很笨拙

I've got a website where I connect to a mySQL database to make a number of queries, in the usual fashion. I'm not doing anything more complicated than:

我有一个网站，我连接到mySQL数据库，以通常的方式进行大量的查询。我没有做比以下更复杂的事情：

$result = mysql_query('SELECT * FROM table WHERE condition = "'.mysql_real_escape_string($_POST['condition']).'"');
$row = mysql_fetch_assoc($result);
echo $row['var1'].' '.$row['var2'];

And it works. But I've been reading up about prepared statements and they seem to offer more security and I'd like to use them and replace my database calls with some prepared statements, so I've been looking at the mysqli class.

它有效。但是我一直在阅读准备好的语句，它们似乎提供了更多的安全性，我想使用它们并用一些准备好的语句替换我的数据库调用，所以我一直在查看mysqli类。

But it seem so much more code to achieve the same thing. I understand I'd have to do this to get the above:

但似乎有更多的代码来实现同样的事情。我明白为了得到上述内容，我必须这样做：

$stmt = $db->stmt_init();
if($stmt->prepare('SELECT * FROM table WHERE condition = ?')) {
$condition = $_POST['condition'];
$stmt->bind_param('s', $condition);
$stmt->execute();

$stmt->bind_result($var1, $var2, ...);
if ($stmt->fetch()) {
    echo $var1 . ' - ' . $var2;
}
}

So it seems like a hell of a lot more code, and a bit harder to manage. Am I misunderstanding how to use these or is there a shorter way of doing the "normal" PHP things:

所以它看起来像是一堆更多的代码，而且更难以管理。我是否误解了如何使用这些或者是否有更简单的方法来执行“正常”的PHP事情：

Populating $row, being an array representing one single line from the database.
填充$ row，是一个表示数据库中一行的数组。
Looping over rows, and refilling $row with the "next row" along.
循环遍历行，并使用“下一行”重新填充$ row。
Normal UPDATE enquiries.
正常的UPDATE查询。

The above are all nice and quick to do "normally" but seem like they would take many more lines using prepared statements.

以上都很好，很快“通常”做，但看起来他们会使用预备语句需要更多行。

1 个解决方案

#1

A common way is to wrap database functionality into a class. Here's a simple one implementing caching of the prepared statements:

一种常见的方法是将数据库功能包装到类中。这是一个实现缓存预准备语句的简单方法：

class DB {
  protected $db;
  protected $cache;

  public function __construct($host, $database, $user, $pass, $charset = 'utf8') {
    $this->db = new PDO(sprintf('mysql:dbname=%s;host=%s', $database, $host, $charset),
                        $user, $pass);
    $this->cache = array();
    $this->db->query(sprintf('SET NAMES %s', $charset));
  }

  public function query($query, $vars = array()) {
    //You may input a simple value, no need for arrays with a single argument                              
    if (!is_array($vars))
      $vars = array($vars);

    //Short names inside the function                                                                      
    $db = &$this->db;
    $cache = &$this->cache;

    //Ensure the prepared statement is in cache                                                            
    if (!isset($cache[$query]))
      $cache[$query] = $db->prepare($query);

    //Execute the statement and return all rows                                                            
    $stmt = $cache[$query];
    if ($stmt->execute($vars))
      return $stmt->fetchAll();
    else
      return false;
  }
}

Usage of this is very close to the older database interfaces. Example:

使用它非常接近旧的数据库接口。例：

$db = new DB(host, database, user, pass);
$result = $db->query('SELECT id, name FROM table WHERE id = ? AND address = ?',
                     array(42, 'home'));
foreach ($result as $row) {
  ...
}

#1