index
<?php
/* MYSQL.PHP - all MySQL database access is done through here
all SQL query strings are in here
This module will use the mysqli extension if available, otherwise the mysql
extenstion will be used (or if the setting 'mysql' is set to non-zero in
CONFIG.INI).
See the file MYSQL for more information about this module.
See the file DATABASE for a description of the database design.
The following functions are defined:
These are for posts:
dblistrecords($id)
dbreadrecord($id)
dbgetrecord($id)
dbnewrecord($data)
dbputrecord($id, $data)
dbdelrecord($id)
These are for comments:
dblistcomments($entryid)
dbreadcomment($id)
dbgetcomment($id)
dbwritecomment($entryid, $record)
dbputcomment($id, $data)
dbdelcomment($id)
These are for users (there is one, Admin):
dbreaduser($userid)
dbnewuser($userid, $record)
dbputuser($userid, $record)
These are for Visitors:
dbreadvisitor($from)
dbnewvisitor($from, $record)
dbputvisitor($from, $data)
dbdelvisitor($from)
*/
function dberror() {
return dbrecord('error');
}
function dblistrecords($id = NULL) {
return dbrecord('list',$id);
}
function dbreadrecord($id) {
return dbrecord('read',$id);
}
function dbgetrecord($id) {
return dbrecord('get',$id);
}
function dbnewrecord(/*mixed*/ $data) {
return dbrecord('new',NULL,$data);
}
function dbputrecord($id, /*mixed*/ $data) {
return dbrecord('put',$id,$data);
}
function dbdelrecord($id) {
return dbrecord('delete',$id);
}
function dbrecord($cmd, $id = NULL, $data = NULL) {
debug($cmd);
// the first two commands here were more recently "tacked on" and not often
// used (and might go away)
if ($cmd == 'error')
return _mysql_error();
if ($cmd == 'table')
return _mysql('table'); // get saved value
if ($data) {
if (is_array($data))
$data = recordtostring($data);
$data = _mysql_real_escape_string($data);
}
$db_table = _mysql('table'); // get saved value
$sql = array(
'count' => "SELECT COUNT(*) FROM `$db_table`",
'_count' => "SELECT COUNT(*) FROM `$db_table` WHERE id = $id",
'list' => "SELECT id FROM `$db_table` ORDER BY id",
'_list' => "SELECT id FROM `$db_table` WHERE id = $id",
'id' => "SELECT id FROM `$db_table` WHERE id = $id",
'read' => "SELECT body FROM `$db_table` WHERE id = $id",
'get' => "SELECT body FROM `$db_table` WHERE id = $id",
// called by admin only (same as above but record is raw)
'new' => "INSERT INTO `$db_table` (body) VALUES ('$data')",
'put' => "UPDATE `$db_table` SET body = '$data' WHERE id = $id",
'delete' => "DELETE FROM `$db_table` WHERE id = $id",
);
if ($cmd == 'list' && $id != NULL) // special case
$cmd = '_list';
if ($cmd == 'count' && $id != NULL) // special case
$cmd = '_count';
$rec = _my_mysql_query($sql[$cmd]);
if ($rec === FALSE || $rec === TRUE)
return $rec;
if ($cmd == 'read')
return arraytorecord($rec[0]);
if ($cmd == 'get')
return $rec[0];
return $rec;
}
/* `comments` table */
function dbcountcomments($entryid) {
return dbcomment('count',$entryid);
}
function dblistcomments($entryid) {
return dbcomment('list',$entryid);
}
function dbreadcomment($id) {
return dbcomment('read',$id);
}
function dbgetcomment($id) {
return dbcomment('get',$id);
}
function dbwritecomment($entryid, /*mixed*/ $record) {
return dbcomment('new',$entryid,$record);
}
function dbputcomment($id, $data) {
return dbcomment('put',$id);
}
function dbdelcomment($id) {
return dbcomment('delete',$id);
}
function dbcomment($cmd, $id, $data = '') {
debug($cmd);
$table = _mysql('table').'_comments';
if ($data) {
if (is_array($data))
$data = recordtostring($data);
$data = _mysql_real_escape_string($data);
}
$sql = array(
'count' => "SELECT COUNT(*) FROM `$table` WHERE entryid = $id",
'list' => "SELECT * FROM `$table` WHERE entryid = $id ORDER BY id",
'read' => "SELECT body FROM `$table` WHERE id = $id",
'get' => "SELECT body FROM `$table` WHERE id = $id",
'new' => "INSERT INTO `$table` (body, entryid) VALUES ('$data', '$id')",
'delete' => "DELETE FROM `$table` WHERE id = $id",
'put' => "UPDATE `$table` SET body = '$data' WHERE id = $id",
);
$rec = _my_mysql_query($sql[$cmd]);
if ($rec === FALSE)
return FALSE;
if ($cmd == 'count')
return $rec[0];
if ($cmd == 'read') {
$rec = arraytorecord($rec[0]);
$rec['id'] = $id;
return $rec;
}
if ($cmd == 'get')
return $rec[0];
if ($cmd == 'new') {
$sql = "SELECT LAST_INSERT_ID()";
$rec = _my_mysql_query($sql);
return $rec[0];
}
return $rec;
}
/* the `users` table */
function dbreaduser($userid) {
return dbuser('read',$userid);
}
function dbnewuser($userid, $record) {
return dbuser('new',$userid,$record);
}
function dbputuser($userid, $record) {
return dbuser('put',$userid,$record);
}
function dbuser($cmd, $userid, $data = NULL) {
debug($cmd);
if ($data) {
$data = recordtostring($data);
$data = _mysql_real_escape_string($data);
}
$sql = array(
'read' => "SELECT body FROM users WHERE userid = '$userid'",
'new' => "INSERT INTO users (userid, body) VALUES ('$userid', '$data')",
'put' => "UPDATE users SET body = '$data' WHERE userid = '$userid'",
);
$rec = _my_mysql_query($sql[$cmd]);
if ($rec == FALSE || $rec === TRUE)
return $rec;
$rec[0] = rtrim($rec[0]);
$rec = explode(THIS_EOL,$rec[0]);
$record = arraytorecord($rec);
return $record;
}
/* the `visitor` table */
function dbreadvisitor($from) {
return dbvisitor('read',$from);
}
function dbnewvisitor($from, $record) {
return dbvisitor('new',$from,$record);
}
function dbputvisitor($from, $record) {
return dbvisitor('put',$from,$record);
}
function dbdelvisitor($from) {
return dbvisitor('delete',$from);
}
function dbvisitor($cmd, $from = NULL, $data = NULL) {
debug("'$cmd','$from','$data'");
if ($data) {
$data = recordtostring($data);
$data = _mysql_real_escape_string($data);
}
if ($from)
$from = _mysql_real_escape_string($from);
$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')",
'put' => "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]'"
);
$res = _my_mysql_query($sql[$cmd]);
if ($res === FALSE || $res === TRUE)
return $res;
if ($res === array())
return 0;
if (is_numeric($res[0])) // COUNT()
return $res[0];
foreach ($res as $r)
$records[] = arraytorecord($r);
if ($cmd == 'read')
return $records[0]; // single record
return $records;
}
/* _my_mysql_query - query MySQL and fetch rows */
// the debug calls here can generate many messages but they are very
// valuable when making changes to the code which might introduce a
// temporary problem (and they are useful when trying to learn how MySQL
// works)
function _my_mysql_query($sql) {
debug($sql);
$res = _mysql_query($sql);
debug($res);
if ($res === FALSE) debug(_mysql_error());
if (config('debugdbstat'))
debug(_mysql_stat());
if ($res === FALSE || $res === TRUE)
return $res;
debug('rows: '._mysql_num_rows($res));
$rows = array();
while ($row = _mysql_fetch_row($res))
$rows[] = str_replace("\r",'',$row[0]);
debug($rows);
return $rows;
// SHOW TABLES query returns a resource with no rows if no tables exist.
// SELECT COUNT(*) query returns a resource with one row of 0 if there are
// no rows (empty table).
// SELECT field can also return a resource with no rows if no such field
// exists.
}
/*
PHP.NET: "Thanks to the reference-counting system introduced with
PHP 4's Zend Engine, a resource with no more references to it is
detected automatically, and it is freed by the garbage collector.
For this reason, it is rarely necessary to free the memory manually."
*/
/* _mysql_stop - a fatal error has occured */
function _mysql_stop($msg) {
$msg = 'mysql: ' . $msg;
if (debug()) {
$e = _mysql_error();
$v = _mysql_get_server_info();
getcaller($file,$line,$func);
$msg .= "<br><br>Additional Information:";
$msg .= "<br>_mysql_stop() called by $func() line $line";
$msg .= "<br>MySQL v$v reports error: \"$e\"";
}
error($msg,'fatal');
}
/* the initialize function - also holds the MySQL link and table name */
function _mysql($cmd = NULL, $arg = NULL) {
static $dblink = NULL;
static $dbtable = '';
if ($dblink == NULL) {
_mysql_functions();
$dblink = _mysql_connect();
if (!$dblink)
_mysql_stop('server rejected the passed credentials.');
// select database, optionally create if not found
if (_mysql_select_db(DB_NAME) == FALSE) {
debug("unable to select '".DB_NAME."'");
_mysql_query("CREATE DATABASE ".DB_NAME);
if (_mysql_select_db(DB_NAME) == FALSE)
_mysql_stop("server rejected CREATE DATABASE '".DB_NAME."'");
}
_mysql_start();
}
switch ($cmd) {
case 'link':
return $dblink;
case 'table':
if ($arg) $dbtable = $arg;
return $dbtable;
}
}
/* _mysql_start - will create tables, if allowed, if they do not exist */
function _mysql_start() {
$db_table = config('section');
_mysql('table',$db_table); // this saves the value
// the table name never changes after it has been set
debug("TABLE '$db_table'");
if (config('dbcreated')) // once all tables created
return; // set dbcreated = 1
$r = _my_mysql_query("SHOW TABLES LIKE '$db_table'");
if ($r == FALSE) {
debug("TABLE '$db_table' not found");
// this include has the "temporary" mode setting check in it:
include 'dat/sql.php'; // the SQL table definitions
// the SQL.PHP data references $db_table
foreach ($tables as $sql)
if (_my_mysql_query($sql) == FALSE)
_mysql_stop("unable to create '$db_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 as it 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)
dbnewrecord(file_get_contents($file));
}
else
if (!config('noinstallpost')) {
$data = "title: Install Post\n".
"date: Jan-11, 2013, 9:42pm\n".
"\nWelcome to THIS - The Application.";
dbnewrecord($data);
}
}
// these tables get created just this once and this code then becomes
// uneccessary and can be removed; eventually this will be moved in the
// Admin/Setup code
if (_my_mysql_query("SHOW TABLES LIKE 'users'") == FALSE) {
debug("TABLE 'users' not found");
if (!isset($user_tables))
include 'dat/sql.php';
foreach ($user_tables as $sql)
if (_mysql_query($sql) == FALSE)
_mysql_stop('unable to create table');
$record = array("userid" => AD_NAME,
"password" => AD_PASS,
"body" => "Administrator Account"
);
dbuser('new',$record['userid'],$record);
}
}
/* these "load" either the mysql extenstion or the mysqli extention */
function _mysql_functions() {
if (!function_exists('mysqli_connect') || config('mysql')) :
function _mysql_connect() {
return mysql_connect('localhost',DB_USER,DB_PASS);
}
function _mysql_select_db() {
return mysql_select_db(DB_NAME);
}
function _mysql_error() {
return mysql_error();
}
function _mysql_query($sql) {
return mysql_query($sql);
}
function _mysql_num_rows($res) {
return mysql_num_rows($res);
}
function _mysql_fetch_row($res) {
return mysql_fetch_row($res);
}
function _mysql_real_escape_string($str) {
return mysql_real_escape_string($str);
}
function _mysql_stat() {
return mysql_stat();
}
else:
function _mysql_connect() {
return mysqli_connect('localhost',DB_USER,DB_PASS);
}
function _mysql_select_db() {
return mysqli_select_db(_mysql('link'),DB_NAME);
}
function _mysql_error() {
return mysqli_error(_mysql('link'));
}
function _mysql_get_server_info() {
return mysqli_get_server_info(_mysql('link'));
}
function _mysql_query($sql) {
return mysqli_query(_mysql('link'),$sql);
}
function _mysql_num_rows($res) {
return mysqli_num_rows($res);
}
function _mysql_fetch_row($res) {
return mysqli_fetch_row($res);
}
function _mysql_real_escape_string($str) {
return mysqli_real_escape_string(_mysql('link'),$str);
}
function _mysql_stat() {
return mysqli_stat(_mysql('link'));
}
endif;
}
// experimental
function dbcreate($table) {
$res = _my_mysql_query("SHOW TABLES LIKE '$table'");
if ($res === FALSE)
return _mysql_error();
if (isset($res[0]))
return '';
$db_table = $table;
include 'dat/sql.php'; // the SQL table definitions
if (_my_mysql_query($tables[0]) == FALSE)
return _mysql_error();
if (_my_mysql_query($tables[1]) == FALSE)
return _mysql_error();
return 0;
}
function dbuse($table) {
_mysql('table',$table);
}
// end experimental