PHP PDO
From Phpmaniac
Contents |
What is PHP PDO
PHP Data Objects is a object oriented approach to databases and an abstraction layer. PDO was originally a PECL extension but it was moved into the PHP base in version 5.1 providing a standardised way of accessing and manipulating databases.
Why use PDO
Using PDO has a number of advantages:
- Store database connections in an object
- Escape sql depending on the database back end automatically
- Creates portable code
- Verifies SQL syntax before executing the statement
- Verifies variable types (Whether inverted commas are needed or not)
- Standardised database access provides a method for porting your code to multiple database platforms
Connect to the database
Connecting to the database is easy. All we need to do is create a new instance of the PDO object.
PDO ( string $dsn [, string $username [, string $password [, array $driver_options ]]] )
To create a PDO object we need a DSN, and optionally a user name and password.
DSN - Database Source Name
DSN's are way of describing a database connection usually used with abstraction layers like PDO and ODBC. DSN's usually contain:
- The database driver
- The database host
- The database name
Mysql Example
This example connects to a MySQL database, on localhost and using the database called "db".
- $db_conn = new PDO("mysql:host=localhost;dbname=db", $username, $password);
SQLite Example
This example connects to a SQLite database in the same folder as PHP's current working directory. More SQLite examples available here.
- $db_conn = new PDO("sqlite:test_database"););
PostgreSQL Example
This example connects to a PostGreSQL database called "test_database" on localhost using $username, and $password.
- $db_conn = new PDO("pgsql:dbname=test_database;user=" . $username . ";password=" . $password . ";host=localhost");
Your first query
Queries using PDO are best done by connecting to the database, preparing your query, executing it, then fetching the results. This simple query connects to a Mysql server, on localhost, with a database named "db" using $username and $password as username and password respectively.
- <?php
- $db_conn = new PDO("mysql:host=localhost;dbname=db", $username, $password); // Connect to the database
- $query = $db_conn->prepare("select * from users order by last_name"); // Create a prepared statement
- $query->execute(); // Execute the query
- while($row = $query->fetch()) // Loop through the results
- {
- var_dump($row); // Dump the results to output
- }
- ?>
Queries with variables
Even though I could easily string concatenation to create a dynamic where clause in the previous query, we can't do this safely because we would be vulnerable to SQL injection attacks what can leave us losing data, or worse, people running arbitrary queries on your database.
Prepared statements are a feature in some database abstraction layers which increase security by allowing us to create a template SQL query which will be tested against the database to check for syntax errors, and then escape any variables we want to use in the template. This is important because it gives us a way of escaping the SQL based on the PDO driver that we are using without needing to know what the DBMS servers SQL escaping requirements are.
Variables in prepared statements can be defined as a "?" symbol or a text with a prefixed colon (eg ":test").
- <?php
- $db_conn = new PDO("mysql:host=localhost;dbname=db", $username, $password); // Connect to the database
- $query = $db_conn->prepare("select * from users where first_name=:name order by last_name"); // Create a prepared statement with the variable ":name"
- $query->bindParam(":name", $name); // Set the template variable ":name" to the PHP variable $name
- $query->execute(); // Execute the query
- while($row = $query->fetch()) // Loop through the results
- {
- var_dump($row); // Dump the results to output
- }
- ?>
Database Rows
The results from your PDO using the fetch method will be returned as an array containing two versions of data, the first being an indexed array, and the second will be an associative array with the key being the name of the column.
- <?php
- $db_conn = new PDO("mysql:host=localhost;dbname=db", $username, $password); // Connect to the database
- $query = $db_conn->prepare("select first_name, last_name from users where first_name='Leigh'"); // Create a prepared statement
- $query->execute(); // Execute the query
- while($row = $query->fetch()) // Loop through the results
- {
- var_dump($row); // Dump the results to output
- }
- ?>
Returns
array(4) {
[0]=>
string(5) "Leigh"
[1]=>
string(5) "Finch"
["first_name"]=>
string(5) "Leigh"
["last_name"]=>
string(5) "Finch"
}
Associative results
While having both sets of data can be an advantage, it can be a waste of memory and reduce performance when manipulating large result sets. To reduce the memory footprint of a script it can be useful to return only the associative array, this can be done by passing the PDO associative array constant to fetch (PDO::FETCH_ASSOC).
One of the major advantages of having associative results as opposed to indexed results is that your code will be able to handle changes to the database schema without modification so your code will retain data independence.
- <?php
- $db_conn = new PDO("mysql:host=localhost;dbname=db", $username, $password); // Connect to the database
- $query = $db_conn->prepare("select first_name, last_name from users where first_name='Leigh'"); // Create a prepared statement
- $query->execute(); // Execute the query
- while($row = $query->fetch(PDO::FETCH_ASSOC)) // Loop through the results
- {
- var_dump($row); // Dump the results to output
- }
- ?>
Returns
array(2) {
["first_name"]=>
string(5) "Leigh"
["last_name"]=>
string(5) "Finch"
}
del.icio.us
facebook
reddit
stumble it|
|
