**EDIT I asked this question yesteday when I was unregistered, and now I can't seem to comment on the question or mark the answer as correct but I would like to say thank you for taking the time to write the answer, I found it very helpful and informative and have implemented as many of the changes as I can (I still think its abit wet in places) **
I followed this tutorial to create an sql "factory" http://tomszuszai.com/content/articles/tutorials/how_to_write_a_simple_sql_factory_in_php
The link shows how to make a class who's methods will output sql statements based on the arguments you pass to it, and i've expanded on it with the class below.
Basically as an example of its usage if you wanted to select from a database you can pass it an array of attributes to select, followed by the table name, and if there are conditions to the select you pass it an array where the array key is the column the condition is based on, and the array value is the value of that column - so a simple select would look something like this:
$theAttributes = array('name','age','height')
$theTable = 'people';
$theConditions = array('hair_colour' => 'brown');
$select = $this->executeSelect($theAttributes,$theTable,$theConditions);
and then $insert[0] would be the number of rows that were selected by the query, and $insert[1] would be an array containing each row that was returned as an array.
I'm just wondering if I could get some feedback on it as it seems to work fine for me, but I'm very new to php so some advice such as is this very inefficient, insecure, just plain wrong etc, would be much appreciated thanks!
<?php
require './config/config.inc.php';
require './config/n_spaces.inc.php';
require_once(__DIR__.'/SqlStatement.php');
require_once(__DIR__.'/MySqlStatement.php');
class PDOdriver {
public $conn;
public function __construct()
{
$db_host = DB_HOST;
$dbname = DB_NAME;
$this->conn = new PDO("mysql:host=$db_host;dbname=$dbname",DB_USER,DB_PASS)
or die('Cannot connect to the server, please inform your Network Administrator');
$this->st = new MySqlStatement();
}
public function executeInsert($theAttributes,$theTable,$theConditions = NULL) {
foreach($theAttributes as $index => $value) {
$bind_params[] = ":$index";
}
if(!isset($theConditions)){
$sqlstmt = trim($this->st->setTables($theTable)->setAttributes(array_keys($theAttributes))->setValues($bind_params)->makeInsert());
}
else {
$sqlstmt = trim($this->st->setTables($theTable)->setAttributes(array_keys($theAttributes))->setValues($bind_params)->setConditions($theConditions)->makeInsert());
}
if($stmt = $this->conn->prepare($sqlstmt)) {
foreach($theAttributes as $index => &$value) {
$stmt->bindParam(":$index",$value);
}
if($stmt->execute()) {
$rowCount = $stmt->rowCount();
$success[0] = $rowCount;
$success[1] = $this->conn->lastInsertId();
return $success;
}
else {
$success[0] = 'Cannot Execute';
$stmt = NULL;
return $success;
}
}
else {
$success[0] = 'Cannot Prepare';
$stmt = NULL;
return $success;
}
}
public function executeSelect($theAttributes,$theTable,$theConditions = NULL,$fetch_style = "FETCH_ASSOC") {
if(is_array($theConditions)) {
foreach($theConditions as $index => $value){
$bind_conditions[] = "$index = :$index";
}
$sqlstmt = trim($this->st->setAttributes($theAttributes)->setTables($theTable)->setConditions($bind_conditions)->makeSelect());
}
elseif(!is_null($theConditions) && !is_array($theConditions)){
$sqlstmt = trim($this->st->setAttributes($theAttributes)->setTables($theTable)->setConditions($theConditions)->makeSelect());
}
else {
$sqlstmt = trim($this->st->setAttributes($theAttributes)->setTables($theTable)->makeSelect());
}
if($stmt = $this->conn->prepare($sqlstmt)) {
if(is_array($theConditions)) {
foreach($theConditions as $index => &$value) {
$stmt->bindParam(":$index",$value);
}
}
if($stmt->execute()) {
$success[0] = $stmt->rowCount();
if($success[0] > 0) {
if($fetch_style === "FETCH_ASSOC") {
while($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
$results[] = $result;
}
$success[1] = $results;
}
elseif($fetch_style === "FETCH_BOTH"){
while($result = $stmt->fetch(PDO::FETCH_BOTH)) {
$results[] = $result;
}
$success[1] = $results;
}
elseif($fetch_style === "COUNT"){
}
}
$stmt = NULL;
return $success;
}
else {
$success[0] = 'Cannot Execute';
$stmt = NULL;
return $success;
}
}
else {
$success[0] = 'Cannot Prepare';
$stmt = NULL;
return $success;
}
}
public function executeUpdate($theAttributesColumn,$theAttributesValue,$theTable,$theConditions)
{
if(is_array($theConditions)) {
foreach($theConditions as $index => $value){
$bind_conditions[] = "$index = :$index";
}
$sqlstmt = trim($this->st->setUpdateAttributes($theAttributesColumn, $theAttributesValue)->setTables($theTable)->setConditions($bind_conditions)->makeUpdate());
}
else {
$sqlstmt = trim($this->st->setUpdateAttributes($theAttributesColumn, $theAttributesValue)->setTables($theTable)->makeUpdate());
}
if($stmt = $this->conn->prepare($sqlstmt)) {
if(is_array($theConditions)) {
foreach($theConditions as $index => &$value) {
$stmt->bindParam(":$index",$value);
}
}
if($stmt->execute()) {
$success[0] = $stmt->rowCount();
return $success;
}
else {
$success[0] = 'Cannot Execute';
$stmt = NULL;
return $success;
}
}
else {
$success[0] = 'Cannot Prepare';
$stmt = NULL;
return $success;
}
}
}
?>
