/campsite/src/include/phorum/include/db/mysql.php

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…