php中使用mysql_stmt（预处理语句）来处理SQL语句

时间：2010-03-22 来源：xhq6632

许多PHP脚本通常都会执行除参数以外，其他部分完全相同的查询语句，针对这种重复执行一个查询，每次迭代使用不同的参数情况，MySQL 从4.1版本开始提供了一种名为预处理语句（prepared statement）的机制。它可以将整个命令向MySQL服务器发送一次，以后只有参数发生变化，MySQL服务器只需对命令的结构做一次分析就够了。这不仅大大减少了需要传输的数据量，还提高了命令的处理效率。可以用mysqli扩展模式中提供的mysqli_stmt类的对象，去定义和执行参数化的 SQL命令。以下是使用这种机制实现的一个查询过程。 <?php
/*
* Author:xhq
* Email:[email protected]
* Oicq:119948717
*/
header("Content-type: text/html; charset=utf-8");
$host="";
$user="root";
$password="951753";
$dbname="test";
$dbcharset="utf8";
$table1="movie_name";
$table2="t_group_ids"; $db =@new mysqli($host,$user,$password,$dbname); if (mysqli_connect_errno()){
printf("Error:%s\n",mysqli_connect_error());
exit;
}else {
if ($stmt = $db->prepare("select id,name,ename,recorde from $table1 where name like ?")){
   $st = $db->prepare("set names $dbcharset");
   $st->execute();
   $stmt->bind_param('s',$n);
   $n = "%小%";
   $stmt->execute();
   $stmt->store_result();
   $stmt->bind_result($id,$name,$ename,$recorde);
   while ($stmt->fetch()){
    printf("%s->%s\t%s\t%s<br/>\n",$id,$name,$ename,$recorde);
   }
   $stmt->close();
}
$db->close();
}
?>
在原文的基础上修改了一些。如果是插入操作如下： $stmt = $db->prepare("insert into $table1 (money,user_name) values(?,?)"); $stmt->bind_param('is',$money,$user_name);//其中'is'为对应参数的类型，不清楚看下面说明下面再给出官网上的说明

mysqli_stmt::bind_param

mysqli_stmt_bind_param

(PHP 5)

mysqli_stmt::bind_param -- mysqli_stmt_bind_param — Binds variables to a prepared statement as parameters

Report a bug

Description

Object oriented style (method):

bool mysqli_stmt::bind_param ( string $types , mixed &$var1 [, mixed &$... ] )

Procedural style:

bool mysqli_stmt_bind_param ( mysqli_stmt $stmt , string $types , mixed &$var1 [, mixed &$... ] )

Bind variables for the parameter markers in the SQL statement that was passed to mysqli_prepare().

Note: If data size of a variable exceeds max. allowed packet size (max_allowed_packet), you have to specify b in types and use mysqli_stmt_send_long_data() to send the data in packets.

Note: Care must be taken when using mysqli_stmt_bind_param() in conjunction with call_user_func_array(). Note that mysqli_stmt_bind_param() requires parameters to be passed by reference, whereas call_user_func_array() can accept as a parameter a list of variables that can represent references or values.

Report a bug

Parameters

stmt

Procedural style only: A statement identifier returned by mysqli_stmt_init().

types

A string that contains one or more characters which specify the types for the corresponding bind variables:

Type specification chars

Character	Description
i	corresponding variable has type integer
d	corresponding variable has type double
s	corresponding variable has type string
b	corresponding variable is a blob and will be sent in packets

var1

The number of variables and length of string types must match the parameters in the statement.

Report a bug

Return Values

Returns TRUE on success or FALSE on failure.

Report a bug

Examples

Example #1 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();
?>

Example #2 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 */
mysqli_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);
?>

The above example will output:

1 Row inserted.
1 Row deleted.