Prepared statements

Mon December 10, 2012 14:18 by Elxis Team

PDO prepared statements and the elxisPDOStatement class

Elxis does extend use of prepared statements to execute queries to the database. Class elxisPDOStatement extends PDO's PDOStatement class providing some usefull methods like fetchAllAssoc and fetchPairs. The default fetch mode is PDO::FETCH_OBJ (fetch result sets as objects). There are 2 methods to prepare a statement, prepare without limit capabilities and prepareLimit with limit capabilities (PDO lucks limiting database results and so elxisDatabase fixes that). Both of these methods return an elxisPDOStatement instance.

Public methods

As elxisPDOStatement is an extension of PDOStatement it supports all PDOStatement methods plus some extra ones we have implemented into Elxis.

execute

Execute a prepared statement

public function execute($params=null, $options=array())

getQueryString

Get the query string

public function getQueryString()

fetchAllAssoc

Get result set as an associative array keyed by given field. Inspired by Drupal CMS (thanks guys).

public function fetchAllAssoc($key, $fetchMode=null)

fetchPairs

Fetch a 2-column result set as an array of key-value pais.

public function fetchPairs($key_index=0, $value_index=1)

fetchCol

Return single column as an indexed array.

public function fetchCol($index = 0)

fetchResult

Fetch the value of the first column of the first row.

public function fetchResult()

Examples

1. Prepared statements

// Prepare a statement without limit/offset (method prepare):

$db = eFactory::getDB();

$stmt = $db->prepare($sql, $options, $prefix);

// Prepare a statement with limit/offset (method prepareLimit):

$db = eFactory::getDB();

$stmt = $db->prepareLimit($sql, $offset, $limit, $options, $prefix);

2. Prepare limit in real world

// Get the first 10 published articles

$db = eFactory::getDB();

$sql = "SELECT * FROM ".$db->quoteId('#__content')." WHERE ".$db->quoteId('published')." = 1";

$stmt = $db->prepareLimit($sql, 0, 10);

$stmt->execute();

$rows = $stmt->fetchAll(PDO::FETCH_OBJ);

// See elxisDatabase for the quoteId method

3. Fetch all as an associative array

// Get the first 2 root content categories.

$parent = 0;

$sql = "SELECT catid, title, seotitle FROM #__categories WHERE parent_id = :parent";

$stmt = $eFactory::getDB()->prepareLimit($sql, 0, 2);

$stmt->bindParam(':parent', $parent, PDO::PARAM_INT);

$stmt->execute();

$rows = $stmt->fetchAllAssoc('catid');

//The resulting $rows will be an array like below

Array(
    [1] => stdClass Object(
            [catid] => 1
            [title] => News
            [seotitle] => news
        )
    [23] => stdClass Object (
            [catid] => 23
            [title] => People
            [seotitle] => people
        )
)

4. Fetch pairs

// Get an array having as keys the category id (catid) and as values the category title. Limit the result to the first 5 rows and get only categories having as parent the category with catid = 2.

$parent = 2;

$sql = "SELECT catid, title FROM #__categories WHERE parent_id = :parent";

$stmt = $eFactory::getDB()->prepareLimit($sql, 0, 5);

$stmt->bindParam(':parent', $parent, PDO::PARAM_INT);

$stmt->execute();

$rows = $stmt->fetchPairs();

// The resulting $rows will be an array like below

Array (
    [3] => Europe
    [5] => Asia
    [6] => America
    [7] => Australia
    [8] => Africa
)

5. Fetch result

// Get the number of content categories having as parent the category with catid = 2

$parent = 2;

$sql = "SELECT COUNT(catid) FROM #__categories WHERE parent_id = :parent";

$stmt = $eFactory::getDB()->prepare($sql);

$stmt->bindParam(':parent', $parent, PDO::PARAM_INT);

$stmt->execute();

$total_categories = (int)$stmt->fetchResult();

It has been read 6083 times

Tags: prepared statements elxispdostatement pdo database fetchallassoc fetchcol fetchresult

Database table

Elxis database

Prepared statements

Public methods

execute

getQueryString

fetchAllAssoc

fetchPairs

fetchCol

fetchResult

Examples

1. Prepared statements

2. Prepare limit in real world

3. Fetch all as an associative array

4. Fetch pairs

5. Fetch result

Developers guide

Latest Articles

Develop a search engine

Cron jobs

Mikro blog

Mini Shop

Newsletter module

Elxis APC

Elxis cache

Multiple onload events

Javascript and CSS minifier

Elxis for mobiles and tablets