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  (711st place)
Published
1 Channel
Created
4 Channels
Following
Aug 7, 2018
Last Visit
Mar 4, 2018
Registered
63 points  (420th place)
Reputation
Junior Code Generator
Junior Publisher
Junior Popular Coder
Junior Autobiographer
Serious Autobiographer
Master Autobiographer

Recent Snippets See all snippets by DaveMorton

public by DaveMorton created Apr 2, 2018  623  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;
    }
}
;