<?php
/**
* This class can make an Add, Modify and Delete queries very simple.
*
*/
class Amd extends Error {
//private vars
var $db = null;
var $table = null;
var $sql = '';
var $metadata = array();
var $primary_key = array();
var $auto_increment = array();
function Amd(){
$this->db = new Database();
}
/**
* Private function to get table metadata...
*
* @param string $table
*/
function load_table_metadata($table){
if($table == ''){
$this->error("Invalid table: [$table]");
return false;
}
if($table == $this->table){
//ops, alrady got metadata...
return true;
}
$this->table = trim($table);
$sql = "SHOW COLUMNS FROM {$this->table}";
$this->db->return_as_object = false;
$metadata = $this->db->get_all($sql);
$this->primary_key = array();
$this->auto_increment = array();
foreach ($metadata as $values){
$this->metadata[] = $values['Field'];
if($values["Key"] == "PRI"){
$this->primary_key[$values['Field']] = true;
}
if(strstr($values["Extra"], 'auto_increment')){
$this->auto_increment[$values['Field']] = true;
}
}
return true;
}
/**
* Private funtion to zanitize values...
*
* @param string $field
*/
function zanitization($field, $value){
//echo $this->metadata[$field]['Type'];
$arr_tmp = explode('(',$this->metadata[$field]['Type']);
switch ($arr_tmp[0]) {
case 'int':
case 'bigint':
case 'tinyint':
case 'smallint':
case 'mediumint':
$value = intval($value);
break;
case 'float':
case 'double':
$value = floatval($value);
break;
case 'enum':
$enum_values = str_replace(')','',$arr_tmp[1]);
$enum_values = str_replace("'",'',$enum_values);
$enum_values = split(',',$enum_values);
//check if it's a strange value...
if(!in_array($value, $enum_values)){
//ops!! lets put the default value or the first enum value...
$value = ($this->metadata[$field]['Default']<>null)? $this->metadata[$field] : $enum_values[0];
}
break;
default:
//convert all but double and single quotes!
$value = htmlentities($value, ENT_COMPAT, 'ISO-8859-15'); //you can try 'UTF-8' insted of 'ISO-8859-15'
//quote string with slashes
if(!get_magic_quotes_gpc()) $value = addslashes($value);
break;
}
return $value;
}
/**
* is it an insert or update?
*
* @param array $arr_user
* @return string update|insert
*/
function analize($arr_user, $table){
if(!$this->load_table_metadata($table))
return false;
$counter = 0;
//get the private key
foreach ($this->primary_key as $key => $tmp){
if(isset($arr_user[$key]) and ( intval($arr_user[$key])>0 or strlen($arr_user[$key])>0 ) ){
$counter++;
}
}
if($counter == count($this->primary_key))
//got all primary key: it's an update!!!
return 'update';
//by default is an insert...
return 'insert';
}
/**
* store data into database (either is an update or insert)
*
* @param unknown_type $arr_user
* @param unknown_type $table
* @return unknown
*/
function set($arr_user, $table=''){
$action = $this->analize($arr_user, $table);
if($action == 'insert')
return $this->insert($arr_user, $table);
elseif ($action == 'update')
return $this->update($arr_user, $table);
return false;
}
/**
* Execute an update query with $arr_user data
*
* @param array $arr_user
* @return true|false
*/
function update($arr_user, $table=''){
if(!$this->load_table_metadata($table))
return false;
$arr_where = array();
$arr_val = array();
//parse $arr_user data
foreach ($this->metadata as $field) {
if(isset($arr_user[$field])){
//is it a primary key?
if(isset($this->primary_key[$field])){
$arr_where[] = "{$this->table}.$field='" . $this->zanitization($field, $arr_user[$field]) . "'";
}else{
$arr_val[] = "{$this->table}.$field='" . $this->zanitization($field, $arr_user[$field]) . "'";
}
}
}
$this->sql = "UPDATE {$this->table} SET " . join(', ',$arr_val);
$this->sql.= " WHERE " . join(' and ', $arr_where);
return $this->db->query($this->sql);
}
/**
* Execute an insert query with $arr_user data
*
* @param array $arr_user
* @return true|false
*/
function insert($arr_user, $table=''){
if(!$this->load_table_metadata($table))
return false;
$arr_key = array();
$arr_val = array();
//parse $arr_user data
foreach ($this->metadata as $field) {
//avoid overwrite an auto-increment field
if(!isset($this->auto_increment[$field])){
$arr_key[] = $field;
$arr_val[] = "'".$this->zanitization($field, $arr_user[$field])."'";
}
}
$this->sql = "INSERT INTO {$this->table} (" . join(',',$arr_key);
$this->sql.= ') VALUES (' . join(', ',$arr_val) . ');';
$this->db->query( $this->sql );
return $this->db->insert_id;
}
/**
* Execute a delete query with $arr_user data
*
* @param array $arr_user
* @return true|false
*/
function delete($arr_user, $table = ''){
if(!$this->load_table_metadata($table))
return false;
$arr_where = array();
$this->sql = '';
foreach ($this->primary_key as $field => $tmp){
if(isset($arr_user[$field]) and strlen($arr_user[$field])){
$arr_where[] = "{$this->table}.$field='" . $this->zanitization($field, $arr_user[$field]) . "'";
}
}
if( count($arr_where) )
$this->sql = "DELETE FROM {$this->table} WHERE " . join(' and ', $arr_where);
return $this->db->query($this->sql);
}
}
?>
|
Stumble It!
Bookmark in del.icio.us
