PHP Data Object is a Database Connection Abstraction Library for PHP 5.
PDO Introduction
- a Lightweight DBMS connection abstract library (data access abstraction library)
- PDO can use exceptions to handle errors, which means anything you do with PDO should be wrapped in a try/catch block.
- PDO is a database access layer providing a uniform method of access to multiple databases.
- PHP5 is written in compiled language like C & C++
Database Support
Activation PHP Data Objects Extension
To use PDO, check whether PDO extension exist or not. Try to open your php extension folder. For example, in my computer, the directory: app/php5/ext.
Then, open your php.ini. Usually within c:/windows (depend on your php installation). Uncomment at line extension=php_pdo.dll, extension=php_pdo_mysql.dll. If still not exist, write them.
Restart your apache. You can restart from services. If, you use windows XP, you can access from start > control panel > Performance and Maintenance > Administrative Tools > Services. Find apache, then click restart.
Now, we test to connect to database. We use mysql server. Before test, please create a database named “test”. Then create table “books” with query like this:
CREATE TABLE `books` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(150) NOT NULL,
`author` varchar(150) NOT NULL,
`description` varchar(255) NOT NULL,
`on_sale` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
);
Following query for sample data:
INSERT INTO `books` (`id`, `title`, `author`, `description`, `on_sale`) VALUES (1, 'PHP AJAX', 'Andreas', 'This is good book for learning AJAX', 1);
INSERT INTO `books` (`id`, `title`, `author`, `description`, `on_sale`) VALUES (2, 'PHP Eclipse ', 'George', 'Nice book', 0);
INSERT INTO `books` (`id`, `title`, `author`, `description`, `on_sale`) VALUES (3, 'PHP Prado', 'Junyian', '-', 1);
INSERT INTO `books` (`id`, `title`, `author`, `description`, `on_sale`) VALUES (4, 'PHP Zend Framework', 'Ozulian', 'great', 0);
INSERT INTO `books` (`id`, `title`, `author`, `description`, `on_sale`) VALUES (5, 'PHP Web Services', 'Bobi', '', 0);
INSERT INTO `books` (`id`, `title`, `author`, `description`, `on_sale`) VALUES (6, 'PHP API', 'Hugo', '', 1);
INSERT INTO `books` (`id`, `title`, `author`, `description`, `on_sale`) VALUES (7, 'PHP SEO', 'Monteo', '', 1);
Now, this is sample connection to mysql database:
<?php
$host = "localhost";
$db = "test";
$user = "root";
$pass = "admin";
$conn = new PDO("mysql:host=$host;dbname=$db",$user,$pass);
$sql = "SELECT * FROM books";
$q = $conn->query($sql) or die("failed!");
while($r = $q->fetch(PDO::FETCH_ASSOC)){
echo $r['title'];
}
?>
Prepared Statement
PHP Extension for MySQL and SQLite don’t offer this functionality. Ok, I will show a sample. I believe, from that sample you will understand what is prepare statement.
A prepared statement is a precompiled SQL statement that can be executed multiple times by sending just the data to the server.
It has the added advantage of automatically making the data used in the placeholders safe from SQL injection attacks.
You use a prepared statement by including placeholders in your SQL.
<?php
// configuration
$dbtype = "sqlite";
$dbhost = "localhost";
$dbname = "test";
$dbuser = "root";
$dbpass = "admin";
// database connection
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
$title = 'PHP AJAX';
// query
$sql = "SELECT * FROM books WHERE title = ?";
$q = $conn->prepare($sql);
$q->execute(array($title));
$q->setFetchMode(PDO::FETCH_BOTH);
// fetch
while($r = $q->fetch()){
print_r($r);
}
?>
in this simple example, query depends on a variabel (we write with ?).
$sql = "SELECT * FROM books WHERE title = ?";
Now, we manipulate this query to create the prepared statement and execute it:
$q = $conn->prepare($sql);
$q->execute(array($title))
Another sample:
$title = 'PHP%';
$author = 'Bobi%';
// query
$sql = "SELECT * FROM books WHERE title like ? AND author like ? ";
$q = $conn->prepare($sql);
$q->execute(array($title,$author));
Positional and Named Placeholders
Positional Placeholders
$title = 'PHP%';
$author = 'Bobi%';
// query
$sql = "SELECT * FROM books WHERE title like ? AND author like ? ";
$q = $conn->prepare($sql);
$q->execute(array($title,$author));
The query above used question marks to designate the position of values in the prepared statement. These question marks are called positional placeholders.
We must take care of proper order of the elements in the array that we are passing to the PDOStatement::execute() method.
Named Placeholders
$title = 'PHP%';
$author = 'Bobi%';
// query
$sql = "SELECT * FROM books WHERE title like :title AND author like :author ";
$q = $conn->prepare($sql);
$q->execute(array(':author'=>$author,
':title'=>$title));
This use descriptive names preceded by a colon, instead of question marks. We don't care about position/order of value. That's why it called named placeholders.
Insert and Update Statement Use Prepared Statement
Example for Insert
<?php
// configuration
$dbtype = "sqlite";
$dbhost = "localhost";
$dbname = "test";
$dbuser = "root";
$dbpass = "admin";
// database connection
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
// new data
$title = 'PHP Security';
$author = 'Jack Hijack';
// query
$sql = "INSERT INTO books (title,author) VALUES (:title,:author)";
$q = $conn->prepare($sql);
$q->execute(array(':author'=>$author,
':title'=>$title));
?>
Example for Update
<?php
// configuration
$dbtype = "sqlite";
$dbhost = "localhost";
$dbname = "test";
$dbuser = "root";
$dbpass = "admin";
// database connection
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
// new data
$title = 'PHP Pattern';
$author = 'Imanda';
$id = 3;
// query
$sql = "UPDATE books
SET title=?, author=?
WHERE id=?";
$q = $conn->prepare($sql);
$q->execute(array($title,$author,$id));
?>
Selecting Data
- PDO::FETCH_ASSOC: returns an array indexed by column name
- PDO::FETCH_BOTH (default): returns an array indexed by both column name and number
- PDO::FETCH_BOUND: Assigns the values of your columns to the variables set with the ->bindColumn() method
- PDO::FETCH_CLASS: Assigns the values of your columns to properties of the named class. It will create the properties if matching properties do not exist
- PDO::FETCH_INTO: Updates an existing instance of the named class
- PDO::FETCH_LAZY: Combines PDO::FETCH_BOTH/PDO::FETCH_OBJ, creating the object variable names as they are used
- PDO::FETCH_NUM: returns an array indexed by column number
- PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the column names
I think this post most helpful one for your PDO !!!
All the best!!!
We initialize this project (Actually it is a class) because of we want to develop web report in php easily (The output of report is PDF/XLS because it platform indepent and printer friendly). This project allow php programmer or report designer design php web report easily. Even very junior PHP programmer able to design the PDF/EXCEL report with iReport (java based WYSIWYG report designer), but run natively in PHP. This project is completely difference from php-java bridge, a wrapper class will convered report element from jrxml and pass to tcpdf class.
Code Mustangs 