Prepared statements

Δευ 10 Δεκεμβρίου 2012 14:18 από 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();

Έχει διαβαστεί 6103 φορές

Ετικέτες: 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

Οδηγός προγραμματιστών

Πρόσφατα άρθρα

Κατασκεύη μηχανής αναζήτησης

Cron jobs

Mikro blog

Mini Shop

Ενημερωτικό δελτίο

Elxis APC

Elxis cache

Πολλαπλά onload events

Ελαχιστοποίηση CSS και Javascript

Το Elxis για κινητά τηλέφωνα και ταμπλέτες