PHP Simple PDO class
Here SimplePdo class I use for most my projects, even with orm mappers, because sometimes it is much easier to use raw SQL with lazy loading row by row. Very useful for big tables and cron jobs.
<?php
class SimplePdo
{
protected static $instance = null;
public function __construct() {}
public 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='.config('database.host').';dbname='.config('database.database').';charset=utf8';
self::$instance = new \PDO($dsn, config('database.username'), config('database.connections.password'), $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 = [])
{
$stmt = self::instance()->prepare($sql);
$stmt->execute($args);
return $stmt;
}
}
Where config is fancy function to get your config. Change to whatever config setup you have.
Select some stuff to do in loop (lazy load)
<?php
$stmt = SimplePdo::run("SELECT * FROM my_table");
while ($row = $stmt->fetch()) {
//do some stuff
}
<?php
$stmt = SimplePdo::run("SELECT * FROM my_table WHERE id > ?", [$lastId]);
while ($row = $stmt->fetch()) {
//do some stuff
}
Select some stuff to array (load all rows to variable)
<?php
$data = SimplePdo::run("SELECT * FROM my_table WHERE my_type=?", [$type])->fetchAll();
//with prepare
$stmt = SimplePdo::prepare("SELECT * FROM my_table WHERE my_type=?");
$stmt->execute([$type]);
$data = $stmt->fetchAll();
Select one row
<?php
$id = 1;
$row = SimplePdo::run("SELECT * FROM my_table WHERE id=? LIMIT 1", [$id])->fetch();
var_dump($row);
Select single field value
<?php
$name = SimplePdo::run("SELECT name FROM my_table WHERE id=? LIMIT 1", [$id])->fetchColumn();
var_dump($name);
Update some stuff
<?php
$new = 'Sue';
$stmt = SimplePdo::run("UPDATE my_table SET my_name=? WHERE id=?", [$new, $id]);
var_dump($stmt->rowCount());
//with prepare
$stmt = SimplePdo::prepare("UPDATE my_table SET my_name=? WHERE id=?");
$stmt->execute([$new, $id]);
$data = $stmt->rowCount();
Getting associative array from two columns (similar to pluck in laravel)
<?php
$all = SimplePdo::run("SELECT key, value FROM my_table")->fetchAll(PDO::FETCH_KEY_PAIR);
var_dump($all);
/*
array (
'key1' => 'value1',
'key2' => 'value2',
)
*/
Getting plain array from one column (similar to pluck in laravel)
<?php
$all = SimplePdo::run("SELECT id FROM my_table")->fetchAll(PDO::FETCH_COLUMN, 0);
var_dump($all);
/*
array (1,2,3,4,5)
*/
Getting comma separated values from one column (similar to pluck + implode in laravel)
<?php
$all = SimplePdo::run("SELECT GROUP_CONCAT(id) FROM `mytable` WHERE id < 4")->fetchColumn();
var_dump($all);
/*
string ('1,2,3')
*/
Getting array of rows with id as key
<?php
$all = SimplePdo::run("SELECT id, slug, title FROM `mytable` WHERE id < 4")->fetchAll(\PDO::FETCH_UNIQUE);
var_dump($all);
/*
[
1 => [
"slug" => "first_row_slug",
"title" => "first row title",
],
2 => [
"slug" => "second_row_slug",
"title" => "second row title",
],
3 => [
"slug" => "third_row_slug",
"title" => "third row title",
],
]
*/
Note that first column id should be unique and it is gotten eaten. If you need it, make query to select id two times
<?php
$all = SimplePdo::run("SELECT id, id, slug, title FROM `mytable` WHERE id < 4")->fetchAll(\PDO::FETCH_UNIQUE);
var_dump($all);
/*
[
1 => [
"id" => 1,
"slug" => "first_row_slug",
"title" => "first row title",
],
2 => [
"id" => 2,
"slug" => "second_row_slug",
"title" => "second row title",
],
3 => [
"id" => 3,
"slug" => "third_row_slug",
"title" => "third row title",
],
]
*/