본문 바로가기
PHP
2012.03.28 19:34

PHP-MySQLi-Wrapper

조회 수 6434 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

+ - Up Down Comment Print
?

단축키

Prev이전 문서

Next다음 문서

+ - Up Down Comment Print

https://github.com/carldanley/PHP-MySQLi-Wrapper/blob/master/database.php


<?php
/*------------------------------------------------------------------------------
Class Name: db
Description: An advanced MySQLi wrapper to perform commands with ease.
Author: Carl Danley
Version: 2.0
//----------------------------------------------------------------------------*/
class db{
protected $_user = 'server_username';
protected $_pass = 'server_password';
protected $_host = 'server_hostname';
protected $_binds = array();
protected $_where = array();
protected $_query_type;
protected $_query;
protected $_mysql;
protected $_order_by = false;
protected $_current_database = '';
protected $_encryption_key = 'your_encryption_key_goes_here';

public function __construct($db_name = 'default_database'){
$this->_mysql = new mysqli($this->_host, $this->_user, $this->_pass, $db_name);
}

/**
* This function will return the last records id.
*
* @return string $id The ID of the last inserted mysql row
*/
public function insert_id(){
return $this->_mysql->insert_id;
}

public function __destruct(){
$this->_mysql->close();
}

/**
* This function will handle performing queries for the user.
*
* @param string $query Contains a user-provided select query
* @param int $num_rows Indicates how many rows need to be retrieved before the user's
* @param boolean $get_results Indicates whether or not this function should retrieve results after executing a query
* @return array $results An associative array containing all of the data that is directly correlated to the query performed
*/
public function query($query, $num_rows = false, $get_results = true){
$this->_query = $query;
$statement = $this->_prepare_query();
$this->execute($statement);
if($get_results === true){
$results = $this->_bind_results($statement);
}
return $results;
}

/**
* This function will handle getting data from the user's specified table. It
* will prepare the query, set limits and where statements, and then return
* correlated results.
*
* @param string $table The table name that this function will retrieve data from.
* @param int @num_rows The number of rows to retrieve.
* @return array $results An associative array containing the results from the query.
*/
public function get($table, $num_rows = false, $fields = array('*')){
$this->_query_type = 'get';
$this->_query = 'SELECT ' . implode($fields, ', ') . ' FROM ' . $table;
$statement = $this->_build_query($num_rows);
$this->execute($statement);
$results = $this->_bind_results($statement);
return $results;
}

/**
* This function will take a table name and some data and then insert the
* data into the table.
*
* @param string $table This is the name of the table that the data will be inserted into.
* @param array $data This is the data that will be inserted into the table
* @return boolean $result This indicates whether or not the row could be inserted
*/
public function insert($table, $data){
$this->_query_type = 'insert';
$this->_query = 'INSERT INTO ' . $table;
$statement = $this->_build_query(false, $data);
$this->execute($statement);

return ($statement->affected_rows > 0) ? true : false;
}

/**
* This function will take a table name and the data passed to it and update
* the table using the where clauses.
*
* @param string $table This is the name of the table that will have data updated.
* @param array $data This is the data that will be used to update the table.
* @return boolean $result This indicates whether or not the row could be updated.
*/
public function update($table, $data){
$this->_query_type = 'update';
$this->_query = 'UPDATE ' . $table . ' SET';
$statement = $this->_build_query(false, $data);
$this->execute($statement);

return ($statement->affected_rows > 0) ? true : false;
}

/**
* This function will take a table name and clear the contents of that same
* table. Where parameters can still be used here.
*
* @param string $table This is the name of the table that will have data deleted from it
* @return boolean $result This indicates whether or not the data could be deleted
*/
public function delete($table){
$this->_query_type = 'delete';
$this->_query = 'DELETE FROM ' . $table;
$statement = $this->_build_query(false, false);
$this->execute($statement);

return ($statement->affected_rows > 0) ? true : false;
}

/**
* This function will build the mysql query as we need and then prepare it.
* After everything is prepared, it will return a statement that is ready to
* be executed.
*
* @return mysqli_stmt $statement This is the resultant statement that was created after the query was fully built.
*/
protected function _build_query($num_rows = false, $data = false){
//build the insert statement, if it exists
$this->_query .= (strcmp($this->_query_type, 'insert') === 0) ? $this->_build_insert_clause($data) : '';

//build the update statement, if it exists
$this->_query .= (strcmp($this->_query_type, 'update') === 0) ? $this->_build_update_clause($data) : '';

//add the where clauses to the query
$this->_query .= $this->_build_where_clauses();

//add the order by clause to the query, if it exists
$this->_query .= ($this->_order_by === false) ? '' : ' ORDER BY ' . $this->_order_by;

//add the limit clause to the query, if it exists
$this->_query .= ($num_rows === false) ? '' : ' LIMIT ' . (int)$num_rows;

//the query has been finished, prepare the statement
$statement = $this->_prepare_query();

//now that we have the statement prepared, bind any variables to it
$this->_bind_variables($statement);

return $statement;
}

/**
* This function will take care of formatting the update data that needs to
* be added to the query. It will also bind any data to the variable queue if
* the data exists.
*
* @param array $data This is an associative array containing all of the data that will be used to update the correlated table
* @return string $clause This is the update clause in the order that the data was passed in. It will be appended directly to the query.
*/
protected function _build_update_clause($data = false){
if($data === false || count($data) === 0){
return '';
}

$clause = array();
foreach($data as $key => $value){
$clause[] = $key . '=?';
$this->_add_variable_binding($this->_determine_type($value), $value);
}
$clause = implode($clause, ', ');

return ' ' . $clause;
}

/**
* This function take care of formatting the insert data that needs to be
* added to the query. It will also bind any data to the variable queue if
* data exists.
*
* @param array $data This is an associative array containing the data that will be inserted into the correlated table
* @return string $clause This is the insert clause in the order is was prepared. It will be directly appended to the query.
*/
protected function _build_insert_clause($data = false){
if($data === false || count($data) === 0){
return '';
}

$keys = array_keys($data);
$values = array_fill(0, count($keys), '?');

$clause = ' (' . implode($keys, ', ') . ') VALUES(' . implode($values, ', ') . ')';

//now bind all of the variables
foreach($data as $v){
$this->_add_variable_binding($this->_determine_type($v), $v);
}

return $clause;
}

/**
* This function will bind the variables that have been queued up. They will
* be bound in the same order in which they were queued. These variables will
* be bound to the referenced mysqli statement
*
* @param mysqli_stmt $statement This is the statement that the queued variables will be bound to.
*/
protected function _bind_variables(& $statement){
if(count($this->_binds) === 0){
return '';
}
$types = array();
$values = array();

//setup types and values so they can be written accordingly
foreach($this->_binds as $binding){
$types[] = $binding['type'];
$values[] = $binding['value'];
}

//now start adding the arguments for the binding
$args = array();
$args[] = implode($types, '');
foreach($values as $key => $value){
$args[] = &$values[$key];
}

call_user_func_array(array($statement, 'bind_param'), $args);
}

/**
* This function will insert a where clause so that it can be picked up by
* the next query that's performed.
*
* @param string $field This is the field name of the thing being filtered.
* @param mixed $value This is the value for the correlated field passed as a parameter.
*/
public function where($field, $value){
$this->_where[$field] = $value;
}

/**
* This function will determine the type of the variable passed to it and
* return the correct variable letter so we can use it later for binding the
* parameters.
*
* @param mixed $value This is the variable that will be used to determine the type
* @return string $return This is the type of the variable that was passed as a parameter. Possible values could be 'i', 'b', 'd', and 's'.
*/
protected function _determine_type($value){
return substr(gettype($value), 0, 1);
}

/**
* This function will convert all of the where's into a single string statement
* and return it so it can be appended to the query
*
* @return string $wheres This is a string container holding all of the information for the current query's where clauses.
*/
protected function _build_where_clauses(){
if(empty($this->_where) === true){
return '';
}

$clauses = array();
foreach($this->_where as $key => $value){
$clauses[] = $key . ' = ?';
$this->_add_variable_binding($this->_determine_type($value), $value);
}
return ' WHERE ' . implode($clauses, ' AND ');
}

/**
* This function will handle adding a bind to the queue so it can be properly
* bound the next mysqli statement executed.
*
* @param string $type This is the type of the variable, possible values could be 'i', 'b', 'd', and 's'.
* @param string $value This is the value that will be bound to the statement
*/
protected function _add_variable_binding($type, $value){
$this->_binds[] = array('type' => $type, 'value' => $value);
}

/**
* This function will handle executing the statement passed to it. It also
* clears the cache for any parameters in the list and any other variables
* that need to be cleaned up.
*
* @param mysqli_stmt $statement This is the prepared mysqli query that needs to be executed.
*/
protected function execute(& $statement){
$statement->execute();
$this->reset_cache();
}

/**
* This function will clear out all existing cache that might affect the
* queries to follow
*/
protected function reset_cache(){
$this->_binds = array();
$this->_where = array();
$this->_order_by = false;
}

/**
* This function will handle setting the sorting for a query if needed.
*
* @param string $order This is the ordering string that will be appended to the query
*/
public function order_by($order){
$this->_order_by = $order;
}

/**
* This function will bind all of the results from a raw mysql query and
* return them in an associative array._encryption_key
*
* @param mysqli_stmt $statement The resultant statement object that was generated from the _prepare_query() function
* @return array $results The results from the query organized into an associative array
*/
protected function _bind_results($statement){
$parameters = array();
$results = array();

$metadata = $statement->result_metadata();
while($field = $metadata->fetch_field()){
$parameters[] = &$row[$field->name];
}
call_user_func_array(array($statement, 'bind_result'), $parameters);
while($statement->fetch()){
$tmp = array();
foreach($row as $key => $val){
$tmp[$key] = $val;
}
$results[] = $tmp;
}

return $results;
}

/**
* This function will handle preparing the query for the MySQLI object
*
* @return mysqli_stmt $statement This is the MySQLI statement that will be returned after the query has been prepared
*/
protected function _prepare_query(){
if(($statement = $this->_mysql->prepare($this->_query)) === false){
trigger_error('Query Could not be prepared: <br/>' . $this->_query . '<br/>', E_USER_ERROR);
}
return $statement;
}

/**
* A utility function that handles printing the variable passed to it in
* human readable format with <pre> tag's in place. The function can also
* display the data and stop any script exiting so the user can see the data
* that is passed to this function.
*
* @param mixed $var The variable that will be dumped to the screen.
* @param boolean $exit Boolean indicating whether the script should exit or not.
*/
public function dump($var, $exit = true){
echo '<pre>';
var_dump($var);
echo '</pre>';
if($exit === true){
exit;
}
}

/**
* A utility function to allow the user to switch back and forth through
* different databases with ease.
*
* @param string $db_name This is name of the database that will be switched to, if it exists.
* @return boolean $result This indicates whether or not the database name could be switched to.
*/
public function switch_database($db_name = false){
if(strcmp($this->_current_database, '') === 0 || strcmp($this->_current_database, $db_name) !== 0){
return $this->_mysql->select_db($db_name);
}
else{
return false;
}
}

/**
* A utility function to allow the end user to encrypt text with a basic form
* of encryption
*
* @param string $text This is the text that will be encrypted.
* @return string $return This is the resultant encrypted text.
*/
public function encrypt($text){
$new_str = array();
for($i = 0, $len = strlen($text); $i < $len; $i++){
$pos = $i % strlen($this->_encryption_key);
$a = ord(substr($text, $i, 1));
$b = ord(substr($this->_encryption_key, $pos, 1));

$a = $a ^ $b;
$a = $a << 1;
$a = $a ^ $b;

$new_str[] = chr($a);
}
$return = implode($new_str, '');
$return = base64_encode($return);
return $return;
}
/**
* A utility function to allow the end user to decrypt text with a basic form
* of decryption
*
* @param string $text This is the text that will be encrypted.
* @return string $return This is the resultant encrypted text.
*/
public function decrypt($text){
$new_str = array();
$text = base64_decode($text);
for($i = 0, $len = strlen($text); $i < $len; $i++){
$pos = $i % strlen($this->_encryption_key);
$a = ord(substr($text, $i, 1));
$b = ord(substr($this->_encryption_key, $pos, 1));

$a = $a ^ $b;
$a = $a >> 1;
$a = $a ^ $b;

$new_str[] = chr($a);
}
$return = implode($new_str, '');
return $return;
}
}
//------------------------------------------------------------------------------
?>


Title
List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
36 PHP What does PHP keyword 'var' do? Hojung 2013.12.04 3608
35 MySQL How to reset MySQL’s root password on Mac OS X Hojung 2013.04.24 4945
34 PHP easy way would it be to use str_replace() on the whole file (파일내용변경) Hojung 2012.12.12 5245
33 MySQL Mysql SELECT inside UPDATE Hojung 2012.03.28 5102
» PHP PHP-MySQLi-Wrapper Hojung 2012.03.28 6434
31 PHP MySQL 연결에서 결과값 얻는 PHP 예 (추천) Hojung 2012.03.28 4577
30 PHP MySQL 주요 PHP 함수 (추천) Hojung 2012.03.28 4189
29 PHP Send email using SMTP server's settings Hojung 2012.03.13 4255
28 PHP Send email with mail() Hojung 2012.03.13 3879
27 PHP PHP Upload Progress Bar (APC) Hojung 2012.03.13 4625
26 PHP Rename if exists Hojung 2012.03.12 4006
25 PHP Program execution Functions (exec, system, shell_exec) Hojung 2012.03.11 5711
24 PHP email address validation Hojung 2012.03.11 3748
23 PHP file put contents Hojung 2012.03.01 4279
22 PHP The MVC Design Pattern for PHP Hojung 2012.02.14 5607
21 PHP PHP XML 파서 Hojung 2012.02.02 6663
20 MySQL 모든 호스트의 MySQL 액세스 Hojung 2012.01.27 4771
19 MySQL How Do I Enable Remote Access To MySQL Database Server? Hojung 2012.01.27 5467
18 MySQL 특정게시판의 메일리스트 정보 업데이트 Hojung 2011.12.15 5815
17 MySQL Xpressengine 정회원 메일주소만 Hojung 2011.12.15 6004
Board Pagination ‹ Prev 1 2 Next ›
/ 2

Designed by sketchbooks.co.kr / sketchbook5 board skin

나눔글꼴 설치 안내


이 PC에는 나눔글꼴이 설치되어 있지 않습니다.

이 사이트를 나눔글꼴로 보기 위해서는
나눔글꼴을 설치해야 합니다.

설치 취소

Sketchbook5, 스케치북5

Sketchbook5, 스케치북5

Sketchbook5, 스케치북5

Sketchbook5, 스케치북5