This tutorial expands upon our previous PHP Data Objects tutorial, which discusses how to connect to a MySQL database, and showcases how to communicate with that MySQL database once you are connected. Depending on the type of query you want to run you’ll be issuing statements in two different ways:
For example let’s say you need to run an SQL query to update a username within your database. This is a statement that produces no result set, and as such you would pass your SQL statement to the database handle exec() method, which will execute the statement and return the affected-row count:
$update = $dbh->exec("UPDATE users SET username = NewUsername WHERE id = 1");
If you have an SQL statement that selects rows and produces a result set you’ll want to utilize the query() method. For this example we will simply select all of the records in our users database table.
$result = $dbh->query("SELECT * FROM users");
A successful query() call returns a PDOStatement statement-handle object that is used for all operations on the result set. Some of the information available from a PDOStatement object includes the row contents and the number of columns in the result set.
We can utilize the fetch() method to return each row in our result set, in succession. If the result set is empty the fetch() method will return FALSE. In-order to get the total number of columns in our result set we can use the columnCount() method. Our next PHP Data Objects tutorial will go into detail about how the various methods available to fetch result set rows.