Archive

Archive for August, 2010

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.

Advertisements

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()

PDOStatement->fetch() -No Flags


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

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() without any flags.
In comparison to mysql style functions this is like calling mysql_fetch_array();
PDOStatement->fetch() returns an array indexed by column name and by number.
By default using PDOStatement->fetch() is like using PDOStatement->fetch(PDO::FETCH_BOTH);

<?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())
{
var_dump($result);
}
?>

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

}
Categories: PDOStatement->fetch()

PDO – $stmt->fetch() VS. $stmt->fetchAll

August 16, 2010 1 comment

PDO ::fetch() vs. PDO::fetchAll()

I admit, this explanation is a little lame:

Imagine PDO is a thirsty man. Imagine data is water. PDO, by default,
drinks his water (data) straight from the faucet streaming in his mouth. The main benefit
for PDO to get his water from a stream, is that he gets it fast. His body
can start using the water right away. With PDO, you start
using the data right away because it is streaming in. However, the side effect
of drinking water from a faucet as opposed to a cup, is that you don’t know
how much you drank. PDO doesn’t know how many results it got because the data
isn’t stored in memory — it goes straight to PHP,  so you can began working on it.

If you are familiar with the mysql family of functions, PDO behaves like
mysql_unbuffered_query.

<?php
$dsn = 'mysql:host=localhost;dbname=YourDbName';
$dbh = new PDO($dsn, USERNAME, PASS);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$stmt = $dbh->prepare('SELECT * FROM products');
$stmt->execute();
while ($row = $stmt->fetch()) {
 echo var_dump($row);
}
$dbh = null;
?>

You can change the default way PDO gets data. Instead of making our man drink
from a stream of water from the faucet, he can drink from a cup. We can make
PDO store its data in a nice little array, and then send the array to you.

Just like drinking water, it may take longer to put it in a cup, but the benefit is you know how much
you drank. Just count the elements of the array to see how many results
you got.

<?php
$dsn = 'mysql:host=localhost;dbname=YourDbName';
$dbh = new PDO($dsn, 'user', 'pass');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$stmt = $dbh->prepare('SELECT * FROM products');
$stmt->execute();
$array = $stmt->fetchAll();
var_dump($array);
?>

If you are familiar with the mysql family of functions, this would be like the mysql_query function.

As a caveat, if you do use PDO::fetchAll() remember to let your database do your heavy lifting. Its faster for a database to sort results,  than for PHP to sort them. The manual mentions this.

Categories: PDO

PDO – Sqlite Connection – Code Snippit


A sample Sqlite3 db can be found here: http://download.vive.net/Northwind.zip
 
<pre><?php
try {
 $dbh = new PDO("sqlite:path/to/file");
}
catch(PDOException $e) {
 echo $e->getMessage();
}
?>
Categories: PDO, PDO - connecting