MySQLi增删改查实例

mysqli连接 数据库查询单条数据

<?php
// 假定数据库用户名：root，密码：root，数据库：tianxiangphp_v1.2 
$con=mysqli_connect("localhost","root","root","tianxiangphp_v1.2");
if (mysqli_connect_errno($con))
{
    echo "连接 MySQL 失败: " . mysqli_connect_error();
}

// 执行查询
$res = $con->query("SELECT * FROM userlist");

$data = mysqli_fetch_array($res);
echo "<pre>";
var_dump($data);
mysqli_close($con);
?>

query()与mysqli_query()使用

放在函数内与函数外
mysqli_query($con,”sql语句”);
$con->query(”sql语句”);

查询多条数据fetch_all()

<?php
// 假定数据库用户名：root，密码：root，数据库：tianxiangphp_v1.2
$con=mysqli_connect("localhost","root","root","tianxiangphp_v1.2");
if (mysqli_connect_errno($con))
{
    echo "连接 MySQL 失败: " . mysqli_connect_error();
}

// 执行查询
$res = $con->query("SELECT * FROM userlist");

$data = $res->fetch_all(MYSQLI_ASSOC);
echo "<pre>";
var_dump($data);
mysqli_close($con);
?>

插入单条数据mysqli_query()或query()

为true则插入成功
注意：需要插入多条可循环遍历插入

<?php
// 假定数据库用户名：root，密码：root，数据库：tianxiangphp_v1.2
$con=mysqli_connect("localhost","root","root","tianxiangphp_v1.2");
if (mysqli_connect_errno($con))
{
    echo "连接 MySQL 失败: " . mysqli_connect_error();
}

// 执行插入
$res = mysqli_query($con,"INSERT INTO userlist(mb_type,qy_userid) VALUES (1,666)");
if ($res == TRUE) {
    echo "user entry saved successfully.";
} else {
    echo "INSERT attempt failed" ;
}
?>

修改数据mysqli_query()或query()

$res = mysqli_query($con,"UPDATE userlist SET `mbid`=77 WHERE userid = 1894");
if ($res == TRUE) {
    echo "user entry saved successfully.";
} else {
    echo "update attempt failed" ;
}

删除数据mysqli_query()或query()

mysqli_query($con,”sql语句”);
$con->query(”sql语句”);

$res = mysqli_query($con,"DELETE FROM userlist WHERE userid = 1895");

统计表总条数

$res = $con->query("SELECT * FROM yzm_user");
//统计总条数
$count = mysqli_num_rows($res);