1
<?php
2
/**
3
* MySQLi Data Access Object
4
*
5
* @author Zhou Yuan <yuanzhou19@gmail.com>
6
* @link http://www.infopotato.com/
7
* @copyright Copyright © 2009-2011 Zhou Yuan
8
* @license http://www.opensource.org/licenses/mit-license.php MIT Licence
9
*/
10
11
class MySQLi_DAO extends Base_DAO {
12
/**
13
* An object which represents the connection to a MySQL Server
14
*
15
* @var object
16
*/
17
public $mysqli;
18
19
/**
20
* Constructor
21
*
22
* Allow the user to perform a connect at the same time as initialising the this class
23
*/
24
public function __construct(array $config = NULL) {
25
// If there is no existing database connection then try to connect
26
if ( ! is_object($this->mysqli)) {
27
$this->mysqli = new mysqli($config['host'], $config['user'], $config['pass'], $config['name'], $config['port']);
28
29
// Use this instead of $connect_error if you need to ensure
30
// compatibility with PHP versions prior to 5.2.9 and 5.3.0.
31
if (mysqli_connect_error()) {
32
halt('An Error Was Encountered', 'Connect Error ('.mysqli_connect_errno().') '.mysqli_connect_error(), 'sys_error');
33
}
34
35
if (method_exists($this->mysqli, 'set_charset')) {
36
// Set charset, (PHP 5 >= 5.0.5)
37
$this->mysqli->set_charset($config['charset']);
38
} else {
39
// Specify the client encoding per connection
40
$collation_query = "SET NAMES '{$config['charset']}'";
41
if ( ! empty($config['collate'])) {
42
$collation_query .= " COLLATE '{$config['collate']}'";
43
}
44
$this->exec_query($collation_query);
45
}
46
}
47
}
48
49
/**
50
* Escapes special characters in a string for use in an SQL statement,
51
* taking into account the current charset of the connection
52
*/
53
public function escape($string) {
54
// Only quote and escape string
55
// is_string() will take '' will as string
56
if (is_string($string)) {
57
$string = isset($this->mysqli) ? $this->mysqli->real_escape_string($string) : addslashes($string);
58
}
59
return $string;
60
}
61
62
/**
63
* USAGE: prepare( string $query [, array $params ] )
64
* The following directives can be used in the query format string:
65
* %d (decimal number)
66
* %s (string)
67
*
68
* Both %d and %s are to be left unquoted in the query string and they need an argument passed for them.
69
*/
70
public function prepare($query, array $params = NULL) {
71
if (count($params) > 0) {
72
foreach ($params as $k => $v) {
73
// Only quote and escape string
74
if (is_string($v)) {
75
$params[$k] = isset($this->mysqli) ? "'".$this->mysqli->real_escape_string($v)."'" : "'".addslashes($v)."'";
76
}
77
}
78
// vsprintf - replacing all %d and %s to parameters
79
$query = vsprintf($query, $params);
80
}
81
return $query;
82
}
83
84
/**
85
* Perform a unique query (multiple queries are not supported) and try to determine result value
86
*
87
* @return int Number of rows affected/selected
88
*/
89
public function exec_query($query) {
90
// Initialise return
91
$return_val = 0;
92
93
// Reset stored query result
94
$this->query_result = array();
95
96
// For reg expressions
97
$query = trim($query);
98
99
// Perform the query via std mysqli_query() function.
100
// Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN
101
// queries mysqli_query() will return a result object.
102
// For other successful queries mysqli_query() will return TRUE.
103
$result = $this->mysqli->query($query);
104
105
// If there is an error then take note of it.
106
if ($err_msg = $this->mysqli->error) {
107
halt('An Error Was Encountered', $err_msg, 'sys_error');
108
}
109
110
// Query was an insert, delete, drop, update, replace, alter
111
if (preg_match("/^(insert|delete|drop|supdate|replace|alter)\s+/i", $query)) {
112
// When using UPDATE, MySQL will not update columns where the new value is the same as the old value.
113
// This creates the possibility that $affected_rows may not actually equal the number of rows matched,
114
// only the number of rows that were literally affected by the query.
115
$rows_affected = $this->mysqli->affected_rows;
116
117
// Take note of the last_insert_id
118
// REPLACE works exactly like INSERT, except that if an old row in the table has the same value
119
// as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
120
if (preg_match("/^(insert|replace)\s+/i", $query)) {
121
$this->last_insert_id = $this->mysqli->insert_id;
122
}
123
// Return number fo rows affected
124
$return_val = $rows_affected;
125
} elseif (preg_match("/^(select|describe|desc|show|explain)\s+/i", $query)) {
126
// Store Query Results
127
$num_rows = 0;
128
while ($row = $result->fetch_object()) {
129
// Store relults as an objects within main array
130
$this->query_result[$num_rows] = $row;
131
$num_rows++;
132
}
133
134
$result->free_result();
135
136
// Log number of rows the query returned
137
$this->num_rows = $num_rows;
138
139
// Return number of rows selected
140
$return_val = $this->num_rows;
141
} elseif (preg_match("/^create\s+/i", $query)) {
142
// Table creation returns TRUE on success, or FALSE on error.
143
$return_val = $result;
144
}
145
146
return $return_val;
147
}
148
149
/**
150
* Disable autocommit to begin transaction
151
* MySQL MyISAM tables do not support transactions and will auto-commit even if a transaction has been started
152
*
153
* @access public
154
* @return bool
155
*/
156
public function trans_begin() {
157
$this->mysqli->autocommit(FALSE);
158
}
159
160
/**
161
* Commit Transaction
162
*
163
* @access public
164
* @return bool
165
*/
166
public function trans_commit() {
167
$this->mysqli->commit();
168
}
169
170
/**
171
* Rollback Transaction
172
*
173
* @access public
174
* @return bool
175
*/
176
public function trans_rollback() {
177
$this->mysqli->rollback();
178
}
179
180
}
181
182
// End of file: ./system/core/mysqli_dao.php
Page URI: http://www.infopotato.com/index.php/code/core/mysqli_dao/