Summary! Common code segments for PHP to operate MySQL

Collection of PHP operating MySQL database sample code

This article introduces to you the code segments that are commonly used in the actual development of practical PHP websites to operate the MySQL database, all The code is executed reliably, and this article will be continuously updated! ! !
1. Insert the data table into the database

<?php
$con = mysql_connect("【数据库地址】","【数据库用户名】","【数据库密码】");//创建MySQL连接
mysql_select_db("【数据库名】", $con);//选择MySQL数据库
$sql = "CREATE TABLE abc 
(
id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(id),
openid varchar(32),
nickname varchar(32),
sex varchar(8)
)";//创建名称为abc的数据表，id不能为空且自动递增并设置为主键
mysql_query($sql,$con);//执行一条MySQL语句
mysql_close($con);//关闭mysql连接
?>

2. Insert new records into the database table

<?php
$datatime = date("Y-m-d H:i:s",time());//获取时间
$con = mysql_connect("【数据库地址】","【数据库用户名】","【数据库密码】");//创建MySQL连接
mysql_select_db("【数据库名】", $con);//选择MySQL数据库
mysql_query("SET NAMES &#39;UTF8&#39;");//设置编码（解决插入中文乱码的问题）
mysql_query("INSERT INTO 【数据表名】 (openid, add_time, nickname) 
VALUES (&#39;123&#39;, &#39;$datatime&#39;, &#39;abc&#39;)");//插入新记录
mysql_close($con);//关闭mysql连接
?>

3. Read all the contents of the data table

<?php
$con = mysql_connect("【数据库地址】","数【据库用户名】","【数据库密码】");//创建MySQL连接
mysql_select_db("【数据库名】", $con);//选择MySQL数据库
$result = mysql_query("SELECT * FROM 【数据表名】");//获取数据表的所有数据
while($row = mysql_fetch_array($result)){//从结果集中取得一行作为关联数组，如何没有更多行则返回false
    echo $row[&#39;openid&#39;]."<hr>";//输出表中所有openid字段的值
}
mysql_close($con);//关闭mysql连接
?>

4 , Read matching data from the data table

<?php
$con = mysql_connect("【数据库地址】","【数据库用户名】","【数据库密码】");//创建MySQL连接
mysql_select_db("【数据库名】", $con);//选择MySQL数据库
$result = mysql_query("SELECT * FROM 【数据表名】 WHERE openid=&#39;123&#39;");//获取数据表的openid=123的数据行
while($row = mysql_fetch_array($result)){//从结果集中取得一行作为关联数组，如何没有更多行则返回false
    echo $row[&#39;nickname&#39;]."<hr>";//输出表中所有openid字段的值
}
mysql_close($con);//关闭mysql连接
?>

5. Modify the data in the database table

<?php
$con = mysql_connect("【数据库地址】","【数据库用户名】","【数据库密码】");//创建MySQL连接
mysql_select_db("【数据库名】", $con);//选择MySQL数据库
mysql_query("UPDATE 【数据表名】 SET nickname=&#39;new&#39; WHERE openid=&#39;123&#39;");//更新id=123记录行的nickname字段
mysql_close($con);//关闭mysql连接
?>

6. Delete records from the data table

<?php
$con = mysql_connect("【数据库地址】","【数据库用户名】","【数据库密码】");//创建MySQL连接
mysql_select_db("数据库名", $con);//选择MySQL数据库
mysql_query("DELETE FROM 数据表名 WHERE openid=&#39;123&#39;");//删除openid=123的一行记录
mysql_close($con);//关闭mysql连接
?>

7. Delete from the database Data table

<?php
$con = mysql_connect("【数据库地址】","【数据库用户名】","【数据库密码】");//创建MySQL连接
mysql_select_db("数据库名", $con);//选择MySQL数据库
$sql = "DROP TABLE abc";//删除名为abc的数据表
mysql_query($sql,$con);//执行一条MySQL语句
mysql_close($con);//关闭mysql连接
?>

PHP Data Object (PDO) extension defines a lightweight consistent interface for PHP to access the database. Provides a data access abstraction layer, which means that no matter which database is used, the same functions (methods) can be used to query and obtain data.
PDO is released with PHP5.1 and can also be used in the PECL extension of PHP5.0. It cannot run on previous PHP versions.
The following is an example to illustrate the usage of PDO:

<?php
$host = "【数据库地址】";
$username = "【数据库用户名】";
$password = "【数据库密码】";
$dbname = "【数据库名】";
//将要执行的代码放入try块中,如果这些代码执行过程中某一条语句发生异常,则程序直接跳转到catch块中,由$e收集错误信息和显示。
try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);//创建连接
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //设置 PDO 错误模式，用于抛出异常
    $sql = "CREATE TABLE abc (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    openid varchar(32) NOT NULL,
    nickname varchar(32) NOT NULL,
    sex varchar(8) NOT NULL
    )";//创建名称为abc的数据表，id不能为空且自动递增并设置为主键
    $conn->exec($sql);//使用exec()没有结果返回
}
catch(PDOException $e){
    echo $sql . "<br>" . $e->getMessage();//显示异常信息
}
$conn = null;//关闭连接
?>

If the environment permits, use PDO for MySQL database operations as much as possible.

Recommended: "PHP Video Tutorial"

The above is the detailed content of Summary! Common code segments for PHP to operate MySQL. For more information, please follow other related articles on the PHP Chinese website!