Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Codeception DB helper to extend database functionalities (update & delete)
<?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();
}
}
@RayRom

This comment has been minimized.

Copy link

@RayRom RayRom commented Dec 19, 2014

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)));
}
@user1007017

This comment has been minimized.

Copy link

@user1007017 user1007017 commented May 16, 2017

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

@ghost

This comment has been minimized.

Copy link

@ghost ghost commented Feb 6, 2018

One more solution for it.

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

This comment has been minimized.

Copy link

@natenolting natenolting commented May 29, 2018

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;
    }
@charlesdeb

This comment has been minimized.

Copy link

@charlesdeb charlesdeb commented Nov 7, 2018

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.

@agarzon

This comment has been minimized.

Copy link
Owner Author

@agarzon agarzon commented Nov 16, 2018

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.