PHP: SQL Encapsulation

I love LAMP. For the uninitiated, that's an acronym for a technology platform consisting of “Linux” for the OS, “Apache” for the web server, “mySQL” for the database, and “PHP” for development language. “PERL” and “Python” can be substituted for PHP, although I wouldn't. “mSQL” and, in an alphabetic stretch, Postgre SQL can be inserted in the “M” slot. Overall, it's a well integrated suite of technologies and there is a lot of information, code, and software out there catering to it.

If you're going to write for LAMP, the first thing you're going to have to do is figure out how you're going to get to the database. As one of my buddies is fond of noting, development is nothing more than taking data from one place and putting it somewhere else. Database connectivity methods are like opinions, and everyone has one. I do, too, and I like to think it doesn't stink much.

Full Disclosure: Like almost everything I have written since the dawn of the Internet, this code is heavily scavenged from all over the web. The only parts I've written as original code are the parts required to keep one stolen snippet from breaking another one. I'm a rather indiscriminate thief and I don't pay much attention to sources so, if you recognize a particularly large block of code here as having sprung from your fertile imagination, well, let me know and I'll raise a glass in your honor.

That said, the keystone of my connectivity library is the DB class. I put this in a single file with all my other database code and include it on every page.

class DB {
    function open() {
    global $g_DBHost;
    global $g_DBName;
    global $g_DBID;
    global $g_DBPW;
        $this->host = $g_DBHost;
        $this->db = $g_DBName;
        $this->user = $g_DBID;
        $this->pass = $g_DBPW;
        $this->link = mysql_connect($this->host, $this->user, $this->pass);
        if (!$this->link)
        {
                die('Could not connect: ' . mysql_error());
        }
                mysql_select_db($this->db);
   }
    function select($query)
        {
                $result = mysql_query($query, $this->link);
        $i =0;
         while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                $arrrs[$i] = $row;
                ++$i;
                        }
        mysql_free_result($result);
        return $arrrs;
        }
        function modify($query) {
                return mysql_query($query, $this->link);
   }
   function close() {
       mysql_close($this->link);
   }
}

Note that this is specifically written to enable connectivity from PHP to a mySQL database. All subsequent code refers to this class, so only this would have to be modified to use a different database. Note also that credentials are maintained in a separate server variables include. The ones related to this class might look like this:

$g_DBHost = "localhost";  //  sets the database name
$g_DBName = "mydb";  //  sets the database name
$g_DBID = "myid";  //  sets the database ID
$g_DBPW = "mypassword";  //  sets the database password

You'll note that this class exposes four functions (Methods. Whatever.) Judicious use of these four methods enable further abstraction of the data retrieval process. For example, I use this function for a simple data retrieval”

function sql_select($SQL)
{
        $DB_conn = new DB;
        $DB_conn->open();
        $rs = $DB_conn->select($SQL);
        $DB_conn->close();
        return $rs;
}

This function takes a simple SQL select statement as input and returns the results as an array. For example:

$sql = "Select perms from tblUsers where acct = '$_ACCT' AND pass = '$_PASS'";
$results = sql_select($sql);

returns ...

Array ( [0] => Array ( [perms] => 3 ) )

which is called like this ...

$SESperms = $results[0]["perms"];

You'll see that you don't have to pass anything to this function about the database – the DB class handles all of that.

Deletions are abstracted a little differently, since there really isn't a return set.

function sql_delete($TABLE, $WHERE, $debug)
{
        $DB_conn = new DB;
        $DB_conn->open();
        $SQL = "DELETE FROM $TABLE ";
        $SQL = $SQL . " WHERE $WHERE";
        if($debug)
        {
        $query = $DB_conn->modify($SQL) or die (mysql_error() . " " . $SQL);
        }
                else
        {
                $query = $DB_conn->modify($SQL) or die (mysql_error());
        }
        $DB_conn->close();
    return $query;
}

So ...

$results = sql_delete(“tblUser”, “STATUS = 'fired', false);

... would return “true” if successful, or an error string if it fails.

Inserts are a little more complex, since you'll need to supply information about all of the values you'll be inserting . To enable this, I use arrays:

function sql_insert($INS,$TABLE,$debug){

        $DB_conn = new DB;
        $DB_conn->open();
        $SQL = "INSERT INTO $TABLE VALUES ";
        $VALS = "";
        for($l = 0; $l < sizeof($INS);$l++)
        {
        if(substr($INS[$l],0,1) == "%") // literal, for sql funcs
        {
        $VALS = $VALS . substr($INS[$l],1) . ", ";
    }
        elseif(is_numeric($INS[$l]))
        {
        $VALS = $VALS . $INS[$l] . ", ";
    }
                elseif(is_null($INS[$l]))
        {
            $VALS = $VALS . "NULL,";
    }
                else
        {
                $VALS = $VALS . "'" . str_replace("'","&apos;",$INS[$l]) . "', ";
    }
        }
        $SQL = $SQL . "(" . strrev(substr(strrev($VALS),2)) . ")";
        if($debug)
        {
        $query = $DB_conn->modify($SQL) or die (mysql_error() . " " . $SQL);
        }
                else
        {
                $query = $DB_conn->modify($SQL) or die (mysql_error());
        }
        $DB_conn->close();
        return $query;
}

Since everything is handled like a string for simplicity of use, you'll see a lot of code i use to figure out how to format the insert. To use it, you first need to populate an array of column values, in the order in which they appear in the database table:

$arrVars[0]=("APPO0000239");
$arrVars[1]=("25000");
$arrVars[2]=(100253");

Then you need to call the function:

$results = sql_insert($arrVars,"tblPAID", false);

As before, this would return “true” if successful, or an error string if it fails.

Finally, and with greatest complexity, is the update function.

function sql_update($UPDT,$WHERE,$TABLE,$debug){
        $DB_conn = new DB;
        $DB_conn->open();
        $SQL = "Update $TABLE SET ";
        $SET = "";
        for($l = 0; $l < sizeof($UPDT);$l++)
        {
          if(substr($UPDT[$l][1],0,1) == "%") // literal, for sql funcs
        {
        $val = substr($UPDT[$l][1],1);
    }
    elseif(is_numeric($UPDT[$l][1]))
    {
        $val = $UPDT[$l][1];
    }
    elseif (is_null($UPDT[$l][1]))
    {
          $val = "NULL";
    }
        else
        {
          $val = "'" . str_replace("'","&apos;",$UPDT[$l][1]) . "'";
        }
        $SET = $SET . $UPDT[$l][0] . " = " .  $val . ", ";
        }
          $SQL = $SQL . strrev(substr(strrev($SET),2));
          $SQL = $SQL . " WHERE $WHERE";
          if($debug)
        {
                $query = $DB_conn->modify($SQL) or die (mysql_error() . " " . $SQL);
        }
                else
        {
                $query = $DB_conn->modify($SQL) or die (mysql_error());
        }
        $DB_conn->close();
    return $query;
}

You'll see that this works a lot like the insert, but with the addition of a where clause, since you have to know what to update. You might call it like this:

$arrVars[0]=array("FULLXML",$sxml);
$arrVars[1]=array("LASTTID", $tid);
$where = "INVID = " . $invno;
$results = sql_update($arrVars,$where, "tblINVOICE", true);

Again, this would return “true” if successful, or an error string if it fails.

... and those are the basics. Took me a while to put it together, so hopefully, this will save someone some time.

Tags: