Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 7 Next »

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.

$idlang = 1;
$idart = 2;
$cfg = cRegistry::getConfig();
$db = cRegistry::getDb();

// query(string $statement)
$db->query('SELECT * FROM `' . $cfg['tab']['art_lang'] . '` WHERE idart = ' . cSecurity::toInteger($idart) . ' AND idlang = ' . cSecurity::toInteger($idlang));
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 $... ]])
$db->query('SELECT * FROM `%s` WHERE idart = %d AND idlang = %d', $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)
$values = array($cfg['tab']['art_lang'], $idart, $idlang);
$db->query('SELECT * FROM `%s` WHERE idart = %d AND idlang = %d', $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.

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

Die neue Funktion prepare() ist im Grunde identisch mit der query(), bis auf 2 Punkte.

  1. Während query() die Anweisung auch ausführt, liefert prepare() die aufbereitete Anweisung zurück.
  2. prepare() kann man nicht mit einem Parameter, also nur mit der SQL-Anweisung, aufrufen.

Manchmal möchte man SQL-Anweisungen nicht direkt ausführen, z. B. vorher loggen. In solchen Fällen ist prepare() genau das Richtige.

1. Variant

Erster Parameter ist die SQL-Anweisung in Form eines Formatierungsstrings ist und weitere Parameter die Werte, die mit den Formatierungs-Anweisungen in der SQL-Anweisung verarbeitet werden.

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

2. Variant

Aufruf mit 2 Parametern, wobei der erste Parameter die SQL-Anweisung in Form eines Formatierungsstrings ist und der zweite Parameter eine indexbasierte Liste mit Werten, die mit den Formatierungs-Anweisungen in der SQL-Anweisung verarbeitet werden.

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

3. Variant

Aufruf mit 2 Parameters und als "named Parameter" Version. Der erste Parameter ist die SQL-Anweisung und verwendet zu ersetzende Platzhalter. Der zweite Parameter ist eine assoziative Liste mit Werten, die zu ersetzen sind.

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


  • No labels