/campsite/src/include/phorum/include/db/mysql.php
PHP | 4826 lines | 2735 code | 952 blank | 1139 comment | 641 complexity | 59bf8d53d9cc8b3908053949cfff1479 MD5 | raw file
Possible License(s): BSD-3-Clause, AGPL-1.0, LGPL-2.1, Apache-2.0
Large files files are truncated, but you can click here to view the full file
- <?php
- ////////////////////////////////////////////////////////////////////////////////
- // //
- // Copyright (C) 2006 Phorum Development Team //
- // http://www.phorum.org //
- // //
- // This program is free software. You can redistribute it and/or modify //
- // it under the terms of either the current Phorum License (viewable at //
- // phorum.org) or the Phorum License that was distributed with this file //
- // //
- // This program is distributed in the hope that it will be useful, //
- // but WITHOUT ANY WARRANTY, without even the implied warranty of //
- // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. //
- // //
- // You should have received a copy of the Phorum License //
- // along with this program. //
- ////////////////////////////////////////////////////////////////////////////////
- // cvs-info: $Id: mysql.php 1039 2006-05-10 16:01:56Z brian $
- if (!defined("PHORUM")) return;
- /**
- * The other Phorum code does not care how the messages are stored.
- * The only requirement is that they are returned from these functions
- * in the right way. This means each database can use as many or as
- * few tables as it likes. It can store the fields anyway it wants.
- * The only thing to worry about is the table_prefix for the tables.
- * all tables for a Phorum install should be prefixed with the
- * table_prefix that will be entered in include/db/config.php. This
- * will allow multiple Phorum installations to use the same database.
- */
- /**
- * These are the table names used for this database system.
- */
- // tables needed to be "partitioned"
- $PHORUM["message_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_messages";
- $PHORUM["user_newflags_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_user_newflags";
- $PHORUM["subscribers_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_subscribers";
- $PHORUM["files_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_files";
- $PHORUM["search_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_search";
- // tables common to all "partitions"
- $PHORUM["settings_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_settings";
- $PHORUM["forums_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_forums";
- $PHORUM["user_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_users";
- $PHORUM["user_permissions_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_user_permissions";
- $PHORUM["groups_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_groups";
- $PHORUM["forum_group_xref_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_forum_group_xref";
- $PHORUM["user_group_xref_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_user_group_xref";
- $PHORUM['user_custom_fields_table'] = "{$PHORUM['DBCONFIG']['table_prefix']}_user_custom_fields";
- $PHORUM["banlist_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_banlists";
- $PHORUM["pm_messages_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_pm_messages";
- $PHORUM["pm_folders_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_pm_folders";
- $PHORUM["pm_xref_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_pm_xref";
- $PHORUM["pm_buddies_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_pm_buddies";
- /*
- * fields which are always strings, even if they contain only numbers
- * used in post-message and update-message, otherwise strange things happen
- */
- $PHORUM['string_fields']= array('author', 'subject', 'body', 'email');
- /* A piece of SQL code that can be used for identifying moved messages. */
- define('PHORUM_SQL_MOVEDMESSAGES', '(parent_id = 0 and thread != message_id)');
- /**
- * Get the visible messages for a given page offset. The main Phorum code
- * handles actually sorting the threads into a threaded list if needed.
- *
- * By default, the message body is not included in the fetch queries.
- * If the body is needed in the thread list, $PHORUM['TMP']['bodies_in_list']
- * must be set to "1" (for example using setting.tpl).
- *
- * NOTE: ALL dates should be returned as Unix timestamps
- *
- * @param int $offset - the index of the page to return, starting with 0
- *
- * @return array
- */
- function phorum_db_get_thread_list($offset)
- {
- $PHORUM = $GLOBALS["PHORUM"];
- settype($offset, "int");
- $conn = phorum_db_mysql_connect();
- $table = $PHORUM["message_table"];
- // The messagefields that we want to fetch from the database.
- $messagefields =
- "$table.author,
- $table.datestamp,
- $table.email,
- $table.message_id,
- $table.meta,
- $table.moderator_post,
- $table.modifystamp,
- $table.parent_id,
- $table.sort,
- $table.status,
- $table.subject,
- $table.thread,
- $table.thread_count,
- $table.user_id,
- $table.viewcount,
- $table.closed";
- if(isset($PHORUM['TMP']['bodies_in_list']) && $PHORUM['TMP']['bodies_in_list'] == 1) {
- $messagefields .= "\n,$table.body\n,$table.ip";
- }
- // The sort mechanism to use.
- if($PHORUM["float_to_top"]){
- $sortfield = "modifystamp";
- $index = "list_page_float";
- } else{
- $sortfield = "thread";
- $index = "list_page_flat";
- }
- // Initialize the return array.
- $messages = array();
- // The groups of messages we want to fetch from the database.
- $groups = array();
- if ($offset == 0) $groups[] = "specials";
- $groups[] = "threads";
- if ($PHORUM["threaded_list"]) $groups[] = "replies";
- // for remembering message ids for which we want to fetch the replies.
- $replymsgids = array();
- // Process all groups.
- foreach ($groups as $group) {
- $sql = NULL;
- switch ($group) {
- // Announcements and stickies.
- case "specials":
- $sql = "select $messagefields
- from $table
- where
- status=".PHORUM_STATUS_APPROVED." and
- ((parent_id=0 and sort=".PHORUM_SORT_ANNOUNCEMENT."
- and forum_id={$PHORUM['vroot']})
- or
- (parent_id=0 and sort=".PHORUM_SORT_STICKY."
- and forum_id={$PHORUM['forum_id']}))
- order by
- sort, $sortfield desc";
- break;
- // Threads.
- case "threads":
- if ($PHORUM["threaded_list"]) {
- $limit = $PHORUM['list_length_threaded'];
- $extrasql = '';
- } else {
- $limit = $PHORUM['list_length_flat'];
- }
- $start = $offset * $limit;
- $sql = "select $messagefields
- from $table use index ($index)
- where
- $sortfield > 0 and
- forum_id = {$PHORUM["forum_id"]} and
- status = ".PHORUM_STATUS_APPROVED." and
- parent_id = 0 and
- sort > 1
- order by
- $sortfield desc
- limit $start, $limit";
- break;
- // Reply messages.
- case "replies":
- // We're done if we did not collect any messages with replies.
- if (! count($replymsgids)) break;
- $sortorder = "sort, $sortfield desc, message_id";
- if(isset($PHORUM["reverse_threading"]) && $PHORUM["reverse_threading"])
- $sortorder.=" desc";
- $sql = "select $messagefields
- from $table
- where
- status = ".PHORUM_STATUS_APPROVED." and
- thread in (" . implode(",",$replymsgids) .")
- order by $sortorder";
- break;
- } // End of switch ($group)
- // Continue with the next group if no SQL query was formulated.
- if (is_null($sql)) continue;
- // Fetch the messages for the current group.
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $rows = mysql_num_rows($res);
- if($rows > 0){
- while ($rec = mysql_fetch_assoc($res)){
- $messages[$rec["message_id"]] = $rec;
- $messages[$rec["message_id"]]["meta"] = array();
- if(!empty($rec["meta"])){
- $messages[$rec["message_id"]]["meta"] = unserialize($rec["meta"]);
- }
- // We need the message ids for fetching reply messages.
- if ($group == 'threads' && $rec["thread_count"] > 1) {
- $replymsgids[] = $rec["message_id"];
- }
- }
- }
- }
- return $messages;
- }
- /**
- * Get the recent messages for all forums the user can read, a particular
- * forum, or a particular thread, and returns an array of the messages
- * order by message_id. You can optionally retrieve only new threads.
- *
- * The original version of this function came from Jim Winstead of mysql.com
- *
- * @param int $count
- * Limit the number of returned messages to this number.
- * @param int $forum_id
- * @param int $thread
- * @param boolean $threads_only
- * If set to TRUE, only get the top message from each thread.
- *
- * @return array
- */
- function phorum_db_get_recent_messages($count, $forum_id = 0, $thread = 0, $threads_only = 0)
- {
- $PHORUM = $GLOBALS["PHORUM"];
- settype($count, "int");
- settype($thread, "int");
- $arr = array();
- $allowed_forums = array();
- $conn = phorum_db_mysql_connect();
- // we need to differentiate on which key to use
- if($thread) {
- $use_key='thread_message';
- } else {
- $use_key='forum_max_message';
- }
- $sql = "SELECT {$PHORUM['message_table']}.* FROM {$PHORUM['message_table']} USE KEY($use_key) WHERE status=".PHORUM_STATUS_APPROVED;
- // have to check what forums they can read first.
- // even if $thread is passed, we have to make sure
- // the user can read the forum
- if($forum_id <= 0) {
- $allowed_forums=phorum_user_access_list(PHORUM_USER_ALLOW_READ);
- // if they are not allowed to see any forums, return the emtpy $arr;
- if(empty($allowed_forums))
- return $arr;
- } elseif(is_array($forum_id)) {
- // for an array, check each one and return if none are allowed
- foreach($forum_id as $id){
- if(phorum_user_access_allowed(PHORUM_USER_ALLOW_READ,$id)) {
- $allowed_forums[]=$id;
- }
- }
- // if they are not allowed to see any forums, return the emtpy $arr;
- if(empty($allowed_forums))
- return $arr;
- } else {
- // only single forum, *much* fast this way
- if(!phorum_user_access_allowed(PHORUM_USER_ALLOW_READ,$forum_id)) {
- return $arr;
- }
- settype($forum_id, "int");
- }
- if(count($allowed_forums)){
- $sql.=" and forum_id in (".implode(",", $allowed_forums).")";
- } else {
- $sql.=" and forum_id=$forum_id";
- }
- if($thread){
- $sql.=" and thread=$thread";
- }
- if($threads_only) {
- $sql.= " and parent_id = 0";
- $sql.= " ORDER BY thread DESC";
- } else {
- $sql.= " ORDER BY message_id DESC";
- }
- if($count){
- $sql.= " LIMIT $count";
- }
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- while ($rec = mysql_fetch_assoc($res)){
- $arr[$rec["message_id"]] = $rec;
- // convert meta field
- if(empty($rec["meta"])){
- $arr[$rec["message_id"]]["meta"]=array();
- } else {
- $arr[$rec["message_id"]]["meta"]=unserialize($rec["meta"]);
- }
- if(empty($arr['users'])) $arr['users']=array();
- if($rec["user_id"]){
- $arr['users'][]=$rec["user_id"];
- }
- }
- return $arr;
- }
- /**
- * Get messages which have not yet been approved by a moderator.
- * The main Phorum code handles actually sorting the threads into
- * a threaded list if needed.
- *
- * NOTE: ALL dates should be returned as Unix timestamps
- * @param mixed $forum
- * The forum id to work with.
- * Set to NULL if you want to search all forums.
- * You can also pass an array of forum id's.
- * @param boolean $waiting_only
- * Only take into account messages which have to
- * be approved directly after posting. Do not include
- * messages which are hidden by a moderator.
- * @param int $moddays
- * Limit the search to the last $moddays number of days.
- * @return array
- * An array of messages, indexed by message ID.
- */
- function phorum_db_get_unapproved_list($forum = NULL, $waiting_only=false,$moddays=0)
- {
- $PHORUM = $GLOBALS["PHORUM"];
- $conn = phorum_db_mysql_connect();
- $table = $PHORUM["message_table"];
- $arr = array();
- $sql = "select
- $table.*
- from
- $table ";
- if (is_array($forum)){
- $sql .= "where forum_id in (" . implode(",", $forum) . ") and ";
- } elseif (! is_null($forum)){
- settype($forum, "int");
- $sql .= "where forum_id = $forum and ";
- } else {
- $sql .= "where ";
- }
- if($moddays > 0) {
- $checktime=time()-(86400*$moddays);
- $sql .=" datestamp > $checktime AND";
- }
- if($waiting_only){
- $sql.=" status=".PHORUM_STATUS_HOLD;
- } else {
- $sql="($sql status=".PHORUM_STATUS_HOLD.") " .
- "union ($sql status=".PHORUM_STATUS_HIDDEN.")";
- }
- $sql .=" order by thread, message_id";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- while ($rec = mysql_fetch_assoc($res)){
- $arr[$rec["message_id"]] = $rec;
- $arr[$rec["message_id"]]["meta"] = array();
- if(!empty($rec["meta"])){
- $arr[$rec["message_id"]]["meta"] = unserialize($rec["meta"]);
- }
- }
- return $arr;
- }
- /**
- * Add a message. The message will not be posted if it is a duplicate
- * and $PHORUM['check_duplicate'] is set.
- *
- * The $message is passed by reference and when the function completes
- * successfully, the "message_id" index will be set to the new value.
- * If the "thread" index is set to zero, a new thread will be started and the
- * "thread" index will be filled with the new thread ID upon return.
- *
- * @param array $message
- * The $message array must contain the following indexes:
- * forum_id, thread, parent_id, author, subject, email, ip,
- * user_id, moderator_post, status, sort, msgid, body, closed
- *
- * @param boolean $convert
- *
- * @return boolean
- * TRUE on success, FALSE on failure.
- *
- */
- function phorum_db_post_message(&$message,$convert=false){
- $PHORUM = $GLOBALS["PHORUM"];
- $table = $PHORUM["message_table"];
- $conn = phorum_db_mysql_connect();
- $success = false;
- foreach($message as $key => $value){
- if (is_numeric($value) && !in_array($key,$PHORUM['string_fields'])){
- $message[$key] = (int)$value;
- } elseif(is_array($value)) {
- $message[$key] = mysql_escape_string(serialize($value));
- } else{
- $message[$key] = mysql_escape_string($value);
- }
- }
- if(!$convert) {
- $NOW = time();
- } else {
- $NOW = $message['datestamp'];
- }
- // duplicate-check
- if(isset($PHORUM['check_duplicate']) && $PHORUM['check_duplicate'] && !$convert) {
- // we check for dupes in that number of minutes
- $check_minutes=60;
- $check_timestamp =$NOW - ($check_minutes*60);
- // check_query
- $chk_query="SELECT message_id FROM $table WHERE forum_id = {$message['forum_id']} AND author='{$message['author']}' AND subject='{$message['subject']}' AND body='{$message['body']}' AND datestamp > $check_timestamp";
- $res = mysql_query($chk_query, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $chk_query");
- if(mysql_num_rows($res))
- return 0;
- }
- if(isset($message['meta'])){
- $metaval=",meta='{$message['meta']}'";
- } else {
- $metaval="";
- }
- $sql = "Insert into $table set
- forum_id = {$message['forum_id']},
- datestamp=$NOW,
- thread={$message['thread']},
- parent_id={$message['parent_id']},
- author='{$message['author']}',
- subject='{$message['subject']}',
- email='{$message['email']}',
- ip='{$message['ip']}',
- user_id={$message['user_id']},
- moderator_post={$message['moderator_post']},
- status={$message['status']},
- sort={$message['sort']},
- msgid='{$message['msgid']}',
- body='{$message['body']}',
- closed={$message['closed']}
- $metaval";
- // if in conversion we need the message-id too
- if($convert && isset($message['message_id'])) {
- $sql.=",message_id=".$message['message_id'];
- }
- if(isset($message['modifystamp'])) {
- $sql.=",modifystamp=".$message['modifystamp'];
- }
- if(isset($message['viewcount'])) {
- $sql.=",viewcount=".$message['viewcount'];
- }
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- if ($res){
- $message["message_id"] = mysql_insert_id($conn);
- if(!empty($message["message_id"])){
- $message["datestamp"]=$NOW;
- if ($message["thread"] == 0){
- $message["thread"] = $message["message_id"];
- $sql = "update $table set thread={$message['message_id']} where message_id={$message['message_id']}";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- }
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- // start ft-search stuff
- if($PHORUM["DBCONFIG"]["mysql_use_ft"]){
- $search_text="$message[author] | $message[subject] | $message[body]";
- $sql="insert delayed into {$PHORUM['search_table']} set message_id={$message['message_id']}, forum_id={$message['forum_id']}, search_text='$search_text'";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- }
- // end ft-search stuff
- $success = true;
- // some data for later use, i.e. email-notification
- $GLOBALS['PHORUM']['post_returns']['message_id']=$message["message_id"];
- $GLOBALS['PHORUM']['post_returns']['thread_id']=$message["thread"];
- }
- }
- return $success;
- }
- /**
- * Relate a message to Campsite.
- *
- * It affects campsite.ArticleComments
- *
- * @param array $message
- * The $message array must contain the following indexes:
- * thread, message_id
- *
- * @return boolean
- * TRUE on success, FALSE on failure.
- *
- */
- function phorum_db_relate_message_to_campsite($message)
- {
- $PHORUM = $GLOBALS["PHORUM"];
- $conn = phorum_db_mysql_connect();
- $success = false;
- $sql = "SELECT fk_article_number, fk_language_id
- FROM ArticleComments
- WHERE fk_comment_id = '".$message['thread']."'";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- if ($res) {
- $data = mysql_fetch_array($res);
- if (!empty($data['fk_article_number'])) {
- $article_number = $data['fk_article_number'];
- $article_language = $data['fk_language_id'];
- $sql = "INSERT INTO ArticleComments
- (fk_article_number, fk_language_id, fk_comment_id, is_first)
- VALUES ('".$article_number."', '".$article_language."', '".
- $message['message_id']."', '0')";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- if ($res) {
- $success = true;
- }
- }
- }
- return $success;
- }
- /**
- * Delete a message.
- *
- * @param int $message_id The id of the message which should be deleted
- * @param int $mode The mode of deletion,
- * PHORUM_DELETE_MESSAGE for reconnecting the children,
- * PHORUM_DELETE_TREE for deleting the children
- */
- function phorum_db_delete_message($message_id, $mode = PHORUM_DELETE_MESSAGE)
- {
- $PHORUM = $GLOBALS["PHORUM"];
- $conn = phorum_db_mysql_connect();
- settype($message_id, "int");
- $threadset = 0;
- // get the parents of the message to delete.
- $sql = "select forum_id, message_id, thread, parent_id from {$PHORUM['message_table']} where message_id = $message_id ";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $rec = mysql_fetch_assoc($res);
- if($mode == PHORUM_DELETE_TREE){
- $mids = phorum_db_get_messagetree($message_id, $rec['forum_id']);
- }else{
- $mids = $message_id;
- }
- // unapprove the messages first so replies will not get posted
- $sql = "update {$PHORUM['message_table']} set status=".PHORUM_STATUS_HOLD." where message_id in ($mids)";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $thread = $rec['thread'];
- if($thread == $message_id && $mode == PHORUM_DELETE_TREE){
- $threadset = 1;
- }else{
- $threadset = 0;
- }
- if($mode == PHORUM_DELETE_MESSAGE){
- $count = 1;
- // change the children to point to their parent's parent
- // forum_id is in here for speed by using a key only
- $sql = "update {$PHORUM['message_table']} set parent_id=$rec[parent_id] where forum_id=$rec[forum_id] and parent_id=$rec[message_id]";
- mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- }else{
- $count = count(explode(",", $mids));
- }
- // delete the messages
- $sql = "delete from {$PHORUM['message_table']} where message_id in ($mids)";
- mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- // start ft-search stuff
- $sql="delete from {$PHORUM['search_table']} where message_id in ($mids)";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- // end ft-search stuff
- // it kind of sucks to have this here, but it is the best way
- // to ensure that it gets done if stuff is deleted.
- // leave this include here, it needs to be conditional
- include_once("./include/thread_info.php");
- phorum_update_thread_info($thread);
- // we need to delete the subscriptions for that thread too
- $sql = "DELETE FROM {$PHORUM['subscribers_table']} WHERE forum_id > 0 AND thread=$thread";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- // this function will be slow with a lot of messages.
- phorum_db_update_forum_stats(true);
- return explode(",", $mids);
- }
- /**
- * Get all attached messages to a message.
- *
- * @param int $parent_id
- * @param int $forum_id
- *
- * @return string
- */
- function phorum_db_get_messagetree($parent_id, $forum_id){
- $PHORUM = $GLOBALS["PHORUM"];
- settype($parent_id, "int");
- settype($forum_id, "int");
- $conn = phorum_db_mysql_connect();
- $sql = "Select message_id from {$PHORUM['message_table']} where forum_id=$forum_id and parent_id=$parent_id";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $tree = "$parent_id";
- while($rec = mysql_fetch_row($res)){
- $tree .= "," . phorum_db_get_messagetree($rec[0],$forum_id);
- }
- return $tree;
- }
- /**
- * Update a message with new data.
- *
- * @param int $message_id
- * @param array $message
- * See phorum_db_post_message() for a list of available fields.
- * You can pass in as many or as few fields as you wish to update.
- *
- * @return boolean
- * TRUE on success, FALSE on failure.
- */
- function phorum_db_update_message($message_id, $message)
- {
- $PHORUM = $GLOBALS["PHORUM"];
- settype($message_id, "int");
- if (count($message) > 0){
- $conn = phorum_db_mysql_connect();
- foreach($message as $field => $value){
- if (is_numeric($value) && !in_array($field,$PHORUM['string_fields'])){
- $fields[] = "$field=$value";
- }elseif (is_array($value)){
- $value = mysql_escape_string(serialize($value));
- $message[$field] = $value;
- $fields[] = "$field='$value'";
- }else{
- $value = mysql_escape_string($value);
- $message[$field] = $value;
- $fields[] = "$field='$value'";
- }
- }
- $sql = "update {$PHORUM['message_table']} set " . implode(", ", $fields) . " where message_id=$message_id";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- if($res){
- // start ft-search stuff
- if(isset($message["author"]) && isset($message["subject"]) && isset($message["body"])){
- $search_text="$message[author] | $message[subject] | $message[body]";
- $sql="replace delayed into {$PHORUM['search_table']} set message_id={$message_id}, forum_id={$message['forum_id']}, search_text='$search_text'";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- }
- // end ft-search stuff
- }
- return ($res > 0) ? true : false;
- }else{
- trigger_error("\$message cannot be empty in phorum_update_message()", E_USER_ERROR);
- }
- }
- /**
- * Get the first message that matches the search parameters. If
- * you pass in multiple matches targets, this will return an array
- * of messages. Otherwise it will return a single message.
- *
- * @param mixed $value
- * The value in the database column that you want to match.
- * This can be an array, in which case this will return the messages
- * that match any of the given values in the array.
- * @param string $field
- * The database column you are searching to find $value.
- * @param boolean $ignore_forum_id
- * @return array
- */
- function phorum_db_get_message($value, $field="message_id", $ignore_forum_id=false)
- {
- $PHORUM = $GLOBALS["PHORUM"];
- $field=mysql_escape_string($field);
- $multiple=false;
- $conn = phorum_db_mysql_connect();
- $forum_id_check = "";
- if (!$ignore_forum_id && !empty($PHORUM["forum_id"])){
- $forum_id_check = "(forum_id = {$PHORUM['forum_id']} OR forum_id={$PHORUM['vroot']}) and";
- }
- if(is_array($value)) {
- $checkvar="$field IN('".implode("','",$value)."')";
- $multiple=true;
- } else {
- $value=mysql_escape_string($value);
- $checkvar="$field='$value'";
- }
- $sql = "select {$PHORUM['message_table']}.* from {$PHORUM['message_table']} where $forum_id_check $checkvar";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $ret = $multiple ? array() : NULL;
- if(mysql_num_rows($res)){
- if($multiple) {
- while($rec=mysql_fetch_assoc($res)) {
- // convert meta field
- if(empty($rec["meta"])){
- $rec["meta"]=array();
- } else {
- $rec["meta"]=unserialize($rec["meta"]);
- }
- $ret[$rec['message_id']]=$rec;
- }
- } else {
- $rec = mysql_fetch_assoc($res);
- // convert meta field
- if(empty($rec["meta"])){
- $rec["meta"]=array();
- } else {
- $rec["meta"]=unserialize($rec["meta"]);
- }
- $ret=$rec;
- }
- }
- return $ret;
- }
- /**
- * Get messages with the given thread id. Returns an array of messages.
- *
- * @param int $thread
- * @param int $page
- * @return array
- */
- function phorum_db_get_messages($thread,$page=0)
- {
- $PHORUM = $GLOBALS["PHORUM"];
- settype($thread, "int");
- $conn = phorum_db_mysql_connect();
- $forum_id_check = "";
- if (!empty($PHORUM["forum_id"])){
- $forum_id_check = "(forum_id = {$PHORUM['forum_id']} OR forum_id={$PHORUM['vroot']}) and";
- }
- // are we really allowed to show this thread/message?
- $approvedval = "";
- if(!phorum_user_access_allowed(PHORUM_USER_ALLOW_MODERATE_MESSAGES)) {
- $approvedval="AND {$PHORUM['message_table']}.status =".PHORUM_STATUS_APPROVED;
- }
- if($page > 0) {
- $start=$PHORUM["read_length"]*($page-1);
- $sql = "select {$PHORUM['message_table']}.* from {$PHORUM['message_table']} where $forum_id_check thread=$thread $approvedval order by message_id LIMIT $start,".$PHORUM["read_length"];
- } else {
- $sql = "select {$PHORUM['message_table']}.* from {$PHORUM['message_table']} where $forum_id_check thread=$thread $approvedval order by message_id";
- if(isset($PHORUM["reverse_threading"]) && $PHORUM["reverse_threading"]) $sql.=" desc";
- }
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $arr = array();
- while ($rec = mysql_fetch_assoc($res)){
- $arr[$rec["message_id"]] = $rec;
- // convert meta field
- if(empty($rec["meta"])){
- $arr[$rec["message_id"]]["meta"]=array();
- } else {
- $arr[$rec["message_id"]]["meta"]=unserialize($rec["meta"]);
- }
- if(empty($arr['users'])) $arr['users']=array();
- if($rec["user_id"]){
- $arr['users'][]=$rec["user_id"];
- }
- }
- if(count($arr) && $page != 0) {
- // selecting the thread-starter
- $sql = "select {$PHORUM['message_table']}.* from {$PHORUM['message_table']} where $forum_id_check message_id=$thread $approvedval";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- if(mysql_num_rows($res) > 0) {
- $rec = mysql_fetch_assoc($res);
- $arr[$rec["message_id"]] = $rec;
- $arr[$rec["message_id"]]["meta"]=unserialize($rec["meta"]);
- }
- }
- return $arr;
- }
- /**
- * Return the index of a message in a thread.
- * @param int $thread
- * @param int $message_id
- * @return int
- */
- function phorum_db_get_message_index($thread=0,$message_id=0) {
- $PHORUM = $GLOBALS["PHORUM"];
- // check for valid values
- if(empty($message_id) || empty($message_id)) {
- return 0;
- }
- settype($thread, "int");
- settype($message_id, "int");
- $approvedval="";
- $forum_id_check="";
- $conn = phorum_db_mysql_connect();
- if (!empty($PHORUM["forum_id"])){
- $forum_id_check = "(forum_id = {$PHORUM['forum_id']} OR forum_id={$PHORUM['vroot']}) AND";
- }
- if(!phorum_user_access_allowed(PHORUM_USER_ALLOW_MODERATE_MESSAGES)) {
- $approvedval="AND {$PHORUM['message_table']}.status =".PHORUM_STATUS_APPROVED;
- }
- $sql = "select count(*) as msg_index from {$PHORUM['message_table']} where $forum_id_check thread=$thread $approvedval AND message_id <= $message_id order by message_id";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $rec = mysql_fetch_assoc($res);
- return $rec['msg_index'];
- }
- /**
- * Search the database for the supplied search criteria and returns
- * an array with two elements. One is the count of total messages that
- * matched, the second is an array of the messages from the results
- * based on the $start (0 base) given and the $length given.
- *
- * @param string $search
- * @param int $offset
- * @param int $length
- * @param string $match_type
- * @param int $match_date
- * @param string $match_forum
- *
- * @return array
- */
- function phorum_db_search($search, $offset, $length, $match_type, $match_date, $match_forum)
- {
- $PHORUM = $GLOBALS["PHORUM"];
- $start = $offset * $PHORUM["list_length"];
- $arr = array("count" => 0, "rows" => array());
- $conn = phorum_db_mysql_connect();
- // have to check what forums they can read first.
- $allowed_forums=phorum_user_access_list(PHORUM_USER_ALLOW_READ);
- // if they are not allowed to search any forums, return the emtpy $arr;
- if(empty($allowed_forums) || ($PHORUM['forum_id']>0 && !in_array($PHORUM['forum_id'], $allowed_forums)) ) return $arr;
- // Add forum 0 (for announcements) to the allowed forums.
- $allowed_forums[] = 0;
- if($PHORUM['forum_id']!=0 && $match_forum!="ALL"){
- $forum_where=" and forum_id={$PHORUM['forum_id']}";
- } else {
- $forum_where=" and forum_id in (".implode(",", $allowed_forums).")";
- }
- // prepare terms
- if($match_type=="PHRASE"){
- $terms = array('"'.$search.'"');
- } elseif($match_type=="AUTHOR"){
- $terms = mysql_escape_string($search);
- } else {
- $quote_terms=array();
- if ( strstr( $search, '"' ) ){
- //first pull out all the double quoted strings (e.g. '"iMac DV" or -"iMac DV"')
- preg_match_all( '/-*"(.*?)"/', $search, $match );
- $search = preg_replace( '/-*".*?"/', '', $search );
- $quote_terms = $match[0];
- }
- //finally pull out the rest words in the string
- $terms = preg_split( "/\s+/", $search, 0, PREG_SPLIT_NO_EMPTY );
- //merge them all together and return
- $terms = array_merge($terms, $quote_terms);
- }
- if($PHORUM["DBCONFIG"]["mysql_use_ft"]){
- if($match_type=="AUTHOR"){
- $id_table=$PHORUM['search_table']."_auth_".md5(microtime());
- $sql = "create temporary table $id_table (key(message_id)) ENGINE=HEAP select message_id from {$PHORUM['message_table']} where author='$terms' $forum_where";
- if($match_date>0){
- $ts=time()-86400*$match_date;
- $sql.=" and datestamp>=$ts";
- }
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- } else {
- if(count($terms)){
- $use_key="";
- $extra_where="";
- /* using this code on larger forums has shown to make the search faster.
- However, on smaller forums, it does not appear to help and in fact
- appears to slow down searches.
- if($match_date){
- $min_time=time()-86400*$match_date;
- $sql="select min(message_id) as min_id from {$PHORUM['message_table']} where datestamp>=$min_time";
- $res=mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $min_id=mysql_result($res, 0, "min_id");
- $use_key=" use key (primary)";
- $extra_where="and message_id>=$min_id";
- }
- */
- $id_table=$PHORUM['search_table']."_ft_".md5(microtime());
- if($PHORUM["DBCONFIG"]["mysql_use_ft"]){
- if($match_type=="ALL" && count($terms)>1){
- $against="+".mysql_escape_string(implode(" +", $terms));
- } else {
- $against=mysql_escape_string(implode(" ", $terms));
- }
- $clause="MATCH (search_text) AGAINST ('$against' IN BOOLEAN MODE)";
- } else {
- if($match_type=="ALL"){
- $conj="and";
- } else {
- $conj="or";
- }
- // quote strings correctly
- foreach ($terms as $id => $term) {
- $terms[$id] = mysql_escape_string($term);
- }
- $clause = "( search_text like '%".implode("%' $conj search_text like '%", $terms)."%' )";
- }
- $sql = "create temporary table $id_table (key(message_id)) ENGINE=HEAP select message_id from {$PHORUM['search_table']} $use_key where $clause $extra_where";
- $res = mysql_unbuffered_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- }
- }
- if(isset($id_table)){
- // create a temporary table of the messages we want
- $table=$PHORUM['search_table']."_".md5(microtime());
- $sql="create temporary table $table (key (forum_id, status, datestamp)) ENGINE=HEAP select {$PHORUM['message_table']}.message_id, {$PHORUM['message_table']}.datestamp, status, forum_id from {$PHORUM['message_table']} inner join $id_table using (message_id) where status=".PHORUM_STATUS_APPROVED." $forum_where";
- if($match_date>0){
- $ts=time()-86400*$match_date;
- $sql.=" and datestamp>=$ts";
- }
- $res=mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $sql="select count(*) as count from $table";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $total_count=mysql_result($res, 0, 0);
- $sql="select message_id from $table order by datestamp desc limit $start, $length";
- $res = mysql_unbuffered_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $idstring="";
- while ($rec = mysql_fetch_row($res)){
- $idstring.="$rec[0],";
- }
- $idstring=substr($idstring, 0, -1);
- }
- } else { // not using full text matching
- if($match_type=="AUTHOR"){
- $sql_core = "from {$PHORUM['message_table']} where author='$terms' $forum_where";
- if($match_date>0){
- $ts=time()-86400*$match_date;
- $sql_core.=" and datestamp>=$ts";
- }
- $sql = "select count(*) $sql_core";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $total_count=mysql_result($res, 0, 0);
- $sql = "select message_id $sql_core order by datestamp desc limit $start, $length";
- $res = mysql_unbuffered_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $idstring="";
- while ($rec = mysql_fetch_row($res)){
- $idstring.="$rec[0],";
- }
- $idstring=substr($idstring, 0, -1);
- } else {
- if(count($terms)){
- $id_table=$PHORUM['search_table']."_ft_".md5(microtime());
- if($match_type=="ALL"){
- $conj="and";
- } else {
- $conj="or";
- }
- // quote strings correctly
- foreach ($terms as $id => $term) {
- $terms[$id] = mysql_escape_string($term);
- }
- $clause = "( concat(author, ' | ', subject, ' | ', body) like '%".implode("%' $conj concat(author, ' | ', subject, ' | ', body) like '%", $terms)."%' )";
- $sql = "select count(*) from {$PHORUM['message_table']} where status=".PHORUM_STATUS_APPROVED." and $clause $forum_where";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $total_count=mysql_result($res, 0, 0);
- $sql = "select message_id from {$PHORUM['message_table']} where status=".PHORUM_STATUS_APPROVED." and $clause $forum_where order by datestamp desc limit $start, $length";
- $res = mysql_unbuffered_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $idstring="";
- while ($rec = mysql_fetch_row($res)){
- $idstring.="$rec[0],";
- }
- $idstring=substr($idstring, 0, -1);
- }
- }
- }
- if($idstring){
- $sql="select * from {$PHORUM['message_table']} where message_id in ($idstring) order by datestamp desc";
- $res = mysql_unbuffered_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $rows = array();
- while ($rec = mysql_fetch_assoc($res)){
- $rows[$rec["message_id"]] = $rec;
- }
- $arr = array("count" => $total_count, "rows" => $rows);
- }
- return $arr;
- }
- /**
- * Return the closest thread that is greater than $key.
- * @param int $key
- * @return mixed
- */
- function phorum_db_get_newer_thread($key){
- $PHORUM = $GLOBALS["PHORUM"];
- settype($key, "int");
- $conn = phorum_db_mysql_connect();
- $keyfield = ($PHORUM["float_to_top"]) ? "modifystamp" : "thread";
- // are we really allowed to show this thread/message?
- $approvedval = "";
- if(!phorum_user_access_allowed(PHORUM_USER_ALLOW_MODERATE_MESSAGES) && $PHORUM["moderation"] == PHORUM_MODERATE_ON) {
- $approvedval="AND {$PHORUM['message_table']}.status =".PHORUM_STATUS_APPROVED;
- } else {
- $approvedval="AND {$PHORUM['message_table']}.parent_id = 0";
- }
- $sql = "select thread from {$PHORUM['message_table']} where forum_id={$PHORUM['forum_id']} $approvedval and $keyfield>$key order by $keyfield limit 1";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- return (mysql_num_rows($res)) ? mysql_result($res, 0, "thread") : 0;
- }
- /**
- * Returns the closest thread that is less than $key.
- * @param int $key
- * @return mixed
- */
- function phorum_db_get_older_thread($key){
- $PHORUM = $GLOBALS["PHORUM"];
- settype($key, "int");
- $conn = phorum_db_mysql_connect();
- $keyfield = ($PHORUM["float_to_top"]) ? "modifystamp" : "thread";
- // are we really allowed to show this thread/message?
- $approvedval = "";
- if(!phorum_user_access_allowed(PHORUM_USER_ALLOW_MODERATE_MESSAGES) && $PHORUM["moderation"] == PHORUM_MODERATE_ON) {
- $approvedval="AND {$PHORUM['message_table']}.status=".PHORUM_STATUS_APPROVED;
- } else {
- $approvedval="AND {$PHORUM['message_table']}.parent_id = 0";
- }
- $sql = "select thread from {$PHORUM['message_table']} where forum_id={$PHORUM['forum_id']} $approvedval and $keyfield<$key order by $keyfield desc limit 1";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- return (mysql_num_rows($res)) ? mysql_result($res, 0, "thread") : 0;
- }
- /**
- * Load the settings in the global $PHORUM['SETTINGS'] variable.
- *
- * @return void
- */
- function phorum_db_load_settings(){
- global $PHORUM;
- $conn = phorum_db_mysql_connect();
- $sql = "select * from {$PHORUM['settings_table']}";
- $res = mysql_query($sql, $conn);
- if(!$res && !defined("PHORUM_ADMIN")){
- if (mysql_errno($conn)==1146){
- // settings table does not exist
- return;
- } elseif(($err = mysql_error())){
- phorum_db_mysql_error("$err: $sql");
- }
- }
- if (empty($err) && $res){
- while ($rec = mysql_fetch_assoc($res)){
- // only load the default forum options in the admin
- if($rec["name"]=="default_forum_options" && !defined("PHORUM_ADMIN")) continue;
- if ($rec["type"] == "V"){
- if ($rec["data"] == 'true'){
- $val = true;
- }elseif ($rec["data"] == 'false'){
- $val = false;
- }elseif (is_numeric($rec["data"])){
- $val = $rec["data"];
- }else{
- $val = "$rec[data]";
- }
- }else{
- $val = unserialize($rec["data"]);
- }
- $PHORUM[$rec['name']]=$val;
- $PHORUM['SETTINGS'][$rec['name']]=$val;
- }
- }
- }
- /**
- * Update Phorum settings.
- *
- * @param array $settings
- *
- * @return boolean
- */
- function phorum_db_update_settings($settings){
- global $PHORUM;
- if (count($settings) > 0){
- $conn = phorum_db_mysql_connect();
- foreach($settings as $field => $value){
- if (is_numeric($value)){
- $type = 'V';
- }elseif (is_string($value)){
- $value = mysql_escape_string($value);
- $type = 'V';
- }else{
- $value = mysql_escape_string(serialize($value));
- $type = 'S';
- }
- $sql = "replace into {$PHORUM['settings_table']} set data='$value', type='$type', name='$field'";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- }
- return ($res > 0) ? true : false;
- }else{
- trigger_error("\$settings cannot be empty in phorum_db_update_settings()", E_USER_ERROR);
- }
- }
- /**
- * Get all forums for a flat/collapsed display and return the data in
- * an array.
- *
- * @param mixed $forum_ids
- * Can be an array of forum IDs or an int for one forum ID.
- * @param int $parent_id
- * @param unknown $vroot
- * @param unknown $inherit_id
- *
- * @return array
- */
- function phorum_db_get_forums($forum_ids = 0, $parent_id = -1, $vroot = null, $inherit_id = null){
- $PHORUM = $GLOBALS["PHORUM"];
- settype($parent_id, "int");
- $conn = phorum_db_mysql_connect();
- if (is_array($forum_ids)) {
- $int_ids = array();
- foreach ($forum_ids as $id) {
- settype($id, "int");
- $int_ids[] = $id;
- }
- $forum_ids = implode(",", $int_ids);
- } else {
- settype($forum_ids, "int");
- }
- $sql = "select * from {$PHORUM['forums_table']} ";
- if ($forum_ids){
- $sql .= " where forum_id in ($forum_ids)";
- } elseif ($inherit_id !== null) {
- $sql .= " where inherit_id = $inherit_id";
- if(!defined("PHORUM_ADMIN")) $sql.=" and active=1";
- } elseif ($parent_id >= 0) {
- $sql .= " where parent_id = $parent_id";
- if(!defined("PHORUM_ADMIN")) $sql.=" and active=1";
- } elseif($vroot !== null) {
- $sql .= " where vroot = $vroot";
- } else {
- $sql .= " where forum_id <> 0";
- }
- $sql .= " order by display_order ASC, name";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $forums = array();
- while ($row = mysql_fetch_assoc($res)){
- $forums[$row["forum_id"]] = $row;
- }
- return $forums;
- }
- /**
- * Update the forums stats.
- *
- * @param boolean $refresh
- * If true, it pulls the numbers from the table.
- * @param int $msg_count_change
- * @param int $timestamp
- * @param int $thread_count_change
- * @param int $sticky_count_change
- *
- * @return void
- */
- function phorum_db_update_forum_stats($refresh=false, $msg_count_change=0, $timestamp=0, $thread_count_change=0, $sticky_count_change=0)
- {
- $PHORUM = $GLOBALS["PHORUM"];
- $conn = phorum_db_mysql_connect();
- // always refresh on small forums
- if (isset($PHORUM["message_count"]) && $PHORUM["message_count"]<1000) {
- $refresh=true;
- }
- if($refresh || empty($msg_count_change)){
- $sql = "select count(*) as message_count from {$PHORUM['message_table']} where forum_id={$PHORUM['forum_id']} and status=".PHORUM_STATUS_APPROVED;
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $message_count = (int)mysql_result($res, 0, "message_count");
- } else {
- $message_count="message_count+$msg_count_change";
- }
- if($refresh || empty($timestamp)){
- $sql = "select max(modifystamp) as last_post_time from {$PHORUM['message_table']} where status=".PHORUM_STATUS_APPROVED." and forum_id={$PHORUM['forum_id']}";
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $last_post_time = (int)mysql_result($res, 0, "last_post_time");
- } else {
- $last_post_time = $timestamp;
- }
- if($refresh || empty($thread_count_change)){
- $sql = "select count(*) as thread_count from {$PHORUM['message_table']} where forum_id={$PHORUM['forum_id']} and parent_id=0 and status=".PHORUM_STATUS_APPROVED;
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $thread_count = (int)mysql_result($res, 0, "thread_count");
- } else {
- $thread_count="thread_count+$thread_count_change";
- }
- if($refresh || empty($sticky_count_change)){
- $sql = "select count(*) as sticky_count from {$PHORUM['message_table']} where forum_id={$PHORUM['forum_id']} and sort=".PHORUM_SORT_STICKY." and parent_id=0 and status=".PHORUM_STATUS_APPROVED;
- $res = mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- $sticky_count = (int)mysql_result($res, 0, "sticky_count");
- } else {
- $sticky_count="sticky_count+$sticky_count_change";
- }
- $sql = "update {$PHORUM['forums_table']} set thread_count=$thread_count, message_count=$message_count, sticky_count=$sticky_count, last_post_time=$last_post_time where forum_id={$PHORUM['forum_id']}";
- mysql_query($sql, $conn);
- if ($err = mysql_error()) phorum_db_mysql_error("$err: $sql");
- }
- /**
- * Move a thread to the given forum.
- *
- * @param in…
Large files files are truncated, but you can click here to view the full file