Database functions in Concrete CMS 5.7+

Edit

Improvements?

Let us know by posting here.

This document requires that you have read about the Service Container.

To get the connection to the current database, we used to write $db = Loader::db(); (v5.6 style), $db = Database::get(); or $db = Database::getActiveConnection(); (v5.7).

The best method is now the following:

$db = $app->make('database')->connection();

A lot of connection methods have been deprecated. Here's the list of their replacements.

Connection methods

Replacement of $db->Execute()

Use $db->executeQuery()

  • parameters differences:
    $db->Execute() accepts as second parameter either an array or a single value to specify a parameter value.
    For $db->executeQuery() the second parameter must be an array.
  • return value differences:
    in case of failures, $db->executeQuery() will throw a PDOException exception.
    Its return value will always be an instance of the Concrete\Core\Database\Driver\PDOStatement class (even for update and delete queries).

Replacement of $db->Query()

Use $db->executeQuery(), as described above.

Replacement of $db->GetRow()

Use $db->fetchAssoc()

  • parameters differences:
    $db->GetRow() accepts as second parameter either an array or a single value to specify a parameter value.
    For $db->fetchAssoc() the second parameter must be an array.
  • return value differences:
    $db->GetRow() returns an empty array if the requested row wasn't found.
    $db->fetchAssoc() returns false if the row can't be found.

Replacement of $db->qstr()

Use $db->quote()

  • parameters differences:
    none
  • return value differences:
    none

Replacement of $db->GetOne()

Use $db->fetchColumn()

  • parameters differences:
    $db->GetOne() accepts as second parameter either an array or a single value to specify a parameter value.
    For $db->fetchColumn() the second parameter must be an array.
  • return value differences:
    none: the return value is still false if no record is found, or the column value otherwise.

Replacement of $db->GetAll()

Use $db->fetchAll()

  • parameters differences:
    $db->GetAll() accepts as second parameter either an array or a single value to specify a parameter value.
    For $db->fetchAll() the second parameter must be an array.
  • return value differences:
    none: the return value is still an empty array if no record is found, or an array of arrays otherwise.

Replacement of $db->GetArray()

Use $db->fetchAll()

  • parameters differences:
    $db->GetArray() accepts as second parameter either an array or a single value to specify a parameter value.
    For $db->fetchAll() the second parameter must be an array.
  • return value differences:
    none: the return value is still an empty array if no record is found, or an array of arrays otherwise.

Replacement of $db->GetAssoc()

In case of two columns in the result

Use $db->executeQuery()->fetchAll(\PDO::FETCH_KEY_PAIR);

  • parameters differences:
    $db->GetAssoc() accepts as second parameter either an array or a single value to specify a parameter value.
    For $db->executeQuery() the second parameter must be an array.
  • return value differences:
    none: the return value is still an empty array if no record is found, or an array of arrays otherwise.

In case of more than two columns in the result

Use this code:

$result = [];
foreach ($db->fetchAll(…) as $row) {
    $key = array_shift($row);
    $result[$key] = $row;
}
  • parameters differences:
    $db->GetAssoc() accepts as second parameter either an array or a single value to specify a parameter value.
    For $db->fetchAll() the second parameter must be an array.
  • return value differences:
    none: the return value is still an empty array if no record is found, or an array of arrays otherwise.

Replacement of $db->Replace()

There's no current replacement for it at the moment.

Replacement of $db->GetCol()

Use this code:

$result = [];
foreach ($db->fetchAll(…) as $row) {
    $result[] = $row[key($row)];
}
  • parameters differences:
    $db->GetCol() accepts as second parameter either an array or a single value to specify a parameter value.
    For $db->fetchAll() the second parameter must be an array.
  • return value differences:
    none: the return value is still an empty array if no record is found, or an array of arrays otherwise.

Replacement of $db->Insert_ID()

Use $db->lastInsertId()

  • return value differences: none: the return value is still the value of the autoincrement ID of the last inserted record (and still '0' if none).

Replacement of $db->BeginTrans()

Use $db->beginTransaction()

  • return value differences:
    $db->BeginTrans() returns false in case of errors.
    $db->beginTransaction() throws a PDOException in case of errors, no value is returned.

Replacement of $db->CommitTrans()

Use $db->commit()

  • return value differences:
    $db->CommitTrans() returns false in case of errors.
    $db->commit() throws a Doctrine\DBAL\ConnectionException or a PDOException in case of errors, no value is returned.

Replacement of $db->RollbackTrans()

Use $db->rollBack()

  • return value differences:
    $db->RollbackTrans() returns false in case of errors.
    $db->rollBack() throws a Doctrine\DBAL\ConnectionException or a PDOException in case of errors, no value is returned.

Replacement of $db->StartTrans()

Concrete CMS 5.7 and later versions only support nested transactions, so there's no way to replicate $db->StartTrans().
You should use multiple transactions ($db->beginTransaction()).

Replacement of $db->CompleteTrans()

Concrete 5.7 and later versions only support nested transactions, so there's no way to replicate $db->CompleteTrans().
You should use multiple transactions ($db->commit()).

Replacement of $db->FailTrans()

Concrete 5.7 and later versions only support nested transactions, so there's no way to replicate $db->FailTrans().
You should use multiple transactions ($db->rollBack()).