Database accessor functions

Question

I've been away from PHP for at least 5 years and I'm just starting to look into it again. I have a set of functions I created and used for database access and I'm wondering if they are still good to use.

<?php
//
// A group of database function to hide any errors that may occur and
// allow for some form of fallback. If dbConnect() fails for any reason
// an error is displayed on the page, all other functions return empty
// values. For example dbSQL() will return an empty recordset. Pages will
// display the error once but still function to some degree.
// 

$dbConnection = false;
if (empty($dbType))
{
    $dbType = "MYSQL";
}

// INPUTS:  $type -- The type of database that is going to be used
//                   MYSQL, MSSQL
// OUTPUTS: None
//
// EXAMPLE: dbSetType("MYSQL");
function dbSetType($type)
{
    global $dbType;
    $dbType=$type;
}


// INPUTS:  NONE
// OUTPUTS: true if the database API for PHP is installed false if no
//
// EXAMPLE: if(!dbOK()) { print "Error"; }
function dbOK()
{
    global $dbType;
    if($dbType == "MYSQL")
    {
        if(function_exists('mysql_connect'))
        {
            return true;
        }
        return false;
    }
    elseif ($dbType == "MSSQL")
    {
        if(function_exists('mssql_connect'))
        {
            return true;
        }
        return false;
    }
    return false;
}


// INPUTS:  $server ---- Server name, "localhost" if same server as web server
//          $database -- The database name to use
//          $username -- Username to connect to $server with
//          $password -- Password of the user
// OUTPUTS: None
//
// EXAMPLE: dbConnect("localhost", "test", "root", "");
function dbConnect ($server, $database, $username, $password)
{
    global $dbType;
    global $dbConnection;
    if($dbType == "MYSQL")
    {
        if (dbOK())
        {
            $dbConnection = mysql_connect ($server, $username, $password); 
            if (!$dbConnection)
            { 
                print "<h1>Can not connect to ".$server." with user ".$username."</h1>";
            }
            else
            { 
                $db_select = mysql_select_db ($database); 
                if (!$db_select)
                { 
                    print "<h1>Database ".$database." does not exist</h1>";
                }
            }
        }
        else
        {
            print "<h1>mySQL module is not installed</h1>";
        }
    }
    elseif ($dbType == "MSSQL")
    {
        if (dbOK())
        {
            $dbConnection = mssql_connect ($server, $username, $password); 
            if (!$dbConnection)
            { 
                print "<h1>Can not connect to ".$server." with user ".$username."</h1>";
            }
            else
            { 
                $db_select = mssql_select_db ($database); 
                if (!$db_select)
                { 
                    print "<h1>Database ".$database." does not exist</h1>";
                }
            }
        }
        else
        {
            print "<h1>MSSQL module is not installed</h1>";
        }
    }
}


// Internal function should never be called outside of the dbSQL() function.
// This function returns the Nth parameter passed into dbSQL during the
// replacement of the $1, $2, $3, etc. in the $sql string.
function dbSQL__callback($at)
{
    global $dbSQL__parameters;
    return $dbSQL__parameters[$at[1]-1];
}


// INPUTS:  $sql --------- A SQL statment with $// to be replace by cleaned data
//          $parameters -- An array of unclean data to be inserted into the SQL
// OUTPUTS: A recordset resulting from the SQL statment if approprate, false on
//          error
//
// EXAMPLE: dbSQL("SELECT * FROM t WHERE col1=$1 AND col2=$2", array(1, "hi"));
function dbSQL($sql, $parameters = array(), $debug = false)
{
    global $dbType;
    global $dbConnection;
    global $dbSQL__parameters;
    if (dbOK())
    {
        if ($dbConnection)
        {           
            foreach ($parameters as $k=>$v)
            {
                $v = trim($v);
                if (is_int($v))
                {
                    $parameters[$k] = $v;
                }
                else
                {
                    if (is_null($v))
                    {
                        $parameters[$k] = "'BLANK'";
                    }
                    else
                    {
                        if (get_magic_quotes_gpc())
                        {
                            $v = stripslashes($v);
                        }
                        if ($dbType == "MYSQL")
                        {
                            $parameters[$k] = "'".mysql_real_escape_string($v)."'";
                        }
                        elseif ($dbType == "MSSQL")
                        {
                            $parameters[$k] = "'".mssql_escape_string($v)."'";
                        }
                    }
                }
            }
            $dbSQL__parameters = $parameters;
            $safeSQL = preg_replace_callback('/\$([0-9]+)/', 'dbSQL__callback', $sql);

            if ($debug == true)
            {
                print "<p>SQL: ".$safeSQL."</p><br />";
            }

            if ($dbType == "MYSQL")
            {
                $ret = mysql_query($safeSQL, $dbConnection) or die(mysql_error());
            }
            elseif ($dbType="MSSQL")
            {
                $ret = mssql_query($safeSQL, $dbConnection) or die(mssql_get_last_message());
            }
            return $ret;
        }
    }

    return false;
}


// INPUTS:  $recordset -- A recordset as returned by dbSQL()
// OUTPUTS: Number of rows in the recordset
//
// EXAMPLE: $rows = dbRecordTotalRows($rs);
function dbRecordTotalRows($recordset)
{
    global $dbType;
    global $dbConnection;
    if (dbOK())
    {
        if ($dbConnection)
        {
            if ($dbType == "MYSQL")
            {
                return mysql_num_rows($recordset);
            }
            elseif($dbType == "MSSQL")
            {
                return mssql_num_rows($recordset);
            }
        }
    }

    return 0;
}


// INPUTS:  $recordset -- A recordset as returned by dbSQL()
// OUTPUTS: None
//
// EXAMPLE: dbRecordNextRow($rs);
function dbRecordNextRow($recordset)
{
    global $dbConnection;
    if (dbOK())
    {
        if ($dbConnection)
        {
            $recordset->MoveNext();
        }
    }
}


// INPUTS:  $recordset -- A recordset as returned by dbSQL()
// OUTPUTS: Array of key=value pair for the current row, false if
//          past last row of recordset
//
// EXAMPLE: $row = dbRecordGetRow($rs);
function dbRecordGetRow($recordset)
{
    global $dbType;
    global $dbConnection;
    if (dbOK())
    {
        if ($dbConnection)
        {
            if ($dbType == "MYSQL")
            {
                $row = mysql_fetch_array($recordset);
            }
            elseif ($dbType == "MSSQL")
            {
                $row = mssql_fetch_array($recordset);
            }
            return $row;
        }
    }

    return null;
}


// INPUTS:  $row -------- A row as returned by dbRecordGetRow()
//          $fieldname -- The name of the field whos value is returned.
// OUTPUTS: Value in the requested field
//
// EXAMPLE: $value = dbRowGetField($row, "id");
function dbRowGetField($row, $fieldname)
{
    if (dbOK())
    {
        return stripslashes($row[$fieldname]);
    }

    return null;
}


function dbGetLastInsertID()
{
    global $dbType;
    if ($dbType == "MSSQL")
    {
        $sql="select SCOPE_IDENTITY() AS last_insert_id";
        $parms = Array();
        $ret = dbSQL($sql, $parms);
        $row = dbRecordGetRow($ret);
        return dbRowGetField($row, "last_insert_id");
    }
    return -1;
}

// INPUTS:  None
// OUTPUTS: None
//
// EXAMPLE: dbDisconnect();
function dbDisconnect()
{
    global $dbType;
    global $dbConnection;
    if (dbOK())
    {
        if ($dbConnection)
        {
            if ($dbType == "MYSQL")
            {
                mysql_close($dbConnection);
            }
            elseif ($dbType == "MSSQL")
            {
                mssql_close($dbConnection);
            }
            $dbConnection = false;
        }
    }
}


// INPUTS:  $string_to_escape -- This is the unsafe string to pass to the mssql database
// OUTPUTS: A safe string that is ok to pass to a mssql database
//
// EXAMPLE: mssql_escape_string("Not 's'a'f'e' String");
function mssql_escape_string($string_to_escape)
{
    $replaced_string = str_replace("'","''",$string_to_escape); 
    $replaced_string = str_replace("%","[%]",$replaced_string); 
    $replaced_string = str_replace("_","[_]",$replaced_string); 
    return $replaced_string; 
} 

/* End Of File */

Answer 1

A general OOP advice: use the factory pattern and move MySQL related codes to a MySqlDatabaseclass and MSSQL related codes to an MsSqlDatabase class. In this way you'll have two separate classes (one for MySQL and one for MSSQL) instead of the if-elseif statements in (almost) every method. You will also need a common interface which both classes implement.

interface Database {
    public function dbOK();
    public function dbRecordTotalRows($recordset);
    public dbDisconnect();
    ...
}

You could put your common methods (usually the ones which don't contain the if-elseif statements) to a common abstract base class:

class AbstractDatabase implements Database {
    public function dbGetLastInsertID();
    ...
}

Then the two concrete implementations:

class MsSqlDatabase extends AbstractDatabase {
    ...
}

class MySqlDatabase extends AbstractDatabase {
    ...
}

And finally the factory method:

function createDatabase($type) {
    if ($type == "MSSQL") {
        return new MsSqlDatabase();
    } else if ($type == "MYSQL") {
        return new MySqlDatabase();
    } else {
        throw new Exception('Invalid type: ' . $type);
    }
}

If you use this pattern you could easily create new implementations (you only need for example a new PostgreSqlDatabase class and a new else if statement in the factory method) and you get rid of a lot of error-prone if-elseif statements.

---

One small thing:

function dbOK()
{
    if($dbType == "MYSQL") { ... }
    elseif ($dbType == "MSSQL") { ... }
    return false;
}

In similar cases instead of the last line (return false) log the error and throw an exception to the user or call a die() since it's an internal error.