DaveMorton
DaveMorton
Reputation Top 5%
Dave Morton
I've been working in web design/development since the late 1990's, making interfaces and games for local dialup BBS boards.
1 Snippet
Published
1 Channel
Created
4 Channels
Following
63 points
Reputation
Junior Code Generator
Junior Publisher
Junior Popular Coder
Junior Autobiographer
Serious Autobiographer
Master Autobiographer

Recent Snippets See all snippets by DaveMorton

public by  915  2  5  1

connect.php - Database Abstraction Functions

<?php
/* 
 *  %[Filename]=[connect.php]%
 * This is a complete set of PDO abstraction functions
 * to make connecting, reading from and writing to a
 * MySQL database faster and easier. It's meant to be
 * used as a snippet in RapidPHP/WeBuilder from Karlis
 * Bluementals, but can easily be used in many other
 * web development IDEs. Items with %[something]% or
 * %[something]=[value]% are replaceable parameters
 * in RapidPHP/WeBuilder and can be swapped out with
 * whatever you like
 */

/* DB Credentials */

$dbh    = '%[Hostname]=[localhost]%';
$dbn    = '%[DB Name]%';
$dbu    = '%[Username]=[username]%';
$dbp    = '%[Password]=[password]%';
$dbPort = '%[DB Port]=[3306]%';

$db = db_open($dbh, $dbu, $dbp, $dbn, $dbPort);

/**
 * function db_open()
 * Connect to the database
 *
 * @internal param string $dbh    - db host
 * @internal param string $dbu    - db user
 * @internal param string $dbp    - db password
 * @internal param string $dbn    - db name
 * @internal param string $dbPort - db port
 * @return resource $dbConn - the database connection resource
 */
function db_open($dbh, $dbu, $dbp, $dbn, $dbPort=3306)
{
    try {
        $dbConn = new PDO("mysql:host=$dbh;dbname=$dbn;charset=utf8", $dbu, $dbp);
        $dbConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $dbConn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        $dbConn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
    }
    catch (Exception $e)
    {
        exit('The application has encountered a problem with connecting to the database. With any luck, the following message will help: ' . $e->getMessage());
    }
    return $dbConn;
}

/**
 * function db_close()
 * Close the connection to the database
 *
 * @internal param resource $dbConn - the open connection
 *
 * @return null
 */
function db_close()
{
    return null;
}

/*
 * function db_fetch_all
 * Fetches all matching rows of data from the database
 *
 * @param (string) $sql - The SQL query to execute
 * @param (mixed) $params - either an array of placeholder/value pairs, or null, for no parameters
 * @param (string) $file - the path/filename of the file that the function call originated in
 * @param (string) $function - the name of the function that the function call originated in
 * @param (string) $line - the line number of the originating function call
 *
 * @return (mixed) $out - Either the row of data from the DB query, or false, if the query fails
 */

function db_fetch_all($sql, $params = null, $file = 'unknown', $function = 'unknown', $line = 'unknown')
{
    global $dbConn;
    try
    {
        $sth = $dbConn->prepare($sql);
        ($params === null) ? $sth->execute() : $sth->execute($params);
        return $sth->fetchAll();
    }
    catch (Exception $e)
    {
        //error_log("bad SQL encountered in file $file, line #$line. SQL:\n$sql\n", 3, _LOG_PATH_ . 'badSQL.txt');
        $pdoError = print_r($dbConn->errorInfo(), true);
        $psError  = print_r($sth->errorInfo(), true);
        return false;
    }
}

/*
 * function db_fetch_row
 * Fetches a single row of data from the database
 *
 * @param (string) $sql - The SQL query to execute
 * @param (mixed) $params - either an array of placeholder/value pairs, or null, for no parameters
 * @param (string) $file - the path/filename of the file that the function call originated in
 * @param (string) $function - the name of the function that the function call originated in
 * @param (string) $line - the line number of the originating function call
 *
 * @return (mixed) $out - Either the row of data from the DB query, or false, if the query fails
 */
function db_fetch_row($sql, $params = null, $file = 'unknown', $function = 'unknown', $line = 'unknown')
{
    global $dbConn;
    //error_log(print_r($dbConn, true), 3, _LOG_PATH_ . 'dbConn.txt');
    try
    {
        $sth = $dbConn->prepare($sql);
        ($params === null) ? $sth->execute() : $sth->execute($params);
        $out = $sth->fetch();
        return $out;
    }
    catch (Exception $e)
    {
        //error_log("bad SQL encountered in file $file, line #$line. SQL:\n$sql\n", 3, _LOG_PATH_ . 'badSQL.txt');
        $pdoError = print_r($dbConn->errorInfo(), true);
        $psError  = print_r($sth->errorInfo(), true);
        return false;
    }
}

/*
 * function db_write
 * Writes and/or updates data to the database
 *
 * @param (string) $sql - The SQL query to execute
 * @param (mixed) $params - either an array of placeholder/value pairs, or null, for no parameters
 * @param (string) $file - the path/filename of the file that the function call originated in
 * @param (string) $function - the name of the function that the function call originated in
 * @param (string) $line - the line number of the originating function call
 *
 * @return (mixed) $out - Either the number of rows affected from the DB query, or false, if the query fails
 */

function db_write($sql, $params = null, $multi = false, $file = 'unknown', $function = 'unknown', $line = 'unknown')
{
    global $dbConn;
    try
    {
        $sth = $dbConn->prepare($sql);
        switch (true)
        {
            case ($params === null):
                $sth->execute();
                break;
            case ($multi === true):
                foreach ($params as $row)
                {
                    $sth->execute($row);
                }
                break;
            default:
                $sth->execute($params);
        }
        return $sth->rowCount();
    }
    catch (Exception $e)
    {
        $pdoError = print_r($dbConn->errorInfo(), true);
        $psError  = print_r($sth->errorInfo(), true);
        error_log("bad SQL encountered in file $file, line #$line. SQL:\n$sql\nPDO Error:\n$pdoError\nSTH Error:\n$psError\nEsception Message:\n" . $e->getMessage(), 3, _LOG_PATH_ . 'db_write.txt');
        return false;
    }
}
;