php-mysql连接数据库增删改查

使用mysqli扩展操作，支持预处理，扩展性强，sql防注入

支持面向过程和面向对象以及PDO实例化操作

面向过程操作：（代码书写繁琐）

//连接数据库
$link = mysqli_connect("localhost","root","root","zb")or die("连接数据库失败".mysqli_error());
mysqli_query($link,"set names utf-8");   //设置编码，防止发生乱码
//模糊查询
$sql = mysqli_query($link,"SELECT post_title FROM wp_posts order by id desc limit 5");
//循环输出结果
do {      //do...while 循环
    echo "<pre>";
    
    print_r($info);

}while($info = mysqli_fetch_array($sql));

面向对象操作：（支持预处理，扩展性不强，只针对mysql数据库）

$conn = new mysqli("localhost","root","root","zb");
// 检查连接是否成功
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 插入数据（增）
$sql = "INSERT INTO user (username, password) VALUES (?, ?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param("ss", $value1, $value2); // 根据实际列类型替换's'和绑定变量
$value1 = '李四';
$value2 = '789456';
$result = $stmt->execute();
if ($result) {
	echo "插入数据成功！";
}else{
	echo "插入数据失败！";
}
// 删除数据（删）
$sql = "DELETE FROM user WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $delete_id);
$delete_id = 1;
$result2 = $stmt->execute();
if ($result2) {
	echo "删除成功";
}else {
	echo "删除失败！";
}
// 更新数据（改）
$sql = "UPDATE user SET username = ? WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("si", $new_value, $id);
$new_value = '王五';
$id = 1;
$result1 = $stmt->execute();
if ($result1) {
	echo "修改数据成功！";
}else {
	echo "修改数据失败！";
}
// 查询数据（查）
$sql = "SELECT * FROM user WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $id); // 假设id是整数
$id = 1;
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    // 处理查询结果
    echo "<pre>";
    print_r($row);
}
// 关闭连接
$conn->close();

PDO实例化：（代码简便明了，扩展性强）

/* 连接 */
try {
    $pdo = new PDO("mysql:host=数据库地址;dbname=数据库名称", "数据库账号", "数据库密码");
} catch (PDOException $e) {
    die ("连接数据库失败：".$e->getMessage());
}

// 增
/* 最简单insert */
$queryObj = $pdo->query("INSERT INTO `pages` (name, date) values('Sun', '2020-02-02 00:00:00')");
echo $queryObj->rowCount(); // 影响条数
echo 'New ID: '.$pdo->lastinsertid(); // 最新ID
/* 例子 */
$sql = "INSERT INTO `user` (`login` ,`password`) VALUES (:login, :password)";
$stmt = $pdo->prepare($sql);
$stmt->execute( array(':login'=>'John',':password'=>md5('ADCkiller')) );    
echo $pdo->lastinsertid();
/* 例子 */
$sql = "INSERT INTO score (sno, cno) VALUES (:sno, :cno)";
$result = $pdo->prepare($sql);
$result->bindParam(':sno',$sno);
$result->bindParam(':cno',$cno);
$sno = 'one';
$cno = 100;
$result->execute(); //返回布尔；
var_dump( $pdo->lastInsertId() );

// 删
/* 最简单delete */
$rowCount = $pdo->query("DELETE FROM `pages` WHERE `id` = 888888")->rowCount();
echo $rowCount;
/* 带参数 */
$sql = "DELETE FROM `pages` WHERE `id` = :id ";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':id', 100000);
$stmt->execute();
echo $stmt->rowCount();
 
// 改
$rowsAffected = $pdo->exec("UPDATE `pages` SET `status`= 0 WHERE `id`= 221678");
var_dump( $rowsAffected ); // 影响行数
/* 例子 */
$sql = "UPDATE `pages` SET `status`=:status WHERE `id`=:id";
$stmt = $pdo->prepare($sql);
$stmt->execute(array(':status'=> 1, ':id'=>'221676'));
$rowsUpdated = $stmt->rowCount(); // 如果数据重复，没有改动，也是返回0，请注意
echo $rowsUpdated;
 
// 查
/* 最简单查询，PDO::FETCH_ASSOC等解释在最下面 */
$queryObj = $pdo->query("select * from users where username='admin'");
$resultArrOneRow = $queryObj->fetch( PDO::FETCH_ASSOC ); // 单行数据，一维数组，建议foreach() + fetch()
 
$queryObj = $pdo->query("select * from users where username='admin'");
$resultArrRows = $queryObj->fetchAll( PDO::FETCH_ASSOC ); // 所有数据，二维数组

/* 带参数查询 */
$login = 'admin';
$sql = "SELECT * FROM `users` WHERE `username` = :login_string";
$stmt = $pdo->prepare($sql);
$stmt->execute( array(':login_string' => $login) );
$result = $stmt->fetchAll( PDO::FETCH_ASSOC );
//print_r( $result ); 
 
/* 带参数查询 */
$login = 'adm%';
$sql = "SELECT * FROM `users` WHERE `username` LIKE :login_string";
$stmt = $pdo->prepare($sql);
$stmt->execute( array(':login_string' => $login) );
$result = $stmt->fetchAll( PDO::FETCH_ASSOC );
//print_r( $result ); 
 
/* Left join */
$STM = $pdo->prepare("SELECT * FROM table1 LEFT JOIN table2 ON table1.typeid = table2.id WHERE table1.name = ? ");
$STM->bindValue(1, 'david');
$STM->execute();
$result = $STM->fetchAll( PDO::FETCH_ASSOC );
//print_r( $result ); 

/* right join */
$searching_name = 'david';
$STM = $pdo->prepare("SELECT * FROM table1 RIGHT JOIN table2 ON table2.id = table1.typeid WHERE table1.name = :name ");
$STM->bindParam(':name', $searching_name); // bindParam第二个参数只能是一个变量，bindValue就随意
$STM->execute();
$result = $STM->fetchAll( PDO::FETCH_ASSOC );
//print_r( $result ); 
 
/* 关闭 */
$pdo = null;