4 * Nucleus: PHP/MySQL Weblog CMS (http://nucleuscms.org/)
5 * Copyright (C) 2002-2010 The Nucleus Group
7 * This program is free software; you can redistribute it and/or
8 * modify it under the terms of the GNU General Public License
9 * as published by the Free Software Foundation; either version 2
10 * of the License, or (at your option) any later version.
11 * (see nucleus/documentation/index.html#license for more info)
14 * @license http://nucleuscms.org/license.txt GNU General Public License
15 * @copyright Copyright (C) 2002-2010 The Nucleus Group
20 * complete sql_* wrappers for mysql functions
22 * functions moved from globalfunctions.php: sql_connect, sql_disconnect, sql_query
30 if (!function_exists('sql_fetch_assoc'))
33 * Errors before the database connection has been made
35 function startUpError($msg, $title) {
37 <html xmlns="http://www.w3.org/1999/xhtml">
38 <head><title><?php echo htmlspecialchars($title)?></title></head>
40 <h1><?php echo htmlspecialchars($title)?></h1>
48 * Connects to mysql server
50 function sql_connect_args($mysql_host = 'localhost', $mysql_user = '', $mysql_password = '', $mysql_database = '') {
51 global $MYSQL_HANDLER;
54 if (strpos($mysql_host,':') === false) {
60 list($host,$port) = explode(":",$mysql_host);
63 $port = ';port='.trim($port);
71 switch ($MYSQL_HANDLER[1]) {
74 if (is_numeric($portnum)) $port = ':'.intval($portnum);
76 $DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$mysql_database, $mysql_user, $mysql_password);
79 if (is_numeric($portnum)) $port = ','.intval($portnum);
81 $DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$mysql_database, $mysql_user, $mysql_password);
84 if (is_numeric($portnum)) $port = ':'.intval($portnum);
86 $DBH = new PDO($MYSQL_HANDLER[1].':dbname=//'.$host.$port.'/'.$mysql_database, $mysql_user, $mysql_password);
89 if (is_numeric($portnum)) $port = ';PORT='.intval($portnum);
91 $DBH = new PDO($MYSQL_HANDLER[1].':DRIVER={IBM DB2 ODBC DRIVER};HOSTNAME='.$host.$port.';DATABASE='.$mysql_database.';PROTOCOL=TCPIP;UID='.$mysql_user.';PWD='.$mysql_password);
95 if (is_numeric($portnum)) $port = ';port='.intval($portnum);
97 $DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$mysql_database, $mysql_user, $mysql_password);
101 if (is_numeric($portnum)) $port = ':'.intval($portnum);
103 $DBH = new PDO($MYSQL_HANDLER[1].':'.$mysql_database, $mysql_user, $mysql_password);
107 $DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$mysql_database, $mysql_user, $mysql_password);
113 } catch (PDOException $e) {
115 startUpError('<p>a1 Error!: ' . $e->getMessage() . '</p>', 'Connect Error');
117 //echo '<hr />DBH: '.print_r($DBH,true).'<hr />';
122 * Connects to mysql server
124 function sql_connect() {
125 global $MYSQL_HOST, $MYSQL_USER, $MYSQL_PASSWORD, $MYSQL_DATABASE, $MYSQL_CONN, $MYSQL_HANDLER, $SQL_DBH;
128 if (strpos($MYSQL_HOST,':') === false) {
133 list($host,$port) = explode(":",$MYSQL_HOST);
136 $port = ';port='.trim($port);
144 switch ($MYSQL_HANDLER[1]) {
147 if (is_numeric($portnum)) $port = ':'.intval($portnum);
149 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
152 if (is_numeric($portnum)) $port = ','.intval($portnum);
154 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
157 if (is_numeric($portnum)) $port = ':'.intval($portnum);
159 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':dbname=//'.$host.$port.'/'.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
162 if (is_numeric($portnum)) $port = ';PORT='.intval($portnum);
164 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':DRIVER={IBM DB2 ODBC DRIVER};HOSTNAME='.$host.$port.';DATABASE='.$MYSQL_DATABASE.';PROTOCOL=TCPIP;UID='.$MYSQL_USER.';PWD='.$MYSQL_PASSWORD);
168 if (is_numeric($portnum)) $port = ';port='.intval($portnum);
170 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
174 if (is_numeric($portnum)) $port = ':'.intval($portnum);
176 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':'.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
180 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
184 //$SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
186 // <add for garble measure>
187 if (strpos($MYSQL_HANDLER[1], 'mysql') === 0) {
188 $resource = $SQL_DBH->query("show variables LIKE 'character_set_database'");
189 $resource->bindColumn('Value', $charset);
190 $resource->fetchAll();
191 $SQL_DBH->exec("SET CHARACTER SET " . $charset);
193 // </add for garble measure>*/
194 } catch (PDOException $e) {
196 startUpError('<p>a2 Error!: ' . $e->getMessage() . '</p>', 'Connect Error');
198 // echo '<hr />DBH: '.print_r($SQL_DBH,true).'<hr />';
199 $MYSQL_CONN &= $SQL_DBH;
205 * disconnects from SQL server
207 function sql_disconnect(&$dbh=NULL) {
209 if (is_null($dbh)) $SQL_DBH = NULL;
213 function sql_close(&$dbh=NULL) {
215 if (is_null($dbh)) $SQL_DBH = NULL;
220 * executes an SQL query
222 function sql_query($query,$dbh=NULL) {
223 global $SQLCount,$SQL_DBH;
225 //echo '<hr />SQL_DBH: ';
227 //echo '<hr />DBH: ';
230 //echo $query.'<hr />';
231 if (is_null($dbh)) $res = $SQL_DBH->query($query);
232 else $res = $dbh->query($query);
233 if ($res->errorCode() != '00000') {
234 $errors = $res->errorInfo();
235 print("SQL error with query $query: " . $errors[0].'-'.$errors[1].' '.$errors[2] . '<p />');
242 * executes an SQL error
244 function sql_error($dbh=NULL)
247 if (is_null($dbh)) $error = $SQL_DBH->errorInfo();
248 else $error = $dbh->errorInfo();
249 if ($error[0] != '00000') {
250 return $error[0].'-'.$error[1].' '.$error[2];
256 * executes an SQL db select
258 function sql_select_db($db,&$dbh=NULL)
260 global $MYSQL_HOST, $MYSQL_USER, $MYSQL_PASSWORD, $MYSQL_DATABASE, $MYSQL_CONN, $MYSQL_HANDLER, $SQL_DBH;
261 //echo '<hr />'.print_r($dbh,true).'<hr />';
266 list($host,$port) = explode(":",$MYSQL_HOST);
269 $port = ';port='.trim($port);
275 //$SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.trim($host).$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
276 //$SQL_DBH = sql_connect();
277 switch ($MYSQL_HANDLER[1]) {
280 if (is_numeric($portnum)) $port = ':'.intval($portnum);
282 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
285 if (is_numeric($portnum)) $port = ','.intval($portnum);
287 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
290 if (is_numeric($portnum)) $port = ':'.intval($portnum);
292 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':dbname=//'.$host.$port.'/'.$db, $MYSQL_USER, $MYSQL_PASSWORD);
295 if (is_numeric($portnum)) $port = ';PORT='.intval($portnum);
297 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':DRIVER={IBM DB2 ODBC DRIVER};HOSTNAME='.$host.$port.';DATABASE='.$db.';PROTOCOL=TCPIP;UID='.$MYSQL_USER.';PWD='.$MYSQL_PASSWORD);
301 if (is_numeric($portnum)) $port = ';port='.intval($portnum);
303 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
307 if (is_numeric($portnum)) $port = ':'.intval($portnum);
309 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':'.$db, $MYSQL_USER, $MYSQL_PASSWORD);
313 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
317 } catch (PDOException $e) {
318 startUpError('<p>a3 Error!: ' . $e->getMessage() . '</p>', 'Connect Error');
323 if ($dbh->exec("USE $db") !== false) return 1;
329 * executes an SQL real escape
331 function sql_real_escape_string($val,$dbh=NULL)
333 return addslashes($val);
337 * executes an PDO::quote() like escape, ie adds quotes arround the string and escapes chars as needed
339 function sql_quote_string($val,$dbh=NULL) {
342 return $SQL_DBH->quote($val);
344 return $dbh->quote($val);
348 * executes an SQL insert id
350 function sql_insert_id($dbh=NULL)
354 return $SQL_DBH->lastInsertId();
356 return $dbh->lastInsertId();
360 * executes an SQL result request
362 function sql_result($res, $row = 0, $col = 0)
365 if (intval($row) < 1) {
366 $results = $res->fetch(PDO::FETCH_BOTH);
367 return $results[$col];
370 for ($i = 0; $i < intval($row); $i++) {
371 $results = $res->fetch(PDO::FETCH_BOTH);
373 $results = $res->fetch(PDO::FETCH_BOTH);
374 return $results[$col];
379 * frees sql result resources
381 function sql_free_result($res)
388 * returns number of rows in SQL result
390 function sql_num_rows($res)
392 return $res->rowCount();
396 * returns number of rows affected by SQL query
398 function sql_affected_rows($res)
400 return $res->rowCount();
404 * Get number of fields in result
406 function sql_num_fields($res)
408 return $res->columnCount();
412 * fetches next row of SQL result as an associative array
414 function sql_fetch_assoc($res)
417 $results = $res->fetch(PDO::FETCH_ASSOC);
422 * Fetch a result row as an associative array, a numeric array, or both
424 function sql_fetch_array($res)
427 $results = $res->fetch(PDO::FETCH_BOTH);
432 * fetches next row of SQL result as an object
434 function sql_fetch_object($res)
437 $results = $res->fetchObject();
442 * Get a result row as an enumerated array
444 function sql_fetch_row($res)
447 $results = $res->fetch(PDO::FETCH_NUM);
452 * Get column information from a result and return as an object
454 function sql_fetch_field($res,$offset = 0)
458 $results = $res->getColumnMeta($offset);
459 foreach($results as $key=>$value) {
466 * Get current system status (returns string)
468 function sql_stat($dbh=NULL)
479 * Returns the name of the character set
481 function sql_client_encoding($dbh=NULL)
492 * Get SQL client version
494 function sql_get_client_info()
497 return $SQL_DBH->getAttribute(constant("PDO::ATTR_CLIENT_VERSION"));
501 * Get SQL server version
503 function sql_get_server_info($dbh=NULL)
507 return $SQL_DBH->getAttribute(constant("PDO::ATTR_SERVER_VERSION"));
509 return $dbh->getAttribute(constant("PDO::ATTR_SERVER_VERSION"));
513 * Returns a string describing the type of SQL connection in use for the connection or FALSE on failure
515 function sql_get_host_info($dbh=NULL)
519 return $SQL_DBH->getAttribute(constant("PDO::ATTR_SERVER_INFO"));
521 return $dbh->getAttribute(constant("PDO::ATTR_SERVER_INFO"));
525 * Returns the SQL protocol on success, or FALSE on failure.
527 function sql_get_proto_info($dbh=NULL)
538 * Get the name of the specified field in a result
540 function sql_field_name($res, $offset = 0)
542 $column = $res->getColumnMeta($offset);
544 return $column['name'];
549 /**************************************************************************
550 Unimplemented mysql_* functions
552 # mysql_ data_ seek (maybe useful)
553 # mysql_ errno (maybe useful)
554 # mysql_ fetch_ lengths (maybe useful)
555 # mysql_ field_ flags (maybe useful)
556 # mysql_ field_ len (maybe useful)
557 # mysql_ field_ name (maybe useful)
558 # mysql_ field_ seek (maybe useful)
559 # mysql_ field_ table (maybe useful)
560 # mysql_ field_ type (maybe useful)
561 # mysql_ info (maybe useful)
562 # mysql_ list_ processes (maybe useful)
563 # mysql_ ping (maybe useful)
564 # mysql_ set_ charset (maybe useful, requires php >=5.2.3 and mysql >=5.0.7)
565 # mysql_ thread_ id (maybe useful)
567 # mysql_ db_ name (useful only if working on multiple dbs which we do not do)
568 # mysql_ list_ dbs (useful only if working on multiple dbs which we do not do)
570 # mysql_ pconnect (probably not useful and could cause some unintended performance issues)
571 # mysql_ unbuffered_ query (possibly useful, but complicated and not supported by all database drivers (pdo))
573 # mysql_ change_ user (deprecated)
574 # mysql_ create_ db (deprecated)
575 # mysql_ db_ query (deprecated)
576 # mysql_ drop_ db (deprecated)
577 # mysql_ escape_ string (deprecated)
578 # mysql_ list_ fields (deprecated)
579 # mysql_ list_ tables (deprecated)
580 # mysql_ tablename (deprecated)
582 *******************************************************************/