Codeception DB helper to extend database functionalities (update & delete)

DbHelper.php

<?php
namespace Codeception\Module;

/**
 * Additional methods for DB module
 *
 * Save this file as DbHelper.php in _support folder
 * Enable DbHelper in your suite.yml file
 * Execute `codeception build` to integrate this class in your codeception
 */
class DbHelper extends \Codeception\Module
{

    /**
     * Delete entries from $table where $criteria conditions
     * Use: $I->deleteFromDatabase('users', ['id' => '111111', 'banned' => 'yes']);
     *
     * @param  string $table    tablename
     * @param  array $criteria conditions. See seeInDatabase() method.
     * @return boolean Returns TRUE on success or FALSE on failure.
     */
    public function deleteFromDatabase($table, $criteria)
    {
        $dbh = $this->getModule('Db')->dbh;
        $query = "delete from %s where %s";
        $params = [];
        foreach ($criteria as $k => $v) {
            $params[] = "$k = ?";
        }
        $params = implode(' AND ', $params);
        $query = sprintf($query, $table, $params);
        $this->debugSection('Query', $query, json_encode($criteria));
        $sth = $dbh->prepare($query);

        return $sth->execute(array_values($criteria));
    }

/**
 * Update entries from $table set $data where $criteria conditions
 * Use: $I->updateFromDatabase('users', ['startdate' => '2014-12-12'], ['id' => '111111']);
 *
 * @param string $table tablename
 * @param array $data data changes for update
 * @param array $criteria conditions. See seeInDatabase() method.
 * @return boolean Returns TRUE on success or FALSE on failure.
 */
    public function updateFromDatabase($table, $data, $criteria)
    {
        $dbh = $this->getModule('Db')->dbh;
        $query = "update %s set %s where %s";
        $params = $dataset =[];
        foreach ($criteria as $k => $v) {
            $params[] = "$k = ?";
        }
        $params = implode(' AND ', $params);
        foreach ($data as $c => $d) {
            $dataset[] = "$c = ?";
        }
        $dataset = implode(' , ', $dataset);
        $query = sprintf($query, $table, $dataset, $params);
        $this->debugSection('Query', $query, json_encode($data) . json_encode($criteria));
        $sth = $dbh->prepare($query);

        return $sth->execute(array_values(array_merge($data, $criteria)));
    }

    /**
     * Execute a SQL query
     * Use: $I->executeOnDatabase('UPDATE `users` SET `email` = NULL WHERE `users`.`id` = 1; ');
     *
     * @param  string $sql query
     * @return boolean Returns TRUE on success or FALSE on failure.
     */
    public function executeOnDatabase($sql)
    {
        $dbh = $this->getModule('Db')->dbh;
        $this->debugSection('Query', $sql);
        $sth = $dbh->prepare($sql);

        return $sth->execute();
    }
}

I propose to add one more function 😄

/**
* Update entries from $table set $data where $criteria conditions
* Use: $I->updateFromDatabase('users', array('startdate' => '2014-12-12'), array('id' => '111111');
*
* @param string $table tablename
* @param array $data data changes for update
* @param array $criteria conditions. See seeInDatabase() method.
* @return boolean Returns TRUE on success or FALSE on failure.
*/
public function updateFromDatabase($table, $data, $criteria)
{
$dbh = $this->getModule('Db')->dbh;
$query = "update %s set %s where %s";
$params = array();
$dataset = array();
foreach ($criteria as $k => $v) {
$params[] = "$k = ?";
}
$params = implode(' AND ', $params);
foreach ($data as $c => $d) {
$dataset[] = "$c = ?";
}
$dataset = implode(' , ', $dataset);
$query = sprintf($query, $table, $dataset, $params);
$this->debugSection('Query', $query, json_encode($data) . json_encode($criteria));
$sth = $dbh->prepare($query);

    return $sth->execute(array_values(array_merge($data, $criteria)));
}

How can I use this with Codeception\Extension\MultiDb ?
Running a test shows
[ModuleException] Codeception\Module: Module Db couldn't be connected

One more solution for it.

public function deleteFromDatabase(string $table, array $criteria)
{
    $db_driver = $this->getModule('Db')->driver;
    $db_driver->deleteQueryByCriteria($table, $criteria);
}

Perhaps adding a helper to get more than one column from the db (since $I->grabFromDatabase() only get's one column)

    /**
     * return row a SQL query
     * Use: $I->grabRowFromDatabase('SELECT * FROM `users` WHERE `users`.`id` = 1; ');
     *
     * @param  string $sql query
     * @return boolean Returns result object on success or null on failure.
     * @throws \Codeception\Exception\ModuleException
     */
    public function grabRowFromDatabase($sql)
    {
       $result = $this->grabRowsFromDatabase($sql);
       if($result) {
           return $result[0];
       }
       return null;
    }

    /**
     * return results a SQL query
     * Use: $I->grabRowsFromDatabase('SELECT * FROM `users` ');
     *
     * @param  string $sql query
     * @return boolean Returns result array on success or null on failure.
     * @throws \Codeception\Exception\ModuleException
     */
    public function grabRowsFromDatabase($sql)
    {
        $dbh = $this->getModule('Db')->dbh;
        $this->debugSection('Query', $sql);
        $sth = $dbh->prepare($sql);
        $execute=$sth->execute();
        if ($execute){
            return $sth->fetchAll();
        }
        return null;
    }

Things have moved on in the codeception world since this gist was originally written. I had to make the followign changes to prevent deprecation messages and make the code work in MySQL.

 public function deleteFromDatabase($table, $criteria)
    {
        $dbh = $this->getModule('Db')->_getDbh();     // dbh is deprecated
        $query = "delete from `%s` where %s";         // needs table name delimiters - and a database name wouldn't hurt....
        $params = [];
        foreach ($criteria as $k => $v) {
            $params[] = "`$k` = '$v'";                // field and value name delimiters were needed.
        }
        $params = implode(' AND ', $params);
        $query = sprintf($query, $table, $params);
        codecept_debug($query);
        $this->debugSection('Query', $query, json_encode($criteria));
        $sth = $dbh->prepare($query);
        return $sth->execute(array_values($criteria));
    }

Otherwise, this was very helpful. Many thanks.

Well I made this Loooooong time ago ;). probably is useless by now.

I could make some use of it 👍 😄

We were using this gist for a while, but now we extend the Db module and can simplify it a lot.

class Database extends \Codeception\Module\Db
{
   // ...
   
    /**
     * Delete entries from $table where $criteria conditions
     *
     * NOTE: Records will not be re-added after tests!
     *
     * Examples:
     *
     * ```
     * // Deletes user with 111111 if they are banned
     * $I->deleteFromDatabase('users', ['id' => 111111, 'banned' => 'yes']);
     * ```
     *
     * ```
     * // Deletes all users with an ID >= 1000
     * $I->deleteFromDatabase('users', ['id >=' => 1000]);
     * ```
     *
     * Supported operators: `<`, `>`, `>=`, `<=`, `!=`, `like`.
     *
     * @param string $table
     * @param array<bool|int|string> $criteria Conditions. See seeInDatabase() method.
     * @throws \Exception
     */
    public function deleteFromDatabase(string $table, array $criteria = []): void
    {
        $this->_getDriver()->deleteQueryByCriteria($table, $criteria);
    }
    
    // ...