Using PDO Objects in PHP 5 - Running queries against a specific

Naturally, after learning how to open and close a connection to a specific database system, I'm sure that you'll want to see how to execute a query against a particular database. To perform this crucial task, the PDO extension comes equipped with two primary methods, not surprisingly called "query()" and "exec()" respectively.

However, the two methods behave slightly differently. The second one, that is "exec()," not only performs a specified query, but returns the number of affected rows after performing an "INSERT," "UPDATE" or "DELETE" command.

Now that you see the difference between both methods, please study the following examples. They demonstrate separately how to use each of the methods in question, first for connecting to MySQL, and then for fetching some rows from a sample "USERS" database.

The respective code listings are as follows:

// example using the 'query()' method
try{
            $dbh=new PDO('mysql:host=localhost;dbname=alejandro','user','password');
            foreach($dbh->query('SELECT * FROM users') as $row){
                        echo 'Name :'.$row['name'].' Postal Address :'.$row['address'].' Email :'.$row['email'].'<br />';
            }
} 
catch(PDOException $e){
            echo 'Error : '.$e->getMessage();
            exit();
}

// example using the 'exec()' method (returns the number of rows
affected by the query)
try{
            $dbh=new PDO('mysql:host=localhost;dbname=alejandro','user','password');
            $delrows=$dbh->exec('DELETE FROM users WHERE id<20');
            echo 'Number of deleted rows is the
following :'.$delrows;
} 
catch(PDOException $e){
            echo 'Error : '.$e->getMessage();
            exit();
}

 As shown previously, the first example illustrates the implementation of the handy "query()" method to fetch some rows from a simple database table, while the second case shows how to use "exec()" to run a "DELETE" statement, and finally display the number of deleted rows.

It's worth clarifying here that all of the above examples use a MySQL server, but as you learned in the previous section, the same methods can be utilized with Oracle, MS SQL, SQLite or whatever database system you need to include in your own PHP applications.

So far, so good. At this stage you have hopefully learned how to run queries with the PDO library utilizing the two primary methods bundled with the extension. So what's the next step? Well, in the last section of this tutorial I'm going to show you how to use a brand new method, called "lastInsertId()," which obviously returns the ID of the last-inserted database row.

To learn how this method can be implemented, go ahead and read the new few lines. I'll be there, waiting for you.