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