php笔记（9）——数据库操作与mysqli扩展

数据库扩展

Mysql常用的扩展有原生的mysql库，也可以使用增强版的mysqli扩展，还可以使用PDO进行连接与操作。

mysql扩展进行数据库连接的方法：（该库已不推荐使用）

$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password');

mysqli扩展：

$link = mysqli_connect('mysql_host', 'mysql_user', 'mysql_password');

PDO扩展

$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';
$dbh = new PDO($dsn, $user, $password);

查询

$link = mysqli_connect('localhost','root','');
mysqli_select_db($link,'test');
$res = mysqli_query($link,'select * from user');
$row = mysqli_fetch_array($res);
var_dump($row);

可以通过设定参数MYSQL_NUM只获取数字索引数组，等同于mysql_fetch_row函数，如果设定参数为MYSQL_ASSOC则只获取关联索引数组，等同于mysql_fetch_assoc函数。

$row = mysql_fetch_row($result);
$row = mysql_fetch_array($result, MYSQL_NUM); //这两个方法获取的数据是一样的

$row = mysql_fetch_assoc($result);
$row = mysql_fetch_array($result, MYSQL_ASSOC);

遍历查询结果

while($row = mysqli_fetch_array($res)){
    var_dump($row);
}

插入

$link = mysqli_connect('localhost','root','');
mysqli_select_db($link,'test');
$sql = "insert into user(id, name) values('3','jane')";
mysqli_query($link,$sql);

如果主键id自增长，可以获取插入的id，可用于判断是否插入成功等。

$link = mysqli_connect('localhost','root','');
mysqli_select_db($link,'test');
$sql = "insert into user( name) values('jjj')";
mysqli_query($link,$sql);
$uid = mysqli_insert_id($link);
echo $uid;

更新与删除

数据的更新与删除相对比较简单，只需要构建好相应的sql语句，然后调用mysql_query执行就能完成相应的更新与删除操作。

$sql = "update user set name = '曹操' where id=2 limit 1";
if (mysqli_query($sql)) {
    echo '更新成功';
}

同样的删除可以使用类似以下的代码：

$sql = "delete from user where id=2 limit 1";
if (mysqli_query($sql)) {
    echo '删除成功';
}

对于删除与更新操作，可以通过mysql_affected_rows函数来获取更新过的数据行数，如果数据没有变化，则结果为0。

$sql = "update user set name = '曹操' where id=2 limit 1";
if (mysqli_query($sql)) {
    echo mysqli_affected_rows();
}

关闭连接

$mysqli_close($link);

验证mysqli扩展是否开启

有以下四种方式：

phpinfo();

var_dump(extension_loaded("mysqli"));

var_dump(function_exists("mysqli_connect"));

print_r(get_loaded_extensions());

查询

单条查询语句

$mysqli = @new mysqli('localhost','root','','test');

if($mysqli->connect_error){
    die("error:".$mysqli->connect_error);
}
$sql = "select * from user";
$result = $mysqli->query($sql);
var_dump($result->fetch_all());

多条查询语句

$mysqli = @new mysqli('localhost','root','','test');

if($mysqli->connect_error){
    die("error:".$mysqli->connect_error);
}
$sql = "select * from user;";
$sql .= "select * from user;";
if($mysqli->multi_query($sql)){
    do{
        if($result = $mysqli->store_result()){
            var_dump($result->fetch_all());
        }
    }while($mysqli->more_results() && $mysqli->next_result());//使用next_result()将指针移向第二条查询语句的结果集
}

增删改

增删改的操作和查询一样，只是sql语句不同而已。

并且，增删改也支持多条语句执行。

当执行多条sql语句时，只要第一条执行成功，就返回true。如果其中一条执行出异常，那么前面的语句正常执行，后面的语句将不再执行。

插入示例代码：

$mysqli = @new mysqli('localhost','root','','test');

if($mysqli->connect_error){
    die("error:".$mysqli->connect_error);
}
$name = 'zhoujun';
$sql = "insert into user(name) values('{$name}')";

$mysqli->query($sql);

echo $mysqli->insert_id;

预处理语句

使用预处理执行插入

$mysqli = @new mysqli('localhost','root','','test');

if($mysqli->connect_error){
    die("error:".$mysqli->connect_error);
}

$sql = "insert into user(name) values(?)";

$mysqli_stmt = $mysqli->prepare($sql);
$name = "name";
//第一个参数指定数据类型，i表示整形，s表示字符型，d表示浮点型,
//例如isd表示插入语句中第一个占位符是整形，第二个第三个是字符串和浮点
$mysqli_stmt->bind_param('s',$name);

if($mysqli_stmt->execute()){
    echo $mysqli->insert_id;
}else{
    echo $mysqli->error;
}

使用预处理语句查询

$mysqli = @new mysqli('localhost','root','','test');

if($mysqli->connect_error){
    die("error:".$mysqli->connect_error);
}

$sql = "select * from user where id>?";

$mysqli_stmt = $mysqli->prepare($sql);
$id = 2;
//第一个参数指定数据类型，i表示整形，s表示字符型，d表示浮点型,
//例如isd表示插入语句中第一个占位符是整形，第二个第三个是字符串和浮点
$mysqli_stmt->bind_param('i',$id);

if($mysqli_stmt->execute()){
    $mysqli_stmt->bind_result($id,$name);//将查询结果绑定到变量
    while($mysqli_stmt->fetch()){
        echo 'id:'.$id.'<br>';
        echo 'name:'.$name.'<br>';
        echo '<hr/>';
    }
}else{
    echo 'no data';
}

//释放结果集
$mysqli_stmt->free_result();
//关闭预处理语句
$mysqli_stmt->close();
//关闭连接
$mysqli->close();

使用预处理语句防止sql注入

$mysqli = @new mysqli('localhost','root','','test');

if($mysqli->connect_error){
    die("error:".$mysqli->connect_error);
}

$sql = "select * from user where username=? and password=?";

$mysqli_stmt = $mysqli->prepare($sql);
$username = "name";
$password = "password";
//第一个参数指定数据类型，i表示整形，s表示字符型，d表示浮点型,
//例如isd表示插入语句中第一个占位符是整形，第二个第三个是字符串和浮点
$mysqli_stmt->bind_param('ss',$username,$password);

if($mysqli_stmt->execute()){
    $mysqli_stmt->store_result();
    if($mysqli_stmt->num_rows>0){
        echo '登录成功';
    }else{
        echo '登录失败';
    }
}
//释放结果集
$mysqli_stmt->free_result();
//关闭预处理语句
$mysqli_stmt->close();
//关闭连接
$mysqli->close();

事务处理

$mysqli = @new mysqli('localhost','root','','test');

if($mysqli->connect_error){
    die("error:".$mysqli->connect_error);
}

$mysqli->autocommit(FALSE);//关闭自动提交
//用户king加200块钱
$sql1 = "update account set money=money+200 where name='king'";
$res1 = $mysqli->query($sql1);
$res1_affected_rows = $mysqli->affected_rows;
//用户kqueen减200块钱
$sql2 = "update account set money=money-200 where name='queen'";
$res2 = $mysqli->query($sql2);
$res2_affected_rows = $mysqli->affected_rows;

if($res1 && $res1_affected_rows>0 && $res2 && $res2_affected_rows>0){
    $mysqli->commit();
    $mysqli->autocommit(TRUE);
    echo "转账成功";
}else{
    $mysqli->rollback();
    echo "转账失败";
}