Keeping it Simple with Class Based SQL Queries and PHP

In this post I will look at a class I built to simplify querying the database using the same code over and over and over again. There are a lot of classes out there of course that do this but I built one just to serve my simple purposes. This class had to do a few things that I will list here.

  • Get records from a database
  • Insert records into the database
  • Update a record in the database
  • Delete a record in the database

Working with Vue.js I only needed API type results from my PHP code (which is to say PHP becomes the backend) so I wanted to remove the overhead so I started building the class. Eventually I added some more features although I wouldn’t go through them here.

  • Search records based on a query
  • Pagination

The last two are pretty important but change the basic structure of the class so I can review them later or you can always look at the class itself on Github.

Building the Table Model

Our class starts with two public variables.

public $data;
public $table ="events";

I’m pretty sure I don’t use $data but $table is very important. We can use this class to query any table in the database so we need a reference to the table name. Now when we call the class we can choose the table.

  public function __construct($tbl="")
  {
    if($tbl){
      $this->table = $tbl;
    }
  }

Now we can change the table at anytime. This is a great first step.

Connecting to the database

Well we assume we are using one database here. You can make this change in your code allowing the class to work with different databases. We create a get_connection function and we enter the required values. This might be the most inflexible part of the class. You can always fix that.

  public function get_connection(){
    $dsn = "mysql:host=localhost;dbname=wftutorials";
    $user = "root";
    $passwd = "";
    $conn = new PDO($dsn, $user, $passwd);
    return $conn;
  }

We return the connection object so we can use it in other functions.

Getting all records

The first query we are going to build is to get all the records from our database. This function is shown below. It is pretty simple so I’ll go through it.

  public function getAllRecords($sel=array()){
    $conn = $this->get_connection();
    $query = $conn->prepare("SELECT * from ". $this->table);
    $query->execute([]);
    $results = $query->fetchAll();
    return $this->getResults($results, $sel);
  }

First we get the connection object calling $this->get_connection(). Then we prepare our query a very simple SELECT * FROM table query. We execute and fetch the results.

Finally we call the getResults function. Lets see what this function does. What do you think it does?

  public function getResults($results, $sel=array()){
    $data = [];
    foreach ($results as $row){
      $line = [];
      foreach($row as $key=>$value){
        if(is_numeric($key)){
          continue; // remove number in array
        }
        if(count($sel) > 0){
          if(in_array($key, $sel)){

            $line[$key] = $value; // add if selected
          }
        }else{
          $line[$key] = $value; // add as normal array
        }
      }
      $data[] = $line;
    }
    return $data;
  }

The get results function does two main things. It converts the data from fetchAll into something we can use. It removes any numbered keys in the array and its filters by $sel. If you only want to view specific rows it filters those rows for you. The comments explain it all for you. It looks long but it is pretty basic.

Well we have our records set. Lets focus on inserting records. First let me show you an example of running this the getAllRecords function.

$model = new TableModel('contacts');
$results = $model->getAllRecords();

That’s it. Two lines. Fantastic.

Insert Records into our Database

To insert records we have another simple function but it is made of a few other functions we need to pay attention to. The function is called insertRecord.

public function insertRecord($data=[]){
    $conn = $this->get_connection();
    $cvs = $this->getColsAndValues($data);
    $sql = $this->getInsertSQL($cvs['cols']);
    $query = $conn->prepare($sql); // prepare
    $query->execute($cvs['values']); // execute
    return true;
  }

We do the usual. We get the connection object and then we do something different. We call the getColsAndValues function. The name being a quick explanation lets see what it does.

  private function getColsAndValues($data){
    $cols = [];
    $values = [];
    foreach ($data as $key=> $value){
      $cols[] =  $key;
      $values[] =  $value;
    }
    return [
      'cols' => $cols,
      'values' => $values
    ];
  }

When we call this function we pass in an array with the name of the column and the value to be inserted. However to run the query we need to break that array up into two separate arrays. One for columns and the other for values as the name suggests. Once we do that we return the cols and values.

Next we call the getInsertSQL function passing in the column names we got from our getColsAndValues function.

 private function getInsertSQL($data){
    $sql = "INSERT INTO ". $this->table;
    $sql .= " (";
    foreach ($data as $col){
      $sql .= "`$col`,";
    }
    $sql = rtrim($sql,',');
    $sql .= ") VALUES (";
    foreach ($data as $col){
      $sql .= "?,";
    }
    $sql = rtrim($sql,',');
    $sql .= ")";
    return $sql;
  }

The getInsertSQL as the name suggests creates our INSERT statement using the column names we get from the data we passed in. We also add the placeholders that is part of our parametrized query. We use rtrim to remove any stragglers as we add a comma after we append our values. Once we are finished we return sql which contains the full query.

The last steps within our InsertRecord function are shown below. We are just preparing and running the query.

$query = $conn->prepare($sql); // prepare
$query->execute($cvs['values']); // execute

Now we should have return the last inserted Id using the code below. That is the best way to complete this function. We did not do that however but you can as shown below.

return $conn->lastInsertId();

Lets see how we will use this insert function. We insert records in a table called events.

$model = new TableModel('events');
$model->insertRecord([
'title' => 'My new event',
'description' => 'My event title',
'venue' => 'My venue'
]);

Updating your records

To update a record we call the updateRecordByPk function. It was supposed to be used just to update any record but then for the purposes I was using it only needed to update the record by Primary Key. Again you can add your own version of this as you see fit. Lets look that the function below.

public function updateRecordByPk($id, $data=[], $pk='id'){
    $conn = $this->get_connection();
    $cvs = $this->getColsAndValues($data);
    $sql = $this->getUpdateSQL($cvs['cols'], $pk);
    $query = $conn->prepare($sql); // prepare
    $res = array_merge($cvs['values'],[$id]);
    $query->execute($res); // execute
  }

The parameters are id which is the record primary key, the $data which is the data to be updated and finally the $pk an optional attribute. You can use this incase the primary key is not named id and given my generic naming conventions this might not be the case for you.

Now most of this function is familiar to you. You get the connection. You retrieve the columns and values from the data and then you call the getUpdateSQL function. Lets look at this one.

  private function getUpdateSQL($data, $pk){
    $sql = "UPDATE ". $this->table . " SET ";
    $sql .= "";
    foreach ($data as $col){
      $sql .= "`$col`=?,";
    }
    $sql = rtrim($sql,',');
    $sql .= " WHERE `$pk`=?";
    return $sql;
  }

If it looks simple it is because it really is. The only thing I want to focus on is the primary key. Our update query goes like this

UPDATE tablename SET col=?, col2=? WHERE pk=?

So the above is what we are really creating. This is the template. The pk is the column name of the primary key not its value. Once we are finished building our query we return it for use. Then we merge our dataset and execute.

$res = array_merge($cvs['values'],[$id]);
$query->execute($res); // execute

So our values and the primary key value is merged. Remember pk is the last placeholder this is why we merged the values and the id.

Lets see an example of updating a record.

$model = new TableModel('events');
$model->updateRecordByPk(1,[
  'title' => 'My new event title'
], 'id');

Deleting a Record

The function to delete a record is pretty simple. We already understand how we are building our queries. Lets look at it below.

 public function deleteRecordByPk($id, $pk="id"){
    $conn = $this->get_connection();
    $query = $conn->prepare("DELETE FROM ".$this->table." WHERE $pk=?");
    $query->execute([$id]);
    return true;
  }

Above we get our connection and then we run the DELETE query using the table name and the pk from the function parameters. This is all. We then return true.

An example usage would be as follows.

$model = new TableModel('events');
$model->deleteRecordByPk(1); // delete record with primary key 1

Conclusion

Our very simple API class lets use doing some amazing things. We can even expanding it to add some searching features check out the code below

 public function searchRecords($safe=[], $q, $sel=array()){
    $conn = $this->get_connection();
    $sql = $this->getSearchSQL($safe);
    $query = $conn->prepare($sql); //
    $searchPlaceholders = [];
    foreach ($safe as $s){
      $searchPlaceholders[] = "%$q%";
    }
    $query->execute($searchPlaceholders);
    $results = $query->fetchAll();
    return $this->getResults($results, $sel);
  }

Above is a simple search function we are using simple LIKE comparison.

To view this class in action you can check out the video below. We are using Vue.js as a replacement for jQuery and we need some API help to do this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s