Created: 24 Mar 11
Database - Basic usage of PDO
Working with database (MySQL in this case)
What are the alternatives:
- Working directly with basic functions like "mysql_connect", "mysql_query", etc. It's good, but very old fashioned, not secure, and not cross platform. Once you want to change the database server from mysql to another DB, it's involves many code changes.
- Working with frameworks, like ZendFramework, Codigniter, etc - some times it requires integrations, and not in all cases you can uses the database object solely.
- Writing modules by yourself - You have to manage many issues - security, integrations and unnecessary development time.
PDO - What is that?
Pdo (PHP Data Object) - Built in feature in PHP (requires module, but mostly it's already in). PDO is a class providing basic function for working with databases. It's secure (using "prepare" function), it's cross platform (databases platforms) and it's convenient. Many common frameworks use it, and supply the same function as PDO (class extension).
Tutorial
Let's start with the database connection.
The connection requires DSN, i.e. connection string with the basic required configuration. Connection to mysql database looks like:
$dsn = 'mysql:host=localhost; dbname=my_database_name; charset=utf-8';
try {
$db = new PDO($dsn, 'username', 'password');
} catch (PDOException $e) {
die($e->getMessage());
}
in most cases you shoud run the "set names" mysql function which looks like:
$db->exec('set names "utf8");
what brings us to the basic "exec" method of PDO class. It looks like
$db->exec($SQL);
The function usually does not return a value, and used for queries like "INSERT" or "UPDATE".
for simple queries, like 'select * from ...' you can use in the next way:
foreach ($db->query('select * from books') as $book) {
echo $book['title'];
}
for passing variables to the PDO query, you can use the "prepare" method, where the parameters are being represented with placeholders, and than you "bind" values to those placeholders. It's made this way:
// define statement var.
$stmt->$db->prepare('select * from `books` where `title` = :title);
// bind value to the :title placeholder, and define it as a string
$stmt->bindValue(':title', $title, PDO::PARAM_STR);
// execute the query and get the results
$stmt->execute();
foreach ($stmt->fetchAll() as $row) {
echo $row['author'];
}
Those are the very basic methods which are mostly used. There are many more configuration options, and other useful methods. You can find them at the php.net site PDO tutorials.
Hope it is useful,
Enjoy,
online-php.com Administration