pdo connection class with insert select program

pdo connection class with insert  select program

If you came here just looking for the replacement to the good old mysql_query function to be used in your all-procedural code, it's ok to use an otherwise despised Singleton pattern.

<?php
define('DB_HOST', 'localhost');
define('DB_NAME', 'al_murtaza');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_CHAR', 'utf8');

class DB
{
    protected static $instance = null;

    protected function __construct() {}
    protected function __clone() {}

    public static function instance()
    {
        if (self::$instance === null)
        {
            $opt  = array(
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => FALSE,
            );
            $dsn = 'mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset='.DB_CHAR;
            self::$instance = new PDO($dsn, DB_USER, DB_PASS, $opt);
        }
        return self::$instance;
    }

    public static function __callStatic($method, $args)
    {
        return call_user_func_array(array(self::instance(), $method), $args);
    }

    public static function run($sql, $args = [])
    {
        if (!$args)
        {
             return self::instance()->query($sql);
        }
        $stmt = self::instance()->prepare($sql);
        $stmt->execute($args);
        return $stmt;
    }
}?>

just put this code in the file called, say, database.php, according to think put it somewhere in your site and don't forget to include it anywhere you need a database.

Examples

The examples below demonstrate the use of the singleton approach above. The code is ready to use. You may copy and paste the code below and run it as is. Just don't forget to create a 'database.php' .

<?php
//require_once 'database.php';

# Table creation
DB::query("CREATE temporary TABLE pdowrapper (id int auto_increment primary key, name varchar(255))");

# Prepared statement multiple execution
$stmt = DB::prepare("INSERT INTO pdowrapper VALUES (NULL, ?)");
foreach (['Sam','Bob','Joe'] as $name)
{
    $stmt->execute([$name]);
}
var_dump(DB::lastInsertId());
//string(1) "3"

# Getting rows in a loop
$stmt = DB::run("SELECT * FROM pdowrapper");
while ($row = $stmt->fetch(PDO::FETCH_LAZY))
{
    echo $row['name'],",";
    echo $row->name,",";
    echo $row[1], PHP_EOL;
}
/*
Sam,Sam,Sam
Bob,Bob,Bob
Joe,Joe,Joe
*/

# Getting one row
$id  = 1;
$row = DB::run("SELECT * FROM pdowrapper WHERE id=?", [$id])->fetch();
var_export($row);
/*
array (
  'id' => '1',
  'name' => 'Sam',
)
*/

# Getting single field value
$name = DB::run("SELECT name FROM pdowrapper WHERE id=?", [$id])->fetchColumn();
var_dump($name);
//string(3) "Sam"

# Getting array of rows
$all = DB::run("SELECT name, id FROM pdowrapper")->fetchAll(PDO::FETCH_KEY_PAIR);
var_export($all);
/*
array (
  'Sam' => '1',
  'Bob' => '2',
  'Joe' => '3',
)
*/

# Update
$new = 'Sue';
$stmt = DB::run("UPDATE pdowrapper SET name=? WHERE id=?", [$new, $id]);
var_dump($stmt->rowCount());

//int(1)?>