在当今互联网应用中,数据库已经成为了存储和管理数据的重要工具,而PHP作为一种广泛应用于Web开发的脚本语言,也提供了丰富的数据库操作功能,本文将介绍如何使用PHP进行数据库的连接、查询、插入、更新和删除等基本操作。
一、数据库连接
在使用PHP进行数据库操作之前,首先需要建立与数据库的连接,PHP提供了mysqli和PDO两种扩展来实现与MySQL数据库的连接,下面分别介绍这两种方法。
1、使用mysqli扩展连接数据库
<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // 创建连接 $conn = new mysqli($servername, $username, $password, $dbname); // 检查连接 if ($conn->connect_error) { die("连接失败: " . $conn->connect_error); } echo "连接成功"; ?>
2、使用PDO扩展连接数据库
<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; try { // 创建连接 $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // 设置PDO错误模式为异常抛出 $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); echo "连接成功"; } catch(PDOException $e) { echo "连接失败: " . $e->getMessage(); } ?>
二、数据库查询
查询是数据库操作中最常用的功能之一,PHP提供了多种查询方法,如SELECT语句、预处理语句等,下面分别介绍这两种方法。
1、使用SELECT语句查询数据
<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // 创建连接 $conn = new mysqli($servername, $username, $password, $dbname); // 检查连接 if ($conn->connect_error) { die("连接失败: " . $conn->connect_error); } $sql = "SELECT id, firstname, lastname FROM MyGuests"; $result = $conn->query($sql); if ($result->num_rows > 0) { // 输出数据 while($row = $result->fetch_assoc()) { echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>"; } } else { echo "0 结果"; } $conn->close(); ?>
2、使用预处理语句查询数据(防止SQL注入)
<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; try { // 创建连接和预处理语句对象 $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); $stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests"); // 执行预处理语句并获取结果集对象 $stmt->execute(); // 获取结果集并输出数据 while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>"; } } catch(PDOException $e) { echo "连接失败: " . $e->getMessage();;} } finally { // 关闭连接和预处理语句对象,释放资源 } ?>> <?php try { // 创建连接和预处理语句对象 $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); $stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests"); // 执行预处理语句并获取结果集对象 $stmt->execute(); // 获取结果集并输出数据 while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>"; } } catch(PDOException $e) { echo "连接失败: " . $e->getMessage();; } finally { // 关闭连接和预处理语句对象,释放资源 } ?>> <?php try { // 创建连接和预处理语句对象 $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); $stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests"); // 执行预处理语句并获取结果集对象 $stmt->execute(); // 获取结果集并输出数据 while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>"; } } catch(PDOException $e) { echo "连接失败: " . $e->getMessage();; } finally { // 关闭连接和预处理语句对象,释放资源 } ?>> <?php try { // 创建连接和预处理语句对象 $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); $stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests"); // 执行预处理语句并获取结果集对象 $stmt->execute(); // 获取结果集并输出数据 while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>"; } } catch(PDOException $e) { echo "连接失败: " . $e->getMessage();; } finally { // 关闭连接和预处理语句对象,释放资源 } ?>> <?php try { // 创建连接和预处理语句对象 $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); $stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests"); // 执行预处理语句并获取结果集对象 $stmt->execute(); // 获取结果集并输出数据 while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " .
发表评论