Basic PDO tutorials in PHP entry-level

PDO is a new feature added to PHP 5, because before PHP 5, php4/php3 were a bunch of database extensions to connect and process each database. What are php_mysql.dll, php_pgsql.dll, php_mssql.dll, php_mssql.dll, php_sqlite.dll and other extensions to connect MySQL, PostgreSQL, MS SQL Server, SQLite. Similarly, we must use database abstract classes such as ADOdb, PEAR::DB, PHPlib::DB to help us, which is extremely cumbersome and inefficient. After all, how can we directly use C/C++ to write high-slope extension slope? So, the emergence of PDO is inevitable. Everyone should accept the use with a calm mind, and maybe you will find that it can reduce your efforts.

Install PDO
I am on Windows XP SP2, so the whole process is carried out in the Windows line. As for platforms such as Linux/FreeBSD, please find the information and set it up and install it yourself.
Mine is PHP 5.1.4, and it already comes with the extension of php_pdo.dll, but it needs to be set up a little to use.
Open c:\windows\ , that is my PHP configuration file, find the following line:
extension_dir

This is the directory where our extension exists. My PHP 5 extension is: C:\php5\ext, so I changed this line to:

Then find below:

All PDO drivers can add all the additional ones, but the following php_pdo_oci8.dll, because I don't have the Oralce database installed, I don't have this, so I use a semicolon to comment it out. Then restart our web server, IIS/Apache, mine is IIS, hehe, I despise me, on Windows, it's simple. After restarting, write a file in the document directory of our web server, and add these:

If you can see the output content smoothly:
PDO
PDO support enabled
PDO drivers mysql, pgsql, sqlite, mssql, odbc, firebird
There are instructions for various drivers later: PDO_Firebird, pdo_mssql, pdo_mysql, PDO_ODBC, pdo_pgsql, pdo_sqlite. So, congratulations on the installation successful, otherwise please check the above steps carefully.

PDO Tutorial

I'm using MySQL 4.0.26, but I personally recommend that you use MySQL 4. or MySQL 5., because those versions have a lot of interesting things worth learning. What we need to connect to here is my MySQL 4.0. If you do not install MySQL, please install it yourself. We have established MySQL and added table foo in the test library, including four fields such as id, name, gender, time, etc.

We started to construct the first PDO application and create a file in the web document directory:

$dsn = "mysql:host=localhost;dbname=test";

It is to construct our DSN (data source) and see the information in it: the database type is mysql, the host address is localhost, the database name is test, and there are only a few information. The data source construction methods of different databases are different.
$db = new PDO($dsn, 'root', '');

Initialize a PDO object. The first parameter of the constructor is our data source, the second is the user connecting to the database server, and the third parameter is the password. We cannot guarantee that the connection is successful. We will talk about exceptions later. Here we think that it is successful.
$count = $db->exec("INSERT INTO foo SET name = 'heiyeluren',gender='male',time=NOW()");
echo $count;

Calling the PDO object we connected successfully to execute a query. This query is an operation to insert a record. Using the PDO::exec() method will return a result that affects the record, so we output this result. Finally, you still need to end the object resource:
$db = null;

By default, this is not a long connection. If a database connection is required, you need to add a parameter in the end: array(PDO::ATTR_PERSISTENT => true) becomes like this:
$db = new PDO($dsn, 'root', '', array(PDO::ATTR_PERSISTENT => true));

The operation is that simple at one time. Maybe it is not much different from the previous ones, but it is somewhat similar to ADOdb.

PDO Advanced Tutorial

If we want to extract data, we should use the data acquisition function. (The $db used below are all objects that have been connected above)

We can also use this method of obtaining:

If you want to get all the data into an array at once, you can do this:

The output results are as follows:

Let’s look at the records inside, both numeric index and associated index are all, which wastes resources. We only need the associated index:

Looking at the above code, the setAttribute() method is to set some attributes, the main attributes are: PDO::ATTR_CASE, PDO::ATTR_ERRMODE, etc. What we need to set here is PDO::ATTR_CASE, that is, when we use the associative index to obtain the data set, there are several choices:

PDO::CASE_LOWER -- Force column name is lowercase
PDO::CASE_NATURAL -- Column name is original
PDO::CASE_UPPER -- Force column name to uppercase

We use the setFetchMode method to set the type of the return value of the result set, and the same type is:

PDO::FETCH_ASSOC -- Associative array form
PDO::FETCH_NUM -- Digital index array form
PDO::FETCH_BOTH -- Both array forms are available, which is the default
PDO::FETCH_OBJ -- In the form of an object, similar to the previous mysql_fetch_object()

Of course, in general, we use PDO::FETCH_ASSOC. What to use is based on your own needs, and other reference manuals for obtaining types.

In addition to the above method of obtaining data, there are also this:

Actually it's almost the same. If you want to get a field result in a specified record, you can use PDOStatement::fetchColumn():

Generally, fetchColumn() is used to perform count statistics or some records that only require a single field.

A simple summary
The query operations are mainly PDO::query(), PDO::exec(), and PDO::prepare(). PDO::query() is mainly used for operations that record results return, especially SELECT operations. PDO::exec() is mainly used for operations that do not return a result set, such as INSERT, UPDATE, DELETE and other operations. The result it returns is the number of columns affected by the current operation. PDO::prepare() is mainly a preprocessing operation. It requires $rs->execute() to execute SQL statements in the preprocessing. This method can bind parameters and has a relatively powerful function. It is not something that can be explained simply in this article. You can refer to the manual and other documents. The main operations to obtain the result set are: PDOStatement::fetchColumn(), PDOStatement::fetch(), PDOStatement::fetchALL(). PDOStatement::fetchColumn() is a field that obtains the result and specifies the first record. The default is the first field. PDOStatement::fetch() is used to obtain a record, PDOStatement::fetchAll() is used to obtain all records into one. To obtain the result, you can set the type of result set by PDOStatement::setFetchMode.

There are two peripheral operations, one is PDO::lastInsertId() and PDOStatement::rowCount(). PDO::lastInsertId() returns the last insertion operation, and the primary key column type is the last self-increment ID of the self-increment. PDOStatement::rowCount() is mainly used for the result set of DELETE, INSERT, and UPDATE operations for PDO::query() and PDO::prepare(), and is invalid for PDO::exec() method and SELECT operations.

From the above usage, it can be seen that PDO functions are indeed powerful, and there are some other contents I have not mentioned, such as binding parameters, preprocessing, stored procedures, transaction processing and other functions. There are also different data expansion DSN structures. Oracle database itself needs to learn and understand many special things in depth. This article simply describes some introductory knowledge, which is a simple understanding of PDO.