Versions Compared

Version Old Version 13 New Version Current
Changes made by

Marcus Gnaß

Marcus Gnaß

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Info

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.

...

Code Block
languagephp
linenumberstrue
// query(string $statement)
$idlang = 1;
$idart = 2;
$cfg = cRegistry::getConfig();
$db = cRegistry::getDb();
$sql // query(string $statement)
$db->query(= '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.

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

...

Code Block
languagephp
linenumberstrue
// 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('SELECT * FROM `%s` WHERE idart = %d AND idlang = %d', $values)$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.

...

Code Block
languagephp
linenumberstrue
$values$sql = array('SELECT * FROM   '`:table_art_lang'lang` => $cfg['tab']['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('SELECT * FROM `:table_art_lang` WHERE idart = :idart AND idlang = :idlang', $values);

...

$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 new method cDb::prepare() is nearly itentical to cDb::query() with two exceptions

  1. Whilq cDbWhile 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 wanr want to execute an SQL statement in place, but prepare it, e.g. for logging purposes. This is where cDb::prepare() is handy.

...

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

...

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

...

Code Block
languagephp
linenumberstrue
// 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('SELECT * FROM `:table_art_lang` WHERE idart = :idart AND idlang = :idlang', $values);
$db->query($sql);$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.

...

Code Block
languagephp
linenumberstrue
$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
	('       VALUES (".cSecurity:. 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.

...

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

buildUpdate()

Während update() die Anweisung zusammenbaut und auch gleich ausführt, liefert buildUpdate() die zusammengebaute Anweisung zurückWhile 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.

Code Block
languagephp
linenumberstrue
// 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()

Dei neue Funktion The new method toArray() liefert den aktuellen Datensatz, als Array zurück. Dabei ist es möglich den Datensatz als assoziatives und/oder indexiertes Array zurückzuliefern. Per default wird ein assoziatives Array zurückgeliefertreturns 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.

Code Block
languagephp
linenumberstrue
// 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()

Dei neue Funktion toObject() liefert den aktuellen Datensatz, als Objekt zurück das eine Instanz von stdClass ist (Standard Klasse in PHP). Die Eigenschaften des Objekts entsprechen den Feldnamen der TabelleThe 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.

Code Block
languagephp
linenumberstrue
// 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()

Mit der neuen Funktion The new method escape() kann man Variablen vom Typ String bei Bedarf escapen. Diese Funktion ist eine Alternative zu allows for escaping strings when required. It is an alternative to cSecurity::escapeDB().

Code Block
languagephp
linenumberstrue
// string escape(string $str)

$db$escapedCode = cRegistry::getDb();

$code = ->escape("<html>... more code n' fun ...</html>";

$escapedCode = $db->escape($code);

// Zuvorold warstyle diesexcaping
folgendermaßen möglich

$db $escapedCode = cRegistrycSecurity::getDb();

$code = escapeDB("<html>... more code n' fun ...</html>";

$escapedCode = cSecurity, cRegistry::escapeDB($code, $db);

...

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.