3 * MySQL database connection/querying layer
5 * SourceForge: Breaking Down the Barriers to Open Source Development
6 * Copyright 1999-2001 (c) VA Linux Systems
7 * http://sourceforge.net
9 * @version $Id: database-mysql.php 1423 2003-01-10 14:44:28Z bigdisk $
13 * System-wide database type
15 * @var constant $sys_database_type
18 $sys_database_type = 'mysql' ;
22 * db_connect() - Connect to the database
24 * Notice the global vars that must be set up
25 * Sets up a global $conn variable which is used
26 * in other functions in this library
28 function db_connect() {
29 global $sys_dbhost, $sys_dbuser, $sys_dbpasswd, $conn;
31 db_log_entry('db_connect', NULL);
33 db_log_dbentry('mysqli_connect',"$sys_dbhost, $sys_dbuser, $sys_dbpasswd");
34 $conn = @mysqli_connect($sys_dbhost, $sys_dbuser, $sys_dbpasswd);
35 db_log_dbexit('mysqli_connect',"$conn");
38 // Now map the physical database connections to the
39 // "virtual" list that is used to distribute load in db_query()
40 // Define dummy values to eliminate log messages
42 define('SYS_DB_PRIMARY', 0);
43 define('SYS_DB_STATS', 1);
44 define('SYS_DB_TROVE', 2);
45 define('SYS_DB_SEARCH', 3);
47 db_log_exit('db_connect');
53 * db_query() - Query the database
55 * @param string SQL statement
56 * @param int How many rows do you want returned
57 * @param int Of matching rows, return only rows starting here
59 function db_query($qstring, $limit = '-1', $offset = 0) {
60 global $sys_dbname, $conn;
62 db_log_entry('db_query',"$qstring, $limit, $offset");
65 if (!$offset || $offset < 0) {
68 $qstring=$qstring." LIMIT $offset,$limit";
70 // if ($GLOBALS['IS_DEBUG'])
71 // $GLOBALS['G_DEBUGQUERY'] .= $qstring . "<p><br />\n";
73 db_log_dbentry('mysqli_select_db',"$conn, $sys_dbname");
74 if (!mysqli_select_db($conn, $sys_dbname)) {
75 db_log_dbexit('mysqli_select_db',false);
76 db_log_exit('db_query');
79 db_log_dbexit('mysqli_select_db',true);
81 db_log_dbentry('mysqli_query',"$conn, $sys_dbname");
82 $res = mysqli_query($conn, $qstring);
83 db_log_dbexit('mysqli_query',$res);
85 db_log_exit('db_query',"$res");
90 * db_mquery() - Query the database supporting multi-statements
92 * @param string SQL statement
94 function db_mquery($qstring) {
95 global $sys_dbname, $conn;
97 // if ($GLOBALS['IS_DEBUG'])
98 // $GLOBALS['G_DEBUGQUERY'] .= $qstring . "<p><br />\n";
100 db_log_entry('db_mquery',"$qstring");
102 db_log_dbentry('mysqli_select_db',"$conn, $sys_dbname");
103 if (!mysqli_select_db($conn, $sys_dbname)) {
104 db_log_dbexit('mysqli_select_db',false);
105 $err = mysqli_error($conn);
107 db_log('DB Error = '.$err);
109 db_log_exit('db_mquery',NULL);
112 db_log_dbexit('mysqli_select_db', true);
114 db_log_dbentry('mysqli_multi_query',"$conn, $qstring");
115 if (!mysqli_multi_query($conn, $qstring)) {
116 db_log_dbexit('mysqli_multi_query','false');
117 $err = mysqli_error($conn);
119 db_log('DB Error = '.$err);
121 db_log_exit('db_mquery');
124 db_log_dbexit('mysqli_multi_query',true);
126 db_log_dbentry('mysqli_store_result',"$conn");
127 if ($res = mysqli_store_result($conn)) {
128 db_log_dbexit('mysqli_store_result',"$res");
129 db_log_exit('db_mquery',"$res");
132 $err = mysqli_error($conn);
134 db_log('DB Error = '.$err);
136 db_log_dbexit('mysqli_store_result');
138 db_log_exit('db_mquery',true);
143 * db_next_result() - Get the next result from query with multiple statements.
145 * @param string SQL statement
146 * @param int How many rows do you want returned
147 * @param int Of matching rows, return only rows starting here
149 function db_next_result() {
152 db_log_entry('db_next_result',NULL);
154 db_log_dbentry('mysqli_next_result',"$conn");
155 $ret = mysqli_next_result($conn);
156 db_log_dbexit('mysqli_next_result',"$ret");
157 $err = mysqli_error($conn);
159 db_log('DB Error = '.$err);
163 db_log_dbentry('mysqli_store_result',"$conn");
164 $res = mysqli_store_result($conn);
165 db_log_dbexit('mysqli_store_result',"$res");
170 $err = mysqli_error($conn);
172 db_log('DB Error = '.$err);
176 db_log_exit('db_next_result',$res);
181 * db_begin() - Begin a transaction
183 * Begin a transaction for databases that support them
184 * may cause unexpected behavior in databases that don't
186 function db_begin() {
187 return db_query("BEGIN WORK");
191 * db_commit() - Commit a transaction
193 * Commit a transaction for databases that support them
194 * may cause unexpected behavior in databases that don't
196 function db_commit() {
197 return db_query("COMMIT");
201 * db_rollback() - Roll back a transaction
203 * Rollback a transaction for databases that support them
204 * may cause unexpected behavior in databases that don't
206 function db_rollback() {
207 return db_query("ROLLBACK");
211 * db_numrows() - Returns the number of rows in this result set
213 * @param string Query result set handle
215 function db_numrows($qhandle) {
216 // return only if qhandle exists, otherwise 0
218 return @mysqli_num_rows($qhandle);
225 * db_free_result() - Frees a database result properly
227 * @param string Query result set handle
229 function db_free_result($qhandle) {
230 db_log_entry('db_free_result',"$qhandle");
231 if (!is_object($qhandle)) {
232 db_log_exit('db_free_result');
235 db_log_dbentry('mysqli_free_result',"$qhandle");
236 $res = mysqli_free_result($qhandle);
237 db_log_dbexit('mysqli_free_result',"$res");
238 db_log_exit('db_free_result');
242 * db_reset_result() - Reset a result set.
244 * Reset is useful for db_fetch_array sometimes you need to start over
246 * @param string Query result set handle
247 * @param int Row number
249 function db_reset_result($qhandle,$row=0) {
250 return mysqli_data_seek($qhandle,$row);
254 * db_result() - Returns a field from a result set
256 * @param string Query result set handle
257 * @param int Row number
258 * @param string Field name
260 function db_result($qhandle,$row,$field) {
262 if (!mysqli_data_seek($qhandle,$row)) {
266 $row_data = mysqli_fetch_array($qhandle, MYSQLI_BOTH);
270 return $row_data[$field];
274 * db_numfields() - Returns the number of fields in this result set
276 * @param string Query result set handle
278 function db_numfields($lhandle) {
279 return mysqli_num_fields($lhandle);
283 * db_fieldname() - Returns the name of a field in this result set
285 * @param string Query result set handle
286 * @param int Column number
288 function db_fieldname($lhandle,$fnumber) {
289 $fieldinfo=mysqli_fetch_field_direct($lhandle,$fnumber);
291 return $fieldinfo->name;
298 * db_affected_rows() - Returns the number of rows changed in the last query
300 * @param string Query result set handle
302 function db_affected_rows() {
305 return mysqli_affected_rows($conn);
309 * db_fetch_array() - Fetch an array
311 * Returns an associative array from
312 * the current row of this database result
313 * Use db_reset_result to seek a particular row
315 * @param string Query result set handle
317 function db_fetch_array($qhandle) {
318 return @mysqli_fetch_array($qhandle);
322 * db_insertid() - Returns the last primary key from an insert
324 * @param string Query result set handle
325 * @param string Is the name of the table you inserted into
326 * @param string Is the field name of the primary key
328 function db_insertid($qhandle,$table_name,$pkey_field_name) {
331 return mysqli_insert_id($conn);
335 * db_error() - Returns the last error from the database
337 function db_error() {
340 return mysqli_error($conn);
344 * system_cleanup() - In the future, we may wish to do a number
345 * of cleanup functions at script termination.
347 * For now, we just abort any in-process transaction.
349 function system_cleanup() {
350 global $_sys_db_transaction_level;
351 if ($_sys_db_transaction_level > 0) {
352 echo "Open transaction detected!!!";
353 db_query("ROLLBACK");
357 function db_drop_table_if_exists ($tn) {
358 $sql = "DROP TABLE IF EXISTS $tn;";
359 $rel = db_query ($sql);
363 function db_drop_sequence_if_exists ($tn) {
366 function db_log($message) {
370 $fhlog = fopen('/tmp/db.log', 'a');
374 fwrite($fhlog, $message);
379 function db_log_entry($func, $args) {
380 db_log("\nEntered ".$func.'('.$args.")\n");
383 function db_log_exit($func, $result = NULL) {
384 db_log('Exited '.$func.' returned '.$result."\n");
387 function db_log_dbentry($func, $args) {
388 db_log('Entered '.$func.'('.$args.")\n");
391 function db_log_dbexit($func, $result = NULL) {
392 db_log('Exited '.$func.' returned '.$result."\n");
396 db_log('Database error = '.$err."\n");