1 min read

PHP:mysqli面向过程查询小记

1.连接数据库

$mysqli = mysqli_connect('localhost','root','root','test') OR die('Could not connect: ' . mysqli_error());  

2.设定字符编码

mysqli_set_charset($mysqli,"utf8");

备注:在函数中调用$mysqli。

function use_database() {
    global $mysqli;
        ....

3.SELECT查询例子(不带条件,返回结果集,查询成功存入数组$arr)

$arr = array();
$sql = 'SELECT id, name FROM user';
$res = mysqli_query($mysqli,$sql);
while($row = mysqli_fetch_array($res)){
    array_push($arr,$row);
}

4.SELECT预编译查询(带条件,返回一条信息,返回值绑定变量)

sql = "select uid,id,username from user where username=? and password=? limit 1";
$stmt = mysqli_prepare($mysqli,$sql);  
mysqli_stmt_bind_param($stmt,"ss",$username,$password);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $result['uid'],$result['id'],$result['username']);

if( mysqli_stmt_fetch($stmt) ){
    //登录成功
    //var_dump($result);

}

5.SELECT普通查询(带条件,返回结果集存入数组$arr)

$arr = array();
$sql = 'SELECT * FROM user WHERE id = '.(int)$id.'  ORDER BY id desc';
$res = mysqli_query($mysqli,$sql_m);
while($row = mysqli_fetch_array($res)){
    array_push($arr,$row);

}

6.INSERT预编译查询(插入数据)

$sql = 'INSERT INTO `user` (`id`, `name`, `tel`, `pwd`, `create_date`) VALUES (?, ?, ?, ?, ?)';
$stmt = mysqli_prepare($mysqli,$sql);  
mysqli_stmt_bind_param($stmt,"issss",$id,$name,$tel,$pwd,$time);
mysqli_stmt_execute($stmt);
if( mysqli_affected_rows($mysqli) ){
    exit('添加成功');
}

7.UPDATE预编译查询(更新数据)

$sql = 'UPDATE user SET name = ? ,tel= ? ,pwd = ?, mobile=?,username=? WHERE uid = ? ';
$stmt = mysqli_prepare($mysqli,$sql);  
mysqli_stmt_bind_param($stmt,"sssssi",$name,$tel,$pwd,$mobile,$username,$uid);
mysqli_stmt_execute($stmt);
if( mysqli_affected_rows($mysqli) ){
    exit('用户修改成功!');
}