Skip to content

Instantly share code, notes, and snippets.

@luckyshot
Last active August 4, 2021 04:50
Show Gist options
  • Save luckyshot/9477105 to your computer and use it in GitHub Desktop.
Save luckyshot/9477105 to your computer and use it in GitHub Desktop.
PHP/MySQL (PDO Database) method with named parameters
<?php
/*
PHP/MySQL (PDO) method with named parameters
---------------------------------------------
https://gist.github.com/luckyshot/9477105
Last updated: 12 Sep 17
$config = [
'mysql' => [
'database' => 'database',
'user' => 'root',
'pass' => 'root'
],
];
$db = new DB(
$config['mysql']['user'],
$config['mysql']['pass'],
$config['mysql']['database']
);
$select = $db->query("SELECT * FROM items WHERE id = :id")
->bind(':id', $id)
->select();
$single = $db->query("SELECT * FROM items WHERE id = :id LIMIT 1")
->bind(':id', $id)
->single();
// Returns the inserted ID
$insert = $db->query("INSERT INTO items SET
aaa = :aaa,
bbb = :bbb
")
->bind(':aaa', $aaa )
->bind(':bbb', $bbb )
->insert();
$update = $db->query("UPDATE items SET
aaa = :aaa
WHERE id = :id;
")
->bind(':aaa', $aaa )
->bind(':id', $data['id'])
->execute();
$delete = $db->query("DELETE FROM items
WHERE id = :id
LIMIT 1;
")
->bind(':id', $data['id'])
->execute();
DEBUG:
echo "<pre>";var_export( $this->db->queries );echo "</pre>";
*/
class DB {
private $dbh;
private $stmt;
private $q;
public $queries = [];
public function __construct($user, $pass, $dbname) {
$this->dbh = new PDO(
"mysql:host=127.0.0.1;dbname=$dbname",
$user,
$pass,
array( PDO::ATTR_PERSISTENT => true )
);
$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->query("SET NAMES 'utf8';");
$this->execute();
}
public function query($query) {
$this->queries[] = $query;
$this->stmt = $this->dbh->prepare($query);
return $this;
}
public function bind($pos, $value, $type = null) {
if ( is_numeric( $value ) ){ $this->queries[ count($this->queries)-1 ] = str_replace( $pos, $value, $this->queries[ count($this->queries)-1 ] ); }
else{ $this->queries[ count($this->queries)-1 ] = str_replace( $pos, '"' . $value . '"', $this->queries[ count($this->queries)-1 ] ); }
if ( is_null($type) ) {
switch( true ) {
case is_int($value):
$type = PDO::PARAM_INT;
break;
case is_bool($value):
$type = PDO::PARAM_BOOL;
break;
case is_null($value):
$type = PDO::PARAM_NULL;
break;
default:
$type = PDO::PARAM_STR;
}
}
$this->stmt->bindValue($pos, $value, $type);
return $this;
}
public function execute() {
try{
$result = $this->stmt->execute();
}catch(PDOException $e){
echo '<pre>';
var_export( $e );
var_export( $this->dbh->errorInfo() );
var_export( $this->queries );
}
return $result;
}
public function update() {
return $this->execute();
}
public function delete() {
return $this->execute();
}
// Same as execute() but returns ID
public function insert() {
$this->execute();
return $this->dbh->lastInsertId();
}
public function select( $fetch_style = null ) {
$this->execute();
return $this->stmt->fetchAll( PDO::FETCH_ASSOC );
}
public function single( $fetch_style = null ) {
$this->execute();
return $this->stmt->fetch( PDO::FETCH_ASSOC );
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment