Basic PDO tutorial entry level in PHP

PDO (PHPDataObject) is new to PHP5. in PHP6, only PDO is used to process databases by default. we only need to keep pace with the times to learn about PDO. This is only an entry-level basic tutorial. PDO is a major new feature of PHP 5, because php4/php3 before PHP 5 was a bunch of database extensions to connect to and process various databases, php_mysql.dll, php_pgsql.dll, php_mssql.dll, php_sqlite.dll, and other extensions to connect to MySQL, PostgreSQL, ms SQL Server, and SQLite. Similarly, we must use ADOdb, PEAR: DB, PHPlib :: database abstract classes such as DB are very cumbersome and inefficient to help us. after all, how can we directly use C/C ++ to write php code with a high scaling slope? Therefore, the emergence of PDO is inevitable. you should accept it with a calm learning attitude. maybe you will find it can reduce your efforts.

Install PDO
I am on Windows XP SP2, so the whole process is performed on Windows. for Linux/FreeBSD and other platforms, please find the information to set and install.
My PHP version is 5.1.4 and I already have the php_pdo.dll extension, but I need to set it up a little before it can be used.
Open c: \ windows \ php. ini, which is my PHP configuration file. find the following line:
Extension_dir

This is the directory of our extension. my PHP 5 extension is in: C: \ php5 \ ext, so I will change this line:
Copy codeThe code is as follows:
Extension_dir = "C:/php5/ext"

Then find the following link under php. ini:
Copy codeThe code is as follows:

; Dynamic Extensions;


Below are a bunch of things similar to extension = php_mbstring.dll. here is the PHP extension loading configuration. we will add our PDO extension at the end:
Extension = php_pdo.dll
Extension = php_pdo_mysql.dll
Extension = php_pdo_pgsql.dll
Extension = php_pdo_sqlite.dll
Extension = php_pdo_mssql.dll
Extension = php_pdo_odbc.dll
Extension = php_pdo_firebird.dll
; Extension = php_pdo_oci8.dll

All kinds of PDO drivers can be added, but the php_pdo_oci8.dll in the future will be commented out using semicolons because I have not installed the Oralce database. Then restart our Web server, IIS/Apache, and I use IIS. hey, the table despise me. it's easy on Windows. After the restart, write a phpinfo. php file under the document directory of our Web server, and add the following:
Copy codeThe code is as follows:
Phpinfo ();
?>

In the output content, if you can see smoothly:
PDO
PDO support enabled
PDO drivers mysql, pgsql, sqlite, mssql, odbc, firebird
The following describes various drivers: PDO_Firebird, pdo_mssql, pdo_mysql, PDO_ODBC, pdo_pgsql, and pdo_sqlite. Congratulations, you have successfully installed it. otherwise, please carefully check the above steps.

PDO tutorial

I use MySQL 4.0.26, but I personally recommend that you use MySQL 4.1.x or MySQL 5.0.x because there are many interesting things worth learning. Here, we need to connect to my MySQL 4.0. if you have not installed MySQL, install it on your own. We have created MySQL and added table foo in the test Library, including four fields: id, name, gender, and time.

We started to construct the first PDO application and create a pdo. php file under the Web document directory:
Copy codeThe code is as follows:
$ Dsn = "mysql: host = localhost; dbname = test ";
$ Db = new PDO ($ dsn, 'root ','');
$ Count = $ db-> exec ("insert into foo SET name = 'heiyeluren', gender = 'male', time = NOW ()");
Echo $ count;
$ Db = null;
?>

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

It is to construct our DSN (data source). let's see the following information: the database type is mysql, the host address is localhost, and the database name is test. The data source construction methods for 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 parameter is the user who connects 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 will consider it successful.
$ Count = $ db-> exec ("insert into foo SET name = 'heiyeluren', gender = 'male', time = NOW ()");
Echo $ count;

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

By default, this is not a persistent connection. if you need a persistent connection to the database, you must add the following parameter: array (PDO: ATTR_PERSISTENT => true:
$ Db = new PDO ($ dsn, 'root', '', array (PDO: ATTR_PERSISTENT => true ));

One operation is so simple. it may not be much different from the previous one. 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 is all the connected objects above)
Copy codeThe code is as follows:
Foreach ($ db-> query ("SELECT * FROM foo ")){
Print_r ($ row );
}
?>

We can also use this method:
Copy codeThe code is as follows:
$ Rs = $ db-> query ("SELECT * FROM foo ");
While ($ row = $ rs-> fetch ()){
Print_r ($ row );
}
?>

If you want to get all the data to the array at one time, you can do this:
Copy codeThe code is as follows:
$ Rs = $ db-> query ("SELECT * FROM foo ");
$ Result_arr = $ rs-> fetchAll ();
Print_r ($ result_arr );
?>

The output result is as follows:
Copy codeThe code is as follows:
Array
([0] => Array (
[Id] => 1
[0] => 1
[Name] => heiyeluren
[1] => heiyeluren
[Gender] => male
[2] => male
[Time] => 23:14:23
[3] => 23:14:23
)
}

We can see that the record, the number index and the associated index both exist, which is a waste of resources. we only need to associate the index:
Copy codeThe code is as follows:
$ Db-> setAttribute (PDO: ATTR_CASE, PDO: CASE_UPPER );
$ Rs = $ db-> query ("SELECT * FROM foo ");
$ Rs-> setFetchMode (PDO: FETCH_ASSOC );
$ Result_arr = $ rs-> fetchAll ();
Print_r ($ result_arr );
?>

According to the code above, the setAttribute () method is to set some attributes. the main attributes include PDO: ATTR_CASE, PDO: ATTR_ERRMODE, etc. here we need to set PDO: ATTR_CASE, when we use the associated index to retrieve a dataset, the associated index is in upper or lower case. There are several options:

PDO: CASE_LOWER -- force column name to be lowercase
PDO: CASE_NATURAL -- column names follow the original method
PDO: CASE_UPPER -- force column name to uppercase

We use the setFetchMode method to set the type of the return value for the result set. the same types include:

PDO: FETCH_ASSOC -- join array form
PDO: FETCH_NUM -- numeric index array format
PDO: FETCH_BOTH -- both arrays are available, which is the default
PDO: FETCH_OBJ -- according to the object form, similar to the previous mysql_fetch_object ()

Of course, we generally use PDO: FETCH_ASSOC. For more information about the usage, see the reference manual for other obtaining types.

In addition to the above method of getting data, there are also:
Copy codeThe code is as follows:
$ Rs = $ db-> prepare ("SELECT * FROM foo ");
$ Rs-> execute ();
While ($ row = $ rs-> fetch ()){
Print_r ($ row );
}
?>

Actually, it's almost the same. If you want to obtain the results of a field in a specified record, you can use PDOStatement: fetchColumn ():
Copy codeThe code is as follows:
$ Rs = $ db-> query ("select count (*) FROM foo ");
$ Col = $ rs-> fetchColumn ();
Echo $ col;
?>

FetchColumn () is generally used for count statistics or some records that only need a single field are well-performed.

Summary
Query operations are mainly PDO: query (), PDO: exec (), PDO: prepare (). PDO: query () is mainly used for operations that return record results, especially SELECT operations. PDO: exec () is mainly used for operations that return no result set, for example, INSERT, UPDATE, DELETE, and other operations. the result returned by this operation is the number of columns affected by the current operation. PDO: prepare () is mainly used for preprocessing. you need to use $ rs-> execute () to execute the SQL statements in the preprocessing. this method can bind parameters and has powerful functions, this document is not a simple description. you can refer to the manual and other documents. To obtain a result set, perform the following operations: PDOStatement: fetchColumn (), PDOStatement: fetch (), and PDOStatement: fetchALL (). PDOStatement: fetchColumn () is a field of the first record specified in the result. the default value is the first field. PDOStatement: fetch () is used to obtain a record. PDOStatement: fetchAll () is used to obtain all the record sets to one. The obtained results can be obtained through PDOStatement :: setFetchMode to set the type of the desired result set.

There are also two peripheral operations: PDO: lastInsertId () and PDOStatement: rowCount (). PDO: lastInsertId () is the last insert operation, and the primary key column type is the last auto-increment ID of auto-increment. PDOStatement: rowCount () is the result set affected by the DELETE, INSERT, and UPDATE operations on PDO: query () and PDO: prepare :: the exec () method and SELECT operation are invalid.

From the above usage, we can see that the PDO function is really powerful, and there are some other features that I have not mentioned, such as binding parameters, preprocessing, stored procedures, transaction processing, and so on. In addition, there are also different data expansion DSN structures. Oracle databases have many special things that need to be learned and understood in depth. This article is just a simple description of some basic knowledge, A simple understanding of PDO.