New cDb features

Owned by Marcus Gnaß
Last updated: Dec 05, 2014

Original source (german) - Posting from xmurrix in Community Forum

With CONTENIDO 4.9.0 some core components were refactored. This also affected the class cDb (formerly known as DB_Contenido) and its parent classes. Some code was ported to PHP 5, functions were extended or simply added. This results in much more secure and easier abstraction compared with its predecessor. This article introduces some of the changes that were made.

query()

The method cDb::query() became much more fexible and now can be called in different variants with different parameter lists.

1. Variant

This is the plain old syntax that is already supported in CONTENIDO 4.8.x.

// query(string $statement)
$idlang = 1;
$idart = 2;
$cfg = cRegistry::getConfig();
$db = cRegistry::getDb();
$sql = 'SELECT * FROM `' . $cfg['tab']['art_lang'] . '`
	WHERE idart = ' . cSecurity::toInteger($idart) . '
	AND idlang = ' . cSecurity::toInteger($idlang) . ';';
$db->query($sql);

// access values
if ($db->nextRecord()) echo $db->f('title');

2. Variant

An alternative is to call cDb::query() with multiple parameters, where the first is the SQL statement in which subsequent parameters will be embedded. The embedding will be performed in a way similar to formatting values with a format string.

// query(string $statement [, mixed $args [, mixed $... ]])
$sql = 'SELECT * FROM `%s` WHERE idart = %d AND idlang = %d;';
$db->query($sql, $cfg['tab']['art_lang'], $idart, $idlang);

In this example the method will be called with four parameters. Please assure that for each formatting instruction (e.g. %s) exactly one parameter with its value is given.

  • %s will be substituted by $cfg['tab']['art_lang']
  • the 1st %d will be substituted by $idart
  • the 2nd %d will be substituted by $idlang

Using this variant strings don't have to be manually escaped (cSecurity::escapeDB()) and integer values don't have to be casted (cSecurity::toInteger()) anymore. This will be assured automatically and the code will become more concise and more readable.

3. Variant

Another variant is to call cDb::query() with exactly two parameters, the SQL statement as in the second variant and, as second parameter, an array containing all values that should be used for substitution.

// query(string $statement, array $values)
$sql = 'SELECT * FROM `%s` WHERE idart = %d AND idlang = %d;';
$values = array($cfg['tab']['art_lang'], $idart, $idlang);
$db->query($sql, $values);

Compared the the second variant, all values will just be passed together as an array but there is no difference in the behaviour.

4. Variant

Eventually you can choose to use "named parameters". This variant also takes exactly two parameters, the SQL statement as in the second variant and, as second parameter, an associative array containing all values that should be used for substitution.

$sql = 'SELECT * FROM `:table_art_lang` WHERE idart = :idart AND idlang = :idlang;';
$values = array(
    'table_art_lang' => $cfg['tab']['art_lang'],
    'idart' => cSecurity::toInteger($idart),
    'idlang' => cSecurity::toInteger($idlang)
);
$db->query($sql, $values);

Using this variant integer valus should be casted cause the format string contains no formatting instruction like %d. Though strings still don't have to be escaped.

The tokens start with a colon and equal the arrays keys.

"Named parameters" should not be confused with prepared statements. Database driver that support prepared statements parse the SQL statement once and reuse this on consecutive uses whereas this version perform the substitution on every call!

prepare()

The new method cDb::prepare() is nearly itentical to cDb::query() with two exceptions

  1. While cDb::query() executes the statement, cDb::prepare() just returns the prepared statement.
  2. cDb::prepare() cannot be called with a single parameter, i.e. the SQL statement.

Occasionally you don't want to execute an SQL statement in place, but prepare it, e.g. for logging purposes. This is where cDb::prepare() is handy.

1. Variant

The first is the SQL statement in which subsequent parameters will be embedded. The embedding will be performed in a way similar to formatting values with a format string.

// string prepare(string $statement [, mixed $args [, mixed $... ]])
$sql = 'SELECT * FROM `%s` WHERE idart = %d AND idlang = %d;';
$sql = $db->prepare($sql, $cfg['tab']['art_lang'], $idart, $idlang);
$db->query($sql);

2. Variant

Call with exactly two parameters, the SQL statement as in the first variant and, as second parameter, an array containing all values that should be used for substitution.

// string prepare(string $statement, array $values)
$sql = 'SELECT * FROM `%s` WHERE idart = %d AND idlang = %d;';
$values = array($cfg['tab']['art_lang'], $idart, $idlang);
$sql = $db->prepare($sql, $values);
$db->query($sql);

3. Variant

Call with exactly two parameters with "named parameters". The first parameter is the SQL statement as in the first variant and, as second parameter, an associative array containing all values that should be used for substitution.

// string prepare(string $statement, array $values)
$sql = 'SELECT * FROM `:table_art_lang` WHERE idart = :idart AND idlang = :idlang;';
$values = array(
    'table_art_lang' => $cfg['tab']['art_lang'],
    'idart' => (int) $idart,
    'idlang' => (int) $idlang
);
$sql = $db->prepare($sql, $values);
$db->query($sql);

insert()

The new method insert() can be used to create a new record in a given database table. It is an alternative to the manual creation of an INSERT statement.

The manual creation of an INSERT statement usually looks something like this:

$idcode = 123;
$idcatart = 12;
$idlang = 1;
$idclient = 1;
$code = "<html>... code n' fun ...</html>";
$cfg = cRegistry::getConfig();
$db = cRegistry::getDb();
$sql = 'INSERT INTO ' . $cfg['tab']['code'] . '
	(idcode, idcatart, code, idlang, idclient)
VALUES
	(' . cSecurity::toInteger($idcode) . ',
	' . cSecurity::toInteger($idcatart) . ',
	"' . cSecurity::escapeDB($code, $db) . '",
	' . cSecurity::toInteger($idlang) . ',
	' . cSecurity::toInteger($idclient) . ');';
$db->query($sql);

The method insert() is an alternative. The first parameter is the database table name whereas the second parameter is an associative array with array keys as column names and array values as record values.

// bool insert(string $tablename, array $fields)

$idcatart = 12;
$idlang = 1;
$idclient = 1;
$code = "<html>... code n' fun ...</html>";

$fields = array(
    'idcatart' => (int) $idcatart,
    'idlang' => (int) $idlang,
    'idclient' => (int) $idclient,
    'code' => $code,
);

$cfg = cRegistry::getConfig();

$db = cRegistry::getDb();
$result = $db->insert($cfg['tab']['code'], $fields);

Integer values should be casted whereas strings will be escaped automatically.

The observant reader might have noticed that no idcode was defined. From CONTENIDO 4.9 on the sequence table for managing IDs was removed. It's no more neccessary to retrieve the next ID via $db->nextid() as ID columns are now defined as AUTOINCREMENT and MySQL is responsible for the determination of IDs.

buildInsert()

While insert() creates an INSERT statement and executes it immediatly, buildInsert() will just create and return the statement. This is usefull if the statement should be used otherwise (e.g. logged) before being executed.

// string buildInsert(string $tablename, array $fields)

$idcatart = 12;
$idlang = 1;
$idclient = 1;
$code = "<html>... code n' fun ...</html>";

$fields = array(
    'idcatart' => (int) $idcatart,
    'idlang' => (int) $idlang,
    'idclient' => (int) $idclient,
    'code' => $code,
);

$cfg = cRegistry::getConfig();

$db = cRegistry::getDb();
$sql = $db->buildInsert($cfg['tab']['code'], $fields);
$result = $db->query($sql);

update()

The new method update() is used to update an existing record. It's an alternative to the manual creation of an UPDATE statement.

The manual creation of an UPDATE statement usually looks something like this:

$idcode = 123;
$code = "<html>... more code n' fun ...</html>";
$cfg = cRegistry::getConfig();
$db = cRegistry::getDb();
$sql = "UPDATE ".$cfg["tab"]["code"]." SET code = '".cSecurity::escapeDB($code, $db)."'
        WHERE idcode = " . cSecurity::toInteger($idcode);
$db->query($sql);

The method update() is an alternative. The first parameter is the database table name whereas the second parameter is an associative array with array keys as column names and array values as record values. Furthermore there is an associative array as third parameter containing the WHERE conditions.

// bool update(string $tablename, array $fields, array $where)

$idcode = 123;
$code = "<html>... more code n' fun ...</html>";

$fields = array(
    'code' => $code,
);
$where = array(
    'idcode' => (int) $idcode
);
$cfg = cRegistry::getConfig();
$db = cRegistry::getDb();
$result = $db->update($cfg['tab']['code'], $fields, $where);

Integer values should be casted whereas strings will be escaped automatically.

If the third parameter contains several conditions these will be ANDed.

buildUpdate()

While update() creates an UPDATE statement and executes it immediatly, buildUpdate() will just create and return the statement. This is usefull if the statement should be used otherwise (e.g. logged) before being executed.

// string buildUpdate(string $tablename, array $fields, array $where)

$idcode = 123;
$code = "<html>... more code n' fun ...</html>";

$fields = array(
    'code' => $code,
);
$where = array(
    'idcode' => (int) $idcode
);

$cfg = cRegistry::getConfig();
$db = cRegistry::getDb();
$sql = $db->buildUpdate($cfg['tab']['code'], $fields, $where);
$result = $db->query($sql);

toArray()

The new method toArray() returns the current records data as array. This method allows for returning a normal (idexed) or even an associative array. An associative array will be returned by default.

// string toArray(string $fetchmode)

$idlang = 1;
$idart = 2;
$cfg = cRegistry::getConfig();
$db = cRegistry::getDb();
$sql = $db->prepare('SELECT * FROM `%s` WHERE idart = %d AND idlang = %d', $cfg['tab']['art_lang'], $idart, $idlang);
$db->query($sql);
if ($db->nextRecord()) {
    $assocRs = $db->toArray(); // oder mit Parameter cDbDriverHandler::FETCH_ASSOC
    echo "<pre>\$assocRs: " . print_r($assocRs, true) . "<pre>";

    $indexedRs = $db->toArray(cDbDriverHandler::FETCH_NUMERIC);
    echo "<pre>\$indexedRs: " . print_r($indexedRs, true) . "<pre>";

    $bothdRs = $db->toArray(cDbDriverHandler::FETCH_BOTH);
    echo "<pre>\$bothdRs: " . print_r($bothdRs, true) . "<pre>";
}

toObject()

The new method toObject() returns the current records data as object, being an instance of the class stdClass which is a standard class in PHP. The objects members correspond to the tables column names.

// string toObject(string $fetchmode)

$idlang = 1;
$idart = 2;
$cfg = cRegistry::getConfig();
$db = cRegistry::getDb();
$sql = $db->prepare('SELECT * FROM `%s` WHERE idart = %d AND idlang = %d', $cfg['tab']['art_lang'], $idart, $idlang);
$db->query($sql);
if ($db->nextRecord()) {
    $rs = $db->toObject();
    echo "<pre>\$rs: " . print_r($rs, true) . "<pre>";
    echo "<pre>idartlang: " . $rs->idartlang . "<pre>";
    echo "<pre>title: " . $rs->title . "<pre>";
    echo "<pre>author: " . $rs->author . "<pre>";
}

escape()

The new method escape() allows for escaping strings when required. It is an alternative to cSecurity::escapeDB().

// string escape(string $str)
$escapedCode = cRegistry::getDb()->escape("<html>... more code n' fun ...</html>");

// old style excaping
$escapedCode = cSecurity::escapeDB("<html>... more code n' fun ...</html>", cRegistry::getDb());

These were the most important changes of the database class cDb and its predecessors. Developers now have more options to create SQL statements. This leads to easier and much more readable code.