A PDO class extension with custom methods with which to manipulate a mysql database

PDO_workout.php

<?php
namespace Dara\Db;

/**
 * Class for Mysql database manipulation
 */
class MysqlPDO extends \PDO
{ 
  /**
   * Create a new database to work with
   * 
   * @param  string $db Database name 
   *
   * @return  null
   */
  public function createDb($db)
  { 
    var_export('Creaing database '.$db.'. <br/>');

    $create = $this->prepare('Create database '.$db);
    if ($create->execute()) {
      var_export('database '.$db.' created <br/>');
    }
  } 

  /**
   * Get all entries from a specified database table
   * 
   * @param  string $tableName Table from which data is to be retrieved
   *
   * @return null 
   */
  public function getAll($tableName)
  { 
    var_export('Fetching data from '.$tableName.'. <br />');

    $get = $this->prepare('select name, surname, middles from '.$tableName);
    $get->execute();
    while ($result = $get->fetch(\PDO::FETCH_ASSOC)) {
      var_export('<br />'.$result['name'].' '. $result['surname'].' '.$result['middles'].'<br />');
    }
    var_export('<br/>');  
  }

  /**
   * Delete a specified database
   * 
   * @param  string $db Database to be deleted
   * 
   * @return null
   */
  public function deleteDb($db) 
  {
    var_export('Deleting database '.$db.'. <br />');
    if ($this->exec('drop database '.$db) !== false) {
      var_export('Database '.$db.' deleted. <br />');
    } else {
      var_export('nothing to delete. <br/>');
    }
  } 

  /**
   * Select database to work with
   * 
   * @param  string $db Preferred database to work with
   * 
   * @return null
   */
  public function selectDb($db)
  { 
    var_export('Selecting database '.$db.'. <br/>');
    $this->exec('use '.$db);
    var_export('database '.$db.' selected. <br/>');
  }

  /**
   * Create a database table
   * 
   * @param  string $tableName Name of table to be created in database
   * 
   * @param  array  $fields    Array of fields to be created in the table
   * 
   * @return null
   */
  public function makeTable($tableName, $fields)
  {
    var_export('Creating table '.$tableName.'. <br/>');
    $query = 'Create table '.$tableName.'(';
    for ($i = 0; $i < sizeof($fields); $i++) {
      if ($i == sizeof($fields) - 1) {
        $query .= $fields[$i];
      } else {
        $query .= $fields[$i].', ';
      } 
    }
    $query .=')';

    if($this->exec($query)) {
      var_export('Table '.$tableName.' created. <br />');
    } 
  }

  /**
   * Insert data into a table
   * 
   * @param  string $tableName Table into which data should be inserted
   * 
   * @param  array  $columns   List of columns to be populated with data
   * 
   * @param  array  $data      List of data to be inserted
   * 
   * @return null           
   */
  public function insertRow($tableName, $columns, $data)
  {
    var_export('Inserting data into '.$tableName.'. <br />');
    if (count($columns) != count($data)) {
      var_export('Unequal number of values and columns specified. <br/>');
      return;
    }

    $values = '';
    for ($i = 0; $i < count($data); $i++) {
      if ($i == count($data) - 1) {
        $values .= '\''. $data[$i].'\'';
      } else {
        $values .= '\''. $data[$i].'\',';
      }
    }
    
    try {
      $this->beginTransaction();
      $insert = $this->prepare('insert into '.$tableName.' ('.implode(',',$columns).') Values ('.$values.')');
      $insert->execute();
      $this->commit();

      var_export('Data inserted. <br />'); 

    } catch (Exception $e) {
      $this->rollback();
      return 'Unable to insert: '.$e->getMessage();
    }
  }

  /**
   * Delete a row from a table
   * 
   * @param  string $tableName Table from which data is to be deleted
   * 
   * @param  string $column    Column to be matched with search keyword
   * 
   * @param  string $keyword   Keyword to be matched in the row to be deleted
   * 
   * @return null
   */
  public function deleteRow($tableName, $column, $keyword)
  {
    var_export('Deleting data from '.$tableName.'. <br />');
    try {
      $this->beginTransaction();
      $delete = $this->prepare('delete from '.$tableName.' where '.$column.' like \'%'.$keyword.'%\'');
      $delete->execute();
      $this->commit();
      var_export('Data deleted. <br />');
    } catch (Exception $e) {
      $this->rollback();
      return 'Unable to delete: '.$e->getMessage();
    }
  }

  /**
   * Edit a row in a specified table
   * 
   * @param  string $tableName     Table on which edit is to be carried out
   * 
   * @param  string $keyColumn     Column to be matched with serach keyword
   * 
   * @param  string $keyword       Keyword to be matched in row to be edited
   * 
   * @param  array $modifications multidimensional array of column to be edited and the new data to use for replacement
   * 
   * @return null
   */
  public function editRow($tableName, $keyColumn, $keyword, $modifications)
  {
    var_export('Editing '.$tableName.'. <br/>');
    try {
      $this->beginTransaction();
      $update = $this->prepare('update '.$tableName.' set '.$modifications['column'].' = \''.$modifications['new-value'].'\' where '.$keyColumn.' like \'%'.$keyword.'%\'');
      $update->execute();
      $this->commit();
      var_export($tableName. ' table edited. <br/>');
    } catch (Exception $e) {
      $this->rollback();
      return 'Unable to edit: '.$e->getMessage();
    }
  }
}

//You might need to change $user and $pass here to match your own Mysql details
$user = 'root';
$pass = '';
$dbName = 'PDO_test_db';

$link = new MysqlPDO('mysql:host=localhost', $user, $pass);
$link->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

$link->createDb($dbName);
$link->selectDb($dbName);

$link->makeTable('users', ['name varchar(15)', 'surname varchar(15)','middles varchar(50)']);

$link->insertRow('users', ['name','middles'], ['Dan', 'Ricky']);
$link->getAll('users');

$link->insertRow('users', ['name','middles'], ['Anita', 'Victor']);
$link->getAll('users');

$link->insertRow('users', ['name','middles','surname'], ['Amiel', 'Grace','Bolt']);
$link->getAll('users');

$link->deleteRow('users', 'surname', 'Bolt');
$link->getAll('users');

$link->editRow('users', 'name', 'Anita', ['column' => 'middles', 'new-value' => 'Baker']);
$link->getAll('users');

$link->deleteDb($dbName);