PHP Simple Database Class

Lately I was working on a few projects that did not include WordPress as a base framework, and the thing I found I missed the most was WordPress $wpdb object – it is the object that deals with all database interactions and makes it extremely easy to do anything “databasie”, from inserting to tables to querying complex joins.

I perceptually liked the ability to choose how you wanted the query results to be returned; either as an object or as an associative array.

The $wpdb class in WordPress is based on http://justinvincent.com/ezsql and at first I thought to just include this class in my projects. But after a bit of consideration I realized this class was WAY to complicated for a simple project, and although very good it was a bit of an “overkill” for me.

So I sat down and came up with the code below which is a much more simplified version of those classes – with a constructor and destructor and three methods for updating inserting and preforming general queries (which also allows deletions).

It probably does not escape all strings and needs a bit of re-working but its a good start, let me know what you think;

<?php
class db_core{
    private $connection;

    //constructor
    public function __construct($host,$user,$pass,$database){
        $this->connection = new mysqli($host,$user,$pass,$database);
    }

    //destructur
    public function __destruct(){
        $this->connection->close();
    }

    //query
    public function query($qry){
        //query
        $result = $this->connection->query($qry);
        if($result){
            $res = array();
            // Cycle through results
            while ($row = $result->fetch_object()){
                $res[] = $row;
            }
            // Free result set
            $result->close();
            return $res;
        }else{
            return false;
        }
    }

    //insert records
    public function insert($tablename,$params){
        $values = "";
        $pars = "";
        foreach($params as $paramk => $paramv){
            if($pars==''){
                $pars = $this->connection->real_escape_string($paramk);
                if(is_numeric($paramv)){
                    $values .= $paramv;
                }else{
                    $values .= "'" . $this->connection->real_escape_string($paramv) . "'";
                }
            }else{
                $pars .= ',' . $this->connection->real_escape_string($paramk);
                if(is_numeric($paramv)){
                    $values .= "," . $paramv;
                }else{
                    $values .= ",'" . $this->connection->real_escape_string($paramv) . "'";
                }
            }
        }

        return $this->connection->query("insert into $tablename($pars) Values($values)");
    }

    //update records
    public function update($tablename,$params,$params2){
        $pars = "";
        $pars2 = "";
        foreach($params as $paramk => $paramv){
            if($pars==''){
                $pars = $this->connection->real_escape_string($paramk);
                if(is_numeric($paramv)){
                    $pars .= '=' . $paramv;
                }else{
                    $pars .= "='" . $this->connection->real_escape_string($paramv) . "'";
                }
            }else{
                $pars .= ',' . $this->connection->real_escape_string($paramk);
                if(is_numeric($paramv)){
                    $pars .= "=" . $paramv;
                }else{
                    $pars .= "='" . $this->connection->real_escape_string($paramv) . "'";
                }
            }
        }
        foreach($params2 as $paramk2 => $paramv2){
            if($pars2==''){
                $pars2 = $this->connection->real_escape_string($paramk2);
                if(is_numeric($paramv2)){
                    $pars2 .= '=' . $paramv2;
                }else{
                    $pars2 .= "='" . $this->connection->real_escape_string($paramv2) . "'";
                }
            }else{
                $pars2 = ' and ' . $this->connection->real_escape_string($paramk2);
                if(is_numeric($paramv2)){
                    $pars2 .= '=' . $paramv2;
                }else{
                    $pars2 .= "='" . $this->connection->real_escape_string($paramv2) . "'";
                }
            }
        }

        return $this->connection->query("update $tablename set $pars where $pars2");
    }
}
?>
Comments
  • Huntly Cameron
    11/09/2012 19:35

    2 words: PHPdoc and Github ;)

    I digress, that’s actually quite handy. I used the Myslqi lib on my last wee project although it was preparing statements, binding parameters and all sorts which might have been a bit overkill for a rather simple 2 page project! :)

Got something to say? Leave a comment

Current day month ye@r *

Latest Posts

Archive