mysqli使用解析
通过mysqli类库操作数据库的步骤
- 建立到mysql的连接
- 打开指定的数据库
- 设置默认客户端的字符集
- 执行sql语句
- 释放结果集
- 关闭连接
建立连接的同时打开指定数据库
<?php
//1.建立到mysql的连接，同时打开指定的数据库
$msyqli = @new mysqli('localhost','root','root','test');
//$mysqli->connect_errno:得到连接产生的错误编号
//$mysqli->connect_error:得到连接产生的错误信息
if($mysqli->connect_errno) {
die('Connect Error :'.$mysqli->connect_error);
}
echo '<hr color="red">';
echo '客户端的信息：'.$mysqli->client_info.'<br />';
echo $mysqli->get_client_info().'<br />';
echo '客户端的版本：'.$mysqli->client_version.'<br />';
echo '<hr />';
echo '服务器端信息：'.$mysqli->server_info.'<br />';
echo $mysqli->get_server_info();
echo '<hr />';
echo '服务器版本：'.$mysqli->server_version.'<br />';
echo '<hr />'
//2.设置默认的客户端编码方式utf8
$mysqli->set_charset('utf8');
//3.执行sql查询
$sql = <<<EOF
CREATE TABLE IF NOT EXISTS mysqli(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL
);
EOF;
/*
SELECT/DESC/DESCRIBE/SHOW/EXPLAIN执行成功返回结果集对象，失败返回false
对于其它sql语句的执行，执行成功返回true，否则返回false
*/
//4.释放结果集（这里没有，省略）
//5.关闭连接
$mysqli->close();
?>实现插入记录操作
<?php
header('content-type:text/html;charset=UTF-8');
$mysqli = @new mysqli('localhost','root','root','test');
if($mysqli->connect_errno) {
die('CONNECT ERROR:'.$mysqli->connect_error);
}
$mysqli->set_charset('utf8');
//执行sql查询
//添加记录，执行单条sql语句
$sql = "INSERT INTO user (username,password) VALUES('queen1','queen1'),(queen2,queen2),(queen3,queen3);";
$res= $mysqli->query($sql);
if($res) {
//得到上一步插入操作产生的AUTO_INCREMENT的值
echo '恭喜您注册成功，你是网站的第'.$mysqli->insert_id.'位用户<br />';
//得到上一步操作产生的受影响记录条数
echo '有'.$mysqli->affected_rows.'条记录受影响';
} else {
echo 'ERROR '.$mysqli->errno.':'.$mysqli->error;
}
$mysqli->close();
?>实现更新记录操作
<?php
header('content-type:test/html;charset=UTF-8');
$mysqli = new mysqli('localhost','root','root','test');
if($mysqli->connect_errno) {
exit('CONNECT ERROR:'.$mysqli->connect_error);
}
$mysqli->set_charset('utf8');
//更新数据
$sql = "UPDATE user SET age += 10";
$res = $mysqli->query($sql);
if($res) {
echo $mysqli->affected_rows.'条记录被更新';
} else {
echo 'ERROR '.$mysqli->errno.':'.$mysqli->error;
}
$mysqli->close();
?>实现删除记录操作
<?php
header('content-type:text/html;charset=utf-8');
$mysqli= new mysqli('localhost','root','root','test');
if($mysqli->connect_errno) {
exit('CONNECT ERROR:'.$mysqli->connect_error)
}
$mysqli->set_charset('utf8');
//删除用户
$sql = "DELETE FROM user WHERE id <= 6";
$res = $mysqli->query($sql);
if($res) {
echo $mysqli->affected_rows.'条记录被删除';
} else {
echo 'ERROR '.$mysqli->errno.':'.$mysqli->error;
}
$mysqli->close();
?>注意：
affected_rows的值为三种：
- 受影响记录条数
- -1，代表sql语句有问题
- 0，代表没有受影响记录的条数
实现查询记录的操作
<?php
header('content-type:text/html;charset=utf-8');
$mysqli = new mysqli('localhost','root','root','test');
if($mysqli->connect_errno) {
exit('CONNECT ERROR:'.$mysqli->connect_error);
}
$mysqli->set_charset('utf8');
//查询
$sql = "SELECT id,username,age FROM user";
$mysqli_result = $mysqli->query($sql);
if($mysqli_result && $mysqli_result->num_rows>0) {
$mysqli_result->fetch_all();//获取结果集中所有记录，默认返回的是二维的，索引+索引的形式
$rows = $mysqli_result->fetch_all(MYSQLI_NUM);
$rows = $mysqli_result->fetch_all(MYSQLI_ASSOC);
$rows = $mysqli_result->fetch_all(MYSQLI_BOTH);
$row = $mysqli_result->fetch_row();//取得结果集中一条记录作为索引数组返回
$row = $mysqli_result->fetch_assoc();//取得结果集中的一条记录作为关联数组返回
$row = $mysqli_result->fetch_array();
$row = $mysqli_result->fetch_array(MYSQLI_ASSOC);
$row = $mysqli_result->fetch_object();
//移动结果集内部指针
$mysqli_result->data_seek(0);
$row = $mysqli->fetch_assoc();
while($row = $mysqli->fetch-assoc()) {
$rows[] = $row;
}
$mysqli_result->free();
} else {
echo '查询错误或者结果集中没有记录';
}
$mysqli->close();
?>mysqli针对多条sql语句的执行
<?php
header('content-type:text/html;charset=utf-8');
$mysqli = new mysqli('localhost','root','root','test');
if($mysqli->connect_errno) {
exit('CONNECT ERROR:'.$mysqli->connect_error);
}
$mysqli->set_charset('utf8');
$sql = "INSERT INTO user(username,password,age) VALUES('yangzichao','yangzichao',20)";
$sql .= "UPDATE user SET age = 5 WHERE id = 28";
$sql .= "DELETE FROM user WHERE id = 25";
$res = $mysqli->multi_query($sql);
if($res) {
echo $res->affected_rows.'条记录被删除';
} else {
echo 'Error '.$mysqli->errno.':'.$mysqli->error;
}
$mysqli->close();
?><?php
header('content-type:text/html;charset=UTF-8');
$mysqli = new mysqli('localhost','root','root','test');
if($mysqli->connect_errno) {
exit('CONNECT ERROR:'.$mysqli->connect_error);
}
$mysqli->set_charset("utf8");
$sql = "select id,username,age from user;";
$sql .= "select * from mysql.user";
//use_result()/store_result():获取第一条查询产生的结果集
//more_results():检测是否有更多的结果集
//next_result():将结果集指针向下移动一位
if($mysqli->multi_query($sql)) {
do {
if($mysqil_result = $mysqli->store_result()) {
$rows[] = $mysqli_result->fetch_all(MYSQLI_ASSOC);
}
} while($mysqli->more_results() && $mysqli->next_result());
} else {
echo '查询失败：'.$mysqli->error;
}
var_dump($rows);
$mysqli->close();
?>使用预处理语句执行插入操作
<?php
header('content-type:text/html;charset=utf-8');
$mysqli = @new mysqli('localhost','root','root','test');
if($mysqli->connect_errno) {
exit('CONNECT ERROR;'.$mysqli->connect_error);
}
$mysqli->set_charset('utf8');
$sql = "insert user(username,password,age) values (?,?,?);";
//准备预处理语句
$mysqli_stmt = $mysqli->prepare($sql);
$username = 'king';
$password = mad5('king');
$age = 12;
//绑定参数
$mysqli_stmt->bind_param('ssi',$username,$password,$age);
//执行预处理语句
if($mysqli_stmt->execute()) {
echo $mysqli_stmt->insert_id;
echo '<br />';
} else {
echo $mysqli_stmt->error;
}
?>使用预处理语句防止sql注入
<?php
header('content-type:text/html;charset=utf-8');
$mysqli = new mysqli('localhost','root','root','test');
if($mysqli->connect_errno) {
echo 'CONNECT ERROR:'.$mysqli->connect_error;
}
$mysqli->set_charset('utf8');
$sql = "select * from user where username = ? and password = ?;";
$mysqli_stmt = $msyqli->prepare($sql);
$username = $_POST['username'];
$password = md5($_POST['password']);
$mysqli_stmt->bind_param('ss',$username,$password);
if($mysqli_stmt->execute()) {
$mysqli_stmt->store_result();
if($mysqli_stmt->num_rows > 0) {
echo '登陆成功!';
} else {
echo '登陆失败!';
}
}
?>使用预处理语句执行查询操作
<?php
header('content-type:text/html;charset=utf-8');
$mysqli = new mysqli('localhost','root','root','test');
if($mysqli->connect_errno) {
exit('CONNECT ERROR:'.$mysqli->connect_error);
}
$mysqli->set_charset('utf8');
$sql = "select id,username,age from user where id>=?";
$mysqli_stmt = $mysqli->prepare($sql);
$id = 20;
$mysqli_stmt->bind_param('i',$id);
if($mysqli_stmt->execute()) {
//bind_result():绑定结果集中的值到变量
$mysqli_stmt->bind_result($id,$username,$age);
//遍历结果集
while($mysqli_stmt->fetch()) {
echo '编号：'.$id.'<br />';
echo '用户名：'.$username.'<br />';
echo '年龄：'.$age.'<br />';
echo '<hr />';
}
} else {
echo '查询错误:'.$mysqli_stmt->error;
}
$mysqli_stmt->free();
$mysqli->close();
?>
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
