Setting up a database class

Question

I am wondering the best way to setup a database class, to remove repeat code by using OOP. This way if I changed how I connect to the database (switch from PDO to sqlsrv driver) I can just change it in 1 file, instead of all the files that use PDO. I've setup a class that I will show below, but I feel like it may be hard to understand if I hired a developer in the future.

Also, would it be better to have pre-built functions for returning specific data (.getFriends())? I also am going to be using mainly stored procedures, which means, should I create specific functions for doing that? Just to make it more clear to a developer? As you can see, I understand how to do all of this, I am just not sure what a good, clean way is.

<?php

$database = new Database;


class Database
{
public $database;

public function __construct()
{
    $this->database = new PDO("sqlsrv:Server=$IP;Database=$DBNAME", "$UsrName", "$Password");
}

// get rows
//Returns rows from specified query. If no bindParams, then pass empty or       undefined variable.
public function selectRows($query, $bindParams, $fetchMode)
{
    $stmt = $this->database->prepare($query);
    $stmt->execute($bindParams);
    return $stmt->fetchAll($fetchMode);
}

// insert row
public function insertRows($query, $bindParams)
{
    try {
        $stmt = $this->datab->prepare($query);
        $stmt->execute();
        return TRUE;
    } catch (PDOException $e) {
        throw new Exception($e->getMessage());
    }
}

// update row
public function updateRows($query, $bindParams)
{
    $this->insertRow($query);
}

// delete row
public function deleteRows($query, $bindParams)
{
    $this->insertRow($query);
}

}

?>

Answer 1

Unless I'm missing something, your selectRows and insertRows can be used for other operations as well, because you don't actually check that the queries are SELECT or INSERT respectively. In fact you call insertRows when the consuming code calls deleteRows or updateRows so why have all these functions?

In deleteRows and updateRows, you accept an argument ($bindParams) that you don't use. Why?

would it be better to have pre-built functions for returning specific data (Eg..getFriends())?

In my own work, I put such functions in the User class which can in turn call selectRows with the right parameters. It still meets your requirement that only the Database class interacts directly with the DB engine. The Database class has no information about other objects. It just executes queries and returns results. The core of my class is similar to:

public $error; //holds text of error if a query fails
private $results; //holds results of most recent query
private $conn; //holds db connection used by all queries

//connects to the database and saves the connection in $conn property
public function __construct($host, $port, $user, $pwd, $db_name)

//disconnect from the database
public function __destruct()

//executes query after binding params. Return TRUE on success or FALSE on error
//stores results in $results property so it can be accessed by other functions
public function query($sql, array $params = null)

//executes a query and returns a row. If no sql is provided,
// returns row from $results property. Return FALSE on error
public function fetch_row($sql = null, array $params = null)

//executes a query and returns the full results table. If no sql is provided,
// returns table from $results property. Return FALSE on error
public function fetch_array($sql = null, array $params = null)


//executes a query and returns the column of interest. If no sql is provided,
// returns column from $results property. Return FALSE on error
 public function fetch_column($column, $sql = null, array $params = null)

At the start of each function, I set $error to null. If there is an error, I fill $error with the details. So the consuming code can check this variable after executing a query to check for errors:

$result = $db->query(...); // or $db->fetch_array(...)

if($error_msg = $db->error):
  //query failed. Details are in $error_msg
else:
  // query succeeded
endif;

Does that help?

Answer 2

I honestly don't see what value you are intending to derive from this class vs. just working directly with PDO

• It is not doing something like guaranteeing only a single DB connection exists, as one might with a singleton type of pattern.
• It is not abstracting calling code from having to formulate SQL, as you might see frequently done with a DB wrapper class. In fact you leave the caller able to do wonky things like delete rows while calling insert method. The class also offer nothing in terms of dealing with malformed queries, as most of database interactions just assume happy path execution and don't have any error handling. This is a very bad combination towards having an application that is hard to debug.
• It is not providing transactional wrapper around queries.
• It is not validating input data.

These are amongst the reasons that one might have for building a wrapper around a pre-existing database extraction like PDO. I am just not getting a clear understanding on why this class needs to exist at all vs. just working with PDO throughout the application.