如提高运行效率,减小资源占用?
如果你的SQL查询可能会返回大量的记录,那么就用无缓冲查询代替缓冲查询能提高效率。无缓冲查询消耗较少的内存,它还可以让你在查询完成执行前开始处理结果集。不过这也有缺点,无缓冲查询无法使用drizzle_result_row_count()这样的函数,因为PHP的缓存里没有一个完整的结果集,自然也就无法计算它有多少条记录了。
下面的示例使用的是Drizzle 扩展的无缓冲查询:
- < html>
- < head>
- < style type="text/css">
- table,tr,td {
- border: solid 1px black;
- border-collapse: collapse;
- padding: 5px;
- }
- < /style>
- < /head>
- < body>
- < h2>List Items< /h2>
- < ?php
- // set connection parameters
- $host = 'localhost';
- $port = 4427;
- $user = 'root';
- $pass = 'guessme';
- $db = 'test';
- // create drizzle object
- $drizzle = drizzle_create();
- // connect to database server
- $conn = drizzle_con_add_tcp($drizzle, $host, $port, $user, $pass, $db, 0)
- or die('ERROR: ' . drizzle_error($drizzle));
- // execute query
- $sql = 'SELECT ItemDesc, ItemQty FROM items';
- $result = @drizzle_query($conn, $sql)
- or die('ERROR: ' . drizzle_con_error($conn));
- // skip field names
- drizzle_column_skip($result);
- // iterate over records and fields
- echo '< table>';
- while (($row = drizzle_row_read($result))) {
- echo '< tr>';
- while ($fields = drizzle_field_read($result)) {
- // if this is the end of the record
- // go to next record
- if (drizzle_result_return_code($result) == DRIZZLE_RETURN_ROW_END) {
- break;
- }
- echo '< td>' . $fields[0] . '< /td>';
- }
- echo '< /tr>';
- }
- echo '< /table>';
- // free result set
- drizzle_result_free($result);
- // close connection
- drizzle_con_close($conn);
- ?>
- < /body>
- < /html>
前半部分的代码与之前你所看到的是相同的。但是,查询执行完后,drizzle_result_buffer()并没有被调用,而是直接使用了drizzle_row_read()和drizzle_field_read()方法来遍历无缓冲结果集并输出各个字段的值。另外还注意drizzle_column_skip()这个函数,它用来在返回的数据包里跳过列的名字。
下面是用面向对象接口实现了相同功能的脚本:
- < html>
- < head>
- < style type="text/css">
- table,tr,td {
- border: solid 1px black;
- border-collapse: collapse;
- padding: 5px;
- }
- < /style>
- < /head>
- < body>
- < h2>List Items< /h2>
- < ?php
- // set connection parameters
- $host = 'localhost';
- $port = 4427;
- $user = 'root';
- $pass = 'guessme';
- $db = 'test';
- // create drizzle object
- $drizzle = new drizzle();
- // connect to database server
- $conn = $drizzle->addTcp($host, $port, $user, $pass, $db, 0)
- or die('ERROR: ' . $drizzle->error());
- // execute query
- $sql = 'SELECT ItemDesc, ItemQty FROM items';
- $result = @$conn->query($sql)
- or die('ERROR: ' . $conn->error());
- // skip field names
- $result->columnSkip();
- // iterate over records and fields
- echo '< table>';
- while (($row = $result->rowRead())) {
- echo '< tr>';
- while ($fields = $result->fieldRead()) {
- // if this is the end of the record
- // go to next record
- if ($result->returnCode() == DRIZZLE_RETURN_ROW_END) {
- break;
- }
- echo '< td>' . $fields[0] . '< /td>';
- }
- echo '< /tr>';
- }
- echo '< /table>';
- // close connection
- $conn->close();
- // deinitialize object
- unset($drizzle);
- ?>
- < /body>
- < /html>
添加和删除
你也可以使用drizzle_query()函数,或者是query()方法来执行INSERT, UPDATE 和 DELETE查询。下面这个例子演示了执行一个INSERT查询并通过insertId()方法获取新插入的这条记录的ID号。
- < html>
- < head>
- < style type="text/css">
- table,tr,td {
- border: solid 1px black;
- border-collapse: collapse;
- padding: 5px;
- }
- < /style>
- < /head>
- < body>
- < h2>List Items< /h2>
- < ?php
- // set connection parameters
- $host = 'localhost';
- $port = 4427;
- $user = 'root';
- $pass = 'guessme';
- $db = 'test';
- // create drizzle object
- $drizzle = new drizzle();
- // connect to database server
- $conn = $drizzle->addTcp($host, $port, $user, $pass, $db, 0)
- or die('ERROR: ' . $drizzle->error());
- // execute query
- $sql = "INSERT INTO items (ItemDesc, ItemQty) VALUES ('Jam', '2')";
- $result = @$conn->query($sql)
- or die('ERROR: ' . $conn->error());
- // get record ID
- echo 'Record inserted with ID: ' . $result->insertId();
- // close connection
- $conn->close();
- // deinitialize object
- unset($drizzle);
- ?>
- < /body>
- < /html>
输出如下:
下面是另外一个例子,它执行一个DELETE语句,并用affectedRows()方法函数返回受影响的行数。
- < html>
- < head>
- < style type="text/css">
- table,tr,td {
- border: solid 1px black;
- border-collapse: collapse;
- padding: 5px;
- }
- < /style>
- < /head>
- < body>
- < h2>List Items< /h2>
- < ?php
- // set connection parameters
- $host = 'localhost';
- $port = 4427;
- $user = 'root';
- $pass = 'guessme';
- $db = 'test';
- // create drizzle object
- $drizzle = new drizzle();
- // connect to database server
- $conn = $drizzle->addTcp($host, $port, $user, $pass, $db, 0)
- or die('ERROR: ' . $drizzle->error());
- // execute query
- $sql = "DELETE FROM items WHERE ItemID > 4";
- $result = @$conn->query($sql)
- or die('ERROR: ' . $conn->error());
- // get number of records affected
- echo $result->affectedRows() . ' record(s) deleted';
- // close connection
- $conn->close();
- // deinitialize object
- unset($drizzle);
- ?>
- < /body>
- < /html>
Drizzle PHP扩展还提供了drizzle_escape_string()函数和excape()方法,它可以在把用户的输入插入到查询语句前先对其进行转义。处理用户提交的数据是非常重要的安全措施,这样才能减小受到SQL注入攻击的危险。在下面这一个例子里,我们提供了一个不安全的Web表单,用户可以通过这个表单直接向数据库里添加新的条目,从而示范了excape()函数的使用。
- < html>
- < head>
- < style type="text/css">
- table,tr,td {
- border: solid 1px black;
- border-collapse: collapse;
- padding: 5px;
- }
- < /style>
- < /head>
- < body>
- < h2>List Items< /h2>
- < ?php
- // set connection parameters
- $host = 'localhost';
- $port = 4427;
- $user = 'root';
- $pass = 'guessme';
- $db = 'test';
- // create drizzle object
- $drizzle = new drizzle();
- // connect to database server
- $conn = $drizzle->addTcp($host, $port, $user, $pass, $db, 0)
- or die('ERROR: ' . $drizzle->error());
- // check if form submitted
- // sanitize input and add record
- if ($_POST['submit']) {
- // escape input strings
- $desc = $drizzle->escape($_POST['desc']);
- $qty = $drizzle->escape($_POST['qty']);
- // execute query
- $sql = "INSERT INTO items (ItemDesc, ItemQty) VALUES ('$desc', '$qty')";
- $result = @$conn->query($sql)
- or die('ERROR: ' . $conn->error());
- // get record ID
- echo 'Record inserted with ID: ' . $result->insertId();
- }
- // execute query
- $sql = 'SELECT ItemID, ItemDesc, ItemQty FROM items';
- $result = @$conn->query($sql)
- or die('ERROR: ' . $conn->error());
- // buffer result set
- $result->buffer()
- or die('ERROR: ' . $conn->error());
- // check number of rows in result set and print
- if ($result->rowCount()) {
- echo '< table>';
- echo '< tr>';
- echo '< td>ID< /td>';
- echo '< td>Name< /td>';
- echo '< td>Quantity< /td>';
- echo '< /tr>';
- while (($row = $result->rowNext())) {
- echo '< tr>';
- echo '< td>' . $row[0] . '< /td>';
- echo '< td>' . $row[1] . '< /td>';
- echo '< td>' . $row[2] . '< /td>';
- echo '< /tr>';
- }
- echo '< /table>';
- } else {
- echo 'No records found';
- }
- // close connection
- $conn->close();
- // deinitialize object
- unset($drizzle);
- ?>
- < h2>Add Item< /h2>
- < form method="post" action="< ?php echo htmlentities($_SERVER['PHP_SELF']); ?>">
- Item name:
- < br />
- < input type="text" name="desc">
- < br />
- Item quantity:
- < br />
- < input type="text" name="qty">
- < br />
- < input type="submit" name="submit" value="Save">
- < /form>
- < /body>
- < /html>
表格起初是这样的:
提交一条记录后变成这样:
注意这段代码在将用户输入值插入到SQL查询语句中之前都先调用了escape()方法;它会对用户输入数据中的特殊字符进行处理。
PHP之友评论