Functions

Functions WebDev/*nix assorted functions & hacks

MySQL fetch_field(), fetch_row(), fetch_rows(), mres() query($sql) Unicode Unicode configuration You forgot your MySQL root password AGAIN 5 found

auth

Name:
Category: MySQL
Body:

// connect_db($database = '', $file = false, $reconnect = false) /
function connect_db_old($database = '', $file = false, $reconnect = false)
{
    static $connection = false;
    if ($connection && ! $reconnect) return $connection;
    if (! $database) $database = DB_DATABASE;
    // creating db connection. try/catch to avoid it displaying stack trace, which can include password.
    try {
        if ($file) {
            $connection = new \PDO('sqlite:'.$database);
        } else {
            $connection = new \PDO('mysql:host='.DB_HOST.';dbname='.$database.';charset=utf8', DB_USERNAME, DB_PASSWORD);
        }
        return $connection;
    } catch (Exception $e) {
        echo $e->getMessage();
        return false;
    }
}

// todo: rewrite so it can accept credentials for non-standard/random dbs, hosts, user/pass etc? $creds array(5) as optional arg
//       set local variables based on either the constants or that $creds array
function connect_db_new($database = '', $file = false, $reconnect = false)
{
    if (! defined('DB_USER')) {
        echo styled('  Boooh! insufficient db constants to connect');
        return false;
    }
    static $connection = false;
    $options = array();
    if (defined('CA_CERT') and CA_CERT) {
        if (! file_exists(CA_CERT) || ! is_readable(CA_CERT)) throw new Exception("SSL Certificate file (CA_CERT) set but can't be found or read: ".CA_CERT);
        $options[PDO::MYSQL_ATTR_SSL_CA] = CA_CERT;
        $options[PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT] = false; // see https://stackoverflow.com/a/68547582/126584 (sigh)
        $options[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES utf8';
    }
    if (is_object($database) and get_class($database) === 'PDO') $connection = $database; // who knows!
    if ($connection && ! $reconnect) return $connection;
    if (! $database) $database = DB_NAME;
    if (is_numeric(strpos($database, '%'))) $database = '';
    // creating db connection. try/catch to avoid it displaying stack trace, which can include password.
    try {
        if ($file) {
            $connection = new \PDO('sqlite:'.$database);
        } else {
            $connection = new \PDO('mysql:host='.DB_HOST.';dbname='.$database.';charset=utf8', DB_USER, DB_PASS, $options);
        }
        return $connection;
    } catch (Exception $e) {
        echo $e->getMessage();
        return false;
    }
}

// fetch_field($sql)
function fetch_field($sql) {
	$conn = connect_db();
	return ($result = $conn->query($sql)->fetch(PDO::FETCH_ASSOC)) ? array_shift($result) : $result;
}

// fetch_row($sql)
function fetch_row($sql) {
	$conn = connect_db();
	return $conn->query($sql)->fetch(PDO::FETCH_ASSOC);
}

// fetch_rows($sql)
function fetch_rows($sql) {
	$conn = connect_db();
	$data = [];
	$stmt = $conn->query($sql);
	while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) $data[] = $row;
	return $data;
}

// fetch_column($sql)
function fetch_column($sql) {
    $rows = fetch_rows($sql);
    $data = array();
    foreach ($rows as $row) {
        $data[] = current($row);
    }
    return $data;
}

// mres($string)
function mres($string) {
	$conn = connect_db();
	return $conn->quote($string);
}

// query($sql)
function query($sql) {
    $db = connect_db();
    return $db->query($sql); // whatever *that* may be, do with it what you want.
}

// database_exists($db)
function database_exists($db) {
    static $dbs = false;
    if (! $dbs) {
        $dbs = fetch_column('show databases');
    }
    return in_array($db, $dbs);
}

Footer

~~Functions~~ WebDev/nix assorted functions & hacks Search* find all Array - Bash - Classes - Config - DateTime - Debug - dotjs - File - foooo - Git - HTML - Images - Javascript - Linux - Math - Misc - MySQL - OS X - Reading - Redis - Shell scripts - snippets - SQL - Strings - TextMate - Web - \(^o^)/ Fun! - _Misc hacks - _Pages
MySQL fetch_field(), fetch_row(), fetch_rows(), mres() query($sql) Unicode Unicode configuration You forgot your MySQL root password AGAIN 5 found auth	Name: Category: MySQL Body: // connect_db($database = '', $file = false, $reconnect = false) / function connect_db_old($database = '', $file = false, $reconnect = false) { static $connection = false; if ($connection && ! $reconnect) return $connection; if (! $database) $database = DB_DATABASE; // creating db connection. try/catch to avoid it displaying stack trace, which can include password. try { if ($file) { $connection = new \PDO('sqlite:'.$database); } else { $connection = new \PDO('mysql:host='.DB_HOST.';dbname='.$database.';charset=utf8', DB_USERNAME, DB_PASSWORD); } return $connection; } catch (Exception $e) { echo $e->getMessage(); return false; } } // todo: rewrite so it can accept credentials for non-standard/random dbs, hosts, user/pass etc? $creds array(5) as optional arg // set local variables based on either the constants or that $creds array function connect_db_new($database = '', $file = false, $reconnect = false) { if (! defined('DB_USER')) { echo styled(' Boooh! insufficient db constants to connect'); return false; } static $connection = false; $options = array(); if (defined('CA_CERT') and CA_CERT) { if (! file_exists(CA_CERT) \|\| ! is_readable(CA_CERT)) throw new Exception("SSL Certificate file (CA_CERT) set but can't be found or read: ".CA_CERT); $options[PDO::MYSQL_ATTR_SSL_CA] = CA_CERT; $options[PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT] = false; // see https://stackoverflow.com/a/68547582/126584 (sigh) $options[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES utf8'; } if (is_object($database) and get_class($database) === 'PDO') $connection = $database; // who knows! if ($connection && ! $reconnect) return $connection; if (! $database) $database = DB_NAME; if (is_numeric(strpos($database, '%'))) $database = ''; // creating db connection. try/catch to avoid it displaying stack trace, which can include password. try { if ($file) { $connection = new \PDO('sqlite:'.$database); } else { $connection = new \PDO('mysql:host='.DB_HOST.';dbname='.$database.';charset=utf8', DB_USER, DB_PASS, $options); } return $connection; } catch (Exception $e) { echo $e->getMessage(); return false; } } // fetch_field($sql) function fetch_field($sql) { $conn = connect_db(); return ($result = $conn->query($sql)->fetch(PDO::FETCH_ASSOC)) ? array_shift($result) : $result; } // fetch_row($sql) function fetch_row($sql) { $conn = connect_db(); return $conn->query($sql)->fetch(PDO::FETCH_ASSOC); } // fetch_rows($sql) function fetch_rows($sql) { $conn = connect_db(); $data = []; $stmt = $conn->query($sql); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) $data[] = $row; return $data; } // fetch_column($sql) function fetch_column($sql) { $rows = fetch_rows($sql); $data = array(); foreach ($rows as $row) { $data[] = current($row); } return $data; } // mres($string) function mres($string) { $conn = connect_db(); return $conn->quote($string); } // query($sql) function query($sql) { $db = connect_db(); return $db->query($sql); // whatever that may be, do with it what you want. } // database_exists($db) function database_exists($db) { static $dbs = false; if (! $dbs) { $dbs = fetch_column('show databases'); } return in_array($db, $dbs); }
Footer