PHP连接MySQL数据库的几种方法

PHP连接MySQL的五种方法

最新推荐文章于 2023-12-13 20:32:51 发布

原创最新推荐文章于 2023-12-13 20:32:51 发布 · 639 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#php #连接mysql

PHP 专栏收录该内容

113 篇文章

订阅专栏

本文详细介绍了使用PHP连接MySQL数据库的五种常见方法，包括传统的mysql_扩展、mysqli扩展的面向过程和面向对象方式、PDO扩展以及ADODB类库，并提供了具体的代码示例。

1、最简单的方式：mysql - 面向过程

<?php
$con = mysql_connect("localhost", "root", "password");
$select_db = mysql_select_db('test');
if (!$select_db) {
    die("could not connect to the db:\n" .  mysql_error());
}
//查询代码
$sql = "select * from table_name";
$res = mysql_query($sql);
if (!$res) {
    die("could get the res:\n" . mysql_error());
}
while ($row = mysql_fetch_assoc($res)) {
    print_r($row);
}
//关闭数据库连接
mysql_close($con);
?>

2、新的方式：mysqli - 面向过程

<?php
$host = 'localhost';
$user = 'root';
$password = 'password';
$database = 'test';
$conn = mysqli_connect($host, $user, $password, $database, $port); // 连接数据库
//连接数据库错误提示
if (mysqli_connect_errno($conn)) { 
    die("连接 MySQL 失败: " . mysqli_connect_error()); 
}
mysqli_query($conn,"set names utf8"); // 数据库编码格式
// mysqli_set_charset($conn, "utf8"); // 设置默认客户端字符集
// mysqli_select_db($conn, $database); // 更改连接的默认数据库
//查询代码
$sql = “select * from table_name”;
$query = mysqli_query($conn,$sql);
while($row = mysqli_fetch_array($query)){
    echo $row['title'].PHP_EOL;
}
// 释放结果集
mysqli_free_result($result);
// 关闭MySQL数据库连接
mysqli_close($conn);

// 示例
$con = mysqli_connect('host', 'user','password','database','port');
if(!$con){
    echo "connect fail \n";
    die("连接错误: " . mysqli_connect_error());
}
//var_dump($con);die;
mysqli_query($con, "set names latin1");
$sql = "select * from db_name.t_scenario_sp_map where F_sp_id in ('1000000033','1000000034')";
$res = mysqli_query($con, $sql);
//var_dump($res);die;
//var_dump($res->fetch_row());die;
$ret = array();
while($row = mysqli_fetch_assoc($res)){
    $ret[] = $row;
}
var_dump($ret);die;
mysqli_close($con);

/*$ret = mysqli_fetch_assoc($res);
while ($ret){
    echo $ret['F_sp_id'];
    $displayName = mb_convert_encoding($ret['F_sp_display_name'], 'utf-8', 'GBK');
    echo $displayName;
    $ret = mysqli_fetch_assoc($res);
}*/

3、常用方式：mysqli - 面向对象

<?php
//连接数据库方式1
$conn = new mysqli('host', 'user', 'password', 'database', 'port');
//连接数据库方式2
// $conn = new mysqli();
// $conn -> connect('host', 'user', 'password', 'database', 'port');
//check connection (检查PHP是否连接上MYSQL)
if ($conn -> connect_errno) {
    printf("Connect failed: %s\n", $conn->connect_error);
    exit();
}
//查询代码
$sql = 'select * from t_test';
$query = $conn->query($sql);
while($row = $query->fetch_array()){
    echo $row['title'];
}
// 释放结果集
$query -> free_result();
// 关闭MySQL连接
$conn -> close();

4、PDO连接MySQL数据库

<?php
$db = new PDO('mysql:host=host;port=port;database=database', 'user', 'password');
try {
    foreach ($db->query('select * from t_test') as $row){
        print_r($row);
    }
    $db = null; //关闭数据库
} catch (PDOException $e) {
    echo $e->getMessage();
}

// 示例
$dbms='mysql';
$host='';
$port = ;
$dbName='';
$user='';
$pass='';
$dsn="$dbms:host=$host;port=$port;dbname=$dbName";
try {
    $pdo = new PDO($dsn, $user, $pass);
    //print_r($dbh);
    $pdo->exec("set names 'latin1'");
    $sql = "select * from db_name.t_scenario_sp_map where F_sp_id in ('1000000033','1000000034')";
    $stmt = $pdo->prepare($sql);
    $rs = $stmt->execute();
    $ret = array();
    if ($rs) {
        // PDO::FETCH_ASSOC 关联数组形式
        // PDO::FETCH_NUM 数字索引数组形式
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $ret[] = $row;
        }
    }
    var_dump($ret);die;
    /*foreach($pdo->query("select * from db_name.t_scenario_sp_map where F_sp_id in ('1000000033','1000000034')") as $row) {
        var_dump($row);die;
    }*/
    $pdo = null;
} catch (Exception $e) {
    print_r($e);
    die ("Error!: " . $e->getMessage() . "\n");
}
?>

5、ADODB连接MySQL数据库

<?php
// 安装驱动-引入类即可
// adodb.inc.php包含所有数据库类包含的使用函数，必须加载
require_once './adodb5/adodb.inc.php';
$conn = &ADONewConnection('mysql');
$conn->connect('localhost','root','password','test');
$conn->Execute("set names utf8");
//查询代码
$res = $conn->Execute("select * from db_table");
if (!$res){
    echo $conn->ErrorMsg();
}else{
    var_dump($res);
}
//关闭连接
$conn->close();
?>

6、关闭mysql数据库连接方法

MySQL-面向过程：mysql_close($conn);
MySQLi-面向过程：mysqli_close($conn);
MySQLi-面向对象：$conn->close();
PDO：$conn = null;
ADODB：$conn->close(); 或 $conn->free();