How to connect PHP to mysql-mysqli and PDO

The content of this article is to share with you the method of connecting PHP to mysql-mysqli and PDO. Friends in need can refer to it

The PDO extension was mentioned when reading the book To connect to the database, I have been using mysql extension and mysqli extension before, but PDO is basically useless, so I checked some information and specially recorded and reproduced it,

MySQL extensions for PHP (advantages and disadvantages)

Design and develop early extensions that allow PHP applications to interact with MySQL databases. The mysql extension provides a procedure-oriented interface;

and is designed for MySQL4.1.3 or earlier. Therefore, although this extension can interact with MySQL 4.1.3 or newer database servers, it does not support some features provided by later MySQL servers.

Mysqli extension for PHP

Mysqli extension, which we sometimes call MySQL enhanced extension, can be used to use the new advanced features in MySQL4.1.3 or newer versions;

The mysqli extension is included in PHP 5 and later versions;

Mysqli extension has a series of advantages. Compared with the mysql extension, the main improvements are: object-oriented interface, prepared statement support, multi-statement execution support, transaction support, enhancement debugging capabilities and embedded service support.

PHP Data Object (PDO)

PHP Data Object is a database abstraction layer specification in PHP applications. PDO provides a unified API interface so that your PHP application does not care about the specific database server system type to be connected. In other words, if you use PDO's API, you can seamlessly switch database servers whenever needed.

************************************************ *************************************************** *************************************

PHP-MySQL is the most original way for PHP to operate MySQL database Extension, the i in PHP-MySQLi stands for Improvement,

provides relatively advanced functions, and as far as Extension is concerned, it also increases security.

And PDO (PHP Data Object) provides an Abstraction Layer to operate the database

1.mysql and mysqli

mysqli is a new function library provided by php5, (i) represents an improvement, and its execution speed is faster. Of course it is also safer

Mysql is a non-persistent connection function and mysqli is a permanent connection function. That is to say

Mysql will open a connection process every time it is connected, and mysqli will use the same connection process if it is run multiple times, thereby reducing the server overhead. Some friends use new mysqli('localhost when programming ', usenamer', 'password', 'databasename'); always reports

error, Fatal error: Class 'mysqli' not found in d:\…

mysqli class is not php self Did you bring it?

is not enabled by default. You need to change php.ini under win and remove the ";" in front of php_mysqli.dll. Mysqli must be compiled into it under Linux.

1: Mysqli.dll is a database that allows you to operate the database in the form of objects or procedures, and its use is also very easy.

php connects to the mysql database. The most classic way we usually use mysql_connect() , the specific code is as follows:

#[php] view plain copy

##

mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error());  
mysql_select_db($db_name);

Browser prompt: This extension has been obsolete since PHP 5.5.0 , and will be removed in the future. It should be replaced with the MySQLi or PDO_MySQL extension. So after searching, I found the source of the following article.

Heremysql is a The extended API is designed to simplify PHP's operation on the mysql database.

mysqli

##mysqli is an enhanced version of mysql, appearing in PHP 5 and later versions. It is mainly enhanced than mysql in the following aspects:

        1、面向对象接口；2、prepared语句支持（译注：关于prepare请参阅mysql相关文档）；3、多语句执行支持；4、事务支持；5、增强的调试能力；6、嵌入式服务支持

[php] view plain copy

<?php   
$link = mysqli_connect("localhost","root","123456","test") or die("Error " . mysqli_error($link)); //连接,test为数据库的名称  
$query = "SELECT name FROM userInfo" or die("Error in the consult.." . mysqli_error($link));//查询  
$result = mysqli_query($link, $query);//查询的结果  
while($row = mysqli_fetch_array($result)) {   
    echo $row["name"] . "<br>";   
}  
?>

       上面的这段代码是一个完整的从连接到查询再到关闭数据库的一个例子。从该例子中可以看到，mysql_connect()和mysqli_connect()函数的用法上的小区别。

[php] view plain copy

//mysql  
mysql_connect("localhost","root","123456");  
mysql_select_db("userInfo");  
  
//mysqli  
mysqli_connect("localhost","root","123456","test");

    mysqli的连接数据库方法中除了使用上述（又称之为面向过程）之外，另外一种就是面向对象方式：

[php] view plain copy

1. <?php  
   $mysqli = new mysqli("localhost","root","123456","test") //填写mysql用户名、密码及数据库的名称  
       or die("Could not connect to MySQL server!");  
   $mysqli->query("set names utf8"); //设置数据库内数据的编码，相当于同时设置客户端、服务器端和脚本编码方式。  
       $sql  = "select * from userInfo";  
   $result = $mysqli->query($sql);  
   if($result){  
       if($result->num_rows>0){  
           while($row =$result->fetch_array()){//循环输出结果集中的记录  
               echo ($row[0])." ";  
               echo ($row[1])." ";  
               echo ($row[2])." ";  
               echo "<br/>";  
           }  
       }  
   }else{  
       echo "Sorry!";  
   }  
   $result=NULL;   
   $mysqli->close();  
   ?>

        这种用法中，使用mysqli之前要先new一个对象，然后使用该实例对象进行操作。

PDO

    PHP的一个数据对象，它提供了一个统一的连接数据库的API，它代表的是和数据库之间的一个连接，类似于java中的jdbc。

        简单举例如下：

[php] view plain copy

<?php  
$pdo = new PDO(&#39;pgsql:host=192.168.1.111;port=5432;dbname=anydb&#39;, &#39;anyuser&#39;, &#39;pw&#39;);//连接到数据库  
sleep(5);  
$stmt = $pdo->prepare(&#39;SELECT * FROM sometable&#39;);  
$stmt->execute();  
$pdo = null;//关闭连接  
?>

         由于从PHP 6 开始要完全使用PDO方式而非其他方式连接数据库，所以接下来详细研究一番：

1、为什么要用PDO，用PDO有什么好处？

PDO (PHP Data Object) is a lightweight unified interface for PHP to access the database. It provides a data access abstraction layer, which means that no matter what database, the same method can be used to query or read. It should be noted that PDO itself cannot implement any database functions, but must use a PDO driver for a specific database to access database services.

#2. How to use PDO?

##Here we take PHP’s golden partner mysql as an example:

PDO_MYSQL: PDO_MYSQL is the driver for the PDO interface that can complete the connection to the mysql database (note: only used for mysql 3.x or above).

Installation: Open the php.ini file and you can find the following code. Here you can see that the mysql driver is turned on by default (not shown in front) Semicolon used for comments), if you need to connect to other databases, add drivers for other databases by yourself (remove the semicolon in front of the corresponding item, and add if there is none).

[php]

view plain copy

1. //各数据库的PDO驱动  
   extension=php_pdo.dll   
   extension=php_pdo_firebird.dll //Firebird  
   extension=php_pdo_informix.dll //Informix  
   extension=php_pdo_mssql.dll    //sql server  
   extension=php_pdo_mysql.dll    //mysql  
   extension=php_pdo_oci.dll      //Oracle  
   extension=php_pdo_oci8.dll   
   extension=php_pdo_odbc.dll     //DB2  
   extension=php_pdo_pgsql.dll    //PostgreSQL  
   extension=php_pdo_sqlite.dll   //SQLite

连接：通过创建PDO基类的实例创建连接。

[php] view plain copy

//连接到数据库  
$db = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

[php] view plain copy

//处理连接错误  
try {  
    $db = new PDO('mysql:host=localhost;dbname=test', $user, $pass);  
    //查询  
    foreach($db->query('SELECT * from FOO') as $row) {  
        print_r($row);  
    }  
    $dbh = null;  
} catch (PDOException $e) {  
    print "Error!: " . $e->getMessage() . "<br/>";  
    die();  
}

[php] view plain copy

//关闭连接  
$db = null;

    PDO方式连接数据库mysql的实例：

[php] view plain copy

1. <?php  
       $dbms=&#39;mysql&#39;;       //数据库 mysql  
       $host=&#39;localhost&#39;;   //数据库主机名  
       $dbName=&#39;test&#39;;      //数据库名  
       $user=&#39;root&#39;;        //连接用户名  
       $pass=&#39;&#39;;            //密码  
       $dsn="$dbms:host=$host;dbname=$dbName";  
       classdbextendsPDO{  
          publicfunction__construct(){  
               try{  
                   parent::__construct("$GLOBALS[dsn]",$GLOBALS[&#39;user&#39;],$GLOBALS[&#39;pass&#39;]);  
                }catch(PDOException$e){  
                    die("Error: ".$e->__toString()."<br/>");  
               }  
          }  
          publicfinalfunctionquery($sql){  
               try{  
                   returnparent::query($this->setString($sql));  
               }catch(PDOException$e){  
                   die("Error: ".$e->__toString()."<br/>");  
               }  
          }  
          privatefinalfunctionsetString($sql){  
               echo"处理查询";  
               return $sql;  
          }  
       }  
       $db=new db();  
       $db->setAttribute(PDO::ATTR_CASE,PDO::CASE_UPPER);  
       foreach($db->query(&#39;select * from table_name&#39;) as $row){  
          print_r($row);  
       }  
       $db->exec(&#39;delete from table_name where id=11&#39;);  
   ?>

3、更多的PDO方法：

[php] view plain copy

PDO::beginTransaction — 启动一个事务  
PDO::commit — 提交一个事务  
PDO::__construct — 创建一个表示数据库连接的 PDO 实例  
PDO::errorCode — 获取跟数据库句柄上一次操作相关的 SQLSTATE  
PDO::errorInfo — Fetch extended error information associated with the last operation on the database handle  
PDO::exec — 执行一条 SQL 语句，并返回受影响的行数  
PDO::getAttribute — 取回一个数据库连接的属性  
PDO::getAvailableDrivers — 返回一个可用驱动的数组  
PDO::inTransaction — 检查是否在一个事务内  
PDO::lastInsertId — 返回最后插入行的ID或序列值  
PDO::prepare — Prepares a statement for execution and returns a statement object  
PDO::query — Executes an SQL statement, returning a result set as a PDOStatement object  
PDO::quote — Quotes a string for use in a query.  
PDO::rollBack — 回滚一个事务  
PDO::setAttribute — 设置属性

[php] view plain copy

PDOStatement::bindColumn — 绑定一列到一个 PHP 变量  
PDOStatement::bindParam — 绑定一个参数到指定的变量名  
PDOStatement::bindValue — 把一个值绑定到一个参数  
PDOStatement::closeCursor — 关闭游标，使语句能再次被执行。  
PDOStatement::columnCount — 返回结果集中的列数  
PDOStatement::debugDumpParams — 打印一条 SQL 预处理命令  
PDOStatement::errorCode — 获取跟上一次语句句柄操作相关的 SQLSTATE  
PDOStatement::errorInfo — 获取跟上一次语句句柄操作相关的扩展错误信息  
PDOStatement::execute — 执行一条预处理语句  
PDOStatement::fetch — 从结果集中获取下一行  
PDOStatement::fetchAll — 返回一个包含结果集中所有行的数组  
PDOStatement::fetchColumn — 从结果集中的下一行返回单独的一列。  
PDOStatement::fetchObject — 获取下一行并作为一个对象返回。  
PDOStatement::getAttribute — 检索一个语句属性  
PDOStatement::getColumnMeta — 返回结果集中一列的元数据  
PDOStatement::nextRowset — 在一个多行集语句句柄中推进到下一个行集  
PDOStatement::rowCount — 返回受上一个 SQL 语句影响的行数  
PDOStatement::setAttribute — 设置一个语句属性  
PDOStatement::setFetchMode — 为语句设置默认的获取模式。

  

[php] view plain copy

Exception::getMessage — 获取异常消息内容。  
Exception::getPrevious — 返回异常链中的前一个异常  
Exception::getCode — 获取异常代码  
Exception::getFile — 获取发生异常的程序文件名称  
Exception::getLine — 获取发生异常的代码在文件中的行号  
Exception::getTrace — 获取异常追踪信息  
Exception::getTraceAsString — 获取字符串类型的异常追踪信息  
Exception::toString — 将异常对象转换为字符串  
Exception::clone — 异常克隆

本文转自：https://blog.csdn.net/llittlepig/article/details/38272769

The above is the detailed content of How to connect PHP to mysql-mysqli and PDO. For more information, please follow other related articles on the PHP Chinese website!