mysql.php -
index
<?php
/* MYSQL.PHP - all MySQL database access is done through here (or MYSQLI.PHP)
These functions will be used if the mysqli extension is not available or if
the setting 'mysql' is set to non-zero in CONFIG.INI (or if you modify both
INDEX.PHP and ADMIN.PHP to include it).
See the file MYSQL (doc/mysql.html) on how these functions are designed.
*/
setmodule();
_mysql_config();
/* There is a global defined/used here; it's 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... */
function _mysql_config() {
debug(DB_USER. ','. DB_NAME);
// NOTE Initial testing for a persistent connect shows ERRORS as MySQL was
// reporting success for some INSERT queries but actually they were not
// INSERTED; and CREATE TABLE reported 'table already exists' when the table
// DID NOT exist... PHP.NET says it will "only work if you are using a module
// version of PHP" and I am... (5/16/2013)
if (config('mysqlpersistent'))
$link = mysql_pconnect('localhost',DB_USER,DB_PASS);
else
$link = mysql_connect('localhost',DB_USER,DB_PASS);
if (!$link)
_mysql_error('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."'");
if (config('nodbcreate'))
user_error("Setting prevents CREATE DATABASE '".DB_NAME."'",E_USER_ERROR);
_mysql_query("CREATE DATABASE ".DB_NAME);
if (mysql_select_db(DB_NAME) == FALSE)
_mysql_error("server rejected CREATE DATABASE '".DB_NAME."'");
}
// nonexisting tables are created in the following function
}
/* dbstart - will create tables, if allowed, if they do not exist */
// called by INDEX.PHP/ADMIN.PHP at start-up
function dbstart() {
global $db_table;
// this needs to change; see SECTIONS:
$db_table = ($t = config('section')) ? $t : 'root';
debug("TABLE '$db_table'");
// with 'dbcreated', adding a
if (config('dbcreated')) // section will not get it's
return; // "first post"
$r = _mysql_query("SHOW TABLES LIKE '$db_table'");
if ($r == 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 (_mysql_query($sql) == FALSE)
_mysql_error("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 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)
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);
}
}
if (_mysql_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 (_mysql_query($sql) == FALSE)
_mysql_error('unable to create table');
$record = array("userid" => AD_NAME,
"password" => AD_PASS,
"body" => "Administrator Account"
);
dbuser('put',$record['userid'],$record);
}
}
// experimental
function dbcreate($table) {
$res = _mysql_query("SHOW TABLES LIKE '$table'");
if ($res === FALSE)
return mysql_error();
if (isset($res[0]))
return '';
$db_table = $table; // NOT the global
include 'dat/sql.php'; // the SQL table definitions
if (_mysql_query($tables[0]) == FALSE)
return mysql_error();
if (_mysql_query($tables[1]) == FALSE)
return mysql_error();
return 0;
}
function dbuse($table) {
global $db_table;
$db_table = $table;
}
// end experimental
// NOTE There is a flaw here in that sometimes the words 'new', 'put' and
// 'update' are used inconsistently... this will be fixed in the next
// release.
/* "section" table */
function dbquery($sql) {
return dbrecord('query',$sql);
}
function dberror() {
return dbrecord('error');
}
function dbtable() {
return dbrecord('table');
}
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) {
global $db_table;
debug($cmd);
// the first three commands here were more recently "tacked on" and not often
// used (and might go away)
if ($cmd == 'query')
return _mysql_query($id); // NOTE $id and not $data
if ($cmd == 'table')
return $db_table;
if ($cmd == 'error')
return mysql_error();
if ($data) {
if (is_array($data))
$data = recordtostring($data);
$data = mysql_real_escape_string($data);
}
$sql = array(
'count' => "SELECT COUNT(*) FROM `$db_table`",
'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';
$rec = _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 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) {
// should be 'new'
return dbcomment('put',$entryid,$record);
}
// this one needs to be renamed
function dbputcomment($id, $data) {
return dbcomment('update',$id);
}
function dbdelcomment($id) {
return dbcomment('delete',$id);
}
function dbcomment($cmd, $id, $data = '') {
global $db_table;
debug($cmd);
$table = $db_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",
'put' => "INSERT INTO `$table` (body, entryid) VALUES ('$data', '$id')",
'delete' => "DELETE FROM `$table` WHERE id = $id",
'update' => "UPDATE `$table` SET body = '$data' WHERE id = $id",
);
$rec = _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 == 'put') {
$sql = "SELECT LAST_INSERT_ID()";
$rec = _mysql_query($sql);
return $rec[0];
}
return $rec;
}
/* the `users` table */
function dbreaduser($userid) {
return dbuser('read',$userid);
}
function dbputuser($userid, $record, $update = FALSE) {
if ($update)
return dbuser('update',$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'",
'put' => "INSERT INTO users (userid, body) VALUES ('$userid', '$data')",
'update' => "UPDATE users SET body = '$data' WHERE userid = '$userid'",
);
$rec = _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('update',$from,$record);
}
function dbdelvisitor($from) {
return dbvisitor('delete',$from);
}
function dbvisitor($cmd, $from = NULL, $data = NULL) {
debug($cmd);
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')",
'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]'"
);
$res = _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);
return isset($records) ? $records : $res;
}
/* _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 _mysql_query($sql) {
debug($sql);
$res = mysql_query($sql);
debug($res);
if ($res === FALSE) debug(mysql_error());
if (config('debugdbstat'))
debug(mysql_stat(),-1);
if ($res === FALSE || $res === TRUE)
return $res;
debug('rows: '.mysql_num_rows($res));
$r = array();
while ($row = mysql_fetch_row($res))
$r[] = $row[0];
debug($r);
return $r;
// NOTES
//
// 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."
*/
function _mysql_error($msg, $type = 'fatal') {
$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_error() called from $func() line $line in $file";
$msg .= "<br>MySQL v$v reports error: \"$e\"";
}
error($msg,$type); // exits if $type == 'fatal'
}