Page Comparison

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.

...

// query(string $statement)
$idlang = 1;
$idart = 2;
$cfg = cRegistry::getConfig();
$db = cRegistry::getDb();

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

...

// 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'$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.

...

$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('SELECT * FROM `:table_art_lang` WHERE idart = :idart AND idlang = :idlang'$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.

...

"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 The new method cDb::prepare() ist im Grunde identisch mit der is nearly itentical to cDb::query() , bis auf 2 Punkte.

...

with two exceptions

1. While cDb::query() die Anweisung auch ausführt, liefert executes the statement, cDb::prepare() die aufbereitete Anweisung zurückjust returns the prepared statement.
2. cDb::prepare() kann man nicht mit einem Parameter, also nur mit der SQL-Anweisung, aufrufen.

...

1. 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

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 werdenThe 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 = $db->prepare('SELECT * FROM `%s` WHERE idart = %d AND idlang = %d;';
$sql = $db->prepare($sql, $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 werdenCall 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 `%s``:table_art_lang` WHERE idart = %d:idart AND idlang = :idlang;';
$values = array(
    'table_art_lang' => $cfg['tab']['art_lang'],
    'idart' = %d'> (int) $idart,
    'idlang' => (int) $idlang
);
$sql = $db->prepare($sql, $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.

...

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 preparebuildInsert(string $statement$tablename, array $values)
$values$fields)

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

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

$cfg = cRegistry::getConfig();

$db = cRegistry::getDb();
$sql = $db->buildInsert($cfg['tab']['art_lang'],'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(
    'idartidcode' => (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) $idart,
    'idlang

$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 `:table_art_lang` `%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 = :idart%d AND idlang = :idlang', $values %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.