KB Knowledge Base
How To Do A PDO Query

Article By salem


img

pdo -PHP Data Object

Introduction to PDO

Now that we can open and close a connection to the database with PDO, we can make use of it for what databases are made for, storing and retrieving information. The simplest form of query is the PDO query method. As the name suggests, this is used to perform database queries. Before we begin to query a database, lets create a small database with a table for animals. This will be a MySQL database for use throughout much of this tutorial. Remember, because PDO provides a common set of tools for databases, once we have the correct connection, the rest of the code is the same, regardless of the database you choose. When using PDO to query a database, the function used to do so depends on the statement you wish to send to the database. Below we will see three queries on how to INSERT, SELECT and UPDATE.


INSERT

To gather information from a database, we first need to put some info into it. We use the same code from above to connect and disconnect from the database and the INSERT query is accomplished using the PDO::exec method.

';
 /*** INSERT data ***/
 $count = $dbh->exec("INSERT INTO animals(animal_type, animal_name) VALUES ('kiwi', 'troy')");
 /*** echo the number of affected rows ***/
 echo $count;
 /*** close the database connection ***/
 $dbh = null;
 }
 catch(PDOException $e)
 {
 echo $e->getMessage();
 }
 ?>

The output of the script above will look like this: Connected to database 1 This shows us that we connected successfully to the database and then we have displayed the number of affected rows. PDO::exec returns the number of affected rows if successful, or zero (0) if no rows are affected. This may cause issues if you are checking for a Boolean value and why it is recommended using === when to check for type also, as zero (0) may evaluate to Boolean FALSE. The PDO::exec method should be used for SQL statements that do not return a result set. We could use this same method to INSERT many more animals to our database, but a more efficient method would be to use a transaction. This is covered in the section on Transactions.

 

SELECT

Unlike PDO::exec the PDO::query method returns a result set, that is, a group of information from the database in the form of a PDOStatement object. Our database should look a little like the example in the What is PDO section. Using this we can SELECT information.

';
 /*** The SQL SELECT statement ***/
 $sql = "SELECT * FROM animals";
 foreach ($dbh->query($sql) as $row)
 {
 print $row['animal_type'] .' - '. $row['animal_name'] . '
'; } /*** close the database connection ***/ $dbh = null; } catch(PDOException $e) { echo $e->getMessage(); } ?>

From the script above, we can expect the results to look like this:

Connected to database
emu - bruce
funnel web - bruce
lizard - bruce
dingo - bruce
kangaroo - bruce
wallaby - bruce
wombat - bruce
koala - bruce
kiwi - troy

You will have noticed that we can iterate over the result set directly with foreach. This is because internally the PDO statement implements the SPL traversble iterator, thus giving all the benifits of using SPL. For more on SPL refer to the Introduction to SPL page. The greatest benifit of this is that SPL iterators know only one element at a time and thus large result sets become manageable without hogging memory.

 

UPDATE

To update a field in a database with PDO we once again use the PDO::exec method in the same manner as we did with the INSERT

';
 /*** INSERT data ***/
 $count = $dbh->exec("UPDATE animals SET animal_name='bruce' WHERE animal_name='troy'");
 /*** echo the number of affected rows ***/
 echo $count;
 /*** close the database connection ***/
 $dbh = null;
 }
 catch(PDOException $e)
 {
 echo $e->getMessage();
 }
 ?>

Once again we see that the connection is made to the database and one row is affected as now the kiwi has become a true Australian like the rest of the creatures. PDO::exec should be used for all database queries where no result set is required.

 

FETCH Modes

The section above showed how using PDO::query we can fetch information from the database. The PDO::query method returns a PDOStatement object that can be utilized in much the same was as mysql_fetch_object() or pg_fetch_object(). Of course there are times when an numerical index is needed or an associative index. PDO::query provides for this also by allowing the coder to set the fetch mode for via the PDOStatement object or via PDOStatement::setFetchMode().

 

FETCH ASSOC

To fetch an associative array from our results the constant PDO::FETCH_ASSOC is used and returns the column names as indexes or keys of the resulting array.

';
 /*** The SQL SELECT statement ***/
 $sql = "SELECT * FROM animals";
 /*** fetch into an PDOStatement object ***/
 $stmt = $dbh->query($sql);
 /*** echo number of columns ***/
 $result = $stmt->fetch(PDO::FETCH_ASSOC);
 /*** loop over the object directly ***/
 foreach($result as $key=>$val)
 {
 echo $key.' - '.$val.'
'; } /*** close the database connection ***/ $dbh = null; } catch(PDOException $e) { echo $e->getMessage(); } ?>
The above code will give a result like this:
Connected to database

animal_id - 1
animal_type - emu
animal_name - bruce
PDO has returned the results as a PDOStatement object that we can iterate over directly. The resulting indexes are the names of the fields within the animals database.  

FETCH NUM

Like PDO::FETCH_ASSOC, the PDO::FETCH_NUM produces a numerical index of the result set rather than the field names.
';
 /*** The SQL SELECT statement ***/
 $sql = "SELECT * FROM animals";
 /*** fetch into an PDOStatement object ***/
 $stmt = $dbh->query($sql);
 /*** echo number of columns ***/
 $result = $stmt->fetch(PDO::FETCH_NUM);
 /*** loop over the object directly ***/
 foreach($result as $key=>$val)
 {
 echo $key.' - '.$val.'
'; } /*** close the database connection ***/ $dbh = null; } catch(PDOException $e) { echo $e->getMessage(); } ?>
The above code will give a result like this:
Connected to database

0 - 1
1 - emu
2 - bruce
As you can see above the indexes are now numeric in the result set  

FETCH BOTH

There may be times you need to fetch both numerical and associative indexes. PDO::FETCH_BOTH produces a numerical and associative index of the result set so you can use either, or both.


/*** mysql username ***/
 $username = 'username';
/*** mysql password ***/
 $password = 'password';
 try {
 $dbh = new PDO("mysql:host=$hostname;dbname=animals", $username, $password);
 /*** echo a message saying we have connected ***/
 echo 'Connected to database
'; /*** The SQL SELECT statement ***/ $sql = "SELECT * FROM animals"; /*** fetch into an PDOStatement object ***/ $stmt = $dbh->query($sql); /*** echo number of columns ***/ $result = $stmt->fetch(PDO::FETCH_BOTH); /*** loop over the object directly ***/ foreach($result as $key=>$val) { echo $key.' - '.$val.'
'; } /*** close the database connection ***/ $dbh = null; } catch(PDOException $e) { echo $e->getMessage(); } ?>
Now we see the results have included both indexes.
Connected to database
animal_id - 1
0 - 1
animal_type - emu
1 - emu
animal_name - bruce
2 - bruce

FETCH OBJECT

This little gem takes the result set and returns it as an anonymous object or stdClass and maps the field names from the database as object properties with the values the values of stored in the database.
';
 /*** The SQL SELECT statement ***/
 $sql = "SELECT * FROM animals";
 /*** fetch into an PDOStatement object ***/
 $stmt = $dbh->query($sql);
 /*** echo number of columns ***/
 $obj = $stmt->fetch(PDO::FETCH_OBJ);
 /*** loop over the object directly ***/
 echo $obj->animal_id.'
'; echo $obj->animal_type.'
'; echo $obj->animal_name; /*** close the database connection ***/ $dbh = null; } catch(PDOException $e) { echo $e->getMessage(); } ?>
The above code gives the results like this:
Connected to database
1
emu
bruce

The use of the field names as class properties makes integrating results into an Object Oriented envioronment simple.  

FETCH LAZY

PDO::FETCH_LAZY is odd as it combines PDO::FETCH_BOTH and PDO::FETCH_OBJ. I am unsure why you would want to do this, but it must have been important enough for somebody to create it. The code below is that of PDO::FETCH_BOTH and is reproduced here for examples sake.
';
 /*** The SQL SELECT statement ***/
 $sql = "SELECT * FROM animals";
 /*** fetch into an PDOStatement object ***/
 $stmt = $dbh->query($sql);
 /*** echo number of columns ***/
 $result = $stmt->fetch(PDO::FETCH_BOTH);
 /*** loop over the object directly ***/
 foreach($result as $key=>$val)
 {
 echo $key.' - '.$val.'
'; } /*** close the database connection ***/ $dbh = null; } catch(PDOException $e) { echo $e->getMessage(); } ?>
The above code will give a result the same as that of PDO::FETCH_BOTH. Genius!  

FETCH CLASS

PDO::FETCH_CLASS instantiates a new instance of the specified class. The field names are mapped to properties (variables) within the class called. This saves quite a bit of code and speed is enhanced as the mappings are dealt with internally.
animal_type);
 }
 } /*** end of class ***/
 /*** mysql hostname ***/
 $hostname = 'localhost';
 /*** mysql username ***/
 $username = 'username';
 /*** mysql password ***/
 $password = 'password';
 try {
 $dbh = new PDO("mysql:host=$hostname;dbname=animals", $username, $password);
 /*** echo a message saying we have connected ***/
 echo 'Connected to database
'; /*** The SQL SELECT statement ***/ $sql = "SELECT * FROM animals"; /*** fetch into an PDOStatement object ***/ $stmt = $dbh->query($sql); /*** fetch into the animals class ***/ $obj = $stmt->fetchALL(PDO::FETCH_CLASS, 'animals'); /*** loop of the object directly ***/ foreach($obj as $animals) { /*** call the capitalizeType method ***/ echo $animals->capitalizeType().'
'; } /*** close the database connection ***/ $dbh = null; } catch(PDOException $e) { echo $e->getMessage(); } ?>
The code above produces a list of animal types, with the first letter capitalized like this:
Connected to database


Emu
Funnel Web
Lizard
Dingo
Kangaroo
Wallaby
Wombat
Koala
Kiwi
  The PDO::FETCH_CLASS constant has fetched the results directly into the animals class where we were able to directly manipulate the results, nifty. PDO provides an alternative to PDO::fetch and PDO::FETCH_CLASS. PDOStatement::fetchObject() will bundle them together to give the same result as shown here.
animal_type);
 }
 } /*** end of class ***/
 /*** mysql hostname ***/
 $hostname = 'localhost';
 /*** mysql username ***/
 $username = 'username';
 /*** mysql password ***/
 $password = 'password';
 try {
 $dbh = new PDO("mysql:host=$hostname;dbname=animals", $username, $password);
 /*** echo a message saying we have connected ***/
 echo 'Connected to database
'; /*** The SQL SELECT statement ***/ $sql = "SELECT * FROM animals"; /*** fetch into an PDOStatement object ***/ $stmt = $dbh->query($sql); /*** fetch into the animals class ***/ $animals = $stmt->fetchObject('animals'); /*** echo the class properties ***/ echo $animals->animal_id.'
'; echo $animals->capitalizeType().'
'; echo $animals->animal_name; /*** close the database connection ***/ $dbh = null; } catch(PDOException $e) { echo $e->getMessage(); } ?>
The above code gives the results like this:
Connected to database
1
Emu
bruce
Note that we have called the animals::capitalizeType() method to show that we are in fact working with an instance of the animals class. PDO::fetchObject() will also work as a substitute for PDO::FETCH_OBJ.

FETCH INTO

The PDO::FETCH_INTO constant allows us to fetch the data into an existing instance of a class. Like PDO::FETCH_CLASS the field names are mapped to the class properties. With this in mind, we should be able to replicate the behaviour of PDO::FETCH_CLASS by instantiating the new object when setting the fetch mode. In this instance, the fetch mode is set using PDO::setFetchMode() method.
animal_type);
 }
} /*** end of class ***/
/*** instantiate a new animals instance ***/
 $animals = new animals;
$animals->animal_id = 10;
$animals->animal_type = 'crocodile';
$animals->animal_name = 'bruce';
/*** mysql hostname ***/
 $hostname = 'localhost';
/*** mysql username ***/
 $username = 'username';
/*** mysql password ***/
 $password = 'password';
try {
 $dbh = new PDO("mysql:host=$hostname;dbname=animals", $username, $password);
 /*** echo a message saying we have connected ***/
 echo 'Connected to database
'; /*** The SQL SELECT statement ***/ $sql = "SELECT * FROM animals"; /*** fetch into an PDOStatement object ***/ $stmt = $dbh->query($sql); /*** set the fetch mode with PDO::setFetchMode() ***/ $stmt->setFetchMode(PDO::FETCH_INTO, new animals); /*** loop over the PDOStatement directly ***/ foreach($stmt as $animals) { echo $animals->capitalizeType().'
'; } /*** close the database connection ***/ $dbh = null; } catch(PDOException $e) { echo $e->getMessage(); } ?>
Once again, the above code produces a list of animal types, with the first letter capitalized like this:
Connected to database
Emu
Funnel Web
Lizard
Dingo
Kangaroo
Wallaby
Wombat
Koala
Kiwia



Tags: , , , , ,

Spin up a VPS server in no time flat

Simple setup. Full root access. Straightforward pricing.

DEPLOY VPS SERVER

Leave a Reply



Feedbacks