PageRenderTime 69ms CodeModel.GetById 32ms RepoModel.GetById 1ms app.codeStats 0ms

/include/db/mysql.php

https://bitbucket.org/webop/webop-forum
PHP | 7740 lines | 4257 code | 857 blank | 2626 comment | 539 complexity | 20abd1693334d73630673cbe96199826 MD5 | raw file
Possible License(s): LGPL-2.1

Large files files are truncated, but you can click here to view the full file

  1. <?php
  2. ////////////////////////////////////////////////////////////////////////////////
  3. // //
  4. // Copyright (C) 2010 Phorum Development Team //
  5. // http://www.phorum.org //
  6. // //
  7. // This program is free software. You can redistribute it and/or modify //
  8. // it under the terms of either the current Phorum License (viewable at //
  9. // phorum.org) or the Phorum License that was distributed with this file //
  10. // //
  11. // This program is distributed in the hope that it will be useful, //
  12. // but WITHOUT ANY WARRANTY, without even the implied warranty of //
  13. // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. //
  14. // //
  15. // You should have received a copy of the Phorum License //
  16. // along with this program. //
  17. // //
  18. ////////////////////////////////////////////////////////////////////////////////
  19. /**
  20. * This script implements a MySQL Phorum database layer.
  21. *
  22. * The other Phorum code does not care how data is stored.
  23. * The only requirement is that it is returned from these functions
  24. * in the right way. This means each database can use as many or as
  25. * few tables as it likes. It can store the fields anyway it wants.
  26. *
  27. * The only thing to worry about is the table_prefix for the tables.
  28. * all tables for a Phorum install should be prefixed with the
  29. * table_prefix that will be entered in include/db/config.php. This
  30. * will allow multiple Phorum installations to use the same database.
  31. *
  32. * @todo
  33. * phorum_api_user_check_access() is used in this layer, but the
  34. * include file for that is not included here. Keep it like that
  35. * or add the required include? Or is it functionality that doesn't
  36. * belong here and could better go into the core maybe?
  37. *
  38. * @package PhorumDBLayer
  39. * @copyright 2010, Phorum Development Team
  40. * @license Phorum License, http://www.phorum.org/license.txt
  41. */
  42. // Bail out if we're not loaded from the Phorum code.
  43. if (!defined('PHORUM')) return;
  44. // ----------------------------------------------------------------------
  45. // Definitions
  46. // ----------------------------------------------------------------------
  47. // {{{ Constant and variable definitions
  48. // The table prefix, which allows for storing multiple Phorum data sets
  49. // in one single database.
  50. $prefix = $PHORUM['DBCONFIG']['table_prefix'];
  51. /**
  52. * These are the table names that are used by this database system.
  53. */
  54. $PHORUM['message_table'] = $prefix . '_messages';
  55. $PHORUM['user_newflags_table'] = $prefix . '_user_newflags';
  56. $PHORUM['subscribers_table'] = $prefix . '_subscribers';
  57. $PHORUM['files_table'] = $prefix . '_files';
  58. $PHORUM['search_table'] = $prefix . '_search';
  59. $PHORUM['settings_table'] = $prefix . '_settings';
  60. $PHORUM['forums_table'] = $prefix . '_forums';
  61. $PHORUM['user_table'] = $prefix . '_users';
  62. $PHORUM['user_permissions_table'] = $prefix . '_user_permissions';
  63. $PHORUM['groups_table'] = $prefix . '_groups';
  64. $PHORUM['forum_group_xref_table'] = $prefix . '_forum_group_xref';
  65. $PHORUM['user_group_xref_table'] = $prefix . '_user_group_xref';
  66. $PHORUM['user_custom_fields_table'] = $prefix . '_user_custom_fields';
  67. $PHORUM['banlist_table'] = $prefix . '_banlists';
  68. $PHORUM['pm_messages_table'] = $prefix . '_pm_messages';
  69. $PHORUM['pm_folders_table'] = $prefix . '_pm_folders';
  70. $PHORUM['pm_xref_table'] = $prefix . '_pm_xref';
  71. $PHORUM['pm_buddies_table'] = $prefix . '_pm_buddies';
  72. $PHORUM['message_tracking_table'] = $prefix . '_messages_edittrack';
  73. /**
  74. * Message fields which are always strings, even if they contain numbers only.
  75. * Used in post-message and update-message, otherwise strange things happen.
  76. */
  77. $PHORUM['string_fields_message'] = array('author', 'subject', 'body', 'email');
  78. /**
  79. * Forum fields which are always strings, even if they contain numbers only.
  80. */
  81. $PHORUM['string_fields_forum'] = array('name', 'description', 'template');
  82. /**
  83. * User fields which are always strings, even if they contain numbers only.
  84. */
  85. $PHORUM['string_fields_user'] = array('username', 'real_name', 'display_name',
  86. 'password', 'password_temp', 'sessid_lt', 'sessid_st', 'email', 'email_temp',
  87. 'signature', 'user_language', 'user_template', 'moderator_data', 'settings_data'
  88. );
  89. /**
  90. * Function call parameter $return for {@link phorum_db_interact()}.
  91. * Makes the function return a database connection handle.
  92. */
  93. define('DB_RETURN_CONN', 0);
  94. /**
  95. * Function call parameter $return for {@link phorum_db_interact()}.
  96. * Makes the function return a SQL quoted value.
  97. */
  98. define('DB_RETURN_QUOTED', 1);
  99. /**
  100. * Function call parameter $return for {@link phorum_db_interact()}.
  101. * Makes the function return the query statement handle for a SQL query.
  102. */
  103. define('DB_RETURN_RES', 2);
  104. /**
  105. * Function call parameter $return for {@link phorum_db_interact()}.
  106. * Makes the function return a single database row for a SQL query.
  107. */
  108. define('DB_RETURN_ROW', 3);
  109. /**
  110. * Function call parameter $return for {@link phorum_db_interact()}.
  111. * Makes the function return an array of rows for a SQL query.
  112. */
  113. define('DB_RETURN_ROWS', 4);
  114. /**
  115. * Function call parameter $return for {@link phorum_db_interact()}.
  116. * Makes the function return a single database row for a SQL query
  117. * as an associative array
  118. */
  119. define('DB_RETURN_ASSOC', 5);
  120. /**
  121. * Function call parameter $return for {@link phorum_db_interact()}.
  122. * Makes the function return an array of rows for a SQL query
  123. * as associative arrays.
  124. */
  125. define('DB_RETURN_ASSOCS', 6);
  126. /**
  127. * Function call parameter $return for {@link phorum_db_interact()}.
  128. * Makes the function return a single value for a SQL query.
  129. */
  130. define('DB_RETURN_VALUE', 7);
  131. /**
  132. * Function call parameter $return for {@link phorum_db_interact()}.
  133. * Makes the function return the number of selected rows for a SQL query.
  134. */
  135. define('DB_RETURN_ROWCOUNT', 8);
  136. /**
  137. * Function call parameter $return for {@link phorum_db_interact()}.
  138. * Makes the function return the new auto_increment id value for
  139. * an insert SQL query.
  140. */
  141. define('DB_RETURN_NEWID', 9);
  142. /**
  143. * Function call parameter $return for {@link phorum_db_interact()}.
  144. * Makes the function return an error for a SQL query or NULL if there
  145. * was no error.
  146. */
  147. define('DB_RETURN_ERROR', 10);
  148. /**
  149. * Function call parameter $return for {@link phorum_db_interact()}.
  150. * Makes the function close the connection to the database.
  151. * The function will return no data.
  152. */
  153. define('DB_CLOSE_CONN', 11);
  154. /**#@+
  155. * Constant for the phorum_db_interact() function call $flags parameter.
  156. */
  157. define('DB_NOCONNECTOK', 1);
  158. define('DB_MISSINGTABLEOK', 2);
  159. define('DB_DUPFIELDNAMEOK', 4);
  160. define('DB_DUPKEYNAMEOK', 8);
  161. define('DB_DUPKEYOK', 16);
  162. define('DB_TABLEEXISTSOK', 32);
  163. define('DB_GLOBALQUERY', 64);
  164. define('DB_MASTERQUERY', 128);
  165. /**#@-*/
  166. /**#@+
  167. * Constant for the phorum_db_get_recent_messages() function call
  168. * $list_type parameter.
  169. */
  170. define('LIST_RECENT_MESSAGES', 0);
  171. define('LIST_RECENT_THREADS', 1);
  172. define('LIST_UPDATED_THREADS', 2);
  173. /**#@-*/
  174. // }}}
  175. // ----------------------------------------------------------------------
  176. // Utility functions (not directly part of the Phorum db API)
  177. // ----------------------------------------------------------------------
  178. // {{{ Function: phorum_db_mysql_connect()
  179. /**
  180. * A wrapper function for connecting to the database.
  181. *
  182. * This function should not be used from the db layer code. Instead the
  183. * phorum_db_interact() function should be used in combination with the
  184. * DB_RETURN_CONN return type. This function is only implemented for
  185. * module writers that use this function in their code.
  186. *
  187. * @return $conn - A database connection resource handle.
  188. * @deprecated
  189. */
  190. function phorum_db_mysql_connect() {
  191. return phorum_db_interact(DB_RETURN_CONN, NULL, NULL, DB_MASTERQUERY);
  192. }
  193. // }}}
  194. // {{{ Function: phorum_db_sanitize_mixed()
  195. /**
  196. * This function will sanitize a mixed variable based on a given type
  197. * for safe use in SQL queries.
  198. *
  199. * @param mixed &$var
  200. * The variable to be sanitized. Passed by reference, so the original
  201. * variable will be updated. It can be either a single variable or an
  202. * array containing multiple variables.
  203. *
  204. * @param string $type
  205. * Either "int" or "string" (the default).
  206. */
  207. function phorum_db_sanitize_mixed(&$var, $type)
  208. {
  209. if (is_array($var)) {
  210. foreach ($var as $id => $val) {
  211. if ($type == 'int') {
  212. $var[$id] = (int)$val;
  213. } else {
  214. $var[$id] = phorum_db_interact(DB_RETURN_QUOTED, $val);
  215. }
  216. }
  217. } else {
  218. if ($type=='int') {
  219. $var = (int)$var;
  220. } else {
  221. $var = phorum_db_interact(DB_RETURN_QUOTED, $var);
  222. }
  223. }
  224. }
  225. // }}}
  226. // {{{ Function: phorum_db_validate_field()
  227. /**
  228. * Check if a value that will be used as a field name in a SQL query
  229. * contains only characters that would appear in a field name.
  230. *
  231. * @param string $field_name
  232. * The field name to check.
  233. *
  234. * @return boolean
  235. * Whether the field name is valid or not (TRUE or FALSE).
  236. */
  237. function phorum_db_validate_field($field_name)
  238. {
  239. $valid = preg_match('!^[a-zA-Z0-9_]+$!', $field_name);
  240. return (bool)$valid;
  241. }
  242. // }}}
  243. // ----------------------------------------------------------------------
  244. // API functions
  245. // ----------------------------------------------------------------------
  246. // {{{ Function: phorum_db_check_connection()
  247. /**
  248. * @todo
  249. * we can save a function call by directly calling
  250. * phorum_db_interact(). I'm also not sure if we need
  251. * to do this check from common.php. We could take care
  252. * of this in the db layer error handling too. Have to
  253. * think about this ...
  254. *
  255. * Checks if a database connection can be made.
  256. *
  257. * @return boolean
  258. * TRUE if a connection can be made, FALSE otherwise.
  259. */
  260. function phorum_db_check_connection()
  261. {
  262. return phorum_db_interact(
  263. DB_RETURN_CONN,
  264. NULL, NULL,
  265. DB_NOCONNECTOK | DB_MASTERQUERY
  266. ) ? TRUE : FALSE;
  267. }
  268. // }}}
  269. // {{{ Function: phorum_db_close_connection()
  270. /**
  271. * Close the database connection.
  272. */
  273. function phorum_db_close_connection()
  274. {
  275. phorum_db_interact(DB_CLOSE_CONN);
  276. }
  277. // }}}
  278. // {{{ Function: phorum_db_run_queries()
  279. /**
  280. * Execute an array of queries.
  281. *
  282. * @param array $queries
  283. * An array of SQL queries to execute.
  284. *
  285. * @return mixed
  286. * NULL if all queries were executed successfully or an error
  287. * message on failure.
  288. */
  289. function phorum_db_run_queries($queries)
  290. {
  291. $PHORUM = $GLOBALS['PHORUM'];
  292. $error = NULL;
  293. foreach ($queries as $sql)
  294. {
  295. // Because this function is used from the upgrade scripts,
  296. // we ignore errors about duplicate fields and keys. That
  297. // way running the same upgrade scripts twice (in case there
  298. // were problems during the first run) won't bring up fatal
  299. // errors in case fields or keys are created a second time.
  300. $error = phorum_db_interact(
  301. DB_RETURN_ERROR,
  302. $sql, NULL,
  303. DB_DUPFIELDNAMEOK | DB_DUPKEYNAMEOK | DB_TABLEEXISTSOK |
  304. DB_GLOBALQUERY | DB_MASTERQUERY
  305. );
  306. if ($error !== NULL) break;
  307. }
  308. return $error;
  309. }
  310. // }}}
  311. // {{{ Function: phorum_db_load_settings()
  312. /**
  313. * Load the Phorum settings in the $PHORUM array.
  314. *
  315. * These settings are key/value pairs that are read from the settings
  316. * table. In the settings table, a data type is provided for each setting.
  317. * The supported types are:
  318. *
  319. * - V = Value: the value of this field is used as is.
  320. * - S = Serialized: the value of this field is a serialzed PHP variable,
  321. * which will be unserialized before storing it in $PHORUM
  322. */
  323. function phorum_db_load_settings()
  324. {
  325. global $PHORUM;
  326. // At install time, there is no settings table.
  327. // So we ignore errors if we do not see that table.
  328. $settings = phorum_db_interact(
  329. DB_RETURN_ROWS,
  330. "SELECT name, data, type
  331. FROM {$PHORUM['settings_table']}",
  332. NULL,
  333. DB_MISSINGTABLEOK
  334. );
  335. foreach ($settings as $setting)
  336. {
  337. $val = $setting[2] == 'V'
  338. ? $setting[1]
  339. : unserialize($setting[1]);
  340. $PHORUM[$setting[0]] = $val;
  341. }
  342. }
  343. // }}}
  344. // {{{ Function: phorum_db_update_settings()
  345. /**
  346. * Store or update Phorum settings.
  347. *
  348. * @param array $settings
  349. * An array containing key/value pairs that have to be stored in the
  350. * settings table. Values can be either scalars or arrays. This
  351. * function will automatically serialize the arrays before storing them.
  352. *
  353. * @return boolean
  354. * TRUE if all settings were stored successfully. This function will
  355. * always return TRUE, so we could do without a return value. The
  356. * return value is here for backward compatibility.
  357. */
  358. function phorum_db_update_settings($settings)
  359. {
  360. global $PHORUM;
  361. if (count($settings) > 0)
  362. {
  363. foreach ($settings as $field => $value)
  364. {
  365. if (is_array($value)) {
  366. $value = serialize($value);
  367. $type = 'S';
  368. } else {
  369. $type = 'V';
  370. }
  371. $field = phorum_db_interact(DB_RETURN_QUOTED, $field);
  372. $value = phorum_db_interact(DB_RETURN_QUOTED, $value);
  373. // Try to insert a new settings record.
  374. $res = phorum_db_interact(
  375. DB_RETURN_RES,
  376. "INSERT INTO {$PHORUM['settings_table']}
  377. (data, type, name)
  378. VALUES ('$value', '$type', '$field')",
  379. NULL,
  380. DB_DUPKEYOK | DB_MASTERQUERY
  381. );
  382. // If no result was returned, then the query failed. This probably
  383. // means that we already have the settings record in the database.
  384. // So instead of inserting a record, we need to update one here.
  385. if (!$res) {
  386. phorum_db_interact(
  387. DB_RETURN_RES,
  388. "UPDATE {$PHORUM['settings_table']}
  389. SET data = '$value',
  390. type = '$type'
  391. WHERE name = '$field'",
  392. NULL,
  393. DB_MASTERQUERY
  394. );
  395. }
  396. }
  397. }
  398. else trigger_error(
  399. 'phorum_db_update_settings(): $settings cannot be empty',
  400. E_USER_ERROR
  401. );
  402. return TRUE;
  403. }
  404. // }}}
  405. // {{{ Function: phorum_db_get_thread_list()
  406. /**
  407. * Retrieve a list of visible messages for a given page offset.
  408. *
  409. * By default, the message body is not included in the fetch queries.
  410. * To retrieve bodies as well, a true value has to be passed for the
  411. * $include_bodies parameter.
  412. *
  413. * NOTE: ALL dates must be returned as Unix timestamps
  414. *
  415. * @param integer $page
  416. * The index of the page to return, starting with 0.
  417. *
  418. * @param boolean $include_bodies
  419. * Whether to include the message bodies in the return data or not.
  420. *
  421. * @return array
  422. * An array of messages, indexed by message id.
  423. */
  424. function phorum_db_get_thread_list($page, $include_bodies=FALSE)
  425. {
  426. $PHORUM = $GLOBALS['PHORUM'];
  427. settype($page, 'int');
  428. // The messagefields that we want to fetch from the database.
  429. $messagefields =
  430. 'author, datestamp, email, message_id, forum_id, meta,
  431. moderator_post, modifystamp, parent_id, msgid, sort, moved, status,
  432. subject, thread, thread_count, user_id, viewcount, threadviewcount,
  433. closed, ip, recent_message_id, recent_user_id, recent_author';
  434. // Include the message bodies in the thread list if requested.
  435. if ($include_bodies) {
  436. $messagefields .= ',body';
  437. }
  438. // The sort mechanism to use.
  439. if ($PHORUM['float_to_top']) {
  440. $sortfield = 'modifystamp';
  441. $index = 'list_page_float';
  442. } else {
  443. $sortfield = 'thread';
  444. $index = 'list_page_flat';
  445. }
  446. // Initialize the return array.
  447. $messages = array();
  448. // The groups of messages which we want to fetch from the database.
  449. // stickies : sticky messages (only on the first page)
  450. // threads : thread starter messages (always)
  451. // replies : thread reply messages (only in threaded list mode)
  452. $groups = array();
  453. if ($page == 0) $groups[] = 'stickies';
  454. $groups[] = 'threads';
  455. if ($PHORUM['threaded_list']) $groups[] = 'replies';
  456. // For remembering the message ids for which we want to fetch the replies.
  457. $replymsgids = array();
  458. // Process all groups.
  459. foreach ($groups as $group)
  460. {
  461. $sql = NULL;
  462. switch ($group)
  463. {
  464. // Stickies.
  465. case 'stickies':
  466. $sql = "SELECT $messagefields
  467. FROM {$PHORUM['message_table']}
  468. WHERE status=".PHORUM_STATUS_APPROVED." AND
  469. parent_id=0 AND
  470. sort=".PHORUM_SORT_STICKY." AND
  471. forum_id={$PHORUM['forum_id']}
  472. ORDER BY sort, $sortfield desc";
  473. break;
  474. // Threads.
  475. case 'threads':
  476. if ($PHORUM['threaded_list']) {
  477. $limit = $PHORUM['list_length_threaded'];
  478. } else {
  479. $limit = $PHORUM['list_length_flat'];
  480. }
  481. $start = $page * $limit;
  482. $sql = "SELECT $messagefields
  483. FROM {$PHORUM['message_table']}
  484. USE INDEX ($index)
  485. WHERE $sortfield > 0 AND
  486. forum_id = {$PHORUM['forum_id']} AND
  487. status = ".PHORUM_STATUS_APPROVED." AND
  488. parent_id = 0 AND
  489. sort > 1
  490. ORDER BY $sortfield DESC
  491. LIMIT $start, $limit";
  492. break;
  493. // Reply messages.
  494. case 'replies':
  495. // We're done if we did not collect any messages with replies.
  496. if (! count($replymsgids)) break;
  497. $sortorder = "sort, $sortfield DESC, message_id";
  498. if (!empty($PHORUM['reverse_threading']))
  499. $sortorder.=' DESC';
  500. $sql = "SELECT $messagefields
  501. FROM {$PHORUM['message_table']}
  502. WHERE status = ".PHORUM_STATUS_APPROVED." AND
  503. thread in (" . implode(",",$replymsgids) .")
  504. ORDER BY $sortorder";
  505. break;
  506. } // End of switch ($group)
  507. // Continue with the next group if no SQL query was formulated.
  508. if ($sql === NULL) continue;
  509. // Query the messages for the current group.
  510. $rows = phorum_db_interact(DB_RETURN_ASSOCS, $sql, 'message_id');
  511. foreach ($rows as $id => $row)
  512. {
  513. // Unpack the thread message meta data.
  514. $row['meta'] = empty($row['meta'])
  515. ? array()
  516. : unserialize($row['meta']);
  517. // Add the row to the list of messages.
  518. $messages[$id] = $row;
  519. // We need the message ids for fetching reply messages.
  520. if ($group == 'threads' && $row['thread_count'] > 1) {
  521. $replymsgids[] = $id;
  522. }
  523. }
  524. }
  525. return $messages;
  526. }
  527. // }}}
  528. // {{{ Function: phorum_db_get_recent_messages
  529. /**
  530. * Retrieve a list of recent messages for all forums for which the user has
  531. * read permission, for a particular forum, for a list of forums or for a
  532. * particular thread. Optionally, only top level thread messages can be
  533. * retrieved.
  534. *
  535. * The original version of this function came from Jim Winstead of mysql.com
  536. *
  537. * @param integer $length
  538. * Limit the number of returned messages to this number.
  539. *
  540. * @param integer $offset
  541. * When using the $length parameter to limit the number of returned
  542. * messages, this parameter can be used to specify the retrieval offset.
  543. *
  544. * @param integer $forum_id
  545. * A forum_id, an array of forum_ids or 0 (zero) to retrieve messages
  546. * from any forum.
  547. *
  548. * @param integer $thread
  549. * A thread id or 0 (zero) to retrieve messages from any thread.
  550. *
  551. * @param integer $list_type
  552. * This parameter determines the type of list that has to be returned.
  553. * Options for this parameter are:
  554. * - LIST_RECENT_MESSAGES: return a list of recent messages
  555. * - LIST_RECENT_THREADS: return a list of recent threads
  556. * - LIST_UPDATED_THREADS: return a list of recently updated threads
  557. *
  558. * @return array
  559. * An array of recent messages, indexed by message_id. One special key
  560. * "users" is set too. This one contains an array of all involved
  561. * user_ids.
  562. */
  563. function phorum_db_get_recent_messages($length, $offset = 0, $forum_id = 0, $thread = 0, $list_type = LIST_RECENT_MESSAGES)
  564. {
  565. $PHORUM = $GLOBALS['PHORUM'];
  566. // Backward compatibility for the old $threads_only parameter.
  567. if (is_bool($list_type)) {
  568. $list_type = $list_type ? LIST_RECENT_THREADS : LIST_RECENT_MESSAGES;
  569. }
  570. settype($length, 'int');
  571. settype($offset, 'int');
  572. settype($thread, 'int');
  573. settype($list_type, 'int');
  574. phorum_db_sanitize_mixed($forum_id, 'int');
  575. // In case -1 is used as "any" value by the caller.
  576. if ($forum_id < 0) $forum_id = 0;
  577. if ($thread < 0) $thread = 0;
  578. // Parameter checking.
  579. if ($list_type < 0 || $list_type > 3) trigger_error(
  580. "phorum_db_get_recent_messages(): illegal \$list_type parameter used",
  581. E_USER_ERROR
  582. );
  583. if ($list_type != LIST_RECENT_MESSAGES && $thread) trigger_error(
  584. "phorum_db_get_recent_messages(): \$thread parameter can only be " .
  585. "used with \$list_type = LIST_RECENT_MESSAGES",
  586. E_USER_ERROR
  587. );
  588. // We have to check what forums the active Phorum user can read first.
  589. // Even if a $thread is passed, we have to make sure that the user
  590. // can read the containing forum. Here we convert the $forum_id argument
  591. // into an argument that is usable for phorum_api_user_check_access(),
  592. // in such way that it will always return an array of accessible forum_ids.
  593. if ($forum_id == 0) {
  594. $forum_id = PHORUM_ACCESS_LIST;
  595. } elseif(!is_array($forum_id)) {
  596. $forum_id = array($forum_id => $forum_id);
  597. }
  598. $allowed_forums = phorum_api_user_check_access(
  599. PHORUM_USER_ALLOW_READ, $forum_id
  600. );
  601. // If the user is not allowed to see any forum,
  602. // then return an empty array.
  603. if (empty($allowed_forums)) return array();
  604. // We need to differentiate on which key to use.
  605. // If selecting on a specific thread, then the best index
  606. // to use would be the thread_message index.
  607. if ($thread) {
  608. $use_key = 'thread_message';
  609. }
  610. // Indexes to use if we query exactly one forum.
  611. elseif (count($allowed_forums) == 1)
  612. {
  613. switch($list_type) {
  614. case LIST_RECENT_MESSAGES:
  615. $use_key = 'new_count';
  616. break;
  617. case LIST_RECENT_THREADS:
  618. $use_key = 'new_threads';
  619. break;
  620. case LIST_UPDATED_THREADS:
  621. $use_key = 'list_page_float';
  622. break;
  623. }
  624. }
  625. // Indexes to use if we query more than one forum.
  626. else
  627. {
  628. switch($list_type) {
  629. case LIST_RECENT_MESSAGES:
  630. $use_key = 'PRIMARY';
  631. break;
  632. case LIST_RECENT_THREADS:
  633. $use_key = 'recent_threads';
  634. break;
  635. case LIST_UPDATED_THREADS:
  636. $use_key = 'updated_threads';
  637. break;
  638. }
  639. }
  640. // Build the SQL query.
  641. $sql = "SELECT *
  642. FROM {$PHORUM['message_table']}
  643. USE INDEX ($use_key)
  644. WHERE status=".PHORUM_STATUS_APPROVED;
  645. if (count($allowed_forums) == 1) {
  646. $sql .= " AND forum_id = " . array_shift($allowed_forums);
  647. } else {
  648. $sql .= " AND forum_id IN (".implode(",", $allowed_forums).")";
  649. }
  650. if ($thread) {
  651. $sql.=" AND thread = $thread";
  652. }
  653. $sql .= " AND moved = 0";
  654. if ($list_type == LIST_RECENT_THREADS ||
  655. $list_type == LIST_UPDATED_THREADS) {
  656. $sql .= ' AND parent_id = 0';
  657. }
  658. if ($list_type == LIST_UPDATED_THREADS) {
  659. $sql .= ' ORDER BY modifystamp DESC';
  660. } else {
  661. $sql .= ' ORDER BY message_id DESC';
  662. }
  663. if ($length) {
  664. if ($offset > 0) {
  665. $sql .= " LIMIT $offset, $length";
  666. } else {
  667. $sql .= " LIMIT $length";
  668. }
  669. }
  670. // Retrieve matching messages from the database.
  671. $messages = phorum_db_interact(DB_RETURN_ASSOCS, $sql, 'message_id');
  672. // Post processing of received messages.
  673. $involved_users = array();
  674. foreach ($messages as $id => $message)
  675. {
  676. // Unpack the message meta data.
  677. $messages[$id]['meta'] = empty($message['meta'])
  678. ? array()
  679. : unserialize($message['meta']);
  680. // Collect all involved users.
  681. if (isset($message['user_id'])) {
  682. $involved_users[$message['user_id']] = $message['user_id'];
  683. }
  684. }
  685. // Store the involved users in the message array.
  686. $messages['users'] = $involved_users;
  687. return $messages;
  688. }
  689. // }}}
  690. // {{{ Function: phorum_db_get_unapproved_list()
  691. /**
  692. * Retrieve a list of messages which have not yet been approved by a moderator.
  693. *
  694. * NOTE: ALL dates must be returned as Unix timestamps
  695. *
  696. * @param $forum_id - The forum id to work with or NULL in case all
  697. * forums have to be searched. You can also pass an
  698. * array of forum ids.
  699. * @param $on_hold_only - Only take into account messages which have to
  700. * be approved directly after posting. Do not include
  701. * messages which were hidden by a moderator.
  702. * @param $moddays - Limit the search to the last $moddays number of days.
  703. *
  704. * @return - An array of messages, indexed by message id.
  705. */
  706. function phorum_db_get_unapproved_list($forum_id = NULL, $on_hold_only=FALSE, $moddays=0, $countonly = FALSE)
  707. {
  708. $PHORUM = $GLOBALS['PHORUM'];
  709. settype($on_hold_only, 'bool');
  710. settype($moddays, 'int');
  711. settype($countonly, 'bool');
  712. phorum_db_sanitize_mixed($forum_id, 'int');
  713. // Select a message count or full message records?
  714. $sql = 'SELECT ' . ($countonly ? 'count(*) ' : '* ') .
  715. 'FROM ' . $PHORUM['message_table'] . ' WHERE ';
  716. if (is_array($forum_id)) {
  717. $sql .= 'forum_id IN (' . implode(', ', $forum_id) . ') AND ';
  718. } elseif ($forum_id !== NULL) {
  719. $sql .= "forum_id = $forum_id AND ";
  720. }
  721. if ($moddays > 0) {
  722. $checktime = time() - (86400*$moddays);
  723. $sql .= " datestamp > $checktime AND";
  724. }
  725. if ($on_hold_only) {
  726. $sql .= ' status = '.PHORUM_STATUS_HOLD;
  727. } else {
  728. // Use an UNION for speed. This is much faster than using
  729. // a (status=X or status=Y) query.
  730. $sql = "($sql status = ".PHORUM_STATUS_HOLD.") UNION " .
  731. "($sql status = ".PHORUM_STATUS_HIDDEN.")";
  732. }
  733. if (!$countonly) {
  734. $sql .= ' ORDER BY thread, message_id';
  735. }
  736. // Retrieve and return data for counting unapproved messages.
  737. if ($countonly) {
  738. $count_per_status = phorum_db_interact(DB_RETURN_ROWS, $sql);
  739. $sum = 0;
  740. foreach ($count_per_status as $count) $sum += $count[0];
  741. return $sum;
  742. }
  743. // Retrieve unapproved messages.
  744. $messages = phorum_db_interact(DB_RETURN_ASSOCS, $sql, 'message_id');
  745. // Post processing of received messages.
  746. foreach ($messages as $id => $message) {
  747. $messages[$id]['meta'] = empty($message['meta'])
  748. ? array()
  749. : unserialize($message['meta']);
  750. }
  751. return $messages;
  752. }
  753. // }}}
  754. // {{{ Function: phorum_db_post_message()
  755. /**
  756. * Store a new message in the database.
  757. *
  758. * The message will not be posted if it is a duplicate and if
  759. * $PHORUM['check_duplicate'] is set.
  760. *
  761. * The $message is passed by reference and in case the function completes
  762. * successfully, the "message_id" index will be set to the new value.
  763. * If the "thread" index is set to zero, a new thread will be started and the
  764. * "thread" index will be filled with the new thread id upon return.
  765. *
  766. * @param array &$message
  767. * The message to post. This is an array, which should contain the
  768. * following fields: forum_id, thread, parent_id, author, subject, email,
  769. * ip, user_id, moderator_post, status, sort, msgid, body, closed.
  770. * Additionally, the following optional fields can be set: meta,
  771. * modifystamp, viewcount, threadviewcount.
  772. *
  773. * @param boolean $convert
  774. * True in case the message is being inserted by a database conversion
  775. * script. This will let you set the datestamp and message_id of the
  776. * message from the $message data. Also, the duplicate message check
  777. * will be fully skipped.
  778. *
  779. * @return integer
  780. * The message_id that was assigned to the new message.
  781. */
  782. function phorum_db_post_message(&$message, $convert=FALSE)
  783. {
  784. $PHORUM = $GLOBALS['PHORUM'];
  785. settype($convert, 'bool');
  786. foreach ($message as $key => $value) {
  787. if (is_numeric($value) &&
  788. !in_array($key,$PHORUM['string_fields_message'])) {
  789. $message[$key] = (int)$value;
  790. } elseif (is_array($value)) {
  791. $value = serialize($value);
  792. $message[$key] = phorum_db_interact(DB_RETURN_QUOTED, $value);
  793. } else {
  794. $message[$key] = phorum_db_interact(DB_RETURN_QUOTED, $value);
  795. }
  796. }
  797. // When converting messages, the post time should be in the message.
  798. $NOW = $convert ? $message['datestamp'] : time();
  799. // Check for duplicate posting of messages, unless we are converting a db.
  800. if (isset($PHORUM['check_duplicate']) && $PHORUM['check_duplicate'] && !$convert) {
  801. // Check for duplicate messages in the last hour.
  802. $check_timestamp = $NOW - 3600;
  803. $sql = "SELECT message_id
  804. FROM {$PHORUM['message_table']}
  805. WHERE forum_id = {$message['forum_id']} AND
  806. author ='{$message['author']}' AND
  807. subject ='{$message['subject']}' AND
  808. body ='{$message['body']}' AND
  809. datestamp > $check_timestamp";
  810. // Return 0 if at least one message can be found.
  811. if (phorum_db_interact(DB_RETURN_ROWCOUNT, $sql) > 0) return 0;
  812. }
  813. $insertfields = array(
  814. 'forum_id' => $message['forum_id'],
  815. 'datestamp' => $NOW,
  816. 'thread' => $message['thread'],
  817. 'parent_id' => $message['parent_id'],
  818. 'author' => "'" . $message['author'] . "'",
  819. 'subject' => "'" . $message['subject'] . "'",
  820. 'email' => "'" . $message['email'] . "'",
  821. 'ip' => "'" . $message['ip'] . "'",
  822. 'user_id' => $message['user_id'],
  823. 'moderator_post' => $message['moderator_post'],
  824. 'status' => $message['status'],
  825. 'sort' => $message['sort'],
  826. 'msgid' => "'" . $message['msgid'] . "'",
  827. 'body' => "'" . $message['body'] . "'",
  828. 'closed' => $message['closed'],
  829. 'moved' => 0
  830. );
  831. // The meta field is optional.
  832. if (isset($message['meta'])) {
  833. $insertfields['meta'] = "'{$message['meta']}'";
  834. }
  835. // The moved field is optional.
  836. if (!empty($message['moved'])) {
  837. $insertfields['moved'] = 1;
  838. }
  839. // When handling a conversion, the message_id can be set.
  840. if ($convert && isset($message['message_id'])) {
  841. $insertfields['message_id'] = $message['message_id'];
  842. }
  843. if (isset($message['modifystamp'])) {
  844. $insertfields['modifystamp'] = $message['modifystamp'];
  845. }
  846. if (isset($message['viewcount'])) {
  847. $insertfields['viewcount'] = $message['viewcount'];
  848. }
  849. if (isset($message['threadviewcount'])) {
  850. $insertfields['threadviewcount'] = $message['threadviewcount'];
  851. }
  852. // Insert the message and get the new message_id.
  853. $message_id = phorum_db_interact(
  854. DB_RETURN_NEWID,
  855. "INSERT INTO {$PHORUM['message_table']}
  856. (".implode(', ', array_keys($insertfields)).")
  857. VALUES (".implode(', ', $insertfields).")",
  858. NULL,
  859. DB_MASTERQUERY
  860. );
  861. $message['message_id'] = $message_id;
  862. $message['datestamp'] = $NOW;
  863. // Updates for thread starter messages.
  864. if ($message['thread'] == 0)
  865. {
  866. phorum_db_interact(
  867. DB_RETURN_RES,
  868. "UPDATE {$PHORUM['message_table']}
  869. SET thread = $message_id
  870. WHERE message_id = $message_id",
  871. NULL,
  872. DB_MASTERQUERY
  873. );
  874. $message['thread'] = $message_id;
  875. }
  876. if(empty($PHORUM['DBCONFIG']['empty_search_table'])) {
  877. // Full text searching updates.
  878. $search_text = $message['author'] .' | '.
  879. $message['subject'] .' | '.
  880. $message['body'];
  881. phorum_db_interact(
  882. DB_RETURN_RES,
  883. "INSERT DELAYED INTO {$PHORUM['search_table']}
  884. (message_id, forum_id,
  885. search_text)
  886. VALUES ({$message['message_id']}, {$message['forum_id']},
  887. '$search_text')",
  888. NULL,
  889. DB_MASTERQUERY
  890. );
  891. }
  892. return $message_id;
  893. }
  894. // }}}
  895. // {{{ Function: phorum_db_update_message()
  896. /**
  897. * Update a message in the database.
  898. *
  899. * Note: an update of the full text searching database is only handled
  900. * if all fields that we incorporate in full text searching (author,
  901. * subject and body) are in the update fields. If one of the fields is
  902. * provided, without providing the other two, then changes in the field
  903. * will not reflect in the full text searching info.
  904. *
  905. * @param $message_id - The message_id of the message to update.
  906. * @param $message - An array containing the data for the message fields
  907. * that have to be updated. You can pass as many or
  908. * as few message fields as you wish to update.
  909. */
  910. function phorum_db_update_message($message_id, $message)
  911. {
  912. $PHORUM = $GLOBALS['PHORUM'];
  913. settype($message_id, 'int');
  914. if (count($message) == 0) trigger_error(
  915. '$message cannot be empty in phorum_update_message()',
  916. E_USER_ERROR
  917. );
  918. foreach ($message as $field => $value)
  919. {
  920. if (phorum_db_validate_field($field))
  921. {
  922. if (is_numeric($value) &&
  923. !in_array($field, $PHORUM['string_fields_message'])) {
  924. $fields[] = "$field = $value";
  925. } elseif (is_array($value)) {
  926. $value = phorum_db_interact(DB_RETURN_QUOTED,serialize($value));
  927. $message[$field] = $value;
  928. $fields[] = "$field = '$value'";
  929. } else {
  930. $value = phorum_db_interact(DB_RETURN_QUOTED, $value);
  931. $message[$field] = $value;
  932. $fields[] = "$field = '$value'";
  933. }
  934. }
  935. }
  936. phorum_db_interact(
  937. DB_RETURN_RES,
  938. "UPDATE {$PHORUM['message_table']}
  939. SET " . implode(', ', $fields) . "
  940. WHERE message_id = $message_id",
  941. NULL,
  942. DB_MASTERQUERY
  943. );
  944. // Full text searching updates.
  945. if (!empty($PHORUM['DBCONFIG']['mysql_use_ft']) &&
  946. isset($message['author']) &&
  947. isset($message['subject']) &&
  948. isset($message['body']) &&
  949. empty($PHORUM['DBCONFIG']['empty_search_table']) ) {
  950. $search_text = $message['author'] .' | '.
  951. $message['subject'] .' | '.
  952. $message['body'];
  953. phorum_db_interact(
  954. DB_RETURN_RES,
  955. "REPLACE DELAYED INTO {$PHORUM['search_table']}
  956. SET message_id = {$message_id},
  957. forum_id = {$message['forum_id']},
  958. search_text = '$search_text'",
  959. NULL,
  960. DB_MASTERQUERY
  961. );
  962. }
  963. }
  964. // }}}
  965. // {{{ Function: phorum_db_delete_message()
  966. /**
  967. * Delete a message or a message tree from the database.
  968. *
  969. * @param integer $message_id
  970. * The message_id of the message which should be deleted.
  971. *
  972. * @param integer $mode
  973. * The mode of deletion. This is one of:
  974. * - PHORUM_DELETE_MESSAGE: Delete a message and reconnect
  975. * its reply messages to the parent of the deleted message.
  976. * - PHORUM_DELETE_TREE: Delete a message and all its reply messages.
  977. */
  978. function phorum_db_delete_message($message_id, $mode = PHORUM_DELETE_MESSAGE)
  979. {
  980. $PHORUM = $GLOBALS['PHORUM'];
  981. settype($message_id, 'int');
  982. settype($mode, 'int');
  983. // Find the info for the message that has to be deleted.
  984. $msg = phorum_db_interact(
  985. DB_RETURN_ASSOC,
  986. "SELECT forum_id, message_id, thread, parent_id
  987. FROM {$PHORUM['message_table']}
  988. WHERE message_id = $message_id"
  989. );
  990. if (empty($msg)) trigger_error(
  991. "No message found for message_id $message_id", E_USER_ERROR
  992. );
  993. // Find all message_ids that have to be deleted, based on the mode.
  994. if ($mode == PHORUM_DELETE_TREE) {
  995. $mids = phorum_db_get_messagetree($message_id, $msg['forum_id']);
  996. $where = "message_id IN ($mids)";
  997. $mids = explode(',', $mids);
  998. } else {
  999. $mids = array($message_id);
  1000. $where = "message_id = $message_id";
  1001. }
  1002. // First, the messages are unapproved, so replies will not get posted
  1003. // during the time that we need for deleting them. There is still a
  1004. // race condition here, but this already makes things quite reliable.
  1005. phorum_db_interact(
  1006. DB_RETURN_RES,
  1007. "UPDATE {$PHORUM['message_table']}
  1008. SET status=".PHORUM_STATUS_HOLD."
  1009. WHERE $where",
  1010. NULL,
  1011. DB_MASTERQUERY
  1012. );
  1013. $thread = $msg['thread'];
  1014. // Change reply messages to point to the parent of the deleted message.
  1015. if ($mode == PHORUM_DELETE_MESSAGE)
  1016. {
  1017. // The forum_id is in here for speeding up the query
  1018. // (with the forum_id a lookup key will be used).
  1019. phorum_db_interact(
  1020. DB_RETURN_RES,
  1021. "UPDATE {$PHORUM['message_table']}
  1022. SET parent_id = {$msg['parent_id']}
  1023. WHERE forum_id = {$msg['forum_id']} AND
  1024. parent_id = {$msg['message_id']}",
  1025. NULL,
  1026. DB_MASTERQUERY
  1027. );
  1028. }
  1029. // Delete the messages.
  1030. phorum_db_interact(
  1031. DB_RETURN_RES,
  1032. "DELETE FROM {$PHORUM['message_table']}
  1033. WHERE $where",
  1034. NULL,
  1035. DB_MASTERQUERY
  1036. );
  1037. // Delete the read flags.
  1038. phorum_db_interact(
  1039. DB_RETURN_RES,
  1040. "DELETE FROM {$PHORUM['user_newflags_table']}
  1041. WHERE $where",
  1042. NULL,
  1043. DB_MASTERQUERY
  1044. );
  1045. // Delete the edit tracking.
  1046. phorum_db_interact(
  1047. DB_RETURN_RES,
  1048. "DELETE FROM {$PHORUM['message_tracking_table']}
  1049. WHERE $where",
  1050. NULL,
  1051. DB_MASTERQUERY
  1052. );
  1053. // Full text searching updates.
  1054. phorum_db_interact(
  1055. DB_RETURN_RES,
  1056. "DELETE FROM {$PHORUM['search_table']}
  1057. WHERE $where",
  1058. NULL,
  1059. DB_MASTERQUERY
  1060. );
  1061. // It kind of sucks to have this here, but it is the best way
  1062. // to ensure that thread info gets updated if messages are deleted.
  1063. // Leave this include down here, so it is included conditionally.
  1064. include_once('./include/thread_info.php');
  1065. phorum_update_thread_info($thread);
  1066. // We need to delete the subscriptions for the thread too.
  1067. phorum_db_interact(
  1068. DB_RETURN_RES,
  1069. "DELETE FROM {$PHORUM['subscribers_table']}
  1070. WHERE forum_id > 0 AND thread = $thread",
  1071. NULL,
  1072. DB_MASTERQUERY
  1073. );
  1074. // This function will be slow with a lot of messages.
  1075. phorum_db_update_forum_stats(TRUE);
  1076. return $mids;
  1077. }
  1078. // }}}
  1079. // {{{ Function: phorum_db_get_messagetree()
  1080. /**
  1081. * Build a tree of all child (reply) messages below a message_id.
  1082. *
  1083. * @param integer $message_id
  1084. * The message_id for which to build the message tree.
  1085. *
  1086. * @param integer $forum_id
  1087. * The forum_id for the message.
  1088. *
  1089. * @return string
  1090. * A string containing a comma separated list of child message_ids
  1091. * for the given message_id.
  1092. */
  1093. function phorum_db_get_messagetree($message_id, $forum_id)
  1094. {
  1095. $PHORUM = $GLOBALS['PHORUM'];
  1096. settype($message_id, 'int');
  1097. settype($forum_id, 'int');
  1098. // Find all children for the provided message_id.
  1099. $child_ids = phorum_db_interact(
  1100. DB_RETURN_ROWS,
  1101. "SELECT message_id
  1102. FROM {$PHORUM['message_table']}
  1103. WHERE forum_id = $forum_id AND
  1104. parent_id = $message_id"
  1105. );
  1106. // Recursively build the message tree.
  1107. $tree = "$message_id";
  1108. foreach ($child_ids as $child_id) {
  1109. $tree .= ',' . phorum_db_get_messagetree($child_id[0], $forum_id);
  1110. }
  1111. return $tree;
  1112. }
  1113. // }}}
  1114. // {{{ Function: phorum_db_get_message()
  1115. /**
  1116. * Retrieve message(s) from the messages table by comparing value(s)
  1117. * for a specified field in that table.
  1118. *
  1119. * You can provide either a single value or an array of values to search
  1120. * for. If a single value is provided, then the function will return the
  1121. * first matching message in the table. If an array of values is provided,
  1122. * the function will return all matching messages in an array.
  1123. *
  1124. * @param mixed $value
  1125. * The value that you want to search on in the messages table.
  1126. * This can be either a single value or an array of values.
  1127. *
  1128. * @param string $field
  1129. * The message field (database column) to search on.
  1130. *
  1131. * @param boolean $ignore_forum_id
  1132. * By default, this function will only search for messages within the
  1133. * active forum (as defined by $PHORUM["forum_id"). By setting this
  1134. * parameter to a true value, the function will search in any forum.
  1135. *
  1136. * @param boolean $write_server
  1137. * This value can be set to true to specify that the message should be
  1138. * retrieved from the master (aka write-server) in case replication
  1139. * is used.
  1140. *
  1141. * @return mixed
  1142. * Either a single message or an array of messages (indexed by
  1143. * message_id), depending on the $value parameter. If no message is
  1144. * found at all, then either an empty array or NULL is returned
  1145. * (also depending on the $value parameter).
  1146. */
  1147. function phorum_db_get_message($value, $field='message_id', $ignore_forum_id=FALSE, $write_server=FALSE)
  1148. {
  1149. $PHORUM = $GLOBALS['PHORUM'];
  1150. phorum_db_sanitize_mixed($value, 'string');
  1151. settype($ignore_forum_id, 'bool');
  1152. if (!phorum_db_validate_field($field)) trigger_error(
  1153. 'phorum_db_get_message(): Illegal database field ' .
  1154. '"' . htmlspecialchars($field) . '"', E_USER_ERROR
  1155. );
  1156. $forum_id_check = '';
  1157. if (!$ignore_forum_id && !empty($PHORUM['forum_id'])) {
  1158. $forum_id_check = "forum_id = {$PHORUM['forum_id']} AND ";
  1159. }
  1160. if (is_array($value)) {
  1161. $multiple = TRUE;
  1162. $checkvar = "$field IN ('".implode("','",$value)."')";
  1163. $limit = '';
  1164. } else {
  1165. $multiple=FALSE;
  1166. $checkvar = "$field = '$value'";
  1167. $limit = 'LIMIT 1';
  1168. }
  1169. $return = $multiple ? array() : NULL;
  1170. if($write_server) {
  1171. $flags = DB_MASTERQUERY;
  1172. } else {
  1173. $flags = 0;
  1174. }
  1175. $messages = phorum_db_interact(
  1176. DB_RETURN_ASSOCS,
  1177. "SELECT *
  1178. FROM {$PHORUM['message_table']}
  1179. WHERE $forum_id_check $checkvar
  1180. $limit",
  1181. NULL,
  1182. $flags
  1183. );
  1184. foreach ($messages as $message)
  1185. {
  1186. $message['meta'] = empty($message['meta'])
  1187. ? array()
  1188. : unserialize($message['meta']);
  1189. if (! $multiple) {
  1190. $return = $message;
  1191. break;
  1192. }
  1193. $return[$message['message_id']] = $message;
  1194. }
  1195. return $return;
  1196. }
  1197. // }}}
  1198. // {{{ Function: phorum_db_get_messages()
  1199. /**
  1200. * Retrieve messages from a specific thread.
  1201. *
  1202. * @param integer $thread
  1203. * The id of the thread.
  1204. *
  1205. * @param integer $page
  1206. * A page offset (based on the configured read_length) starting with 1.
  1207. * All messages are returned in case $page is 0.
  1208. *
  1209. * @param boolean $ignore_mod_perms
  1210. * If this parameter is set to a true value, then the function will
  1211. * return hidden messages, even if the active Phorum user is not
  1212. * a moderator.
  1213. *
  1214. * @param boolean $write_server
  1215. * This value can be set to true to specify that the message should be retrieved
  1216. * from the master (aka write-server) in case replication is used
  1217. *
  1218. * @return array
  1219. * An array of messages, indexed by message_id. One special key "users"
  1220. * is set too. This one contains an array of all involved user_ids.
  1221. */
  1222. function phorum_db_get_messages($thread, $page=0, $ignore_mod_perms=FALSE, $write_server = FALSE)
  1223. {
  1224. $PHORUM = $GLOBALS['PHORUM'];
  1225. settype($thread, 'int');
  1226. settype($page, 'int');
  1227. settype($ignore_mod_perms, 'int');
  1228. // Check if the forum_id has to be checked.
  1229. $forum_id_check = '';
  1230. if (!empty($PHORUM['forum_id'])) {
  1231. $forum_id_check = "forum_id = {$PHORUM['forum_id']} AND";
  1232. }
  1233. // Determine if not approved messages should be displayed.
  1234. $approvedval = '';
  1235. if (!$ignore_mod_perms &&
  1236. !phorum_api_user_check_access(PHORUM_USER_ALLOW_MODERATE_MESSAGES)) {
  1237. $approvedval = 'AND status ='.PHORUM_STATUS_APPROVED;
  1238. }
  1239. $sql = "SELECT *
  1240. FROM {$PHORUM['message_table']}
  1241. WHERE $forum_id_check
  1242. thread = $thread
  1243. $approvedval
  1244. ORDER BY message_id";
  1245. if ($page > 0) {
  1246. // Handle the page offset.
  1247. $start = $PHORUM['read_length'] * ($page-1);
  1248. $sql .= " LIMIT $start,".$PHORUM['read_length'];
  1249. } else {
  1250. // Handle reverse threading. This is only done if $page is 0.
  1251. // In that case, the messages for threaded read are retrieved.
  1252. if (!empty($PHORUM['reverse_threading']))
  1253. $sql.=' DESC';
  1254. }
  1255. if($write_server) {
  1256. $flags = DB_MASTERQUERY;
  1257. } else {
  1258. $flags = 0;
  1259. }
  1260. $messages = phorum_db_interact(DB_RETURN_ASSOCS, $sql, 'message_id', $flags);
  1261. $involved_users = array();
  1262. foreach ($messages as $id => $message)
  1263. {
  1264. // Unpack the message meta data.
  1265. $messages[$id]['meta'] = empty($message['meta'])
  1266. ? array()
  1267. : unserialize($message['meta']);
  1268. // Collect all involved users.
  1269. if ($message['user_id']) {
  1270. $involved_users[$message['user_id']] = $message['user_id'];
  1271. }
  1272. }
  1273. // Always include the thread starter message in the return data.
  1274. // It might not be in the messagelist if a page offset is used
  1275. // (since the thread starter is only on the first page).
  1276. if (count($messages) && !isset($messages[$thread]))
  1277. {
  1278. $starter = phorum_db_interact(
  1279. DB_RETURN_ASSOC,
  1280. "SELECT *
  1281. FROM {$PHORUM['message_table']}
  1282. WHERE $forum_id_check
  1283. message_id = $thread
  1284. $approvedval",
  1285. NULL,
  1286. $flags
  1287. );
  1288. if ($starter)
  1289. {
  1290. // Unpack the message meta data.
  1291. $starter['meta'] = empty($starter['meta'])
  1292. ? array()
  1293. : unserialize($starter['meta']);
  1294. $messages[$thread] = $starter;
  1295. // Add to involved users.
  1296. if ($starter['user_id']) {
  1297. $involved_users[$starter['user_id']] = $starter['user_id'];
  1298. }
  1299. }
  1300. }
  1301. // Store the involved users in the message array.
  1302. $messages['users'] = $involved_users;
  1303. return $messages;
  1304. }
  1305. // }}}
  1306. // {{{ Function: phorum_db_get_message_index()
  1307. /**
  1308. * Retrieve the index of a message (the offset from the thread starter
  1309. * message) within a thread.
  1310. *
  1311. * @param integer $thread
  1312. * The thread id.
  1313. *
  1314. * @param integer $message_id
  1315. * The message id for which to determine the index.
  1316. *
  1317. * @return integer
  1318. * The index of the message, starting with 0.
  1319. */
  1320. function phorum_db_get_message_index($thread=0, $message_id=0)
  1321. {
  1322. $PHORUM = $GLOBALS['PHORUM'];
  1323. // check for valid values
  1324. if (empty($thread) || empty($message_id)) {
  1325. return 0;
  1326. }
  1327. settype($thread, 'int');
  1328. settype($message_id, 'int');
  1329. $forum_id_check = '';
  1330. if (!empty($PHORUM['forum_id'])) {
  1331. $forum_id_check = "forum_id = {$PHORUM['forum_id']} AND";
  1332. }
  1333. $approvedval = '';
  1334. if (!phorum_api_user_check_access(PHORUM_USER_ALLOW_MODERATE_MESSAGES)) {
  1335. $approvedval='AND status ='.PHORUM_STATUS_APPROVED;
  1336. }
  1337. $index = phorum_db_interact(
  1338. DB_RETURN_VALUE,
  1339. "SELECT count(*)

Large files files are truncated, but you can click here to view the full file