Archive

Archive for the ‘PDO’ Category

PDO->exec VS. PDO->query

September 7, 2010 1 comment

PDO->exec VS. PDO->query

These functions are for direct execution of SQL. You probably shouldn’t use them. I advocate the use of prepared statements instead:

Prepared statements automatically escape your input

You compile a pepared statement once, and execute as many times as you like

Nevertheless, PDO->exec and PDO->query do exist. So what are they good for ? What’s the difference between the two ?

Well, let’s talk about escaping first.
First, before you input any data into your database using PDO->exec() or PDO->query() you need to escape your data using the PDO->quote() method.

The PDO->quote method will escape your data using the database specific driver for your data base. Now let’s quote something..

<?php
$dbh = new PDO('sqlite:RandomData.sqlite');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$badinput = "Ain't no shame in my game, man";
echo $dbh->quote($badinput);
//'Ain''t no shame in my game, man'
?>

So what is PDO->exec?  PDO->exec just inserts, updates or deletes. It only returns the number of rows affected or false.

<?php

$dbh = new PDO('sqlite:RandomData.sqlite');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
//lets see pdo exec in action
$data = $dbh->quote('aint no shame in my game');
$sql = $dbh->exec("INSERT INTO junk (data) VALUES ($data)");
var_dump($sql); //returns int(1)
?>

PDO->query() is more versatile than PDO->exec.
Here we substitute PDO->exec with PDO->query. We got the # of rows affected by calling the PDOStatement->rowCount() method


<?php
$dbh = new PDO('sqlite:RandomData.sqlite');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$data = $dbh->quote('aint no shame in my game');
$sql = $dbh->query("INSERT INTO junk (data) VALUES ($data)");
var_dump($sql); //Returns PDOStatement object(PDOStatement)#1 (1) {
 //["queryString"]=>
 //string(59) "INSERT INTO junk (data) VALUES ('aint no shame in my game')"
echo $sql->rowCount(); //returns the # of rows affected
?>

The PDOStatement->rowCount() method returns the # of rows that inserted, updated, or deleted… but it’s fickle on returning the # of rows from a select statement. The ability to return the # of rows from a select statement depends on the database.

A fool proof method of getting the number of rows from a select statement in PDO is to just use count() on the array you are returned.

Here is an example

<?php
$dbh = new PDO('sqlite:RandomData.sqlite');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$data = $dbh->query('Select * FROM junk');
$result = $data->fetchAll(PDO::FETCH_ASSOC);
echo count($result);
?>

Advertisements
Categories: PDO

The use of foreach with the PDOStatement class

September 6, 2010 Leave a comment

First, a little on the Traversable interface…

The traversible interface has no constants, static properites, static methods, properties or methods.


Interface [ <internal:Core> interface Traversable ] {

- Constants [0] {
}

- Static properties [0] {
}

- Static methods [0] {
}

- Properties [0] {
}

- Methods [0] {
}

You can’t implement the traversable interface either. If you try to implement the traversable interface you get this error:

Class test must implement interface Traversable as part of either Iterator or IteratorAggregate in Unknown on line 0

So what is the Traversable interface good for? If something implements the Traversable interface, you can use foreach() on it.

The PDOStatement class implements the Traversible Interface,

Here are the two ways I know of that you can use to foreach() your PDOStatement object.

Number 1.

In the example below “$sqlite” is a PDOStatement object returned by $dbh->query. You can use foreach to iterate over the results

I’m using this database:


<?php
//Connect to the database
$dbh = new PDO('sqlite:RandomData.sqlite');
//$sqlite is a PDOStatement object, and the query is executed with $dbh->query
$sqlite = $dbh->query("SELECT * FROM junk");
//iterate over the values
foreach($sqlite as $value) {
var_dump($value);
}
?>


Number 2.


<?php

//Connect to the database
$dbh = new PDO('sqlite:RandomData.sqlite');

//Our PDOStatement object is returned to us when we call $dbh->prepare.

$sqlite = $dbh->prepare("SELECT * FROM junk");

//Next we call $dqlite->execute(); which actually executes the prepared statement.
$sqlite->execute();

//Now that the prepared statement is executed, our results are ready to be iterated over
foreach($sqlite as $value) {
var_dump($value);
}
//If I did not call $sqlite->execute() the foreach call would not have worked, because the prepared statement would not have been executed.
?>

Categories: PDO

PDO has two classes: PDO and PDOStatement

September 3, 2010 Leave a comment

PDO Vs. PDOStatement

PDO is composed of two classes: PDO and PDOStatement.

As per the manual:
The PDO class represents a connection between PHP and a database server.

When you connect to the database in PDO you are creating an instance of the PDO class


<?php
$dbh = new PDO('sqlite:northwind.sqlite');
//$dbh is an instance of the pdo class
?>

After you connect to the database, if you prepare a statement, you create an instance of the PDOStatement class which is an object separate
of the PDO class.

<?php
$dbh = new PDO();
$sql = $dbh->prepare('SELECT CategoryName FROM Categories where CategoryID = 1');
//$sql is an instance of the PDO statement class
?>

In the code above $dbh->prepare does 2 things:
$dbh->prepare prepares an SQL statement to be executed (If your database doesn’t support prepared statements, PDO simulates them). The SQL statement is sent to the database and parsed. If the SQL doesn’t parse,  a return value of false is given, or an exception is thrown, depending on your error settings. If the statement parses then a PDOStatement object is returned.

The PDOStatement object has all the methods you need to work with your PDO statement. For instance, the PDOStatement object allows you to execute your prepared statement.


<?php
$dbh = new PDO('sqlite:northwind.sqlite');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); //this makes PDO throw an error warning if an error happens
$sql = $dbh->prepare('SELECT CategoryName FROM Categories where CategoryID = 1');
$sql->execute();
while ($result = $sql->fetch(PDO::FETCH_ASSOC))
{
var_dump($result);
}
?>

And of course as the code above shows, once you execute your prepared statement you can iterate over it with a while loop.


while ($result = $sql->fetch(PDO::FETCH_ASSOC))
{
var_dump($result);
}

Now that you know there is a difference between the PDO class and the PDO statement class.
You need to know that there are two ways to create a PDOStatement object in PDO.
The first is the PDO->prepare() method which we discussed. It creates a
prepared statement for you to reuse and execute over and over. If you don’t know
what prepared statements are, I’ll cover it some other time, look them up on
wikipedia for now.

The second way to get a PDOStatement object is the PDO->query() method.
The PDO->query method is for statements you don’t plan on executing often. I would consider it quick and dirty.


<?php
$dbh = new PDO('sqlite:northwind.sqlite');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$sql = $dbh->query('SELECT * from categories');
foreach ($sql as $value)
{
var_dump($value);
}
?>

Lastly, don’t make a mistake and think that PDO->exec() will return a PDOStatement object.  PDO->exec() is a method that allows your PDO object to insert, update, or delete data in your table but NOT select. PDO->exec just returns the number of rows affected on success. Consider PDO->exec() a very convenient method to do something quick to your database (but not select). Of course, you could forget that PDO->exec() even exists and just use PDO->query for all your “quick and dirty” needs… If you insist on using PDO->exec, or PDO->query don’t forget that you need to escape all incoming data.

Categories: PDO

PDOStatement->fetchColumn


Here is the database I used: http://download.vive.net/Northwind.zip

I think of PDOStatement->fetchColumn as a handy function to fetch data from a field
as a string. Or, if you think as a database as a spreadsheet, we’d be fetching
a single cell as a string.

<?php
$dbh = new PDO('sqlite:northwind.sqlite');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$sql = $dbh->prepare('SELECT CategoryName FROM categories where CategoryID = 1');
$sql->execute();
$result = $sql->fetchColumn();
echo $result; //Beverage
?>

As you can see the columns are zero indexed.


<?php
$dbh = new PDO('sqlite:northwind.sqlite');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$sql = $dbh->prepare('SELECT CategoryName, Description FROM categories where CategoryID = 1');
$sql->execute();
echo $sql->fetchColumn();
$sql->execute();
echo $result = $sql->fetchColumn(1);

//BeveragesSoft drinks, coffees, teas, beers, and ales

So why is it called fetchColumn? THe best guess I’ve heard is that a database can fetch an entire row at a time, but only a single column. If you think about it, an entire row but a single column is the exact same thing as a single cell, or field.

PDOStatement->fetch() & PDO::FETCH_CLASS, How to fetch data into an object


My purpose is to experiment with PDOStatement->fetch(PDO::FETCH_CLASS).

Here is the database I used: http://download.vive.net/Northwind.zip
Here is what the table I used looked like:

Using PDOStatement->fetch(PDO::FETCH_CLASS, ‘Category’) DOES NOT work if you do the following:

<?php
//THIS CODE DOES NOT WORK AS EXPECTED

class Categories
{
 public $CategoryName;
}
$dbh = new PDO('sqlite:northwind.sqlite');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$sql = $dbh->prepare('SELECT CategoryName FROM categories');
$sql->execute();
while ($result = $sql->fetch(PDO::FETCH_CLASS, 'Categories')) {
 var_dump($result);
}
?>

Here is the code that works as expected


<?php
class Categories
{
public $CategoryName;
}
$dbh = new PDO('sqlite:northwind.sqlite');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$sql = $dbh->prepare('SELECT CategoryName FROM categories');
$sql->setFetchMode(PDO::FETCH_CLASS, 'Categories');
$sql->execute();
//it made no difference if i did this $sql->fetch(PDO::FETCH_CLASS);
//if I did $sql->fetch(PDO::FETCH_CLASS, 'Categories'); I was given a warning
while ($result = $sql->fetch()) {
var_dump($result);
}
?>

Returns
object(Categories)#3 (1) {
[“CategoryName”]=>
string(9) “Beverages”
}
object(Categories)#4 (1) {
[“CategoryName”]=>
string(10) “Condiments”
}
object(Categories)#3 (1) {
[“CategoryName”]=>
string(11) “Confections”
}
object(Categories)#4 (1) {
[“CategoryName”]=>
string(14) “Dairy Products”
}
object(Categories)#3 (1) {
[“CategoryName”]=>
string(14) “Grains/Cereals”
}
object(Categories)#4 (1) {
[“CategoryName”]=>
string(12) “Meat/Poultry”
}
object(Categories)#3 (1) {
[“CategoryName”]=>
string(7) “Produce”
}
object(Categories)#4 (1) {
[“CategoryName”]=>
string(7) “Seafood”
}

The codes works as expected because of the call to the PDOStatement->setFetchMode function.

Categories: PDOStatement->fetch()

The PDO fetch flags are actually constants which represent integers


PDO Fetch Flags – The Constants Represent Integers

If you look at the prototype of the PDOStatement->fetch() function,
you can see that it only accepts integers as its input

PDOStatement::fetch  ([ int $fetch_style = PDO::FETCH_BOTH  [, int $cursor_orientation = PDO::FETCH_ORI_NEXT  [, int $cursor_offset = 0  ]]] )

For example, when you use PDOStatement::fetch(PDO::FETCH_ASSOC), you are putting in a constant PDO:FETCH_ASSOC
and that constant represents the integer 2.

Therefore $PDOStatement->fetch(2) is equivilent to $PDOStatement->fetch(PDO::FETCH_ASSOC);
Try it, it works.

I can think of no sane reason why you would ever want to use the integer over the constant.
If you did, your code would be very hard to read. But as a simple curiosity, here are
the integer values of the PDO fetch flags.

PDO::FETCH_ASSOC = 2
PDO::FETCH_BOTH = 4
PDO::FETCH_BOUND = 6
PDO::FETCH_CLASS = 8
PDO::FETCH_INTO = 9
PDO::FETCH_LAZY = 1
PDO::FETCH_NUM = 3
PDO::FETCH_OBJ = 5

<?php
 echo    PDO::FETCH_ASSOC."\n";// 2
 echo    PDO::FETCH_BOTH."\n";// 4
 echo    PDO::FETCH_BOUND."\n";// 6
 echo    PDO::FETCH_CLASS."\n";// 8
 echo    PDO::FETCH_INTO."\n";// 9
 echo    PDO::FETCH_LAZY."\n"; //1
 echo    PDO::FETCH_NUM."\n"; //3
 echo    PDO::FETCH_OBJ."\n"; //5
?>
Categories: PDOStatement->fetch()

PDOStatement->fetch(PDO::FETCH_ASSOC);


My purpose is to experiment with PDOStatement->fetch(PDO::FETCH_ASSOC).

Here is the database I used: http://download.vive.net/Northwind.zip
Here is what the table I used looked like:

In this test case, I used PDOStatement->fetch(PDO::FETCH_ASSOC).
The return value is an associative array, the index names are the column names. If you’re used to the mysql type functions this function is like mysql_fetch_assoc(), or mysql_fetch_array($result, MYSQL_ASSOC).

<?php
$dbh = new PDO('sqlite:northwind.sqlite');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$sql = $dbh->prepare('SELECT CategoryName FROM categories');
$sql->execute();
while ($result = $sql->fetch(PDO::FETCH_ASSOC))
{
var_dump($result);
}
?>
Returns

array(1) {
 ["CategoryName"]=>
 string(9) "Beverages"
}
array(1) {
 ["CategoryName"]=>
 string(10) "Condiments"
}
array(1) {
 ["CategoryName"]=>
 string(11) "Confections"
}
array(1) {
 ["CategoryName"]=>
 string(14) "Dairy Products"
}
array(1) {
 ["CategoryName"]=>
 string(14) "Grains/Cereals"
}
array(1) {
 ["CategoryName"]=>
 string(12) "Meat/Poultry"
}
array(1) {
 ["CategoryName"]=>
 string(7) "Produce"
}
array(1) {
 ["CategoryName"]=>
 string(7) "Seafood"
}


Categories: PDOStatement->fetch()