mysqli.php -
index
<?php
/* MYSQLI.PHP - all MySQL database access is done through here
See the file MYSQL for more information about this module.
See the file DATABASE for a description of the database design.
There are two globals here that are used by most of the functions. (As any
THIS module that has global data, these globals are "internal" only and are
not used anywhere else. They probably should be renamed though.) Using such
a global is not the smartest way of doing things but it is cheap and easy --
and if we had implemented a class there would still be a global, just one
created by NEW somewhere...
The following functions are defined:
dbstart()
dbquery($sql)
dblistrecords()
dbreadrecord($id)
dbgetrecord($id)
dbnewrecord($data)
dbputrecord($id, $data)
dbdelrecord($id)
dblistcomments($entryid)
dbreadcomment($id)
dbgetcomment($id)
dbwritecomment($entryid, $record)
dbputcomment($id, $data)
dbdelcomment($id)
dbreaduser($userid)
dbputuser($uid, $record, $update = FALSE)
dbreadvisitor($from)
dbputvisitor($from, $data, $update = FALSE)
*/
setmodule();
_mysqli_config();
function _mysqli_config() {
global $db;
debug(DB_USER. ','. DB_NAME);
$db = new db(DB_USER,DB_PASS);
if ($db->connect_errno)
_mysql_error('server rejected the passed connect credentials.');
// select database, optionally create if not found
if ($db->select_db(DB_NAME) == FALSE) {
debug("unable to select '".DB_NAME."'");
if (config('nodbcreate'))
user_error("Setting prevents CREATE DATABASE '".DB_NAME."'",E_USER_ERROR);
$db->query("CREATE DATABASE ".DB_NAME);
if ($db->select_db(DB_NAME) == FALSE)
_mysql_error('server rejected CREATE DATABASE');
}
// nonexisting tables are created in the following function
}
/* dbstart - will create, if allowed, if TABLE(s) not exist */
function dbstart() {
global $db, $db_table;
// this needs to change:
$db_table = ($t = config('section')) ? : 'root';
debug("TABLE '$db_table'");
// with 'dbcreated', adding a
if (config('dbcreated')) // section will not get it's
return; // "first post"
if ($db->query("SHOW TABLES LIKE '$db_table'") == FALSE) {
debug("TABLE '$db_table' not found");
if (config('nodbcreate'))
user_error("Setting prevents CREATE TABLE '$db_table'.",E_USER_ERROR);
// this include has the "temporary" mode setting check in it:
include 'dat/sql.php'; // the SQL table definitions
foreach ($tables as $sql)
if ($db->query($sql) == FALSE)
_mysql_error('server rejected CREATE TABLE');
// there are two tables in the $tables array, one for
// posts and one for comments
// importing data is slow and it is MySQL's fault as it creates
// many files and directories for the data while gets written
// (fast when TEMPORARY tables, slow when real tables)
// and MySQL's stat of 'Queries per second avg: 0.46' is no
// good because it's an AVERAGE and not actual query time!
$dir = config('importdir');
if (is_dir($dir) && ($files = glob($dir.'*.txt'))) {
natsort($files);
foreach ($files as $file) {
$data = file_get_contents($file);
dbnewrecord($data);
}
}
else
if (!config('noinstallpost')) {
$data = "title: Install Post\n".
"date: Jan-11, 2013, 9:42pm\n".
"\nWelcome to THIS - The Application.";
dbnewrecord($data);
}
}
if ($db->query("SHOW TABLES LIKE 'users'") == FALSE) {
debug("TABLE users not found");
if (config('nodbcreate'))
user_error("Setting prevents CREATE TABLE 'users'",E_USER_ERROR);
foreach ($user_tables as $sql)
if ($db->query($sql) == FALSE)
_mysql_error('server rejected CREATE TABLE');
// there're actually two tables created here, `users`
// and `visitors`; we assume if not the first then
// the other needs to be created too
$record = array("userid" => AD_NAME,
"password" => AD_PASS,
"body" => "Administrator Account"
);
dbputuser($record['userid'],$record);
}
}
// experimental
function dbcreate($table) {
global $db;
$res = $db->query("SHOW TABLES LIKE '$table'");
if ($res === FALSE)
return $db->error;
if (isset($res[0]))
return '';
$db_table = $table; // NOT the global
include 'dat/sql.php'; // the SQL table definitions
if ($db->query($tables[0]) == FALSE)
return $db->error;
if ($db->query($tables[1]) == FALSE)
return $db->error;
return 0;
}
function dbuse($table) {
global $db_table;
$db_table = $table;
}
// end experimental
/* dbquery - sometimes ya just wanna do a query */
function dbquery($sql) {
global $db;
return $db->query($sql);
}
function dberror() {
global $db;
return $db->error;
}
function dbtable() {
global $db_table;
return $db_table;
}
// be sure to look at MYSQL.PHP for a different way to implement these
// functions that we think are more efficient
/* "section" table */
function dblistrecords($id = NULL) {
global $db, $db_table;
$sql = "SELECT id FROM $db_table ORDER BY id";
if ($id !== NULL)
$sql = "SELECT id FROM $db_table WHERE id = $id";
return $db->query($sql);
}
function dbreadrecord($id) {
global $db, $db_table;
$sql = "SELECT body FROM $db_table WHERE id = $id";
$rec = $db->query($sql);
if ($rec == FALSE) return FALSE;
return arraytorecord($rec[0]);
}
// called by admin only (same as above but record is raw)
function dbgetrecord($id) {
global $db, $db_table;
$sql = "SELECT body FROM $db_table WHERE id = $id";
$rec = $db->query($sql);
if ($rec == FALSE) return FALSE;
return $rec[0];
}
function dbnewrecord(/*mixed*/ $data) {
global $db, $db_table;
if (is_array($data))
$data = recordtostring($data);
$data = addslashes($data);
$sql = "INSERT INTO $db_table (body) VALUES ('$data')";
return $db->query($sql);
}
function dbputrecord($id, /*mixed*/ $data) {
global $db, $db_table;
if (is_array($data))
$data = recordtostring($data);
$data = addslashes($data);
$sql = "UPDATE `$db_table` SET body = '$data' WHERE id = $id";
return $db->query($sql);
}
function dbdelrecord($id) {
global $db, $db_table;
$sql = "DELETE FROM $db_table WHERE id = $id";
return $db->query($sql);
}
/* `comments` table */
function dblistcomments($entryid) {
global $db, $db_table;
$sql = "SELECT id FROM {$db_table}_comments WHERE entryid = $entryid ORDER BY id";
$ids = $db->query($sql);
if ($ids === FALSE) return FALSE;
return $ids;
}
function dbreadcomment($id) {
global $db, $db_table;
$sql = "SELECT body FROM {$db_table}_comments WHERE id = $id";
$rec = $db->query($sql);
if ($rec == FALSE) return FALSE;
$record = arraytorecord($rec[0]);
$record['id'] = $id;
return $record;
}
function dbgetcomment($id) {
global $db, $db_table;
$sql = "SELECT body FROM {$db_table}_comments WHERE id = $id";
$rec = $db->query($sql);
if ($rec == FALSE) return FALSE;
return $rec[0];
}
function dbwritecomment($entryid, /*mixed*/ $record) {
global $db, $db_table;
if (!is_array($record))
$data = $record;
else
$data = recordtostring($record);
$data = addslashes($data);
$sql = "INSERT INTO {$db_table}_comments (body, entryid) ".
"VALUES ('$data', '$entryid')";
$res = $db->query($sql);
if ($res == FALSE) return FALSE;
$sql = "SELECT LAST_INSERT_ID()";
$rec = $db->query($sql);
return $rec[0];
}
function dbputcomment($id, $data) {
global $db, $db_table;
$data = addslashes($data);
$sql = "UPDATE {$db_table}_comments SET body = '$data' ".
"WHERE id = $id";
return $db->query($sql);
}
function dbdelcomment($id) {
global $db, $db_table;
$sql = "DELETE FROM {$db_table}_comments WHERE id = $id";
return $db->query($sql);
}
/* the `users` table */
// NOTE all calls to these functions are in INC/USERS.PHP
function dbreaduser($userid) {
global $db;
$sql = "SELECT body FROM users WHERE userid = '$userid'";
$rec = $db->query($sql);
if ($rec == FALSE)
return FALSE;
$rec[0] = rtrim($rec[0]);
$rec = explode("\n",$rec[0]);
$record = arraytorecord($rec);
return $record;
}
function dbputuser($uid, $record, $update = FALSE) {
global $db;
$data = recordtostring($record);
$data = addslashes($data);
$sql = "INSERT INTO users (userid, body) ".
"VALUES ('$uid', '$data')";
if ($update)
$sql = "UPDATE users SET body = '$data' ".
"WHERE userid = '$uid'";
return $db->query($sql);
}
/* the `visitor` table */
function dbvisitor($cmd, $from = NULL, $data = NULL) {
global $db, $visitor_sql;
if ($data) {
$data = recordtostring($data);
$data = addslashes($data);
}
if ($from)
$from = addslashes($from);
$visitor_sql = array(
'read' =>
"SELECT body FROM visitors WHERE name = '$from'",
'all' =>
"SELECT body FROM visitors",
'count' =>
"SELECT COUNT(*) FROM visitors",
'new' =>
"INSERT INTO visitors (name, body) VALUES ('$from', '$data')",
'update' =>
"UPDATE visitors SET body = '$data' WHERE name = '$from'",
'delete' =>
"DELETE FROM visitors WHERE name = '$from'",
'lookup' =>
"SELECT body FROM visitors WHERE name REGEXP '^[$from]'",
'range' =>
"SELECT COUNT(*) FROM visitors WHERE name REGEXP '^[$from]'"
);
$sql = $visitor_sql[$cmd];
$res = $db->query($sql);
if ($res === FALSE || $res === TRUE)
return $res;
if ($res === array())
return 0;
if (is_numeric($res[0]))
return $res[0];
foreach ($res as $r)
$records[] = arraytorecord($r);
return isset($records) ? $records : $res;
}
// yes, these are like an encapsulation of an encapsulation; we want that!
function dbreadvisitor($from) {
$rec = dbvisitor('read',$from);
if (!is_array($rec)) return $rec;
return $rec[0];
}
function dbnewvisitor($from, $record) {
return dbvisitor('new',$from,$record);
}
function dbputvisitor($from, $record) {
return dbvisitor('update',$from,$record);
}
function dbdelvisitor($from) {
return dbvisitor('delete',$from);
}
/* common error handler */
function _mysql_error($msg) {
global $db;
$msg = 'mysqli: ' . $msg;
if (debug()) {
$e = ($db->connect_error) ? $db->connect_error : $db->error;
$v = $db->server_info;
getcaller($file,$line,$func);
$msg .= "<br><br>Additional Information:";
$msg .= "<br>_mysql_error() called from $func() line $line in $file";
$msg .= "<br>MySQLi v$v reports error: \"$e\"";
}
error($msg,'fatal'); // exits
}
/* the DB class */
class db extends mysqli {
function __construct($user, $pass) {
$host = 'localhost';
if (config('mysqlpersistent'))
$host = 'p:'.$host;
@parent::__construct($host,$user,$pass);
}
function query($sql) {
debug($sql);
$res = parent::query($sql);
debug($res);
if ($res === false)
debug($this->error);
if (config('debugdbstat'))
debug($this->stat,-1);
if ($res === false || $res === true)
return $res; // *
$rows = array();
if ($res->num_rows)
while ($row = $res->fetch_array(MYSQLI_NUM))
$rows[] = str_replace("\r",'',$row[0]);
/* PMA can put CRs in! grrrr! */
$res->free();
return $rows;
}
}
/*
* This is odd... an object of type mysqli_result can be this:
object(mysqli_result)#2 (5) {
["current_field"]=>
int(0)
["field_count"]=>
int(1)
["lengths"]=>
NULL
["num_rows"]=>
int(1)
["type"]=>
int(0)
}
and when it is that, the loose test '$res == FALSE' is TRUE!
*/
?>