Use PDO to Access Just About Any Database from PHP Page 2

How Do I Run Queries with PDO?

Now that we can make connections to databases of different types, how can we run queries? PDO makes this very easy. To demonstrate, let’s start by looking at how to retrieve data from a database using the SELECT statement:

Untitled Document
<!--?php
// declare mysql database server connection details
$Myhostname = 'localhost';
class="code>$Myusername = 'Yourusername';
$password = 'passwrd';
$db = 'mysql';

try {
$handler = new PDO("mysql:host=$host;db="$db, $MyUsername, $MyPasswrd);
//Inform user of connection status
echo 'You are connected to MySQL';

//Define SQL SELECT statement
$getdata = "SELECT * FROM MyTable";
foreach ($handler->query($sql) as $row)
{
echo $row['columname1'] .' - '. $row['columname2'] . '
';
}

// close the database connection
$handler = null;
}
catch(PDOException $ex)
{
echo $ex->getMessage();
}
?>

For those of you who have used PHP to connect to MySQL, the above statements will be very familiar. You will also note that foreach() is used to iterate through the array of records, which is different from the while() loop that is commonly used when accessing arrays using MySQL. This is mainly because PDO uses the Standard PHP Library traversable iterator internally.

Next we look at how to UPDATE a record:

Untitled Document

<!--?php

// declare mysql database server connection details

$Myhostname = 'localhost';

$Myusername = 'Yourusername';

$password = 'passwrd';

$db = 'mysql';

try {

$handler = new PDO("mysql:host=$host;db="$db, $MyUsername, $MyPasswrd);

//Inform user of connection status

echo 'You are connected to MySQL';


//run UPDATE statement

$affected_rows = $handler->exec("UPDATE MyTable SET columnname1='Avalue1' WHERE columname1='Avalue2'");


//show user number of records changed

echo $ affected_rows;


//close the database connection

$handler = null;

}
catch(PDOException $ex)

{
echo $ex->getMessage();

}
?>

Notice that this time we’ve used PDO::exec(). It should be used for queries that do not expect a result set to be returned. To insert a record, simply change:

$affected_rows = $handler->exec("UPDATE MyTable SET columnname1='Avalue1' WHERE columname1='Avalue2'");

To:

$ affected_rows = $handler->exec("INSERT INTO MyTable(columname1, columname2) VALUES ('AValue1', 'AValue2')");

Conclusion

In conclusion, just two of the reasons why you should use PDO in your applications include the following:

• It supports a greater number of database systems that are supported by PHP.
• You do not need to write a lot of code to connect to any database, simply write it once and use with any database.

PDO is also the best choice when it comes to writing portable database applications, precisely because of its database abstraction.