A Common Class / Function for DB transactions

PHP
1

Hi folks,

currently when i need to insert, update or delete somthing from database, i write entire query. so this makes lot of queries in my codes. is there any common class or somthing, so that i just pass things to it and it will do the insert or delete or what ever.

currently i am writing queries using pdo as below.

//AD RECORD//
$query = $db->prepare("INSERT INTO visas(id,visa_number,position_id,country,number_of_visas)
                       VALUES('',:visa_number,:position_id,:country,:number_of_visas)");
                      
$query->execute(array(':visa_number' => $visa_number, 
                      ':position_id' => $position_id, 
                      ':country' => $country, 
                      ':number_of_visas' => $num_of_visas));

$affected_rows = $query->rowCount();

//SELECT//
$query="SELECT * 
        FROM visas 
        WHERE visa_number='$visa_number'
        AND position_id='$position'
        AND country='$country'";
$result = $db->query($query);
$row_count = $result->rowCount();
  
while($row = $result->fetch()) {
    $id=$row['id'];
    $position=get_position_by_id($row['position_id'],$db);
    $country=$row['country'];
    $number_of_visas=$row['number_of_visas'];
}
2

A procedural function or a class method? Is that what you’re talking about? For example this is how I manage my small CMS:

<?php
namespace calendar_project\database;

class OnlineJournal implements iCrud {
  
  protected $query = NULL;
  protected $stmt = NULL;
  protected $result = NULL;
  protected $query_params = NULL;
  protected $row = NULL;
  protected $status_message = NULL;
  protected $category = 'home_page';

  public function __construct(array $data = NULL) {
    if (is_array($data) && !empty($data['title']) && !empty($data['message_post'])) {
      $db = Database::getInstance();
      $pdo = $db->getConnection();

      /* Set the query variable */
      $this->query = 'INSERT INTO blog (category, client_id, created_by, title, message_post, date_added) VALUES (:category, :client_id, :created_by, :title, :message_post, NOW())';

      /* Set the query variable */
      $this->stmt = $pdo->prepare($this->query);
     
      /* Execute the Query */
      $this->result = $this->stmt->execute(array(':category' => $this->category, ':client_id' => $data['client_id'], ':created_by' => $data['created_by'], ':title' => $data['title'], ':message_post' => $data['message_post']));

      return 'Data Successfully Inserted!';
    }
  }

  public function create($data) {
    return self::__construct($data);
  }

  public function setCategory($category) {
    $this->category = $category;
  }
  
  public function getCategory() {
    return $this->category;
  }

  public function read() {
    $db = Database::getInstance();
    $pdo = $db->getConnection();
    $this->query = 'SELECT id, category, client_id, created_by, title, message_post,  date_updated, date_added FROM blog WHERE category=:category ORDER BY date_added DESC';
    $this->stmt = $pdo->prepare($this->query);
    $this->stmt->execute([':category' => $this->category]);
    return $this->stmt; // if there is data in the database table then return:
  }

  public function update($data) {

    $db = Database::getInstance();
    $pdo = $db->getConnection();
    /* Update the edited blog */
    $this->query = 'UPDATE blog SET title=:title, message_post=:message_post, date_updated=NOW() WHERE id=:id';
    /* Prepare the statement */
    $this->stmt = $pdo->prepare($this->query);
    /* Execute the statement */
    $this->result = $this->stmt->execute(array(':title' => $data['title'], ':message_post' => $data['message_post'], ':id' => $data['id']));

    return ($this->result) ? true : false;
  }

  public function delete($id = NULL) {
    $db = Database::getInstance();
    $pdo = $db->getConnection();
    $this->stmt = $pdo->prepare('DELETE FROM blog WHERE id=:id');
    $this->result = $this->stmt->execute(array(':id' => $id));
    return ($this->result) ? true : false;
  }

}
3

Why arent you receiving the pdo object as a constructor argument? The above approach is almost as dangerous as using the global statement to bring a global $db object into scope and just as inflexible since it creates an undocumented dependency between this class and the “Database” class. Not to mention making a call to get a database instance, then a pdo object on each query function is very wasteful of CPU cycles.

Also, your __construct statement is messed up. You do not have to and should never issue a return statement from __construct. The implicit return of __construct is $this. I’ve never tested other return types but I’m going to guess PHP overrides your intended return and returns $this. Also, the purpose of a constructor is to get the object into a ready state and nothing more. Running a query from a __construct statement just leads to a difficult to test to outright untestable pile. Also, if you’re object has to run it’s own constructor more than once you know you’ve done something wrong

Second, database transactions runs a lot deeper than simply having a management class to keep things together. It involves the use of the pdo transaction mechanism. Here’s an example

/*
 * Let's assume we're in a method that needs to write multiple
 * records, held in an array of arrays.  We'll start flagging
 * the transaction
 */

$this->pdo->beginTransaction();
$statement = $this->pdo->prepare($updateQuery);


/*
 * Traverse our data. Typically objects that need to do this
 * extend from the ArrayObject class, so we just iterate over
 * ourself.
 */
try {
  foreach ( $this as $row ) {
    /*
     * Used *responsibly* goto is not evil. PDOStatement::execute
     * returns boolean on whether the query executed successfully.
     * If it doesn't, we want to go immediately to failure.
     * Using goto gets us there without having to use a fail flag variable.
     */
    if (!$statement->execute($row)) goto failure;
  }
} catch (PDOException $e) {
  goto failure;
}

$this->pdo->commit();
return true;

failure:
/* We failed, so roll back any changes made during this transaction 
 * so we can start again later.
 */
$this->pdo->rollBack();
return false;

The above sets up a situation where none of the rows in a batch will be inserted unless all of them are inserted successfully. There are holes in that sample code - the array keys of the row haven’t been colon prefixed for one. But that’s beyond the scope of the sample.

Transactions do not have to be used on read queries. They are most frequently used when updating records which are stored in the database across multiple tables, and therefore require multiple update or create statements to update. For example, a customer record that has address and phone records off on a master address and phone table. When updating the record you’d want all of these to be applied at once.

To discuss the reasoning behind transactional queries in further depth create a topic in the database forum where it’s more appropriate - the above is just scratching the surface.

4

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.