【PHP】5版本 过程式操作MySQL

 

建立连接和释放连接：

# 连接参数
$sever = 'localhost:3309';
$username = 'root';
$password = 'root';

# 调用连接方法，如果失败结束并打印错误信息
$connection = mysql_connect($sever, $username, $password) or die('连接失败: '.mysql_error());

# 释放连接
mysql_close($connection);

 

 

建立持久连接：

$connection = mysql_pconnect($sever, $username, $password) or die('连接失败'.mysql_error());

 

切换库位置：

$sever = 'localhost:3307';
$username = 'root';
$password = '123456';
$schema = 'my_info';

$connection = mysql_pconnect($sever, $username, $password) or die('连接失败'.mysql_error());

# 切换库
mysql_select_db($schema, $connection) or die('选择库'.$schema.' 失败: '.mysql_error());

# 释放连接
mysql_close($connection);

 

设置读写编码：

# 设置读写编码
mysql_query("set character set 'utf8'"); //读库
mysql_query("set names 'utf8'"); //写库

　　

泛用方法mysql_query

用于执行建库，建表，增删改查的SQL语句

建库执行

$sever = 'localhost:3307';
$username = 'root';
$password = '123456';
$schema = 'my_info';

$connection = mysql_pconnect($sever, $username, $password) or die('连接失败'.mysql_error());
mysql_select_db($schema, $connection) or die('选择库'.$schema.' 失败: '.mysql_error());

# 设置读写编码
mysql_query("set character set 'utf8'"); //读库
mysql_query("set names 'utf8'"); //写库

# 准备建库SQL语句
$sql_create_db = "
CREATE DATABASE IF NOT EXISTS `php-db`
CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
";

# 交给mysql_query执行 如发生错误直接结束
mysql_query($sql_create_db) or die('建库失败：'.mysql_error());

# 释放连接
mysql_close($connection);

 

建表执行

$sever = 'localhost:3307';
$username = 'root';
$password = '123456';
$schema = 'php-db';

$connection = mysql_pconnect($sever, $username, $password) or die('连接失败'.mysql_error());

# 切换到php-db库
mysql_select_db($schema, $connection) or die('选择库'.$schema.' 失败: '.mysql_error());

# 设置读写编码
mysql_query("set character set 'utf8'"); //读库
mysql_query("set names 'utf8'"); //写库

# 准备建表SQL语句
$sql_create_table = "
CREATE TABLE IF NOT EXISTS `MyGuests` (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP
)
";

# 交给mysql_query执行 如发生错误直接结束
mysql_query($sql_create_table) or die('建表失败：'.mysql_error());

# 释放连接
mysql_close($connection);

 

插入语句：

$sever = 'localhost:3307';
$username = 'root';
$password = '123456';
$schema = 'php-db';

$connection = mysql_pconnect($sever, $username, $password) or die('连接失败'.mysql_error());

# 切换到php-db库
mysql_select_db($schema, $connection) or die('选择库'.$schema.' 失败: '.mysql_error());

# 设置读写编码
mysql_query("set character set 'utf8'"); //读库
mysql_query("set names 'utf8'"); //写库

# SQL插入语句
$sql_insert = "
    INSERT INTO MyGuests (firstname, lastname, email)
    VALUES ('John', 'Doe', 'john@example.com');
";

# 交给mysql_query执行 如发生错误直接结束
mysql_query($sql_insert) or die('插入失败：'.mysql_error());

# 释放连接
mysql_close($connection);

　

查询数据并渲染：

$sever = 'localhost:3307';
$username = 'root';
$password = '123456';
$schema = 'php-db';

$connection = mysql_pconnect($sever, $username, $password) or die('连接失败'.mysql_error());

# 切换到php-db库
mysql_select_db($schema, $connection) or die('选择库'.$schema.' 失败: '.mysql_error());

# 设置读写编码
mysql_query("set character set 'utf8'"); //读库
mysql_query("set names 'utf8'"); //写库

# SQL查询
$query_sql = "SELECT * FROM MyGuests";
$result = mysql_query($query_sql, $connection) or die('查询失败：'.mysql_error());

# 获取数量结果
echo '数量结果：'.mysql_num_rows($result).'<br>';
if (mysql_num_rows($result) > 0) {

    # 取当前行记录
    while($row = mysql_fetch_assoc($result)) {

        # 获取行记录数据
        echo "Id: "
            .$row["id"]
            ." - Name: "
            .$row["firstname"]
            ." ".$row["lastname"]
            ." - Email: "
            ." ".$row["email"]
            ."<br>";
    }
} else {
    echo '没有记录！';
}

# 释放连接
mysql_close($connection);