return 1;

GSM Bron

A dutch source for mobile phones, which indexes products from other sites.

C#  GD  IRC  Javascript  Python  Xchat  css  gaming  layout  msn  oop  php  site  status  xhtml 

View full tag cloud

Written by Lucas, on Thu 03 January 2008, 23:41:51

Because the fate of the mysql_* functions are uncertain in the next release of PHP, moving to the new data-access api of PHP, PDO, wouldn't be a bad idea.

In my opinion it's a great idea to remove the mysql_* functions and use something more object oriented, and PDO is the perfect solution for that. It supports multiple database engines, although you'll need to correct PDO drivers for each database engine.

In this post I'll explain how to enable PDO on your PHP installation, and how to use it. Click read more for the tutorial

Before we can use it, we need to enable the extension in php.ini, so open that file in your favorite editor and search for the extension part, which you can recognise when you see some lines like this:
extension=php_xxx.dll

At the end of that section add:
extension=php_pdo.dll

But with only pdo you can't do much, you'll need the database drivers for PDO. Almost all databases are supported so pick the one you're using:
extension=php_pdo.dll extension=php_pdo_firebird.dll extension=php_pdo_informix.dll extension=php_pdo_mssql.dll extension=php_pdo_mysql.dll extension=php_pdo_oci.dll extension=php_pdo_oci8.dll extension=php_pdo_odbc.dll extension=php_pdo_pgsql.dll extension=php_pdo_sqlite.dll

Installation on Linux
Make sure you have the following packages installed (These are available in synaptic):
php5-dev
php-pear

You'll need the 'pecl' command. Open up a terminal and enter:


PECL will automaticly download, compile and install the PDO extension. The only thing you need to do is add it to php.ini.

You'll also need a PDO driver, so still in terminal, enter:

for example:

Now add it to php.ini:

extension=pdo.so extension=pdo_mysql.so

And you're finished.

Connecting

Connecting is easy:
try { $conn = new PDO('mysql:host=localhost;dbname=your_db_name;', 'user', 'pass'); } catch(PDOException $e) { die("Could not connect to db: ".$e -> getMessage()); }

the constructor accepts three parameters, one of them is optional. The first is the connection string, it contains the database engine (mysql), the host, and the database name. The second parameter is the database username and the last one the password. Not much special is it?

The connection string may differ if you're using an other database engine.

If you're Database server runs on a different port than the default one, you can add another field to the connection string named 'port':

try { $conn = new PDO('mysql:host=localhost;port=3307;dbname=your_db_name;', 'user', 'pass'); } catch(PDOException $e) { die("Could not connect to db: ".$e -> getMessage()); }

One thing to keep in mind: if it can't connect to the database server, it will throw an Exception, and the default behaviour to handle exceptions is to display a message and a stack trace. And in the stack trance are often parts of the arguments passed with it included. So it will probably reveal your database username and password. It's your responsibility to catch that exception and only display the message.

Executing queries
PDO makes a distinction between queries which do return rows (SELECT etc), and queries which don't return rows (INSERT, UPDATE, DELETE, etc).

For queries which do return rows you use the function query();
$result = $conn -> query("SELECT * FROM table");

The query function will return another object representing the result.

<?php $result = $conn -> query("SELECT * FROM table"); echo "Number of rows: ".$result -> rowCount()."<br />"; while($row = $result -> fetch()) { echo "<pre>", print_r($row), "</pre>"; }
In this piece of code we select every row from table, and echo the number of rows, and then iterate through all the rows, print_r()'ing the row data.

You can also use the foreach statement:

<?php $result = $conn -> query("SELECT * FROM table"); echo "Number of rows: ".$result -> rowCount()."<br />"; foreach($result as $row) { echo "<pre>", print_r($row), "</pre>"; }

To view all functions which are available at the result object view this page, and scroll a bit down for PDOStatement class methods

If you want to execute a query which doesn't return rows, you'll need to use the exec() function. In contrast to the query() function, exec() doesn't return an result object, but instead the number of rows affected by the query.

$affected = $conn -> exec('DELETE FROM table'); echo "Deleted ".$affected." rows!";

Prepared Statements
The most powerful feature of PDO. Prepared statements are a lot safer because the parameters used in queries are escaped automatically. Also it can be used to execute a lot of the same queries, in an easy way.

Here are some examples:
$statement = $conn -> prepare("DELETE FROM users WHERE name = ?"); $statement -> execute(array($_GET['name']));

Notice the '?' in the query. This character is automatically replaced by PDO, including quotes and escaped and all, so you don't need to worry about SQL Injection or something anymore.

$statement = $conn ->prepare("INSERT INTO table (name, age) VALUES (:name, :age)"); $name = ""; $age = 0; $statement -> bindParam(':name', $name); $statement -> bindParam(':age', $age); $name = "Lucas"; $age = 17; $statement -> execute(); $name = "Pete"; $age = 23; $statement -> execute();

This snippet inserts 2 rows, one row with the name 'Lucas', and age 17, and the other row with name 'Pete' and age 23.

As you can see wee can execute the same query multiple times with different values, by just changing the variable.

Conclusion

I hope you learned the basics of using PDO. I would highly recommend using prepared statements, because they're a lot safer, and add extra functionality. Of course PDO offers a lot more, but you can view that in the official documentation located here.

Tags: php oop PDO