Differences in Php-mysql,php-mysqli,pdo

Php-mysql is the most primitive PHP operation MySQL database extension, php-mysqli I for improvement, provides more advanced features, in terms of extension, itself also increased security. The PDO (PHP Data Object) provides a abstraction layer to manipulate the database. We use code to initially compare the differences between them.

Let's look at the general code for a php-mysql:

<?PHPmysql_connect($db _host,$db _user,$db _password); mysql_select_db($dn _name); $result=mysql_query("Select ' Name ' from ' The Users ' WHERE ' location ' = '$location‘");  while($row=Mysql_fetch_array($result,Mysql_assoc)) {        Echo $row[' Name ']; }     Mysql_free_result($result); ?>

The problem with this approach is that you cannot bind Column, which is easy to be injected into SQL (SQL injection is discussed later in this article). So later developed mysql_escape_string (note: 5.3.0) and mysql_real_escape_string () to solve the problem. It is generally written like this:

<? PHP      $query sprintf ("SELECT * from Users WHERE user= '%s ' and password= '%s '",            mysql_real_escape_string ($user),            mysql_real_escape_string($password));      mysql_query ($query?>

A number of improvements have been made in php-mysqli, in addition to solving the above problems through bind column, and also supporting transaction, Multi Query, and providing an object oriented style at the same time, the sample code is as follows:

<?PHP$mysqli=NewMysqli ($db _host,$db _user,$db _password,$db _name); $sql= "INSERT into ' users ' (ID, name, gender, location) VALUES (?,?,?,?)"; $stmt=$mysqli->prepare ($sql); $stmt->bind_param (' Dsss ',$source _id,$source _name,$source _gender,$source _location); $stmt-execute (); $stmt->bind_result ($id,$name,$gender,$location);  while($stmt-Fetch ()) {        Echo $id.$name.$gender.$location; }     $stmt-Close (); $mysqli-close ();?>

Can see this only in the way has php-mysql problem solved, of course, with MySQL is no problem, if one day my database replaced with Oralce,postgresql, it is not easy to do, the code needs to change. So the PDO comes out, it abstracts the operation of the database, without needing what kind of database the bottom of the tube uses. The sample code is as follows:

<?PHP$dsn= "mysql:host=$db _host;d bname=$db _name"; $DBH=NewPDO ($dsn,$db _user,$db _password); $sql= "SELECT ' Name ', ' Location ' from ' Users ' WHERE ' location ' =?, ' name ' =? '; $sth=$DBH->prepare ($sql); $sth->execute (Array($location,$name)); $result=$sth->fetch (PDO::fetch_obj); Echo $result->name.$result-Location ; $DBH=NULL; ?>

There are many benefits to doing so:

1. PDO connects to a database by connection String (such as "mysql:host= $db _host;dbname= $db _name" in the example) to determine which database to connect to.

　　2. PDO can use Pdo::setattribute to set parameters for the connection, such as persistent Connection, returning the wrong way (Exception, e_warning, NULL), even the size of the return class name, and so on.
2. PDO supports bind column, in addition to the basic Prepare, Execute, can bind a single column, and know the column type.
4. PDO is a abstraction Layer, so the amount of code needed to change the database is small.

And, of course, the DBI approach, such as ActiveRecord and Propel ORM (object-relational Mapping), is very useful.

such as ActiveRecord, if you want to use PHP now to operate such SQL statements:

INSERT into ' users ' (ID, name, gender, location) VALUES (1, ' Roga ', ' Male ', ' TPE ')

PDO notation:

<? PHP      $sql = "INSERT into ' users ' (ID, name, gender, location) VALUES (?,?,?,?)" ;     $sth $dbh->prepare ($sql);      $sth->execute (array(1, ' Roga ', ' Male ', ' TPE '?>

The writing of ActiveRecord

<? PHP      $user New User ();      $user->id = 1;     $user->name = ' Roga ';     $user->gender = ' male ';     $user->location = ' TPE ';      Save ($user);? >

The latter greatly reduces the dependence on SQL in writing!

Differences in Php-mysql,php-mysqli,pdo