3 /**********************************************************************
4 * Author: Justin Vincent (jv@jvmultimedia.com)
5 * Web...: http://twitter.com/justinvincent
7 * Desc..: mySQL component (part of ezSQL databse abstraction library)
11 /**********************************************************************
12 * ezSQL error strings - mySQL
15 global $ezsql_mysql_str;
17 $ezsql_mysql_str = array
19 1 => 'Require $dbuser and $dbpassword to connect to a database server',
20 2 => 'Error establishing mySQL database connection. Correct user/password? Correct hostname? Database server running?',
21 3 => 'Require $dbname to select a database',
22 4 => 'mySQL database connection is not active',
23 5 => 'Unexpected error while trying to select database'
26 /**********************************************************************
27 * ezSQL Database specific class - mySQL
30 if ( ! function_exists ('mysql_connect') ) die('<b>Fatal Error:</b> ezSQL_mysql requires mySQL Lib to be compiled and or linked in to the PHP engine');
31 if ( ! class_exists ('ezSQLcore') ) die('<b>Fatal Error:</b> ezSQL_mysql requires ezSQLcore (ez_sql_core.php) to be included/loaded before it can be used');
33 class ezSQL_mysql extends ezSQLcore
37 var $dbpassword = false;
40 var $encoding = false;
41 var $rows_affected = false;
43 /**********************************************************************
44 * Constructor - allow the user to perform a qucik connect at the
45 * same time as initialising the ezSQL_mysql class
48 function ezSQL_mysql($dbuser='', $dbpassword='', $dbname='', $dbhost='localhost', $encoding='')
50 $this->dbuser = $dbuser;
51 $this->dbpassword = $dbpassword;
52 $this->dbname = $dbname;
53 $this->dbhost = $dbhost;
54 $this->encoding = $encoding;
57 /**********************************************************************
58 * Short hand way to connect to mySQL database server
59 * and select a mySQL database at the same time
62 function quick_connect($dbuser='', $dbpassword='', $dbname='', $dbhost='localhost', $encoding='')
65 if ( ! $this->connect($dbuser, $dbpassword, $dbhost,true) ) ;
66 else if ( ! $this->select($dbname,$encoding) ) ;
67 else $return_val = true;
71 /**********************************************************************
72 * Try to connect to mySQL database server
75 function connect($dbuser='', $dbpassword='', $dbhost='localhost')
77 global $ezsql_mysql_str; $return_val = false;
79 // Keep track of how long the DB takes to connect
80 $this->timer_start('db_connect_time');
82 // Must have a user and a password
85 $this->register_error($ezsql_mysql_str[1].' in '.__FILE__.' on line '.__LINE__);
86 $this->show_errors ? trigger_error($ezsql_mysql_str[1],E_USER_WARNING) : null;
88 // Try to establish the server database handle
89 else if ( ! $this->dbh = @mysql_connect($dbhost,$dbuser,$dbpassword,true,131074) )
91 $this->register_error($ezsql_mysql_str[2].' in '.__FILE__.' on line '.__LINE__);
92 $this->show_errors ? trigger_error($ezsql_mysql_str[2],E_USER_WARNING) : null;
96 $this->dbuser = $dbuser;
97 $this->dbpassword = $dbpassword;
98 $this->dbhost = $dbhost;
105 /**********************************************************************
106 * Try to select a mySQL database
109 function select($dbname='', $encoding='')
111 global $ezsql_mysql_str; $return_val = false;
113 // Must have a database name
116 $this->register_error($ezsql_mysql_str[3].' in '.__FILE__.' on line '.__LINE__);
117 $this->show_errors ? trigger_error($ezsql_mysql_str[3],E_USER_WARNING) : null;
120 // Must have an active database connection
121 else if ( ! $this->dbh )
123 $this->register_error($ezsql_mysql_str[4].' in '.__FILE__.' on line '.__LINE__);
124 $this->show_errors ? trigger_error($ezsql_mysql_str[4],E_USER_WARNING) : null;
127 // Try to connect to the database
128 else if ( !@mysql_select_db($dbname,$this->dbh) )
130 // Try to get error supplied by mysql if not use our own
131 if ( !$str = @mysql_error($this->dbh))
132 $str = $ezsql_mysql_str[5];
134 $this->register_error($str.' in '.__FILE__.' on line '.__LINE__);
135 $this->show_errors ? trigger_error($str,E_USER_WARNING) : null;
139 $this->dbname = $dbname;
140 if ( $encoding == '') $encoding = $this->encoding;
143 $encoding = strtolower(str_replace("-","",$encoding));
145 $result = mysql_query("SHOW CHARACTER SET");
146 while($row = mysql_fetch_array($result,MYSQL_ASSOC))
148 $charsets[] = $row["Charset"];
150 if(in_array($encoding,$charsets)){
151 mysql_query("SET NAMES '".$encoding."'");
161 /**********************************************************************
162 * Format a mySQL string correctly for safe mySQL insert
163 * (no mater if magic quotes are on or not)
166 function escape($str)
168 // If there is no existing database connection then try to connect
169 if ( ! isset($this->dbh) || ! $this->dbh )
171 $this->connect($this->dbuser, $this->dbpassword, $this->dbhost);
172 $this->select($this->dbname, $this->encoding);
175 return mysql_real_escape_string(stripslashes($str));
178 /**********************************************************************
179 * Return mySQL specific system date syntax
180 * i.e. Oracle: SYSDATE Mysql: NOW()
188 /**********************************************************************
189 * Perform mySQL query and try to detirmin result value
192 function query($query)
195 // This keeps the connection alive for very long running scripts
196 if ( $this->num_queries >= 500 )
198 $this->num_queries = 0;
200 $this->quick_connect($this->dbuser,$this->dbpassword,$this->dbname,$this->dbhost,$this->encoding);
206 // Flush cached values..
209 // For reg expressions
210 $query = trim($query);
212 // Log how the function was called
213 $this->func_call = "\$db->query(\"$query\")";
215 // Keep track of the last query for debug..
216 $this->last_query = $query;
218 // Count how many queries there have been
219 $this->num_queries++;
222 $this->timer_start($this->num_queries);
224 // Use core file cache function
225 if ( $cache = $this->get_cache($query) )
227 // Keep tack of how long all queries have taken
228 $this->timer_update_global($this->num_queries);
231 if ( $this->use_trace_log )
233 $this->trace_log[] = $this->debug(false);
239 // If there is no existing database connection then try to connect
240 if ( ! isset($this->dbh) || ! $this->dbh )
242 $this->connect($this->dbuser, $this->dbpassword, $this->dbhost);
243 $this->select($this->dbname,$this->encoding);
246 // Perform the query via std mysql_query function..
247 $this->result = @mysql_query($query,$this->dbh);
249 // If there is an error then take note of it..
250 if ( $str = @mysql_error($this->dbh) )
253 $this->register_error($str);
254 $this->show_errors ? trigger_error($str,E_USER_WARNING) : null;
258 // Query was an insert, delete, update, replace
260 if ( preg_match("/^(insert|delete|update|replace|truncate|drop|create|alter|set)\s+/i",$query) )
262 $this->rows_affected = @mysql_affected_rows($this->dbh);
264 // Take note of the insert_id
265 if ( preg_match("/^(insert|replace)\s+/i",$query) )
267 $this->insert_id = @mysql_insert_id($this->dbh);
270 // Return number fo rows affected
271 $return_val = $this->rows_affected;
273 // Query was a select
277 // Take note of column info
279 while ($i < @mysql_num_fields($this->result))
281 $this->col_info[$i] = @mysql_fetch_field($this->result);
285 // Store Query Results
287 while ( $row = @mysql_fetch_object($this->result) )
289 // Store relults as an objects within main array
290 $this->last_result[$num_rows] = $row;
294 @mysql_free_result($this->result);
296 // Log number of rows the query returned
297 $this->num_rows = $num_rows;
299 // Return number of rows selected
300 $return_val = $this->num_rows;
303 // disk caching of queries
304 $this->store_cache($query,$is_insert);
306 // If debug ALL queries
307 $this->trace || $this->debug_all ? $this->debug() : null ;
309 // Keep tack of how long all queries have taken
310 $this->timer_update_global($this->num_queries);
313 if ( $this->use_trace_log )
315 $this->trace_log[] = $this->debug(false);
322 /**********************************************************************
323 * Close the active mySQL connection
326 function disconnect()
328 @mysql_close($this->dbh);