Detailed explanation of using PDO method in php, detailed explanation of phppdo

Detailed explanation of using PDO method in php, detailed explanation of phppdo_PHP tutorial

WBOY

Release： 2016-07-13 10:10:47

Original

1009 people have browsed it

Detailed explanation of using PDO method in php, detailed explanation of phppdo

This article analyzes in detail the method of using PDO in PHP. Share it with everyone for your reference. The specific analysis is as follows:

PDO::exec: returns an int type, indicating the number of items that affect the result.

Copy code The code is as follows:

PDOStatement::execute

Returns a boolean type, true indicates successful execution, false indicates execution failure, these two usually appear in the following code:

Copy code The code is as follows:

$rs0 = $pdo->exec($sql);
$pre = $pdo->prepare($sql);
$rs1 = $pre->execute();

Generally, you can use the value of $rs0 to determine whether the SQL execution is successful or not. If the value is false, it means that the SQL execution failed, 0 means no changes, and a value greater than 0 means how many records were affected.

But $rs1 can only return whether the SQL execution is successful or not. If you need to get the number of affected records, you need to use $pre->rowCount();

I personally like to use MySQL, so I have these two lines in my extensions.ini.

Copy code The code is as follows:

extension=pdo.so
extension=pdo_mysql.so

Then in the program, the code is as follows:

Copy code The code is as follows:

define('DB_NAME','test');
define('DB_USER','test');
define('DB_PASSWD','test');
define('DB_HOST','localhost');
define('DB_TYPE','mysql');
$dbh = new PDO(DB_TYPE.':host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASSWD);

The use of constant settings in this article is my personal habit. You don’t have to be as troublesome as me. When doing the above operations, $dbh itself represents the connection of PDO. So how to use PDO?

The first, lazy method query, don’t think about anything, just use the query function as usual, the code is as follows:

Copy code The code is as follows:

$sql = 'select * from test';
foreach ( $dbh->query($sql) as $value)
{
echo $value[col];
};

The second method is to automatically import prepare. After using PDO, I prefer to use the prepare function to perform actions. The advantage of prepare is that you can write the SQL code first and automatically import it later. The information we need.

I think the biggest advantage of this is that it can reduce many security issues compared to using query directly. First, we use prepare to set the SQL code, and then use bindparm to perform the setting action. The code is as follows:

Copy code The code is as follows:

$sth = $dbh->prepare('update db set zh_CN= :str where SN=:SN' );
$sth->bindParam(':str',$str,PDO::PARAM_STR,12);
$sth->bindParam(':SN',$SN);
$sth->execute();

Please pay attention to :str and :SN in the text. When we use the bindParam function, we can use :word to specify the part that the system needs to apply. For example, we use :str and :SN to specify, and the actual content depends on bindParam can also specify the type we want to input.

First of all, let’s look at the specification of :str. :str Since I am sure that the data is text, I use PD::PARAM_STR to tell the program "this is a string" and give it a range, that is, the length is 12 characters Bits.

We can also be less complicated, like :SN, although it is also specified using bindParam, but we omit the type and length, PHP will use the default type of the variable to apply.

Finally, it is to use $sth->execute(); to perform the execution action. It is basically not difficult, it can even be said to be very simple.

If you have a large amount of data that needs to be applied repeatedly, you can desperately reuse bindParam to specify, such as my :str and :SN. If there are ten pieces of data, I can also add it directly to the data like this Library, the code is as follows:

Copy code The code is as follows:

$sth = $dbh->prepare('insert into db ("zh_CN","zh_TW")values (:str , :SN');
foreach ($array => $value )
{
$sth->bindParam(':str',$value[str],PDO::PARAM_STR,12);
$sth->bindParam(':SN',$value[SN]);
$sth->execute();
}

Even strong people like my friend write all possible SQLs at the back of a file. Later, the process SQL part is all brought in with variables. Anyway, the data can be applied in a ready-made way.

Then, if you use the prepare method to select, the keywords can also be specified using: word as above, the code is as follows:

Copy code The code is as follows:

$sth = $dbh->prepare('select * from db where SN = :SN');
$sth->bindParam(':SN',$value[SN]);
$sth->execute();
while($meta = $sth->fetch(PDO::FETCH_ASSOC))
{
echo $meta["name"];
}

The new thing that appears is fetch, which has similar meaning to mysql_fetch_row(), but in fetch() we find an additional thing called PDO::FETCH_ASSOC.

fetch() provides many ways to obtain data, and PDO::FETCH_ASSOC refers to returning the field name and value of the next data

For example, in the above example, use $meta to obtain the data returned by fetch. At this time, the element name of $meta is the field name of the database, and the content is of course the value itself. This is different from when you use mysql_fetch_row(). Because in addition to the field name, mysql_fetch_row() will also give the element name in addition to the field an element based on a serial number. Doesn't PDO have it?

Of course, as long as PDO::FETCH_ASSOC is changed to PDO::FETCH_BOTH, the usage is no different from mysql_fetch_row().

How to troubleshoot

Debugging is an eternal pain for all programmers. How do we debug using PDO?

In fact, PDO already provides two very convenient functions errorInfo() and errorCode()

Usage is also very simple. Whenever we use execute() to execute, if there is an error, there will be content in errorInfo() and errorCode(). We can do it like this. The code is as follows:

Copy code The code is as follows:

$sth = $dbh->prepare('select * from db where SN = :SN');
$sth->bindParam(':SN',$value[SN]);
$sth->execute();
if ($sth->errorCode())
{
echo "There is an error! There is an error!";
print_r($sth->errorInfo());
}

And $sth->errorInfo() will be an array. This array has three values:

0 is SQLSTATE error code

1 is the error code returned by the Driver you are using

2 Error message returned by the Driver you are using

I hope this article will be helpful to everyone’s PHP programming design.