Skip to content

Instantly share code, notes, and snippets.

@keeprock
Forked from agarzon/DbHelper.php
Created April 15, 2016 11:37
Show Gist options
  • Save keeprock/68430c558a7d5ab761dd484e8dd509c9 to your computer and use it in GitHub Desktop.
Save keeprock/68430c558a7d5ab761dd484e8dd509c9 to your computer and use it in GitHub Desktop.
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', array('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 = array();
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', 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)));
}
/**
* 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();
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment