I'm trying to improve my php oop programming skills. As an exercise I wrote this class that will generate and execute dynamic sql queries. I've also writed a class to call various sanitization filters that php has built in.
QueryBuilder.php class
<?php
class QueryBuilder{
private $db = null;
private $stmt;
private $table;
private $param;
private $cols, $columns;
private $holders, $placehold;
private $fields, $field;
public $data;
public $results;
public function __construct(\PDO $db){
$this->db = $db;
}
public function insert($table, array $data, array $columns){
$holders = $this->setHolders($columns);
$cols = $this->setColumns($columns);
$stmt = $this->db->prepare("INSERT INTO $table ($cols) VALUES ($holders)");
return $stmt->execute($data);
}
public function select($table, array $columns, $field, $param){
$cols = $this->setColumns($columns);
$stmt = $this->db->prepare("SELECT $cols FROM $table WHERE $field = ?");
$stmt->execute(array($param));
$result = $stmt->fetch();
return json_encode($result);
}
public function edit($table, array $columns, array $data, $param){
$fields = $this->setFields($columns);
$stmt = $this->db->prepare("UPDATE $table SET $fields WHERE $param = ?");
return $stmt->execute($data);
}
public function delete($table, array $data, $param){
$stmt = $this->db->prepare("DELETE FROM $table WHERE $param = ?");
return $stmt->execute($data);
}
private function setColumns(array $columns){
$cols = implode(', ', array_values($columns));
return $cols;
}
private function setFields(array $columns){
$fields = implode(' = ?, ', array_values($columns));
return $fields.' = ?';
}
private function setHolders(array $columns){
$holders = array_fill(1 ,count($columns),'?');
return implode(', ',array_values($holders));
}
}
?>
DataSanitizer.php class
<?php
class DataSanitizer{
private $value;
private $sanitized_value;
public function intSanitize(int $value){
$sanitized_value = filter_var($value, FILTER_SANITIZE_NUMBER_INT);
return $sanitized_value;
}
public function stringSanitize(string $value){
$sanitized_value = filter_var($value, FILTER_SANITIZE_STRING);
return $sanitized_value;
}
public function floatSanitize(float $value){
$sanitized_value = filter_var($value, FILTER_SANITIZE_NUMBER_FLOAT);
return $sanitized_value;
}
public function emailSanitize(string $value){
$sanitized_value = filter_var($value, FILTER_SANITIZE_EMAIL);
return $sanitized_value;
}
public function validateEmail(string $value){
$sanitized_value = filter_var($value, FILTER_VALIDATE_EMAIL);
return $sanitized_value;
}
}
?>
Classes usage example:
<?php
// Usaually i use the spl_autoloader_register();
require_once 'QueryBuilder.php';
reqiure_once 'DataSanitizer.php';
// this file holds the PDO connection stored inside the $db variable
require_once 'Config.php';
$query = new QueryBuilder($db);
$sanitize = new DataSanitizer;
$table = 'test_table';
$data = array($sanitize->stringSanitize('hello'),$sanitize->stringSanitize('world'));
$col = array('col1','col2');
$query->insert($table, $data, $col);
?>
