4 * Nucleus: PHP/MySQL Weblog CMS (http://nucleuscms.org/)
5 * Copyright (C) 2002-2011 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-2011 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) {
36 if (!defined('_CHARSET')) {
37 define('_CHARSET', 'UTF-8');
39 if (!defined('_HTML_XML_NAME_SPACE_AND_LANG_CODE')) {
40 define('_HTML_XML_NAME_SPACE_AND_LANG_CODE', 'xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-us" lang="en-us"');
42 sendContentType('text/html','',_CHARSET);
44 <html <?php echo _HTML_XML_NAME_SPACE_AND_LANG_CODE; ?>>
45 <head><meta http-equiv="Content-Type" content="text/html; charset=<?php echo _CHARSET?>" />
46 <title><?php echo htmlspecialchars($title,ENT_QUOTES)?></title></head>
48 <h1><?php echo htmlspecialchars($title,ENT_QUOTES)?></h1>
57 * Connects to mysql server
59 function sql_connect_args($mysql_host = 'localhost', $mysql_user = '', $mysql_password = '', $mysql_database = '') {
60 global $MYSQL_HANDLER;
63 if (strpos($mysql_host,':') === false) {
69 list($host,$port) = explode(":",$mysql_host);
72 $port = ';port='.trim($port);
80 switch ($MYSQL_HANDLER[1]) {
83 if (is_numeric($portnum)) $port = ':'.intval($portnum);
85 $DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$mysql_database, $mysql_user, $mysql_password);
88 if (is_numeric($portnum)) $port = ','.intval($portnum);
90 $DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$mysql_database, $mysql_user, $mysql_password);
93 if (is_numeric($portnum)) $port = ':'.intval($portnum);
95 $DBH = new PDO($MYSQL_HANDLER[1].':dbname=//'.$host.$port.'/'.$mysql_database, $mysql_user, $mysql_password);
98 if (is_numeric($portnum)) $port = ';PORT='.intval($portnum);
100 $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);
104 if (is_numeric($portnum)) $port = ';port='.intval($portnum);
106 $DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$mysql_database, $mysql_user, $mysql_password);
110 if (is_numeric($portnum)) $port = ':'.intval($portnum);
112 $DBH = new PDO($MYSQL_HANDLER[1].':'.$mysql_database, $mysql_user, $mysql_password);
116 $DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$mysql_database, $mysql_user, $mysql_password);
122 } catch (PDOException $e) {
124 startUpError('<p>a1 Error!: ' . $e->getMessage() . '</p>', 'Connect Error');
126 //echo '<hr />DBH: '.print_r($DBH,true).'<hr />';
131 * Connects to mysql server
133 function sql_connect() {
134 global $MYSQL_HOST, $MYSQL_USER, $MYSQL_PASSWORD, $MYSQL_DATABASE, $MYSQL_CONN, $MYSQL_HANDLER, $SQL_DBH;
137 if (strpos($MYSQL_HOST,':') === false) {
142 list($host,$port) = explode(":",$MYSQL_HOST);
145 $port = ';port='.trim($port);
153 switch ($MYSQL_HANDLER[1]) {
156 if (is_numeric($portnum)) $port = ':'.intval($portnum);
158 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
161 if (is_numeric($portnum)) $port = ','.intval($portnum);
163 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
166 if (is_numeric($portnum)) $port = ':'.intval($portnum);
168 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':dbname=//'.$host.$port.'/'.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
171 if (is_numeric($portnum)) $port = ';PORT='.intval($portnum);
173 $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);
177 if (is_numeric($portnum)) $port = ';port='.intval($portnum);
179 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
183 if (is_numeric($portnum)) $port = ':'.intval($portnum);
185 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':'.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
189 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
193 //$SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
195 // <add for garble measure>
196 if (strpos($MYSQL_HANDLER[1], 'mysql') === 0) {
197 if (defined('_CHARSET')){
200 $resource = $SQL_DBH->query("show variables LIKE 'character_set_database'");
201 $resource->bindColumn('Value', $charset);
202 $resource->fetchAll();
203 // in trouble of encoding,uncomment the following line.
204 // $charset = "ujis";
205 // $charset = "utf8";
207 sql_set_charset_jp($charset);
209 // </add for garble measure>*/
210 } catch (PDOException $e) {
212 startUpError('<p>a2 Error!: ' . $e->getMessage() . '</p>', 'Connect Error');
214 // echo '<hr />DBH: '.print_r($SQL_DBH,true).'<hr />';
215 $MYSQL_CONN &= $SQL_DBH;
221 * disconnects from SQL server
223 function sql_disconnect(&$dbh=NULL) {
225 if (is_null($dbh)) $SQL_DBH = NULL;
229 function sql_close(&$dbh=NULL) {
231 if (is_null($dbh)) $SQL_DBH = NULL;
236 * executes an SQL query
238 function sql_query($query,$dbh=NULL) {
239 global $SQLCount,$SQL_DBH;
241 //echo '<hr />SQL_DBH: ';
243 //echo '<hr />DBH: ';
246 //echo $query.'<hr />';
247 if (is_null($dbh)) $res = $SQL_DBH->query($query);
248 else $res = $dbh->query($query);
249 if ($res->errorCode() != '00000') {
250 $errors = $res->errorInfo();
251 print("SQL error with query $query: " . $errors[0].'-'.$errors[1].' '.$errors[2] . '<p />');
258 * executes an SQL error
260 function sql_error($dbh=NULL)
263 if (is_null($dbh)) $error = $SQL_DBH->errorInfo();
264 else $error = $dbh->errorInfo();
265 if ($error[0] != '00000') {
266 return $error[0].'-'.$error[1].' '.$error[2];
272 * executes an SQL db select
274 function sql_select_db($db,&$dbh=NULL)
276 global $MYSQL_HOST, $MYSQL_USER, $MYSQL_PASSWORD, $MYSQL_DATABASE, $MYSQL_CONN, $MYSQL_HANDLER, $SQL_DBH;
277 //echo '<hr />'.print_r($dbh,true).'<hr />';
282 list($host,$port) = explode(":",$MYSQL_HOST);
285 $port = ';port='.trim($port);
291 //$SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.trim($host).$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
292 //$SQL_DBH = sql_connect();
293 switch ($MYSQL_HANDLER[1]) {
296 if (is_numeric($portnum)) $port = ':'.intval($portnum);
298 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
301 if (is_numeric($portnum)) $port = ','.intval($portnum);
303 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
306 if (is_numeric($portnum)) $port = ':'.intval($portnum);
308 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':dbname=//'.$host.$port.'/'.$db, $MYSQL_USER, $MYSQL_PASSWORD);
311 if (is_numeric($portnum)) $port = ';PORT='.intval($portnum);
313 $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);
317 if (is_numeric($portnum)) $port = ';port='.intval($portnum);
319 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
323 if (is_numeric($portnum)) $port = ':'.intval($portnum);
325 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':'.$db, $MYSQL_USER, $MYSQL_PASSWORD);
329 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
333 } catch (PDOException $e) {
334 startUpError('<p>a3 Error!: ' . $e->getMessage() . '</p>', 'Connect Error');
339 if ($dbh->exec("USE $db") !== false) return 1;
345 * executes an SQL real escape
347 function sql_real_escape_string($val,$dbh=NULL)
349 return addslashes($val);
353 * executes an PDO::quote() like escape, ie adds quotes arround the string and escapes chars as needed
355 function sql_quote_string($val,$dbh=NULL) {
358 return $SQL_DBH->quote($val);
360 return $dbh->quote($val);
364 * executes an SQL insert id
366 function sql_insert_id($dbh=NULL)
370 return $SQL_DBH->lastInsertId();
372 return $dbh->lastInsertId();
376 * executes an SQL result request
378 function sql_result($res, $row = 0, $col = 0)
381 if (intval($row) < 1) {
382 $results = $res->fetch(PDO::FETCH_BOTH);
383 return $results[$col];
386 for ($i = 0; $i < intval($row); $i++) {
387 $results = $res->fetch(PDO::FETCH_BOTH);
389 $results = $res->fetch(PDO::FETCH_BOTH);
390 return $results[$col];
395 * frees sql result resources
397 function sql_free_result($res)
404 * returns number of rows in SQL result
406 function sql_num_rows($res)
408 return $res->rowCount();
412 * returns number of rows affected by SQL query
414 function sql_affected_rows($res)
416 return $res->rowCount();
420 * Get number of fields in result
422 function sql_num_fields($res)
424 return $res->columnCount();
428 * fetches next row of SQL result as an associative array
430 function sql_fetch_assoc($res)
433 $results = $res->fetch(PDO::FETCH_ASSOC);
438 * Fetch a result row as an associative array, a numeric array, or both
440 function sql_fetch_array($res)
443 $results = $res->fetch(PDO::FETCH_BOTH);
448 * fetches next row of SQL result as an object
450 function sql_fetch_object($res)
453 $results = $res->fetchObject();
458 * Get a result row as an enumerated array
460 function sql_fetch_row($res)
463 $results = $res->fetch(PDO::FETCH_NUM);
468 * Get column information from a result and return as an object
470 function sql_fetch_field($res,$offset = 0)
474 $results = $res->getColumnMeta($offset);
475 foreach($results as $key=>$value) {
482 * Get current system status (returns string)
484 function sql_stat($dbh=NULL)
495 * Returns the name of the character set
497 function sql_client_encoding($dbh=NULL)
508 * Get SQL client version
510 function sql_get_client_info()
513 return $SQL_DBH->getAttribute(constant("PDO::ATTR_CLIENT_VERSION"));
517 * Get SQL server version
519 function sql_get_server_info($dbh=NULL)
523 return $SQL_DBH->getAttribute(constant("PDO::ATTR_SERVER_VERSION"));
525 return $dbh->getAttribute(constant("PDO::ATTR_SERVER_VERSION"));
529 * Returns a string describing the type of SQL connection in use for the connection or FALSE on failure
531 function sql_get_host_info($dbh=NULL)
535 return $SQL_DBH->getAttribute(constant("PDO::ATTR_SERVER_INFO"));
537 return $dbh->getAttribute(constant("PDO::ATTR_SERVER_INFO"));
541 * Returns the SQL protocol on success, or FALSE on failure.
543 function sql_get_proto_info($dbh=NULL)
554 * Get the name of the specified field in a result
556 function sql_field_name($res, $offset = 0)
558 $column = $res->getColumnMeta($offset);
560 return $column['name'];
565 /**************************************************************************
566 Unimplemented mysql_* functions
568 # mysql_ data_ seek (maybe useful)
569 # mysql_ errno (maybe useful)
570 # mysql_ fetch_ lengths (maybe useful)
571 # mysql_ field_ flags (maybe useful)
572 # mysql_ field_ len (maybe useful)
573 # mysql_ field_ name (maybe useful)
574 # mysql_ field_ seek (maybe useful)
575 # mysql_ field_ table (maybe useful)
576 # mysql_ field_ type (maybe useful)
577 # mysql_ info (maybe useful)
578 # mysql_ list_ processes (maybe useful)
579 # mysql_ ping (maybe useful)
580 # mysql_ set_ charset (maybe useful, requires php >=5.2.3 and mysql >=5.0.7)
581 # mysql_ thread_ id (maybe useful)
583 # mysql_ db_ name (useful only if working on multiple dbs which we do not do)
584 # mysql_ list_ dbs (useful only if working on multiple dbs which we do not do)
586 # mysql_ pconnect (probably not useful and could cause some unintended performance issues)
587 # mysql_ unbuffered_ query (possibly useful, but complicated and not supported by all database drivers (pdo))
589 # mysql_ change_ user (deprecated)
590 # mysql_ create_ db (deprecated)
591 # mysql_ db_ query (deprecated)
592 # mysql_ drop_ db (deprecated)
593 # mysql_ escape_ string (deprecated)
594 # mysql_ list_ fields (deprecated)
595 # mysql_ list_ tables (deprecated)
596 # mysql_ tablename (deprecated)
598 *******************************************************************/
601 * for preventing I/O strings from auto-detecting the charactor encodings by MySQL
603 * Jan.20, 2011 by kotorisan and cacher
604 * refering to their conversation below,
605 * http://japan.nucleuscms.org/bb/viewtopic.php?p=26581
607 * NOTE: shift_jis is only supported for output. Using shift_jis in DB is prohibited.
608 * NOTE: iso-8859-x,windows-125x if _CHARSET is unset.
610 function sql_set_charset_jp($charset) {
611 global $MYSQL_HANDLER,$SQL_DBH;
612 if (strpos($MYSQL_HANDLER[1], 'mysql') === 0) {
613 switch(strtolower($charset)){
\r
623 $charset = 'gb2312';
\r
632 $charset = 'latin1';
\r
635 $mySqlVer = implode('.', array_map('intval', explode('.', sql_get_server_info())));
636 if (version_compare($mySqlVer, '4.1.0', '>=')) {
637 $res = $SQL_DBH->exec("SET CHARACTER SET " . $charset);