1
\$\begingroup\$

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 */
\$\endgroup\$
1
  • \$\begingroup\$ on a side note, in dbConnect you nest checks, ifs, and prints. It's more readable to do it this way if(!check) {print "stuff"; exit();}, and put your "all ok" code under the error checks. hence you have a nice flat readable code, and not a nested spagetti \$\endgroup\$
    – roselan
    Nov 18, 2011 at 12:05

1 Answer 1

2
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.