PHP5: mysqli 插入, 查询, 更新和删除 Insert Update Delete Using mysqli (CRUD)
PHP 5 及以上版本建议使用以下方式连接 MySQL :
MySQLi extension (“i” 意为 improved)
PDO (PHP Data Objects)
Mysqli提供了面向对象和面向过程两种方式来与数据库交互，分别看一下这两种方式。
1. PHP 连接 MySQL
1．面向对象
在面向对象的方式中，mysqli被封装成一个类，它的构造方法如下：
__construct ([ string $host [, string $username [, string $passwd [, string $dbname [, int $port [, string $socket ]]]]]] )
在上述语法中涉及到的参数说明如下。
l host：连接的服务器地址。
l username：连接数据库的用户名，默认值是服务器进程所有者的用户名。
l passwd：连接数据库的密码，默认值为空。
l dbname：连接的数据库名称。
l port：TCP端口号。
l socket：UNIX域socket。
要建立与MySQL的连接可以通过其构造方法实例化mysqli类，例如下面的代码：
<?php
$db_host = "localhost"; //连接的服务器地址
$db_user = "root"; //连接数据库的用户名
$db_psw = "root"; //连接数据库的密码
$db_name = "ikeepstudying"; //连接的数据库名称
// 创建连接
$conn = new mysqli($db_host,$db_user,$db_psw,$db_name);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
// 或者使用
// die('Error : ('. $conn->connect_errno .') '. $conn->connect_error);
}
echo "连接成功";
?>
mysqli还提供了一个连接MySQL的成员方法connect()。当实例化构造方法为空的mysqli类时，用mysqli对象调用connect()方法同样可连接MySQL，例如，下面的代码：
<?php
$db_host = "localhost"; //连接的服务器地址
$db_user = "root"; //连接数据库的用户名
$db_psw = "root"; //连接数据库的密码
$db_name = "ikeepstudying"; //连接的数据库名称
$conn = new mysqli();
$conn->connect($db_host,$db_user,$db_psw,$db_name);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
// 或者使用
// die('Error : ('. $conn->connect_errno .') '. $conn->connect_error);
}
echo "连接成功";
?>
关闭与MySQL服务器的连接通过mysqli对象调用close()方法即可，例如：
1
$conn->close();
2．面向过程
在面向过程的方式中，mysqli扩展提供了函数mysqli_connect()与MySQL建立连接，该函数的语法格式如下：
mysqli_connect ([ string $host [, string $username [, string $passwd [, string $dbname [, int $port [, string $socket ]]]]]] )
mysqli_connect()函数的用法与mysql扩展中的mysql_connect()函数用法十分相似，下面是mysqli_connect()函数的用法示例：
01
<?php
02
$servername = "localhost";
03
$username = "username";
04
$password = "password";
05
$database = "ikeepstudying";
06
07
// 创建连接
08
$conn = mysqli_connect($servername, $username, $password, $database);
09
10
// 检测连接
11
if (!$conn) {
12
die("Connection failed: " . mysqli_connect_error());
13
}
14
echo "连接成功";
15
?>
关闭与MySQL服务器的连接使用mysqli_close()函数，例如：
1
mysqli_close();
2. PHP MySQL 插入数据
在创建完数据库和表后，我们可以向表中添加数据。
以下为一些语法规则：
PHP 中 SQL 查询语句必须使用引号
在 SQL 查询语句中的字符串值必须加引号
数值的值不需要引号
NULL 值不需要引号
INSERT INTO 语句通常用于向 MySQL 表添加新的记录：
1
INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)
以下实例向 “MyGuests” 表添加了新的记录:
1．面向对象
01
<?php
02
$servername = "localhost";
03
$username = "username";
04
$password = "password";
05
$dbname = "ikeepstudying";
06
07
// 创建连接
08
$conn = new mysqli($servername, $username, $password, $dbname);
09
// 检测连接
10
if ($conn->connect_error) {
11
die("连接失败: " . $conn->connect_error);
12
}
13
14
$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')";
15
16
if ($conn->query($sql) === TRUE) {
17
echo "新记录插入成功";
18
} else {
19
echo "Error: " . $sql . "<br>" . $conn->error;
20
}
21
22
$conn->close();
23
?>
插入多条记录:
01
<?php
02
$servername = "localhost";
03
$username = "username";
04
$password = "password";
05
$dbname = "ikeepstudying";
06
07
// 创建链接
08
$conn = new mysqli($servername, $username, $password, $dbname);
09
// 检查链接
10
if ($conn->connect_error) {
11
die("连接失败: " . $conn->connect_error);
12
}
13
14
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
15
VALUES ('John', 'Doe', 'john@example.com');";
16
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
17
VALUES ('Mary', 'Moe', 'mary@example.com');";
18
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
19
VALUES ('Julie', 'Dooley', 'julie@example.com')";
20
21
if ($conn->multi_query($sql) === TRUE) {
22
echo "新记录插入成功";
23
} else {
24
echo "Error: " . $sql . "<br>" . $conn->error;
25
}
26
27
$conn->close();
28
?>
请注意，每个SQL语句必须用分号隔开。
2．面向过程
01
<?php
02
$servername = "localhost";
03
$username = "username";
04
$password = "password";
05
$dbname = "ikeepstudying";
06
07
// 创建连接
08
$conn = mysqli_connect($servername, $username, $password, $dbname);
09
// 检测连接
10
if (!$conn) {
11
die("Connection failed: " . mysqli_connect_error());
12
}
13
14
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
15
VALUES ('John', 'Doe', 'john@example.com')";
16
17
if (mysqli_query($conn, $sql)) {
18
echo "新记录插入成功";
19
} else {
20
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
21
}
22
23
mysqli_close($conn);
24
?>
插入多条记录:
01
<?php
02
$servername = "localhost";
03
$username = "username";
04
$password = "password";
05
$dbname = "ikeepstudying";
06
07
// 创建链接
08
$conn = mysqli_connect($servername, $username, $password, $dbname);
09
// 检查链接
10
if (!$conn) {
11
die("连接失败: " . mysqli_connect_error());
12
}
13
14
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
15
VALUES ('John', 'Doe', 'john@example.com');";
16
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
17
VALUES ('Mary', 'Moe', 'mary@example.com');";
18
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
19
VALUES ('Julie', 'Dooley', 'julie@example.com')";
20
21
if (mysqli_multi_query($conn, $sql)) {
22
echo "新记录插入成功";
23
} else {
24
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
25
}
26
27
mysqli_close($conn);
28
?>
php5和新增了获取最新插入值的ID的函数：mysqli_insert_id($conn)，和之前的mysql_insert_id()类似。
都是用在insert语句之后获取最新的自动id号，要注意的是mysqli_insert_id()，括号里面的一定要是数据库连接而不是执行结果$result。
程序实例：
1
$conn = new mysqli("127.0.0.1","root","","ikeepstudying") or die("数据库连接超时!");
2
$sql = "insert into my_table (name,url) values('ikeepstudying','http://justcode.ikeepstudying.com')";
3
$result = $conn->query($sql) or die("失败");
4
5
echo mysqli_insert_id($conn); //输出刚刚插入数据的ID，注意：括号里面的要是$conn而不是$result
3. PHP MySQL 预处理语句
预处理语句对于防止 MySQL 注入是非常有用的。使用预准备语句可提高重复使用语句的性能，在PHP中，使用prepare()方法来进行预准备语句查询，使用execute()方法来执行预准备语句。PHP有两种预准备语句：一种是绑定结果，另一种是绑定参数。
预处理语句, 绑定参数及绑定结果
预处理语句用于执行多个相同的 SQL 语句，并且执行效率更高。
预处理语句的工作原理如下：
预处理：创建 SQL 语句模板并发送到数据库。预留的值使用参数 “?” 标记 。例如：
INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)
数据库解析，编译，对SQL语句模板执行查询优化，并存储结果不输出。
执行：最后，将应用绑定的值传递给参数（”?” 标记），数据库执行语句。应用可以多次执行语句，如果参数的值不一样。
相比于直接执行SQL语句，预处理语句有两个主要优点：
预处理语句大大减少了分析时间，只做了一次查询（虽然语句多次执行）。
绑定参数减少了服务器带宽，你只需要发送查询的参数，而不是整个语句。
预处理语句针对SQL注入是非常有用的，因为参数值发送后使用不同的协议，保证了数据的合法性。
MySQLi 预处理语句及绑定参数
所谓绑定参数就是把PHP脚本中的自定义变量绑定到SQL语句中的参数（参数使用 “？”代替）上，绑定参数使用bind_param()方法，该方法的语法格式如下：
bool bind_param ( string $types , mixed &$var1 [, mixed &$… ] )
以下实例在 MySQLi 中使用了预处理语句，并绑定了相应的参数:
01
<?php
02
$servername = "localhost";
03
$username = "username";
04
$password = "password";
05
$dbname = "ikeepstudying";
06
07
// 创建连接
08
$conn = new mysqli($servername, $username, $password, $dbname);
09
10
// 检测连接
11
if ($conn->connect_error) {
12
die("连接失败: " . $conn->connect_error);
13
}
14
15
// 预处理及绑定
16
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)");
17
$stmt->bind_param("sss", $firstname, $lastname, $email);
18
19
// 设置参数并执行
20
$firstname = "John";
21
$lastname = "Doe";
22
$email = "john@example.com";
23
$stmt->execute();
24
25
$firstname = "Mary";
26
$lastname = "Moe";
27
$email = "mary@example.com";
28
$stmt->execute();
29
30
$firstname = "Julie";
31
$lastname = "Dooley";
32
$email = "julie@example.com";
33
$stmt->execute();
34
35
echo "新记录插入成功";
36
37
$stmt->close();
38
$conn->close();
39
?>
解析以下实例的每行代码:
1
"INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)"
在 SQL 语句中，我们使用了问号 (?)，在此我们可以将问号替换为整型，字符串，双精度浮点型和布尔值。
接下来，让我们来看下 bind_param() 函数：
1
$stmt->bind_param("sss", $firstname, $lastname, $email);
该函数绑定了 SQL 的参数，且告诉数据库参数的值。 “sss” 参数列处理其余参数的数据类型。s 字符告诉数据库该参数为字符串。
types：绑定的变量的数据类型，它接受的字符种类包括4个，如表所示。
字符种类
代表的数据类型
i
integer
d
double
s
string
b
blob
参数types接受的字符的种类和绑定的变量需要一一对应。
通过告诉数据库参数的数据类型，可以降低 SQL 注入的风险。
注意： 如果你想插入其他数据（用户输入），对数据的验证是非常重要的。
MySQLi 预处理语句及绑定结果
所谓绑定结果就是把PHP脚本中的自定义变量绑定到结果集中的相应字段上，这些变量就代表着所查询的记录，绑定结果的示例代码如下：
01
<?php
02
$mysqli = new mysqli("localhost","root","root","ikeepstudying"); //实例化mysqli
03
$query = "select * from MyGuests";
04
$result = $mysqli->prepare($query); //进行预准备语句查询
05
$result->execute(); //执行预准备语句
06
$result->bind_result($id,$firstname,$lastname,$email); //绑定结果
07
while ($result->fetch()) {
08
echo $id;
09
echo $firstname;
10
echo $lastname;
11
echo $email;
12
}
13
$result->close(); //关闭预准备语句
14
?>
在绑定结果的时候，脚本中的变量要与结果集中的字段一一对应，绑定完以后，通过fetch()方法将绑定在结果集中的变量一一取出来，最后将预准备语句和数据库连接分别关闭。
MySQLi 同时绑定参数和绑定结果
在一个脚本中还可以同时绑定参数和绑定结果，示例代码如下：
01
<?php
02
$conn = new mysqli("localhost","root","root","ikeepstudying"); //实例化mysqli
03
$query = "select * from MyGuests where id < ?";
04
$result = $conn->prepare($query);
05
$result->bind_param("i",$id); //绑定参数
06
$id=4;
07
$result->execute();
08
$result->bind_result($id,$number,$name,$age); //绑定结果
09
while ($result->fetch()) {
10
echo $id;
11
echo $firstname;
12
echo $lastname;
13
echo $email;
14
}
15
16
$result->close();
17
$conn->close();
18
?>
4. PHP MySQL 读取数据, 删除及更新
SELECT 语句用于从数据表中读取数据:
1
SELECT column_name(s) FROM table_name
在mysqli中，执行查询使用query()方法，该方法的语法格式如下：
mixed query ( string $query [, int $resultmode ] )
在上述语法中涉及到的参数说明如下。
l query：向服务器发送的SQL语句。
l resultmode：该参数接受两个值，一个是MYSQLI_STORE_RESULT，表示结果作为缓冲集合返回；另一个是MYSQLI_USE_RESULT，表示结果作为非缓冲集合返回。
下面是使用query()方法执行查询的例子：
1．面向对象
01
<?php
02
$mysqli = new mysqli("localhost","root","root","ikeepstudying"); //实例化mysqli
03
$query = "select * from MyGuests";
04
$result = $mysqli->query($query);
05
if ($result) {
06
if($result->num_rows>0){ //判断结果集中行的数目是否大于0
07
while($row = $result->fetch_array() ){ //循环输出结果集中的记录
08
echo ($row[0])."<br>";
09
echo ($row[1])."<br>";
10
echo ($row[2])."<br>";
11
echo ($row[3])."<br>";
12
echo "<hr>";
13
}
14
}
15
}else {
16
echo "查询失败";
17
}
18
$result->free();
19
$mysqli->close();
20
?>
在上面代码中，num_rows为结果集的一个属性，返回结果集中行的数目。方法fetch_array()将结果集中的记录放入一个数组中并将其返回。最后使用free()方法将结果集中的内存释放，使用close()方法将数据库连接关闭。
对于删除记录（delete）、保存记录（insert）和修改记录（update）的操作，也是使用query()方法来执行的，下面是删除记录的例子：
01
<?php
02
$mysqli = new mysqli("localhost","root","root","ikeepstudying"); //实例化mysqli
03
$query = "delete from MyGuests where id=2";
04
$result = $mysqli->query($query);
05
if ($result){
06
echo "删除操作执行成功";
07
}else {
08
echo "删除操作执行失败";
09
}
10
$mysqli->close();
11
?>
完整实例:
01
<?php
02
$servername = "localhost";
03
$username = "username";
04
$password = "password";
05
$dbname = "ikeepstudying";
06
07
// 创建连接
08
$conn = new mysqli($servername, $username, $password, $dbname);
09
// 检测连接
10
if ($conn->connect_error) {
11
die("连接失败: " . $conn->connect_error);
12
}
13
14
$sql = "SELECT id, firstname, lastname FROM MyGuests";
15
$result = $conn->query($sql);
16
17
if ($result->num_rows > 0) {
18
// 输出每行数据
19
while($row = $result->fetch_assoc()) {
20
echo "<br> id: ". $row["id"]. " - Name: ". $row["firstname"]. " " . $row["lastname"];
21
}
22
} else {
23
echo "0 个结果";
24
}
25
$conn->close();
26
?>
更多实例:
INSERT
1
$stmt = $mysqli->prepare("INSERT INTO movies(filmName,
2
filmDescription,filmImage,filmPrice,filmReview) VALUES (?, ?, ?, ?, ?)");
3
$stmt->bind_param('sssdi', $_POST['filmName'],
4
$_POST['filmDescription'],
5
$_POST['filmImage'],
6
$_POST['filmPrice'],
7
$_POST['filmReview']);
8
$stmt->execute();
9
$stmt->close();
Getting Auto Increment Key Values with insert_id
01
$stmt = $mysqli->prepare("INSERT INTO movies(filmName,
02
filmDescription,filmImage,filmPrice,filmReview) VALUES (?, ?, ?, ?, ?)");
03
$stmt->bind_param('sssdi', $_POST['filmName'],
04
$_POST['filmDescription'],
05
$_POST['filmImage'],
06
$_POST['filmPrice'],
07
$_POST['filmReview']);
08
$stmt->execute();
09
$newId = $stmt->insert_id;
10
$stmt->close();
UPDATE
01
$stmt = $mysqli->prepare("UPDATE movies SET filmName = ?,
02
filmDescription = ?,
03
filmImage = ?,
04
filmPrice = ?,
05
filmReview = ?
06
WHERE filmID = ?");
07
$stmt->bind_param('sssdii',
08
$_POST['filmName'],
09
$_POST['filmDescription'],
10
$_POST['filmImage'],
11
$_POST['filmPrice'],
12
$_POST['filmReview'],
13
$_POST['filmID']);
14
$stmt->execute();
15
$stmt->close();
DELETE
1
$stmt = $mysqli->prepare("DELETE FROM movies WHERE filmID = ?");
2
$stmt->bind_param('i', $_POST['filmID']);
3
$stmt->execute();
4
$stmt->close();
2．面向过程
01
<?php
02
$con = mysqli_connect("localhost","username","password","ikeepstudying");
03
04
// 检测连接
05
if (mysqli_connect_errno())
06
{
07
echo "连接失败: " . mysqli_connect_error();
08
}
09
10
$result = mysqli_query($con,"SELECT * FROM Persons WHERE FirstName='Peter'");
11
12
while($row = mysqli_fetch_array($result))
13
{
14
echo $row['FirstName'] . " " . $row['LastName'];
15
echo "<br>";
16
}
17
?>
5. mysqli 读取数据库的几种方式
mysqli有四种读取数据库数据的方式：mysqli_fetch_array(), mysqli_fetch_object(), mysqli_fetch_accoc(), mysqli_fetch_row()。
区别是：
mysqli_fetch_array()，同时返回关系型数组和计数型数组2种，读取数据的时候我们可以用比如：$result[1] 或者是$result[“username”]。
mysqli_fetch_accoc()，返回关系型数组，读取数据的时候我们可以用比如：$result[“username”]。
mysqli_fetch_row()，返回计数型数组，读取数据的时候我们可以用比如：$result[0]。
mysqli_fetch_object()，返回一个对象，读取数据的时候我们可以用比如：$result->username的方式读取数据。
6. mysqli 一些函数的对比 ( 面向对象 vs 面向过程 )
1. mysqli 连接数据库：
$db = new mysqli(host,user,password,database);
这是对象导向的连接方法，还有一种过程导向的连接方法。前者返回一个对象，后者返回一个资源。
$db = mysqli_connect(host,user,password,database);
2.mysqli 连接出错函数：
$db->connect_error
or
mysqli_connect_error();
如果连接出错，该函数返回一个错误号，否则返回0；
3.选择数据库：
$db->select_db(dbname);
or
mysqli_select_db(db_resource,db_name);
4.mysqli 执行查询
$result = $db->query($query);
or
$result = mysqli_query($db,$query);
5.返回结果的行数：
$num_results = $result->num_rows;
or
$num_results = mysqli_num_rows($result);
6.取出每一行的结果（返回关系型数组）：
$row = $result->fetch_assoc();
or
$row = mysqli_fetch_assoc($result);
7.取出每一行的结果（返回计数型数组）：
$row = $result->fetch_row($result);
or
$row = mysqli_fetch_row($result);
8.取出每一行的结果（返回一个对象）：
$row = $result->fetch_object();
or
$row = mysqli_fetch_object($result);
9.释放查询结果：
$result->free();
or
mysqli_free_result($result);
10.关闭数据库连接：
$db->close();
or
mysqli_close($db);
11.一条查询所影响的行数：
$db->affected_rows;
or
mysqli_affected_rows($result);
12.模式化SQL语句执行：
$query = “insert into books values(?, ?, ?, ?)”;
$stmt = $db->prepare($query);
$stmt->bind_param(“sssd”, $isbn, $author, $title, $price);
$stmt->execute();
echo $stmt->affected_rows.’ book inserted into database.’;
$stmt->close();
在过程化执行方式中，
$db->prepare()对应mysqli_stmt_prepare()函数；
$stmt->bind_param()对应mysqli_stmt_bind_param()函数；
$stmt->execute() 对应mysqli_stmt_execute()函数；