Класс для работы с БД
- Автор темы phillip
- Дата начала
CrashX
В прошлом XSiteCMS
- Регистрация
- 6 Июн 2008
- Сообщения
- 682
- Реакции
- 114
если есть оборертка то можно хоть на фаилах делать
у меня щас благодаря обреркет есть MySQL,MySQLi,MSSQL,MariaDB,PG
ну PDO чуть позже тк в моем случае нужна скорость разработки, оптимизция если попутная, класс подменить всегда можно в котром будет обертка для PDO )
у меня щас благодаря обреркет есть MySQL,MySQLi,MSSQL,MariaDB,PG
ну PDO чуть позже тк в моем случае нужна скорость разработки, оптимизция если попутная, класс подменить всегда можно в котром будет обертка для PDO )
denover
Постоялец
- Регистрация
- 5 Июн 2008
- Сообщения
- 56
- Реакции
- 7
Вот самописный класс для работы с базой. Может кому будет интересно.
PHP:
<?php
// filename: db_mysqli_new.php
//
// TEST CASE
//
// TEST 1: Connection creation
//$objDB = DB_MYSQLi::getInstance();
//tester('TEST 1: Connection creation', $objDB);
// TEST 2: Statment Object instance
//$objStmt = DB_MYSQLi_Prepare_Statment::getInstance();
//tester('TEST 2: Statment Object instance', $objStmt);
// TEST 3: Stetment generation preparetion
//$sql_select_jobs = $objStmt->buildSelectQuery('sessions',array(), 5);
//tester('TEST 3: Stetment generation preparetion', $sql_select_jobs);
// TEST 4: Execute SQL stetmant and retrieve results
//$arr_results = $objDB->query($sql_select_jobs);
//tester('TEST 4: Execute SQL stetmant and retrieve results', $arr_results->fetchArray_result() );
class Stat_Count
{
static function countme($ret = false) {
static $numm;
if ($ret == true) return $numm;
$numm++;
}
}
/**
* array $glb_connection
*
* @desc hold DB connection information
* @todo move to separete db_config file
*/
class DB_MYSQLi
{
/**
* @desc implements the singelton
*/
private static $instance;
private $dblink = null;
private $errors = array();
private $debug = true;
/**
* @desc
* array(
'hostname'=>''
,'username'=>''
,'passwors'=>''
,'database'=>''
)
*/
private function __construct ( $conn = array() )
{
}
public function connect ($conn = array() )
{
//$this->_connection = $conn;
$this->dblink = @mysqli_connect (
$conn['hostname'],
$conn['username'],
$conn['password'],
$conn['database']) or die('Connect Error');
// TODO validate if connection created
// ....
if (mysqli_connect_errno()) {
die(mysqli_connect_error());
exit();
}
//mysqli_query($this->dblink, "SET NAMES utf8;");
$this->query( "SET NAMES utf8;" );
//mysqli_set_charset($this->dblink, "utf8");
}
public static function getInstance()
{
if ( !self::$instance ) {
self::$instance = new DB_MYSQLi( );
self::$instance->connect( getDBConnectionParams() );
}
return self::$instance;
}
public function getDBLink()
{
return $this->dblink;
}
public static function getDBConnection()
{
if (self::$instance) {
return self::$instance->getDbLink();;
}
return false;
}
/**
* sends a query to mysql
* Example:
* $objDb->query ('SELECT * FROM ? WHERE id=?', array('users', $user_id));
*
* @access public
* @param string $strSQL Contains the SQL query which shall be executed
* @param optional array $params
*
* @return boolean return false on sql error
*/
function query($strSQL = '', $params=array(), $return_n_q = false)
{
if (strlen($strSQL) < 4)
return false;
/*
check if $param is set ,
replace '?' with values
*/
if (count($params) > 0) {
$strSQL = DB_MYSQLi_Prepare_Statment::replaceQuestionMarks($strSQL, $params);
}
//tester('$strSQL', $strSQL);
//print_r($this->_dblink->error);
//$this->_result = mysql_query($strSQL, $this->_dblink) or die( mysql_error() );
$last_results = mysqli_query($this->dblink, $strSQL);
//tester('$last_results', $last_results);
//TODO: The $last_results NOT ALL TIME OBJECT. IT OBJECT OR BULLEAN !!!
if ( mysqli_error($this->dblink) ) {
$this->_setError('FAIL SQL: '.$strSQL.'<br />'.mysqli_error($this->dblink));
//return $return_n_q;
}
//print_r( $strSQL);
//return ;
Stat_Count::countme();
//return $this->getLastId();
//$result = new DB_MYSQLi_Query($last_results , mysqli_insert_id($this->dblink) );
//tester('$result',$result->getNumRows());
return new DB_MYSQLi_Query($last_results , mysqli_insert_id($this->dblink) );;
}
/**
* Records errors that was made
*
* @access private
* @param string $str error messages
*/
function _setError ($str) {
$this->_errors[] = $str;
if ( $this->debug )
print $str.'<br />';
}
}
class DB_MYSQLi_Query
{
private $rs_query;
private $last_insert_id;
function __construct ( $rs_query=false, $last_insert_id = 0 )
{
if (is_object($rs_query))
$this->rs_query = $rs_query;
if ( is_int($last_insert_id) )
$this->last_insert_id = $last_insert_id;
}
/**
* Returns number of rows after SELECT commanfd
*
* @return integer number of rows
*/
public function getNumRows()
{
if (is_object($this->rs_query) )
return $this->rs_query->num_rows;
else
return 0;
}
/**
* Get last inserted id after insert statement
*
* @access public
* @return integer last inserted id
*/
function getLastId()
{
return $this->last_insert_id;
}
/**
* returs assiciative array of records keys and values !!! one record !!!
*
* @access public
* @return array
*/
function fetchArray()
{
// return mysqli_fetch_assoc($this->rs_query);
return $this->rs_query->fetch_assoc();
}
/**
* @desc return assiciative array of records keys and values
* @access public
* @return array
*/
function fetchArray_result( $idsValueFoKey = array() )
{
$result = array();
/*if ( 1 == $this->getNumRows() )
return $this->fetchArray();*/
while( $res = $this->fetchArray() ) {
if( !count($idsValueFoKey) ){
$result[] = $res;
}else{
$key_name = '';
foreach( $idsValueFoKey as $key ){
$key_name.= "['".$res[$key]."']";
}
//var_dump( $key_name );
eval('$result'.$key_name.'=$res;');
}
}
return $result;
}
function fetchArray_result_stac($key_field='', $val_field='', $prepend_key=false)
{
$result = array();
if (!empty($key_field) && !empty($val_field))
{
if (true == $prepend_key)
{
while( $res = $this->fetchArray() ) {
$result[$res[$key_field]] = $res[$key_field].' :: '.$res[$val_field];
}
} else {
while( $res = $this->fetchArray() ) {
$result[$res[$key_field]] = $res[$val_field];
}
}
} elseif(!empty($key_field)) {
while( $res = $this->fetchArray() ) {
$result[$res[$key_field]] = $res;
}
} else {
while( $res = $this->fetchArray() ) {
$result[] = $res;
}
}
return $result;
}
}
class DB_MYSQLi_Prepare_Statment
{
private static $instance;
private function __construct()
{
}
public static function getInstance()
{
if ( !self::$instance ) {
self::$instance = new DB_MYSQLi_Prepare_Statment();
}
return self::$instance;
}
/**
* Builds SQL query for SELECT WHERE command from array.
*
* @param string $tablename
* @param array $arrData
* @param string $implode AND or OR
* @param array $orderby field=>asc/desc
*
* @access public
* @return unknown
*/
function buildSelectQuery($tablename = '', $arrData = array(), $limit=false, $implode = ' AND ', $orderby = array(), $limitfrom=false, $SelectFields = array() )
{
/*
empty params check & tablename at least 3 chars
*/
// if (0 == strlen($tablename) || 0 == count($arrData))
if (0 == strlen($tablename))
return false;
//$this->query( 'SHOW COLUMNS FROM ?', array($tablename) );
//$objRSQuery = DB_MYSQLi::getInstance()->query( 'SHOW COLUMNS FROM ?', array($tablename) );
$objRSQuery = DB_MYSQLi::getInstance()->query( 'SHOW COLUMNS FROM '.$tablename.'' );
//$res = $this->fetchArray_result();
if ( false === $objRSQuery )
return false;
$where = array();
$table_fields = array();
$strSQL = "";
while( $value = $objRSQuery->fetchArray() )
{
$table_fields[] = $value['Field'];
//tester('$value[\'Field\']', $value['Field']);
if ( !array_key_exists($value['Field'], $arrData) )
{
continue;
}
if( !is_array( $arrData[$value['Field']] ) ){
if( is_numeric($arrData[$value['Field']]) )
$where[] = '`' . $value['Field'] . '` = \''.self::_escapeString ( $arrData[$value['Field']] ).'\'';
else
$where[] = '`' . $value['Field'] . '` LIKE \''.self::_escapeString ( $arrData[$value['Field']] ).'\'';
}else{
$mix = array();
foreach( $arrData[$value['Field']] as $mix_value ){
$mix[] = self::_escapeString ( $mix_value );
}
$where[] = '`' . $value['Field'] . '` IN (\''.implode( "', '", $mix ).'\')';
}
}
$selct_fields = '*';
if( count($SelectFields) ){
$selct_fields = implode(',', $SelectFields);
}
if( count($where) )
{
$where_list = implode($implode, $where);
$strSQL = 'SELECT '.$selct_fields.' FROM '.$tablename.' WHERE '.$where_list.'';
//print $strSQL;
}
else
{
$strSQL = 'SELECT '.$selct_fields.' FROM '.$tablename.'';
}
//ORDER BY
//var_dump($orderby);
$order_by_list = array();
if( is_array($orderby) && count($orderby) )
{
$order_list = '';
foreach( $orderby as $field=>$order )
{
if( in_array($field, array('rand()') ) ){
$order_by_list[] = $field;
}
elseif ( !in_array($field, $table_fields) )
{
continue;
}else{
$order_by_list[] = '`'.$field.'` '.$order;
}
}
if( count($order_by_list) )
{
$strSQL .= ' ORDER BY '.implode(', ', $order_by_list);
}
}
if (false !== $limit && (int)$limit > 0) {
$strSQL .= ' LIMIT '.( $limitfrom !== false ? (int)$limitfrom.',' : '' ).(int)$limit;
}
//tester('$strSQL', $strSQL);
//print $strSQL.'<hr/>';
//$strSQL .= ';';
return $strSQL;
}
/**
* Builds SQL query for DELETE WHERE command from array.
*
* @param string $tablename
* @param array $arrData
* @param string $implode AND or OR
*
* @access public
* @return unknown
*/
public function buildDeleteQuery($tablename = '', $arrData = array(), $implode = ' AND ')
{
/*
empty params check & tablename at least 3 chars
*/
if (0 == strlen($tablename) || 0 == count($arrData))
return false;
$objRSQuery = DB_MYSQLi::getInstance()->query( 'SHOW COLUMNS FROM '.$tablename );
//$res = $this->fetchArray_result();
//tester('$objRSQuery', $objRSQuery);
$where = array();
$strSQL = "";
//foreach( $res as $key=>$value )
while( $value = $objRSQuery->fetchArray() )
{
if ( !array_key_exists($value['Field'], $arrData) )
{
continue;
}
$where[] = '`' . $value['Field'] . '` = \''.self::_escapeString ( $arrData[$value['Field']] ).'\'';
}
if( count($where) )
{
$where_list = implode($implode, $where);
$strSQL = 'DELETE FROM '.$tablename.' WHERE '.$where_list;
}
//tester('$strSQL', $strSQL); die();
//$strSQL .= ';';
return $strSQL;
}
public function buildReplaceQuery($tablename = '', $arrData = array()){
/*
empty params check & tablename at least 3 chars
*/
if (0 == strlen($tablename) || 0 == count($arrData))
return false;
//$this->query( 'SHOW COLUMNS FROM ?', array($tablename) );
$objRSQuery = DB_MYSQLi::getInstance()->query( 'SHOW COLUMNS FROM '.$tablename );
//$res = $this->fetchArray_result();
//tester('$objRSQuery', $objRSQuery);
$fields = array();
$values = array();
$strSQL = "";
//foreach( $res as $key=>$value )
while( $value = $objRSQuery->fetchArray() )
{
if ( !array_key_exists($value['Field'], $arrData) )
{
continue;
}
$fields[] = '`' . $value['Field'] . '`';
$values[] = "'" . self::_escapeString ( $arrData[$value['Field']] ) . "'";
}
$field_list = implode(',', $fields);
$value_list = implode(', ', $values);
$strSQL = 'REPLACE '.$tablename.' ('.$field_list.') VALUES ('.$value_list.')';
//$strSQL .= ';';
return $strSQL;
}
/**
* Builds SQL query for INSERT command from array.
*
* @example
* $tname = 'news';
* $aData = array('news_title'=>'best article', 'create_tm'=>time());
* $sSQL = MySql::buildInsertQuery($tname, $aRules, $aData);
* The function takes care of characters escaping
*
* @param string $tablename tname of table
* @param array $arrData Associative array of values to insert into the<br>
* specified table. Keys are the table column names and<br>
* values are the values to be inserted into the table.<br>
* Arrays and values with special SQL characters are handled<br>
* properly.
*
* @access public
* @return mixed $strSQL SQL statement that was built. on failure return false
*/
public function buildInsertQuery($tablename = '', $arrData = array())
{
/*
empty params check & tablename at least 3 chars
*/
if (0 == strlen($tablename) || 0 == count($arrData))
return false;
//$this->query( 'SHOW COLUMNS FROM ?', array($tablename) );
$objRSQuery = DB_MYSQLi::getInstance()->query( 'SHOW COLUMNS FROM '.$tablename );
//$res = $this->fetchArray_result();
//tester('$objRSQuery', $objRSQuery);
$fields = array();
$values = array();
$strSQL = "";
//foreach( $res as $key=>$value )
while( $value = $objRSQuery->fetchArray() )
{
if ( !array_key_exists($value['Field'], $arrData) )
{
continue;
}
$fields[] = '`' . $value['Field'] . '`';
$values[] = "'" . self::_escapeString ( $arrData[$value['Field']] ) . "'";
}
$field_list = implode(',', $fields);
$value_list = implode(', ', $values);
$strSQL = 'INSERT INTO '.$tablename.' ('.$field_list.') VALUES ('.$value_list.')';
//$strSQL .= ';';
return $strSQL;
}
/**
* Builds SQL query for UPDATE command from array with set of arrayed rules.
*
* @example
* $tname = 'news';
* $aRules = array ('news_id'=>2);
* $aData = array('news_title'=>'best article', 'update_tm'=>time());
* $sSQL = MySql::buildUpdateQuery($tname, $aRules, $aData);
* The function takes care of characters escaping
*
* @access public
* @param string $tablename Name of table
* @param array $arrRules Associative array of WHERE clause conditions for the table update.<br>
* These are in the form of key = value.
* @param array $arrData Associative array of values to update in the specified table.<br>
* Keys are the table column names and values are the values to <br>
* be updated in the table. Arrays and values with special <br>
* SQL characters are handled properly.
* @return mixed SQL statement that was built. on failure return false
*/
public function buildUpdateQuery($tablename = '', $arrRules = array(), $arrData = array())
{
$strSQL = '';
if (0 == strlen($tablename) || 0 == count($arrData))
return false;
$objRSQuery = DB_MYSQLi::getInstance()->query( 'SHOW COLUMNS FROM '.$tablename );
$tableFields = array();
while( $value = $objRSQuery->fetchArray() ){
$tableFields[] = $value['Field'];
}
if (is_array($arrRules) && 0 == count($arrRules)) {
return false;
}
elseif ( is_array($arrRules) && 0 != count($arrRules) )
{
$where = array();
foreach ($arrRules as $Field=>$value)
{
if ( !in_array($Field, $tableFields) )
{
continue;
}
$where[] = '`' . $Field . '` = \''.self::_escapeString ( $value ).'\'';
}
$_rules = implode(' AND ', $where);
}
else
{
$_rules = '';
}
//var_dump( $_rules );
if (is_string($arrRules) && 0 == strlen($arrRules))
{
return false;
}
elseif (is_string($arrRules) && 0 != strlen($arrRules))
{
$_rules = $arrRules;
}
/*else
$_rules = $arrRules;*/
if (isset($arrData['id']))
unset($arrData['id']);
$update_array = self::_escapeArray( $arrData );
$arrPairs = array();
$strPaires = '';
foreach ($update_array as $key=>$val) {
if ( !in_array($key, $tableFields) )
{
continue;
}
array_push($arrPairs, $key."= '".$val."'");
}
$strPaires = implode(', ', $arrPairs);
$strSQL = 'UPDATE '.$tablename.' SET '.$strPaires.' WHERE '.$_rules;
return $strSQL;
}
/**
* Return a formatted string replace '?' to array elements
* Example: $str = 'SELECT * FROM ? WHERE id=?';
* $values = array('tblnews', 1);
* $frmtStr = MySql::_replaceQuestionMarks($str, $values);
*
* @access private
* @param string $str string with '?'
* @param array $arrParams array of values that should replace '?'
* @return string formatted string without '?'
*/
public static function replaceQuestionMarks($str = 0, $arrParams = array())
{
$escapted_params = self::_escapeArray($arrParams);
return vsprintf(str_replace('?','%s',$str), $escapted_params);
}
/**
* Escapes special characters in a string for use in a SQL statement
*
* Notice: mysql_real_escape_string(...) can be used when the connection to MySql
* establish. if isnt then it will connect.
*
* @access private
* @param string $str
* @return mixed on connection seccues escapeted string else false
*/
public static function _escapeString($str)
{
$str = trim($str);
if (0 == strlen($str))
return false;
$db_conn = DB_MYSQLi::getDBConnection();
//print '<script>alert(\'out put :'.print_r($db_conn,true).' \');</script>';
//die();
if (false == $db_conn)
return $str;
//print '<hr/>';
//var_dump($str);
return mysqli_real_escape_string($db_conn, $str);
//return $str;
}
private static function _escapeArray( $_array )
{
$ret=array();
foreach ($_array as $k=>$v) {
//if (is_numeric($v))
// $ret[$k]=$v;
//else
$ret[$k]="".self::_escapeString($v)."";
}
return $ret;
}
function fetchArray_result_STAC($key_field='', $val_field='', $prepend_key=false)
{
$result = array();
/*if ( 1 == $this->getNumRows() )
return $this->fetchArray();*/
if (!empty($key_field) && !empty($val_field))
{
if (true == $prepend_key)
{
while( $res = $this->fetchArray() ) {
$result[$res[$key_field]] = $res[$key_field].' :: '.$res[$val_field];
}
} else {
while( $res = $this->fetchArray() ) {
$result[$res[$key_field]] = $res[$val_field];
}
}
} elseif(!empty($key_field)) {
while( $res = $this->fetchArray() ) {
$result[$res[$key_field]] = $res;
}
} else {
while( $res = $this->fetchArray() ) {
$result[] = $res;
}
}
return $result;
}
}
$objDb = DB_MYSQLi::getInstance();
$objDbStmt = DB_MYSQLi_Prepare_Statment::getInstance();
karapet
Постоялец
- Регистрация
- 9 Сен 2008
- Сообщения
- 169
- Реакции
- 105
я этот юзаю
Для просмотра скрытого содержимого вы должны войти или зарегистрироваться.
a_n_d_y
Гуру форума
- Регистрация
- 26 Мар 2006
- Сообщения
- 470
- Реакции
- 62
И чем это отличается от работы со стандартными функциями mysql?
trooll
PHP кодер
- Регистрация
- 22 Дек 2008
- Сообщения
- 482
- Реакции
- 117
При таком раскладе я считаю и в правду, не стоит заморачиваться c функциями, и просто использовать стандартные функции PHP для работы с MySQL.
Класс, либо как в вашем случае архив функций для работы с БД, как правило я считаю создается в первую очередь для организации рутинных действий для осуществления безопасности работы с полученными и отправленными данными в базу или из нее.
Второй момент это автоматизация добавления, получения, изменения, удаления данных в базе.
Третий момент, это создание архитектуры позволяющая работать с разными типами БД. Одним заранее сформулированным языком и по определенным правилам.