| [ Index ] |
|
Code source de Phorum 5.1.25 |
1 <?php 2 3 //////////////////////////////////////////////////////////////////////////////// 4 // // 5 // Copyright (C) 2006 Phorum Development Team // 6 // http://www.phorum.org // 7 // // 8 // This program is free software. You can redistribute it and/or modify // 9 // it under the terms of either the current Phorum License (viewable at // 10 // phorum.org) or the Phorum License that was distributed with this file // 11 // // 12 // This program is distributed in the hope that it will be useful, // 13 // but WITHOUT ANY WARRANTY, without even the implied warranty of // 14 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. // 15 // // 16 // You should have received a copy of the Phorum License // 17 // along with this program. // 18 //////////////////////////////////////////////////////////////////////////////// 19 20 // cvs-info: $Id: mysql.php 2456 2007-09-12 22:32:38Z brian $ 21 22 if (!defined("PHORUM")) return; 23 24 /** 25 * The other Phorum code does not care how the messages are stored. 26 * The only requirement is that they are returned from these functions 27 * in the right way. This means each database can use as many or as 28 * few tables as it likes. It can store the fields anyway it wants. 29 * The only thing to worry about is the table_prefix for the tables. 30 * all tables for a Phorum install should be prefixed with the 31 * table_prefix that will be entered in include/db/config.php. This 32 * will allow multiple Phorum installations to use the same database. 33 */ 34 35 /** 36 * These are the table names used for this database system. 37 */ 38 39 // tables needed to be "partitioned" 40 $PHORUM["message_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_messages"; 41 $PHORUM["user_newflags_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_user_newflags"; 42 $PHORUM["subscribers_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_subscribers"; 43 $PHORUM["files_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_files"; 44 $PHORUM["search_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_search"; 45 46 // tables common to all "partitions" 47 $PHORUM["settings_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_settings"; 48 $PHORUM["forums_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_forums"; 49 $PHORUM["user_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_users"; 50 $PHORUM["user_permissions_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_user_permissions"; 51 $PHORUM["groups_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_groups"; 52 $PHORUM["forum_group_xref_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_forum_group_xref"; 53 $PHORUM["user_group_xref_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_user_group_xref"; 54 $PHORUM['user_custom_fields_table'] = "{$PHORUM['DBCONFIG']['table_prefix']}_user_custom_fields"; 55 $PHORUM["banlist_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_banlists"; 56 $PHORUM["pm_messages_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_pm_messages"; 57 $PHORUM["pm_folders_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_pm_folders"; 58 $PHORUM["pm_xref_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_pm_xref"; 59 $PHORUM["pm_buddies_table"] = "{$PHORUM['DBCONFIG']['table_prefix']}_pm_buddies"; 60 /* 61 * fields which are always strings, even if they contain only numbers 62 * used in post-message and update-message, otherwise strange things happen 63 */ 64 $PHORUM['string_fields']= array('author', 'subject', 'body', 'email'); 65 66 /* A piece of SQL code that can be used for identifying moved messages. */ 67 define('PHORUM_SQL_MOVEDMESSAGES', "({$PHORUM['message_table']}.parent_id = 0 and {$PHORUM['message_table']}.thread != {$PHORUM['message_table']}.message_id)"); 68 69 /** 70 * This function executes a query to select the visible messages from 71 * the database for a given page offset. The main Phorum code handles 72 * actually sorting the threads into a threaded list if needed. 73 * 74 * By default, the message body is not included in the fetch queries. 75 * If the body is needed in the thread list, $PHORUM['TMP']['bodies_in_list'] 76 * must be set to "1" (for example using setting.tpl). 77 * 78 * NOTE: ALL dates should be returned as Unix timestamps 79 * 80 * @param offset - the index of the page to return, starting with 0 81 * @param messages - an array containing forum messages 82 */ 83 84 function phorum_db_get_thread_list($offset) 85 { 86 $PHORUM = $GLOBALS["PHORUM"]; 87 88 settype($offset, "int"); 89 90 $conn = phorum_db_mysql_connect(); 91 92 $table = $PHORUM["message_table"]; 93 94 // The messagefields that we want to fetch from the database. 95 $messagefields = 96 "$table.author, 97 $table.datestamp, 98 $table.email, 99 $table.message_id, 100 $table.meta, 101 $table.moderator_post, 102 $table.modifystamp, 103 $table.parent_id, 104 $table.msgid, 105 $table.sort, 106 $table.status, 107 $table.subject, 108 $table.thread, 109 $table.thread_count, 110 $table.user_id, 111 $table.viewcount, 112 $table.closed"; 113 if(isset($PHORUM['TMP']['bodies_in_list']) && $PHORUM['TMP']['bodies_in_list'] == 1) { 114 $messagefields .= "\n,$table.body\n,$table.ip"; 115 } 116 117 // The sort mechanism to use. 118 if($PHORUM["float_to_top"]){ 119 $sortfield = "modifystamp"; 120 $index = "list_page_float"; 121 } else{ 122 $sortfield = "thread"; 123 $index = "list_page_flat"; 124 } 125 126 // Initialize the return array. 127 $messages = array(); 128 129 // The groups of messages we want to fetch from the database. 130 $groups = array(); 131 if ($offset == 0) $groups[] = "specials"; 132 $groups[] = "threads"; 133 if ($PHORUM["threaded_list"]) $groups[] = "replies"; 134 135 // for remembering message ids for which we want to fetch the replies. 136 $replymsgids = array(); 137 138 // Process all groups. 139 foreach ($groups as $group) { 140 141 142 $sql = NULL; 143 144 switch ($group) { 145 146 // Announcements and stickies. 147 case "specials": 148 149 $sql = "select $messagefields 150 from $table 151 where 152 status=".PHORUM_STATUS_APPROVED." and 153 ((parent_id=0 and sort=".PHORUM_SORT_ANNOUNCEMENT." 154 and forum_id={$PHORUM['vroot']}) 155 or 156 (parent_id=0 and sort=".PHORUM_SORT_STICKY." 157 and forum_id={$PHORUM['forum_id']})) 158 order by 159 sort, $sortfield desc"; 160 break; 161 162 // Threads. 163 case "threads": 164 165 if ($PHORUM["threaded_list"]) { 166 $limit = $PHORUM['list_length_threaded']; 167 $extrasql = ''; 168 } else { 169 $limit = $PHORUM['list_length_flat']; 170 } 171 $start = $offset * $limit; 172 173 $sql = "select $messagefields 174 from $table use index ($index) 175 where 176 $sortfield > 0 and 177 forum_id = {$PHORUM["forum_id"]} and 178 status = ".PHORUM_STATUS_APPROVED." and 179 parent_id = 0 and 180 sort > 1 181 order by 182 $sortfield desc 183 limit $start, $limit"; 184 break; 185 186 // Reply messages. 187 case "replies": 188 189 // We're done if we did not collect any messages with replies. 190 if (! count($replymsgids)) break; 191 192 $sortorder = "sort, $sortfield desc, message_id"; 193 if(isset($PHORUM["reverse_threading"]) && $PHORUM["reverse_threading"]) 194 $sortorder.=" desc"; 195 196 $sql = "select $messagefields 197 from $table 198 where 199 status = ".PHORUM_STATUS_APPROVED." and 200 thread in (" . implode(",",$replymsgids) .") 201 order by $sortorder"; 202 break; 203 204 } // End of switch ($group) 205 206 // Continue with the next group if no SQL query was formulated. 207 if (is_null($sql)) continue; 208 209 // Fetch the messages for the current group. 210 $res = mysql_query($sql, $conn); 211 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 212 $rows = mysql_num_rows($res); 213 if($rows > 0){ 214 while ($rec = mysql_fetch_assoc($res)){ 215 $messages[$rec["message_id"]] = $rec; 216 $messages[$rec["message_id"]]["meta"] = array(); 217 if(!empty($rec["meta"])){ 218 $messages[$rec["message_id"]]["meta"] = unserialize($rec["meta"]); 219 } 220 221 // We need the message ids for fetching reply messages. 222 if ($group == 'threads' && $rec["thread_count"] > 1) { 223 $replymsgids[] = $rec["message_id"]; 224 } 225 } 226 } 227 } 228 229 return $messages; 230 } 231 232 /** 233 * This function executes a query to get the recent messages for 234 * all forums the user can read, a particular forum, or a particular 235 * thread, and and returns an array of the messages order by message_id. 236 * You can optionally retrieve only new threads. 237 * 238 * The original version of this function came from Jim Winstead of mysql.com 239 */ 240 function phorum_db_get_recent_messages($count, $forum_id = 0, $thread = 0, $threads_only = 0) 241 { 242 $PHORUM = $GLOBALS["PHORUM"]; 243 settype($count, "int"); 244 settype($thread, "int"); 245 settype($threads_only, "bool"); 246 phorum_db_sanitize_mixed($forum_id, "int"); 247 248 $arr = array(); 249 $allowed_forums = array(); 250 251 $conn = phorum_db_mysql_connect(); 252 253 // we need to differentiate on which key to use 254 if($thread) { 255 $use_key='thread_message'; 256 } else { 257 $use_key='forum_max_message'; 258 } 259 260 $sql = "SELECT {$PHORUM['message_table']}.* FROM {$PHORUM['message_table']} USE KEY($use_key) WHERE status=".PHORUM_STATUS_APPROVED; 261 262 // have to check what forums they can read first. 263 // even if $thread is passed, we have to make sure 264 // the user can read the forum 265 if($forum_id <= 0) { 266 $allowed_forums=phorum_user_access_list(PHORUM_USER_ALLOW_READ); 267 268 // if they are not allowed to see any forums, return the emtpy $arr; 269 if(empty($allowed_forums)) 270 return $arr; 271 } elseif(is_array($forum_id)) { 272 // for an array, check each one and return if none are allowed 273 foreach($forum_id as $id){ 274 $id = (int)$id; 275 if(phorum_user_access_allowed(PHORUM_USER_ALLOW_READ,$id)) { 276 $allowed_forums[]=$id; 277 } 278 } 279 280 // if they are not allowed to see any forums, return the emtpy $arr; 281 if(empty($allowed_forums)) 282 return $arr; 283 } else { 284 // only single forum, *much* fast this way 285 if(!phorum_user_access_allowed(PHORUM_USER_ALLOW_READ,$forum_id)) { 286 return $arr; 287 } 288 settype($forum_id, "int"); 289 } 290 291 if(count($allowed_forums)){ 292 $sql.=" and forum_id in (".implode(",", $allowed_forums).")"; 293 } else { 294 $sql.=" and forum_id=$forum_id"; 295 } 296 297 if($thread){ 298 $sql.=" and thread=$thread"; 299 } 300 301 if($threads_only) { 302 $sql.= " and parent_id = 0"; 303 $sql.= " ORDER BY thread DESC"; 304 } else { 305 $sql.= " ORDER BY message_id DESC"; 306 } 307 308 if($count){ 309 $sql.= " LIMIT $count"; 310 } 311 312 $res = mysql_query($sql, $conn); 313 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 314 315 while ($rec = mysql_fetch_assoc($res)){ 316 $arr[$rec["message_id"]] = $rec; 317 318 // convert meta field 319 if(empty($rec["meta"])){ 320 $arr[$rec["message_id"]]["meta"]=array(); 321 } else { 322 $arr[$rec["message_id"]]["meta"]=unserialize($rec["meta"]); 323 } 324 if(empty($arr['users'])) $arr['users']=array(); 325 if($rec["user_id"]){ 326 $arr['users'][]=$rec["user_id"]; 327 } 328 329 } 330 331 return $arr; 332 } 333 334 335 /** 336 * This function executes a query to select messages from the database 337 * and returns an array. The main Phorum code handles actually sorting 338 * the threads into a threaded list if needed. 339 * 340 * NOTE: ALL dates should be returned as Unix timestamps 341 * @param forum - the forum id to work with. Omit or NULL for all forums. 342 * You can also pass an array of forum_id's. 343 * @param waiting_only - only take into account messages which have to 344 * be approved directly after posting. Do not include 345 * messages which are hidden by a moderator. 346 */ 347 348 function phorum_db_get_unapproved_list($forum = NULL, $waiting_only=false,$moddays=0,$countonly=false) 349 { 350 $PHORUM = $GLOBALS["PHORUM"]; 351 352 settype($waiting_only, "bool"); 353 settype($moddays, "int"); 354 settype($countonly, "bool"); 355 phorum_db_sanitize_mixed($forum, "int"); 356 357 $conn = phorum_db_mysql_connect(); 358 359 $table = $PHORUM["message_table"]; 360 361 $arr = array(); 362 $sum = 0; 363 364 // do we want only a count here? 365 if($countonly) { 366 $selecting = "count(*) as msgcnt"; 367 368 // or the full messages? 369 } else { 370 $selecting = "$table.*"; 371 372 } 373 374 $sql = "select 375 $selecting 376 from 377 $table "; 378 379 if (is_array($forum)){ 380 $sql .= "where forum_id in (" . implode(",", $forum) . ") and "; 381 } elseif (! is_null($forum)){ 382 settype($forum, "int"); 383 $sql .= "where forum_id = $forum and "; 384 } else { 385 $sql .= "where "; 386 } 387 388 if($moddays > 0) { 389 $checktime=time()-(86400*$moddays); 390 $sql .=" datestamp > $checktime AND"; 391 } 392 393 if($waiting_only){ 394 $sql.=" status=".PHORUM_STATUS_HOLD; 395 } else { 396 $sql="($sql status=".PHORUM_STATUS_HOLD.") " . 397 "union ($sql status=".PHORUM_STATUS_HIDDEN.")"; 398 } 399 400 401 if(!$countonly) { 402 $sql .=" order by thread, message_id"; 403 } 404 405 $res = mysql_query($sql, $conn); 406 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 407 408 while ($rec = mysql_fetch_assoc($res)){ 409 if($countonly) { 410 $sum += $rec['msgcnt']; 411 } else { 412 $arr[$rec["message_id"]] = $rec; 413 $arr[$rec["message_id"]]["meta"] = array(); 414 if(!empty($rec["meta"])){ 415 $arr[$rec["message_id"]]["meta"] = unserialize($rec["meta"]); 416 } 417 } 418 } 419 420 if($countonly) { 421 return $sum; 422 } else { 423 return $arr; 424 } 425 } 426 427 428 /** 429 * This function posts a message to the tables. 430 * The message is passed by reference and message_id and thread are filled 431 */ 432 433 function phorum_db_post_message(&$message,$convert=false) 434 { 435 $PHORUM = $GLOBALS["PHORUM"]; 436 $table = $PHORUM["message_table"]; 437 438 settype($convert, "bool"); 439 440 $conn = phorum_db_mysql_connect(); 441 442 $success = false; 443 444 foreach($message as $key => $value){ 445 if (is_numeric($value) && !in_array($key,$PHORUM['string_fields'])){ 446 $message[$key] = (int)$value; 447 } elseif(is_array($value)) { 448 $message[$key] = mysql_escape_string(serialize($value)); 449 } else{ 450 $message[$key] = mysql_escape_string($value); 451 } 452 } 453 454 if(!$convert) { 455 $NOW = time(); 456 } else { 457 $NOW = $message['datestamp']; 458 } 459 460 // duplicate-check 461 if(isset($PHORUM['check_duplicate']) && $PHORUM['check_duplicate'] && !$convert) { 462 // we check for dupes in that number of minutes 463 $check_minutes=60; 464 $check_timestamp =$NOW - ($check_minutes*60); 465 // check_query 466 $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"; 467 $res = mysql_query($chk_query, $conn); 468 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $chk_query"); 469 if(mysql_num_rows($res)) 470 return 0; 471 } 472 473 if(isset($message['meta'])){ 474 $metaval=",meta='{$message['meta']}'"; 475 } else { 476 $metaval=""; 477 } 478 479 $sql = "Insert into $table set 480 forum_id = {$message['forum_id']}, 481 datestamp=$NOW, 482 thread={$message['thread']}, 483 parent_id={$message['parent_id']}, 484 author='{$message['author']}', 485 subject='{$message['subject']}', 486 email='{$message['email']}', 487 ip='{$message['ip']}', 488 user_id={$message['user_id']}, 489 moderator_post={$message['moderator_post']}, 490 status={$message['status']}, 491 sort={$message['sort']}, 492 msgid='{$message['msgid']}', 493 body='{$message['body']}', 494 closed={$message['closed']} 495 $metaval"; 496 497 // if in conversion we need the message-id too 498 if($convert && isset($message['message_id'])) { 499 $sql.=",message_id=".$message['message_id']; 500 } 501 502 if(isset($message['modifystamp'])) { 503 $sql.=",modifystamp=".$message['modifystamp']; 504 } 505 506 if(isset($message['viewcount'])) { 507 $sql.=",viewcount=".$message['viewcount']; 508 } 509 510 511 $res = mysql_query($sql, $conn); 512 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 513 514 if ($res){ 515 $message["message_id"] = mysql_insert_id($conn); 516 517 if(!empty($message["message_id"])){ 518 519 $message["datestamp"]=$NOW; 520 521 if ($message["thread"] == 0){ 522 $message["thread"] = $message["message_id"]; 523 $sql = "update $table set thread={$message['message_id']} where message_id={$message['message_id']}"; 524 $res = mysql_query($sql, $conn); 525 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 526 } 527 528 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 529 530 // start ft-search stuff 531 if(isset($PHORUM["DBCONFIG"]["mysql_use_ft"]) && $PHORUM["DBCONFIG"]["mysql_use_ft"]){ 532 $search_text="$message[author] | $message[subject] | $message[body]"; 533 534 $sql="insert delayed into {$PHORUM['search_table']} set message_id={$message['message_id']}, forum_id={$message['forum_id']}, search_text='$search_text'"; 535 $res = mysql_query($sql, $conn); 536 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 537 } 538 // end ft-search stuff 539 540 $success = true; 541 // some data for later use, i.e. email-notification 542 $GLOBALS['PHORUM']['post_returns']['message_id']=$message["message_id"]; 543 $GLOBALS['PHORUM']['post_returns']['thread_id']=$message["thread"]; 544 } 545 } 546 547 return $success; 548 } 549 550 /** 551 * This function deletes messages from the messages table. 552 * 553 * @param message $ _id the id of the message which should be deleted 554 * mode the mode of deletion, PHORUM_DELETE_MESSAGE for reconnecting the children, PHORUM_DELETE_TREE for deleting the children 555 */ 556 557 function phorum_db_delete_message($message_id, $mode = PHORUM_DELETE_MESSAGE) 558 { 559 $PHORUM = $GLOBALS["PHORUM"]; 560 561 $conn = phorum_db_mysql_connect(); 562 563 settype($message_id, "int"); 564 settype($mode, "int"); 565 566 $threadset = 0; 567 // get the parents of the message to delete. 568 $sql = "select forum_id, message_id, thread, parent_id from {$PHORUM['message_table']} where message_id = $message_id "; 569 $res = mysql_query($sql, $conn); 570 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 571 572 $rec = mysql_fetch_assoc($res); 573 if (empty($rec)) { 574 phorum_db_mysql_error("No message found for message_id $message_id"); 575 } 576 577 if($mode == PHORUM_DELETE_TREE){ 578 $mids = phorum_db_get_messagetree($message_id, $rec['forum_id']); 579 }else{ 580 $mids = $message_id; 581 } 582 583 // unapprove the messages first so replies will not get posted 584 $sql = "update {$PHORUM['message_table']} set status=".PHORUM_STATUS_HOLD." where message_id in ($mids)"; 585 $res = mysql_query($sql, $conn); 586 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 587 588 $thread = $rec['thread']; 589 if($thread == $message_id && $mode == PHORUM_DELETE_TREE){ 590 $threadset = 1; 591 }else{ 592 $threadset = 0; 593 } 594 595 if($mode == PHORUM_DELETE_MESSAGE){ 596 $count = 1; 597 // change the children to point to their parent's parent 598 // forum_id is in here for speed by using a key only 599 $sql = "update {$PHORUM['message_table']} set parent_id=$rec[parent_id] where forum_id=$rec[forum_id] and parent_id=$rec[message_id]"; 600 mysql_query($sql, $conn); 601 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 602 }else{ 603 $count = count(explode(",", $mids)); 604 } 605 606 // delete the messages 607 $sql = "delete from {$PHORUM['message_table']} where message_id in ($mids)"; 608 mysql_query($sql, $conn); 609 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 610 611 // start ft-search stuff 612 $sql="delete from {$PHORUM['search_table']} where message_id in ($mids)"; 613 $res = mysql_query($sql, $conn); 614 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 615 // end ft-search stuff 616 617 // it kind of sucks to have this here, but it is the best way 618 // to ensure that it gets done if stuff is deleted. 619 // leave this include here, it needs to be conditional 620 include_once ("./include/thread_info.php"); 621 phorum_update_thread_info($thread); 622 623 // we need to delete the subscriptions for that thread too 624 $sql = "DELETE FROM {$PHORUM['subscribers_table']} WHERE forum_id > 0 AND thread=$thread"; 625 $res = mysql_query($sql, $conn); 626 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 627 628 // this function will be slow with a lot of messages. 629 phorum_db_update_forum_stats(true); 630 631 return explode(",", $mids); 632 } 633 634 /** 635 * gets all attached messages to a message 636 * 637 * @param id $ id of the message 638 */ 639 function phorum_db_get_messagetree($parent_id, $forum_id){ 640 $PHORUM = $GLOBALS["PHORUM"]; 641 642 settype($parent_id, "int"); 643 settype($forum_id, "int"); 644 645 $conn = phorum_db_mysql_connect(); 646 647 $sql = "Select message_id from {$PHORUM['message_table']} where forum_id=$forum_id and parent_id=$parent_id"; 648 649 $res = mysql_query($sql, $conn); 650 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 651 652 $tree = "$parent_id"; 653 654 while($rec = mysql_fetch_row($res)){ 655 $tree .= "," . phorum_db_get_messagetree($rec[0],$forum_id); 656 } 657 658 return $tree; 659 } 660 661 /** 662 * This function updates the message given in the $message array for 663 * the row with the given message id. It returns non 0 on success. 664 */ 665 666 function phorum_db_update_message($message_id, $message) 667 { 668 $PHORUM = $GLOBALS["PHORUM"]; 669 670 settype($message_id, "int"); 671 672 if (count($message) > 0){ 673 $conn = phorum_db_mysql_connect(); 674 675 foreach($message as $field => $value){ 676 if(phorum_db_validate_field($field)){ 677 if (is_numeric($value) && !in_array($field,$PHORUM['string_fields'])){ 678 $fields[] = "$field=$value"; 679 }elseif (is_array($value)){ 680 $value = mysql_escape_string(serialize($value)); 681 $message[$field] = $value; 682 $fields[] = "$field='$value'"; 683 }else{ 684 $value = mysql_escape_string($value); 685 $message[$field] = $value; 686 $fields[] = "$field='$value'"; 687 } 688 } 689 } 690 691 $sql = "update {$PHORUM['message_table']} set " . implode(", ", $fields) . " where message_id=$message_id"; 692 $res = mysql_query($sql, $conn); 693 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 694 695 if($res){ 696 // start ft-search stuff 697 if(isset($message["author"]) && isset($message["subject"]) && isset($message["body"])){ 698 $search_text="$message[author] | $message[subject] | $message[body]"; 699 $sql="replace delayed into {$PHORUM['search_table']} set message_id={$message_id}, forum_id={$message['forum_id']}, search_text='$search_text'"; 700 $res = mysql_query($sql, $conn); 701 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 702 } 703 // end ft-search stuff 704 } 705 706 return ($res > 0) ? true : false; 707 708 }else{ 709 trigger_error("\$message cannot be empty in phorum_update_message()", E_USER_ERROR); 710 } 711 } 712 713 714 /** 715 * This function executes a query to get the row with the given value 716 * in the given field and returns the message in an array. 717 */ 718 719 function phorum_db_get_message($value, $field="message_id", $ignore_forum_id=false) 720 { 721 $PHORUM = $GLOBALS["PHORUM"]; 722 723 if(!phorum_db_validate_field($field)){ 724 return false; 725 } 726 727 $multiple=false; 728 729 phorum_db_sanitize_mixed($value, "string"); 730 settype($ignore_forum_id, "bool"); 731 732 $conn = phorum_db_mysql_connect(); 733 734 735 $forum_id_check = ""; 736 if (!$ignore_forum_id && !empty($PHORUM["forum_id"])){ 737 $forum_id_check = "(forum_id = {$PHORUM['forum_id']} OR forum_id={$PHORUM['vroot']}) and"; 738 } 739 740 if(is_array($value)) { 741 $checkvar="$field IN('".implode("','",$value)."')"; 742 $multiple=true; 743 } else { 744 $checkvar="$field='$value'"; 745 } 746 747 748 $sql = "select {$PHORUM['message_table']}.* from {$PHORUM['message_table']} where $forum_id_check $checkvar"; 749 $res = mysql_query($sql, $conn); 750 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 751 752 $ret = $multiple ? array() : NULL; 753 754 if(mysql_num_rows($res)){ 755 if($multiple) { 756 while($rec=mysql_fetch_assoc($res)) { 757 // convert meta field 758 if(empty($rec["meta"])){ 759 $rec["meta"]=array(); 760 } else { 761 $rec["meta"]=unserialize($rec["meta"]); 762 } 763 $ret[$rec['message_id']]=$rec; 764 } 765 } else { 766 $rec = mysql_fetch_assoc($res); 767 768 // convert meta field 769 if(empty($rec["meta"])){ 770 $rec["meta"]=array(); 771 } else { 772 $rec["meta"]=unserialize($rec["meta"]); 773 } 774 $ret=$rec; 775 } 776 } 777 778 return $ret; 779 } 780 781 /** 782 * This function executes a query to get the rows with the given thread 783 * id and returns an array of the message. 784 */ 785 function phorum_db_get_messages($thread,$page=0,$ignore_mod_perms = 0) 786 { 787 $PHORUM = $GLOBALS["PHORUM"]; 788 789 settype($thread, "int"); 790 settype($page, "int"); 791 settype($ignore_mod_perms, "bool"); 792 793 $conn = phorum_db_mysql_connect(); 794 795 $forum_id_check = ""; 796 if (!empty($PHORUM["forum_id"])){ 797 $forum_id_check = "(forum_id = {$PHORUM['forum_id']} OR forum_id={$PHORUM['vroot']}) and"; 798 } 799 800 // are we really allowed to show this thread/message? 801 $approvedval = ""; 802 if(!$ignore_mod_perms && !phorum_user_access_allowed(PHORUM_USER_ALLOW_MODERATE_MESSAGES)) { 803 $approvedval="AND {$PHORUM['message_table']}.status =".PHORUM_STATUS_APPROVED; 804 } 805 806 if($page > 0) { 807 $start=$PHORUM["read_length"]*($page-1); 808 $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"]; 809 } else { 810 $sql = "select {$PHORUM['message_table']}.* from {$PHORUM['message_table']} where $forum_id_check thread=$thread $approvedval order by message_id"; 811 if(isset($PHORUM["reverse_threading"]) && $PHORUM["reverse_threading"]) $sql.=" desc"; 812 } 813 814 $res = mysql_query($sql, $conn); 815 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 816 817 $arr = array(); 818 819 while ($rec = mysql_fetch_assoc($res)){ 820 $arr[$rec["message_id"]] = $rec; 821 822 // convert meta field 823 if(empty($rec["meta"])){ 824 $arr[$rec["message_id"]]["meta"]=array(); 825 } else { 826 $arr[$rec["message_id"]]["meta"]=unserialize($rec["meta"]); 827 } 828 if(empty($arr['users'])) $arr['users']=array(); 829 if($rec["user_id"]){ 830 $arr['users'][]=$rec["user_id"]; 831 } 832 833 } 834 835 if(count($arr) && $page != 0) { 836 // selecting the thread-starter 837 $sql = "select {$PHORUM['message_table']}.* from {$PHORUM['message_table']} where $forum_id_check message_id=$thread $approvedval"; 838 $res = mysql_query($sql, $conn); 839 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 840 if(mysql_num_rows($res) > 0) { 841 $rec = mysql_fetch_assoc($res); 842 $arr[$rec["message_id"]] = $rec; 843 $arr[$rec["message_id"]]["meta"]=unserialize($rec["meta"]); 844 } 845 } 846 return $arr; 847 } 848 849 /** 850 * this function returns the index of a message in a thread 851 */ 852 function phorum_db_get_message_index($thread=0,$message_id=0) { 853 $PHORUM = $GLOBALS["PHORUM"]; 854 855 // check for valid values 856 if(empty($thread) || empty($message_id)) { 857 return 0; 858 } 859 860 settype($thread, "int"); 861 settype($message_id, "int"); 862 863 $approvedval=""; 864 $forum_id_check=""; 865 866 $conn = phorum_db_mysql_connect(); 867 868 if (!empty($PHORUM["forum_id"])){ 869 $forum_id_check = "(forum_id = {$PHORUM['forum_id']} OR forum_id={$PHORUM['vroot']}) AND"; 870 } 871 872 if(!phorum_user_access_allowed(PHORUM_USER_ALLOW_MODERATE_MESSAGES)) { 873 $approvedval="AND {$PHORUM['message_table']}.status =".PHORUM_STATUS_APPROVED; 874 } 875 876 $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"; 877 878 $res = mysql_query($sql, $conn); 879 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 880 881 $rec = mysql_fetch_assoc($res); 882 883 return $rec['msg_index']; 884 } 885 886 /** 887 * This function searches the database for the supplied search 888 * criteria and returns an array with two elements. One is the count 889 * of total messages that matched, the second is an array of the 890 * messages from the results based on the $start (0 base) given and 891 * the $length given. 892 */ 893 894 function phorum_db_search($search, $offset, $length, $match_type, $match_date, $match_forum) 895 { 896 $PHORUM = $GLOBALS["PHORUM"]; 897 898 settype($offset, "int"); 899 settype($length, "int"); 900 settype($match_date, "int"); 901 902 $start = $offset * $PHORUM["list_length"]; 903 904 $arr = array("count" => 0, "rows" => array()); 905 906 $conn = phorum_db_mysql_connect(); 907 908 // have to check what forums they can read first. 909 $allowed_forums=phorum_user_access_list(PHORUM_USER_ALLOW_READ); 910 // if they are not allowed to search any forums, return the emtpy $arr; 911 if(empty($allowed_forums) || ($PHORUM['forum_id']>0 && !in_array($PHORUM['forum_id'], $allowed_forums)) ) return $arr; 912 913 // Add forum 0 (for announcements) to the allowed forums. 914 $allowed_forums[] = 0; 915 916 if($PHORUM['forum_id']!=0 && $match_forum!="ALL"){ 917 $forum_where=" and forum_id={$PHORUM['forum_id']}"; 918 } else { 919 $forum_where=" and forum_id in (".implode(",", $allowed_forums).")"; 920 } 921 922 // prepare terms 923 if($match_type=="PHRASE"){ 924 925 if(isset($PHORUM["DBCONFIG"]["mysql_use_ft"]) && $PHORUM["DBCONFIG"]["mysql_use_ft"]){ 926 $terms = array('"'.$search.'"'); 927 } else { 928 $terms = array($search); 929 } 930 931 } elseif($match_type=="AUTHOR"){ 932 933 $terms = mysql_escape_string($search); 934 935 } else { 936 937 $quote_terms=array(); 938 if ( strstr( $search, '"' ) ){ 939 //first pull out all the double quoted strings (e.g. '"iMac DV" or -"iMac DV"') 940 preg_match_all( '/-*"(.*?)"/', $search, $match ); 941 $search = preg_replace( '/-*".*?"/', '', $search ); 942 $quote_terms = $match[0]; 943 } 944 945 //finally pull out the rest words in the string 946 $terms = preg_split( "/\s+/", $search, 0, PREG_SPLIT_NO_EMPTY ); 947 948 //merge them all together and return 949 $terms = array_merge($terms, $quote_terms); 950 951 } 952 953 954 if(isset($PHORUM["DBCONFIG"]["mysql_use_ft"]) && $PHORUM["DBCONFIG"]["mysql_use_ft"]){ 955 956 if($match_type=="AUTHOR"){ 957 958 $id_table=$PHORUM['search_table']."_auth_".md5(microtime()); 959 960 $sql = "create temporary table $id_table (key(message_id)) ENGINE=HEAP select message_id from {$PHORUM['message_table']} where author='$terms' $forum_where"; 961 if($match_date>0){ 962 $ts=time()-86400*$match_date; 963 $sql.=" and datestamp>=$ts"; 964 } 965 966 $res = mysql_query($sql, $conn); 967 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 968 969 } else { 970 971 972 if(count($terms)){ 973 974 $use_key=""; 975 $extra_where=""; 976 977 /* using this code on larger forums has shown to make the search faster. 978 However, on smaller forums, it does not appear to help and in fact 979 appears to slow down searches. 980 981 if($match_date){ 982 $min_time=time()-86400*$match_date; 983 $sql="select min(message_id) as min_id from {$PHORUM['message_table']} where datestamp>=$min_time"; 984 $res=mysql_query($sql, $conn); 985 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 986 $min_id=mysql_result($res, 0, "min_id"); 987 $use_key=" use key (primary)"; 988 $extra_where="and message_id>=$min_id"; 989 } 990 */ 991 992 $id_table=$PHORUM['search_table']."_ft_".md5(microtime()); 993 994 $against = ""; 995 996 if($match_type=="ALL" && count($terms)>1){ 997 foreach($terms as $term){ 998 if($term[0] == "+" || $term[0] == "-"){ 999 $against .= mysql_escape_string($term)." "; 1000 } else { 1001 $against .= "+".mysql_escape_string($term)." "; 1002 } 1003 } 1004 $against = trim($against); 1005 } else { 1006 $against=mysql_escape_string(implode(" ", $terms)); 1007 } 1008 1009 $clause="MATCH (search_text) AGAINST ('$against' IN BOOLEAN MODE)"; 1010 1011 $sql = "create temporary table $id_table (key(message_id)) ENGINE=HEAP select message_id from {$PHORUM['search_table']} $use_key where $clause $extra_where"; 1012 $res = mysql_unbuffered_query($sql, $conn); 1013 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1014 1015 } 1016 } 1017 1018 1019 if(isset($id_table)){ 1020 1021 // create a temporary table of the messages we want 1022 $table=$PHORUM['search_table']."_".md5(microtime()); 1023 $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"; 1024 1025 if($match_date>0){ 1026 $ts=time()-86400*$match_date; 1027 $sql.=" and datestamp>=$ts"; 1028 } 1029 1030 $res=mysql_query($sql, $conn); 1031 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1032 1033 $sql="select count(*) as count from $table"; 1034 $res = mysql_query($sql, $conn); 1035 1036 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1037 $total_count=mysql_result($res, 0, 0); 1038 1039 $sql="select message_id from $table order by datestamp desc limit $start, $length"; 1040 $res = mysql_unbuffered_query($sql, $conn); 1041 1042 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1043 1044 $idstring=""; 1045 while ($rec = mysql_fetch_row($res)){ 1046 $idstring.="$rec[0],"; 1047 } 1048 $idstring=substr($idstring, 0, -1); 1049 1050 } 1051 1052 } else { // not using full text matching 1053 1054 if($match_type=="AUTHOR"){ 1055 1056 $sql_core = "from {$PHORUM['message_table']} where author='$terms' $forum_where $sql_date"; 1057 1058 if($match_date>0){ 1059 $ts=time()-86400*$match_date; 1060 $sql_core.=" and datestamp>=$ts"; 1061 } 1062 1063 $sql = "select count(*) $sql_core"; 1064 $res = mysql_query($sql, $conn); 1065 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1066 $total_count=mysql_result($res, 0, 0); 1067 1068 1069 $sql = "select message_id $sql_core order by datestamp desc limit $start, $length"; 1070 1071 $res = mysql_unbuffered_query($sql, $conn); 1072 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1073 1074 $idstring=""; 1075 while ($rec = mysql_fetch_row($res)){ 1076 $idstring.="$rec[0],"; 1077 } 1078 $idstring=substr($idstring, 0, -1); 1079 1080 } else { 1081 1082 if(count($terms)){ 1083 1084 if($match_type=="ALL"){ 1085 $conj="and"; 1086 } else { 1087 $conj="or"; 1088 } 1089 1090 // quote strings correctly 1091 foreach ($terms as $id => $term) { 1092 $terms[$id] = mysql_escape_string($term); 1093 } 1094 1095 $sql_date = ""; 1096 if($match_date>0){ 1097 $ts=time()-86400*$match_date; 1098 $sql_date =" and datestamp>=$ts"; 1099 } 1100 1101 $clause = "( concat(author, ' | ', subject, ' | ', body) like '%".implode("%' $conj concat(author, ' | ', subject, ' | ', body) like '%", $terms)."%' )"; 1102 1103 $sql = "select count(*) from {$PHORUM['message_table']} where status=".PHORUM_STATUS_APPROVED." and $clause $forum_where $sql_date"; 1104 $res = mysql_query($sql, $conn); 1105 1106 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1107 $total_count=mysql_result($res, 0, 0); 1108 1109 $sql = "select message_id from {$PHORUM['message_table']} where status=".PHORUM_STATUS_APPROVED." and $clause $forum_where $sql_date order by datestamp desc limit $start, $length"; 1110 $res = mysql_unbuffered_query($sql, $conn); 1111 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1112 1113 $idstring=""; 1114 while ($rec = mysql_fetch_row($res)){ 1115 $idstring.="$rec[0],"; 1116 } 1117 $idstring=substr($idstring, 0, -1); 1118 1119 } 1120 1121 } 1122 1123 } 1124 1125 if($idstring){ 1126 $sql="select * from {$PHORUM['message_table']} where message_id in ($idstring) order by datestamp desc"; 1127 $res = mysql_unbuffered_query($sql, $conn); 1128 1129 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1130 1131 $rows = array(); 1132 1133 while ($rec = mysql_fetch_assoc($res)){ 1134 $rows[$rec["message_id"]] = $rec; 1135 } 1136 1137 $arr = array("count" => $total_count, "rows" => $rows); 1138 } 1139 1140 return $arr; 1141 } 1142 1143 /** 1144 * Returns the closest older or newer neighbour thread. 1145 * @param int $key 1146 * @return mixed 1147 */ 1148 function phorum_db_get_older_thread($key) { 1149 return phorum_db_get_neighbour_thread($key, "older"); 1150 } 1151 function phorum_db_get_newer_thread($key) { 1152 return phorum_db_get_neighbour_thread($key, "newer"); 1153 } 1154 function phorum_db_get_neighbour_thread($key, $direction) 1155 { 1156 $PHORUM = $GLOBALS["PHORUM"]; 1157 1158 settype($key, "int"); 1159 1160 $conn = phorum_db_mysql_connect(); 1161 1162 $keyfield = ($PHORUM["float_to_top"]) ? "modifystamp" : "thread"; 1163 1164 switch ($direction) { 1165 case "newer": $compare = ">"; $orderdir = "ASC"; break; 1166 case "older": $compare = "<"; $orderdir = "DESC"; break; 1167 default: 1168 raise_error( 1169 "phorum_db_get_neighbour_thread(): " . 1170 "Illegal direction \"".htmlspecialchars($direction)."\"", 1171 E_USER_ERROR 1172 ); 1173 } 1174 1175 // If the current user is not a moderator for the forum, then 1176 // the neighbour message should be approved. 1177 $approvedval = ""; 1178 if (!phorum_user_access_allowed(PHORUM_USER_ALLOW_MODERATE_MESSAGES)) { 1179 $approvedval = "AND status = ".PHORUM_STATUS_APPROVED; 1180 } 1181 1182 $sql = "select thread from {$PHORUM['message_table']} where forum_id={$PHORUM['forum_id']} and parent_id = 0 $approvedval and $keyfield $compare $key order by $keyfield $orderdir limit 1"; 1183 1184 $res = mysql_query($sql, $conn); 1185 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1186 1187 return (mysql_num_rows($res)) ? mysql_result($res, 0, "thread") : 0; 1188 } 1189 1190 /** 1191 * This function executes a query to get bad items of type $type and 1192 * returns an array of the results. 1193 */ 1194 1195 function phorum_db_load_settings(){ 1196 global $PHORUM; 1197 1198 1199 $conn = phorum_db_mysql_connect(); 1200 1201 $sql = "select * from {$PHORUM['settings_table']}"; 1202 1203 $res = mysql_query($sql, $conn); 1204 if(!$res && !defined("PHORUM_ADMIN")){ 1205 if (mysql_errno($conn)==1146){ 1206 // settings table does not exist 1207 return; 1208 } elseif(($err = mysql_error($conn))){ 1209 phorum_db_mysql_error("$err: $sql"); 1210 } 1211 } 1212 1213 if (empty($err) && $res){ 1214 while ($rec = mysql_fetch_assoc($res)){ 1215 1216 // only load the default forum options in the admin 1217 if($rec["name"]=="default_forum_options" && !defined("PHORUM_ADMIN")) continue; 1218 1219 if ($rec["type"] == "V"){ 1220 if ($rec["data"] == 'true'){ 1221 $val = true; 1222 }elseif ($rec["data"] == 'false'){ 1223 $val = false; 1224 }elseif (is_numeric($rec["data"])){ 1225 $val = $rec["data"]; 1226 }else{ 1227 $val = "$rec[data]"; 1228 } 1229 }else{ 1230 $val = unserialize($rec["data"]); 1231 } 1232 1233 $PHORUM[$rec['name']]=$val; 1234 $PHORUM['SETTINGS'][$rec['name']]=$val; 1235 } 1236 } 1237 } 1238 1239 /** 1240 * This function executes a query to get bad items of type $type and 1241 * returns an array of the results. 1242 */ 1243 1244 function phorum_db_update_settings($settings){ 1245 global $PHORUM; 1246 1247 if (count($settings) > 0){ 1248 $conn = phorum_db_mysql_connect(); 1249 1250 foreach($settings as $field => $value){ 1251 if (is_numeric($value)){ 1252 $type = 'V'; 1253 }elseif (is_string($value)){ 1254 $value = mysql_escape_string($value); 1255 $type = 'V'; 1256 }else{ 1257 $value = mysql_escape_string(serialize($value)); 1258 $type = 'S'; 1259 } 1260 1261 $field = mysql_escape_string($field); 1262 1263 $sql = "replace into {$PHORUM['settings_table']} set data='$value', type='$type', name='$field'"; 1264 $res = mysql_query($sql, $conn); 1265 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1266 } 1267 1268 return ($res > 0) ? true : false; 1269 }else{ 1270 trigger_error("\$settings cannot be empty in phorum_db_update_settings()", E_USER_ERROR); 1271 } 1272 } 1273 1274 /** 1275 * This function executes a query to select all forum data from 1276 * the database for a flat/collapsed display and returns the data in 1277 * an array. 1278 */ 1279 1280 1281 function phorum_db_get_forums($forum_ids = 0, $parent_id = -1, $vroot = null, $inherit_id = null){ 1282 $PHORUM = $GLOBALS["PHORUM"]; 1283 1284 phorum_db_sanitize_mixed($forum_ids, "int"); 1285 settype($parent_id, "int"); 1286 if($vroot != null) settype($vroot, "int"); 1287 if($inherit_id != null) settype($inherit_id, "int"); 1288 1289 $conn = phorum_db_mysql_connect(); 1290 1291 if (is_array($forum_ids)) { 1292 $int_ids = array(); 1293 foreach ($forum_ids as $id) { 1294 settype($id, "int"); 1295 $int_ids[] = $id; 1296 } 1297 $forum_ids = implode(",", $int_ids); 1298 } else { 1299 settype($forum_ids, "int"); 1300 } 1301 1302 $sql = "select * from {$PHORUM['forums_table']} "; 1303 if ($forum_ids){ 1304 $sql .= " where forum_id in ($forum_ids)"; 1305 } elseif ($inherit_id !== null) { 1306 $sql .= " where inherit_id = $inherit_id"; 1307 if(!defined("PHORUM_ADMIN")) $sql.=" and active=1"; 1308 } elseif ($parent_id >= 0) { 1309 $sql .= " where parent_id = $parent_id"; 1310 if(!defined("PHORUM_ADMIN")) $sql.=" and active=1"; 1311 } elseif($vroot !== null) { 1312 $sql .= " where vroot = $vroot"; 1313 } else { 1314 $sql .= " where forum_id <> 0"; 1315 } 1316 1317 $sql .= " order by display_order ASC, name"; 1318 1319 $res = mysql_query($sql, $conn); 1320 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1321 1322 $forums = array(); 1323 1324 while ($row = mysql_fetch_assoc($res)){ 1325 $forums[$row["forum_id"]] = $row; 1326 } 1327 1328 return $forums; 1329 } 1330 1331 /** 1332 * This function updates the forums stats. If refresh is true, it pulls the 1333 * numbers from the table. 1334 */ 1335 1336 function phorum_db_update_forum_stats($refresh=false, $msg_count_change=0, $timestamp=0, $thread_count_change=0, $sticky_count_change=0) 1337 { 1338 $PHORUM = $GLOBALS["PHORUM"]; 1339 1340 settype($refresh, "bool"); 1341 settype($msg_count_change, "int"); 1342 settype($timestamp, "int"); 1343 settype($thread_count_change, "int"); 1344 settype($sticky_count_change, "int"); 1345 1346 $conn = phorum_db_mysql_connect(); 1347 1348 // always refresh on small forums 1349 if (isset($PHORUM["message_count"]) && $PHORUM["message_count"]<1000) { 1350 $refresh=true; 1351 } 1352 1353 if($refresh || empty($msg_count_change)){ 1354 $sql = "select count(*) as message_count from {$PHORUM['message_table']} where forum_id={$PHORUM['forum_id']} and status=".PHORUM_STATUS_APPROVED; 1355 1356 $res = mysql_query($sql, $conn); 1357 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1358 1359 $message_count = (int)mysql_result($res, 0, "message_count"); 1360 } else { 1361 $message_count="message_count+$msg_count_change"; 1362 } 1363 1364 if($refresh || empty($timestamp)){ 1365 1366 $sql = "select max(modifystamp) as last_post_time from {$PHORUM['message_table']} where status=".PHORUM_STATUS_APPROVED." and forum_id={$PHORUM['forum_id']}"; 1367 1368 $res = mysql_query($sql, $conn); 1369 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1370 1371 $last_post_time = (int)mysql_result($res, 0, "last_post_time"); 1372 } else { 1373 1374 $last_post_time = $timestamp; 1375 } 1376 1377 if($refresh || empty($thread_count_change)){ 1378 1379 $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; 1380 $res = mysql_query($sql, $conn); 1381 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1382 $thread_count = (int)mysql_result($res, 0, "thread_count"); 1383 1384 } else { 1385 1386 $thread_count="thread_count+$thread_count_change"; 1387 } 1388 1389 if($refresh || empty($sticky_count_change)){ 1390 1391 $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; 1392 $res = mysql_query($sql, $conn); 1393 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1394 $sticky_count = (int)mysql_result($res, 0, "sticky_count"); 1395 1396 } else { 1397 1398 $sticky_count="sticky_count+$sticky_count_change"; 1399 } 1400 1401 $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']}"; 1402 mysql_query($sql, $conn); 1403 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1404 1405 } 1406 1407 /** 1408 * actually moves a thread to the given forum 1409 */ 1410 function phorum_db_move_thread($thread_id, $toforum) 1411 { 1412 $PHORUM = $GLOBALS["PHORUM"]; 1413 1414 settype($thread_id, "int"); 1415 settype($toforum, "int"); 1416 1417 if($toforum > 0 && $thread_id > 0){ 1418 $conn = phorum_db_mysql_connect(); 1419 // retrieving the messages for the newflags and search updates below 1420 $thread_messages=phorum_db_get_messages($thread_id); 1421 1422 // just changing the forum-id, simple isn't it? 1423 $sql = "UPDATE {$PHORUM['message_table']} SET forum_id=$toforum where thread=$thread_id"; 1424 1425 $res = mysql_query($sql, $conn); 1426 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1427 1428 // we need to update the number of posts in the current forum 1429 phorum_db_update_forum_stats(true); 1430 1431 // and of the new forum 1432 $old_id=$GLOBALS["PHORUM"]["forum_id"]; 1433 $GLOBALS["PHORUM"]["forum_id"]=$toforum; 1434 phorum_db_update_forum_stats(true); 1435 $GLOBALS["PHORUM"]["forum_id"]=$old_id; 1436 1437 // move the new-flags and the search records for this thread 1438 // to the new forum too 1439 unset($thread_messages['users']); 1440 1441 $new_newflags=phorum_db_newflag_get_flags($toforum); 1442 $message_ids = array(); 1443 $delete_ids = array(); 1444 $search_ids = array(); 1445 foreach($thread_messages as $mid => $data) { 1446 // gather information for updating the newflags 1447 if($mid > $new_newflags['min_id']) { // only using it if its higher than min_id 1448 $message_ids[]=$mid; 1449 } else { // newflags to delete 1450 $delete_ids[]=$mid; 1451 } 1452 1453 // gather the information for updating the search table 1454 $search_ids[] = $mid; 1455 } 1456 1457 if(count($message_ids)) { // we only go in if there are messages ... otherwise an error occured 1458 1459 phorum_db_newflag_update_forum($message_ids); 1460 1461 $ids_str=implode(",",$message_ids); 1462 1463 // then doing the update to subscriptions 1464 $sql="UPDATE {$PHORUM['subscribers_table']} SET forum_id = $toforum where thread IN($ids_str)"; 1465 $res = mysql_query($sql, $conn); 1466 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1467 1468 } 1469 1470 if(count($delete_ids)) { 1471 $ids_str=implode(",",$delete_ids); 1472 // then doing the delete 1473 $sql="DELETE FROM {$PHORUM['user_newflags_table']} where message_id IN($ids_str)"; 1474 mysql_query($sql, $conn); 1475 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1476 } 1477 1478 if (count($search_ids)) { 1479 $ids_str = implode(",",$search_ids); 1480 // then doing the search table update 1481 $sql = "UPDATE {$PHORUM['search_table']} set forum_id = $toforum where message_id in ($ids_str)"; 1482 mysql_query($sql, $conn); 1483 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1484 } 1485 1486 } 1487 } 1488 1489 /** 1490 * closes the given thread 1491 */ 1492 function phorum_db_close_thread($thread_id){ 1493 $PHORUM = $GLOBALS["PHORUM"]; 1494 1495 settype($thread_id, "int"); 1496 1497 if($thread_id > 0){ 1498 $conn = phorum_db_mysql_connect(); 1499 1500 $sql = "UPDATE {$PHORUM['message_table']} SET closed=1 where thread=$thread_id"; 1501 1502 $res = mysql_query($sql, $conn); 1503 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1504 } 1505 } 1506 1507 /** 1508 * (re)opens the given thread 1509 */ 1510 function phorum_db_reopen_thread($thread_id){ 1511 $PHORUM = $GLOBALS["PHORUM"]; 1512 1513 settype($thread_id, "int"); 1514 1515 if($thread_id > 0){ 1516 $conn = phorum_db_mysql_connect(); 1517 1518 $sql = "UPDATE {$PHORUM['message_table']} SET closed=0 where thread=$thread_id"; 1519 1520 $res = mysql_query($sql, $conn); 1521 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1522 } 1523 } 1524 1525 /** 1526 * This function executes a query to insert a forum into the forums 1527 * table and returns the forums id on success or 0 on failure. 1528 */ 1529 1530 function phorum_db_add_forum($forum) 1531 { 1532 $PHORUM = $GLOBALS["PHORUM"]; 1533 1534 $conn = phorum_db_mysql_connect(); 1535 1536 foreach($forum as $key => $value){ 1537 if(phorum_db_validate_field($key)){ 1538 if (is_numeric($value)){ 1539 $value = (int)$value; 1540 $fields[] = "$key=$value"; 1541 } elseif($value=="NULL") { 1542 $fields[] = "$key=$value"; 1543 }else{ 1544 $value = mysql_escape_string($value); 1545 $fields[] = "$key='$value'"; 1546 } 1547 } 1548 } 1549 1550 $sql = "insert into {$PHORUM['forums_table']} set " . implode(", ", $fields); 1551 1552 $res = mysql_query($sql, $conn); 1553 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1554 1555 $forum_id = 0; 1556 1557 if ($res){ 1558 $forum_id = mysql_insert_id($conn); 1559 } 1560 1561 return $forum_id; 1562 } 1563 1564 /** 1565 * This function executes a query to remove a forum from the forums 1566 * table and its messages. 1567 */ 1568 1569 function phorum_db_drop_forum($forum_id) 1570 { 1571 $PHORUM = $GLOBALS["PHORUM"]; 1572 1573 settype($forum_id, "int"); 1574 1575 $conn = phorum_db_mysql_connect(); 1576 1577 $tables = array ( 1578 $PHORUM['message_table'], 1579 $PHORUM['user_permissions_table'], 1580 $PHORUM['user_newflags_table'], 1581 $PHORUM['subscribers_table'], 1582 $PHORUM['forum_group_xref_table'], 1583 $PHORUM['forums_table'], 1584 $PHORUM['banlist_table'], 1585 $PHORUM['search_table'] 1586 ); 1587 1588 foreach($tables as $table){ 1589 $sql = "delete from $table where forum_id=$forum_id"; 1590 $res = mysql_query($sql, $conn); 1591 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1592 } 1593 1594 $sql = "select file_id from {$PHORUM['files_table']} left join {$PHORUM['message_table']} using (message_id) where {$PHORUM['files_table']}.message_id > 0 AND link='" . PHORUM_LINK_MESSAGE . "' AND {$PHORUM['message_table']}.message_id is NULL"; 1595 $res = mysql_query($sql, $conn); 1596 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1597 while($rec=mysql_fetch_assoc($res)){ 1598 $files[]=$rec["file_id"]; 1599 } 1600 if(isset($files)){ 1601 $sql = "delete from {$PHORUM['files_table']} where file_id in (".implode(",", $files).")"; 1602 $res = mysql_query($sql, $conn); 1603 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1604 } 1605 1606 1607 } 1608 1609 /** 1610 * This function executes a query to remove a folder from the forums 1611 * table and change the parent of its children. 1612 */ 1613 1614 function phorum_db_drop_folder($forum_id) 1615 { 1616 $PHORUM = $GLOBALS["PHORUM"]; 1617 1618 settype($forum_id, "int"); 1619 1620 $conn = phorum_db_mysql_connect(); 1621 1622 $sql = "select parent_id from {$PHORUM['forums_table']} where forum_id=$forum_id"; 1623 1624 $res = mysql_query($sql, $conn); 1625 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1626 1627 $new_parent_id = mysql_result($res, 0, "parent_id"); 1628 1629 $sql = "update {$PHORUM['forums_table']} set parent_id=$new_parent_id where parent_id=$forum_id"; 1630 1631 $res = mysql_query($sql, $conn); 1632 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1633 1634 $sql = "delete from {$PHORUM['forums_table']} where forum_id=$forum_id"; 1635 1636 $res = mysql_query($sql, $conn); 1637 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1638 } 1639 1640 /** 1641 * This function executes a query to update a forum in the forums 1642 * table and returns non zero on success or 0 on failure. 1643 */ 1644 1645 function phorum_db_update_forum($forum){ 1646 $PHORUM = $GLOBALS["PHORUM"]; 1647 1648 $res = 0; 1649 1650 if (!empty($forum["forum_id"])){ 1651 1652 phorum_db_sanitize_mixed($forum["forum_id"], "int"); 1653 1654 // this way we can also update multiple forums at once 1655 if(is_array($forum["forum_id"])) { 1656 $forumwhere="forum_id IN (".implode(",",$forum["forum_id"]).")"; 1657 } else { 1658 $forumwhere="forum_id=".$forum["forum_id"]; 1659 } 1660 1661 unset($forum["forum_id"]); 1662 1663 $conn = phorum_db_mysql_connect(); 1664 1665 foreach($forum as $key => $value){ 1666 if(phorum_db_validate_field($key)){ 1667 if (is_numeric($value)){ 1668 $value = (int)$value; 1669 $fields[] = "$key=$value"; 1670 } elseif($value=="NULL") { 1671 $fields[] = "$key=$value"; 1672 } else { 1673 $value = mysql_escape_string($value); 1674 $fields[] = "$key='$value'"; 1675 } 1676 } 1677 } 1678 1679 $sql = "update {$PHORUM['forums_table']} set " . implode(", ", $fields) . " where $forumwhere"; 1680 1681 $res = mysql_query($sql, $conn); 1682 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1683 }else{ 1684 trigger_error("\$forum[forum_id] cannot be empty in phorum_update_forum()", E_USER_ERROR); 1685 } 1686 1687 return $res; 1688 } 1689 1690 /** 1691 * 1692 */ 1693 1694 function phorum_db_get_groups($group_id=0) 1695 { 1696 $PHORUM = $GLOBALS["PHORUM"]; 1697 $conn = phorum_db_mysql_connect(); 1698 1699 phorum_db_sanitize_mixed($group_id,"int"); 1700 1701 1702 if(is_array($group_id) && count($group_id)) { 1703 $group_str=implode(',',$group_id); 1704 $where_str=" where group_id IN($group_str)"; 1705 } elseif(!is_array($group_id) && $group_id!=0) { 1706 $where_str=" where group_id=$group_id"; 1707 } else { 1708 $where_str=""; 1709 } 1710 1711 $sql="select * from {$PHORUM['groups_table']}".$where_str; 1712 1713 1714 $res = mysql_query($sql, $conn); 1715 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1716 1717 $groups=array(); 1718 while($rec=mysql_fetch_assoc($res)){ 1719 1720 $groups[$rec["group_id"]]=$rec; 1721 $groups[$rec["group_id"]]["permissions"]=array(); 1722 } 1723 1724 $sql="select * from {$PHORUM['forum_group_xref_table']}".$where_str; 1725 1726 $res = mysql_query($sql, $conn); 1727 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1728 1729 while($rec=mysql_fetch_assoc($res)){ 1730 1731 $groups[$rec["group_id"]]["permissions"][$rec["forum_id"]]=$rec["permission"]; 1732 1733 } 1734 1735 return $groups; 1736 1737 } 1738 1739 /** 1740 * Get the members of a group. 1741 * @param group_id - can be an integer (single group), or an array of groups 1742 * @param status - a specific status to look for, defaults to all 1743 * @return array - users (key is userid, value is group membership status) 1744 */ 1745 1746 function phorum_db_get_group_members($group_id, $status = NULL) 1747 { 1748 $PHORUM = $GLOBALS["PHORUM"]; 1749 $conn = phorum_db_mysql_connect(); 1750 1751 phorum_db_sanitize_mixed($group_id, "int"); 1752 if ($status !== NULL) settype($status, "int"); 1753 1754 if(is_array($group_id)){ 1755 $group_id=implode(",", $group_id); 1756 } 1757 1758 // this join is only here so that the list of users comes out sorted 1759 // if phorum_db_user_get() sorts results itself, this join can go away 1760 $sql="select {$PHORUM['user_group_xref_table']}.user_id, {$PHORUM['user_group_xref_table']}.status from {$PHORUM['user_table']}, {$PHORUM['user_group_xref_table']} where {$PHORUM['user_table']}.user_id = {$PHORUM['user_group_xref_table']}.user_id and group_id in ($group_id)"; 1761 if ($status !== NULL) $sql.=" and {$PHORUM['user_group_xref_table']}.status = $status"; 1762 $sql .=" order by username asc"; 1763 1764 $res = mysql_query($sql, $conn); 1765 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1766 $users=array(); 1767 while($rec=mysql_fetch_assoc($res)){ 1768 $users[$rec["user_id"]]=$rec["status"]; 1769 } 1770 1771 return $users; 1772 1773 } 1774 1775 /** 1776 * 1777 */ 1778 1779 function phorum_db_save_group($group) 1780 { 1781 $PHORUM = $GLOBALS["PHORUM"]; 1782 $conn = phorum_db_mysql_connect(); 1783 1784 $ret=false; 1785 1786 $permissions = $group["permissions"]; 1787 phorum_db_sanitize_mixed($group, "string"); 1788 $group["permissions"] = $permissions; 1789 1790 if(isset($group["name"])){ 1791 $sql="update {$PHORUM['groups_table']} set name='{$group['name']}', open={$group['open']} where group_id={$group['group_id']}"; 1792 1793 $res=mysql_query($sql, $conn); 1794 1795 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1796 1797 } 1798 1799 if(!$err){ 1800 1801 if(isset($group["permissions"])){ 1802 $sql="delete from {$PHORUM['forum_group_xref_table']} where group_id={$group['group_id']}"; 1803 1804 $res=mysql_query($sql, $conn); 1805 1806 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1807 1808 foreach($group["permissions"] as $forum_id=>$permission){ 1809 settype($forum_id, "int"); 1810 settype($permission, "int"); 1811 $sql="insert into {$PHORUM['forum_group_xref_table']} set group_id={$group['group_id']}, permission=$permission, forum_id=$forum_id"; 1812 $res=mysql_query($sql, $conn); 1813 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1814 if(!$res) break; 1815 } 1816 } 1817 } 1818 1819 if($res>0) $ret=true; 1820 1821 return $ret; 1822 1823 } 1824 1825 function phorum_db_delete_group($group_id) 1826 { 1827 $PHORUM = $GLOBALS["PHORUM"]; 1828 $conn = phorum_db_mysql_connect(); 1829 1830 settype($group_id, "int"); 1831 1832 $sql = "delete from {$PHORUM['groups_table']} where group_id = $group_id"; 1833 $res = mysql_query($sql, $conn); 1834 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1835 1836 // delete things associated with groups 1837 $sql = "delete from {$PHORUM['user_group_xref_table']} where group_id = $group_id"; 1838 $res = mysql_query($sql, $conn); 1839 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1840 1841 $sql = "delete from {$PHORUM['forum_group_xref_table']} where group_id = $group_id"; 1842 $res = mysql_query($sql, $conn); 1843 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1844 } 1845 1846 /** 1847 * phorum_db_add_group() 1848 * 1849 * @param $group_name $group_id 1850 * @return 1851 **/ 1852 function phorum_db_add_group($group_name,$group_id=0) 1853 { 1854 $PHORUM = $GLOBALS["PHORUM"]; 1855 $conn = phorum_db_mysql_connect(); 1856 1857 settype($group_id, "int"); 1858 $group_name = mysql_escape_string($group_name); 1859 1860 if($group_id > 0) { // only used in conversion 1861 $sql="insert into {$PHORUM['groups_table']} (group_id,name) values ($group_id,'$group_name')"; 1862 } else { 1863 $sql="insert into {$PHORUM['groups_table']} (name) values ('$group_name')"; 1864 } 1865 1866 $res = mysql_query($sql, $conn); 1867 1868 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1869 1870 $group_id = 0; 1871 1872 if ($res) { 1873 $group_id = mysql_insert_id($conn); 1874 } 1875 1876 return $group_id; 1877 } 1878 1879 /** 1880 * This function returns all moderators for a particular forum 1881 */ 1882 function phorum_db_user_get_moderators($forum_id,$ignore_user_perms=false,$for_email=false) { 1883 1884 $PHORUM = $GLOBALS["PHORUM"]; 1885 $userinfo=array(); 1886 1887 $conn = phorum_db_mysql_connect(); 1888 1889 settype($forum_id, "int"); 1890 settype($ignore_user_perms, "bool"); 1891 settype($for_email, "bool"); 1892 1893 if(!$ignore_user_perms) { // sometimes we just don't need them 1894 if(!$PHORUM['email_ignore_admin']) { 1895 $admincheck=" OR user.admin=1"; 1896 } else { 1897 $admincheck=""; 1898 } 1899 1900 1901 $sql="SELECT DISTINCT user.user_id, user.email, user.moderation_email FROM {$PHORUM['user_table']} as user LEFT JOIN {$PHORUM['user_permissions_table']} as perm ON perm.user_id=user.user_id WHERE (perm.permission >= ".PHORUM_USER_ALLOW_MODERATE_MESSAGES." AND (perm.permission & ".PHORUM_USER_ALLOW_MODERATE_MESSAGES." > 0) AND perm.forum_id=$forum_id)$admincheck"; 1902 1903 1904 $res = mysql_query($sql, $conn); 1905 1906 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1907 1908 while ($row = mysql_fetch_row($res)){ 1909 if(!$for_email || $row[2] == 1) 1910 $userinfo[$row[0]]=$row[1]; 1911 } 1912 1913 } 1914 1915 // get users who belong to groups that have moderator access 1916 $sql = "SELECT DISTINCT user.user_id, user.email, user.moderation_email FROM {$PHORUM['user_table']} AS user, {$PHORUM['groups_table']} AS groups, {$PHORUM['user_group_xref_table']} AS usergroup, {$PHORUM['forum_group_xref_table']} AS forumgroup WHERE user.user_id = usergroup.user_id AND usergroup.group_id = groups.group_id AND groups.group_id = forumgroup.group_id AND forum_id = $forum_id AND permission & ".PHORUM_USER_ALLOW_MODERATE_MESSAGES." > 0 AND usergroup.status >= ".PHORUM_USER_GROUP_APPROVED; 1917 1918 $res = mysql_query($sql, $conn); 1919 1920 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1921 1922 while ($row = mysql_fetch_row($res)){ 1923 if(!$for_email || $row[2] == 1) 1924 $userinfo[$row[0]]=$row[1]; 1925 } 1926 return $userinfo; 1927 } 1928 1929 /** 1930 * This function executes a query to select data about a user including 1931 * his permission data and returns that in an array. 1932 */ 1933 1934 function phorum_db_user_get($user_id, $detailed) 1935 { 1936 $PHORUM = $GLOBALS["PHORUM"]; 1937 1938 $conn = phorum_db_mysql_connect(); 1939 1940 phorum_db_sanitize_mixed($user_id, "int"); 1941 1942 if(is_array($user_id)){ 1943 if (count($user_id)) { 1944 $user_ids=implode(",", $user_id); 1945 } else { 1946 return array(); 1947 } 1948 } else { 1949 $user_ids=(int)$user_id; 1950 } 1951 1952 $users = array(); 1953 1954 $sql = "select * from {$PHORUM['user_table']} where user_id in ($user_ids)"; 1955 $res = mysql_query($sql, $conn); 1956 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1957 1958 if (mysql_num_rows($res)){ 1959 while($rec=mysql_fetch_assoc($res)){ 1960 $users[$rec["user_id"]] = $rec; 1961 } 1962 1963 if ($detailed){ 1964 // get the users' permissions 1965 $sql = "select * from {$PHORUM['user_permissions_table']} where user_id in ($user_ids)"; 1966 1967 $res = mysql_query($sql, $conn); 1968 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1969 1970 while ($row = mysql_fetch_assoc($res)){ 1971 $users[$row["user_id"]]["forum_permissions"][$row["forum_id"]] = $row["permission"]; 1972 } 1973 1974 // get the users' groups and forum permissions through those groups 1975 $sql = "select user_id, {$PHORUM['user_group_xref_table']}.group_id, forum_id, permission from {$PHORUM['user_group_xref_table']} left join {$PHORUM['forum_group_xref_table']} using (group_id) where user_id in ($user_ids) AND {$PHORUM['user_group_xref_table']}.status >= ".PHORUM_USER_GROUP_APPROVED; 1976 1977 $res = mysql_query($sql, $conn); 1978 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1979 1980 while ($row = mysql_fetch_assoc($res)){ 1981 $users[$row["user_id"]]["groups"][$row["group_id"]] = $row["group_id"]; 1982 if(!empty($row["forum_id"])){ 1983 if(!isset($users[$row["user_id"]]["group_permissions"][$row["forum_id"]])) { 1984 $users[$row["user_id"]]["group_permissions"][$row["forum_id"]] = 0; 1985 } 1986 $users[$row["user_id"]]["group_permissions"][$row["forum_id"]] = $users[$row["user_id"]]["group_permissions"][$row["forum_id"]] | $row["permission"]; 1987 } 1988 } 1989 1990 } 1991 $sql = "select * from {$PHORUM['user_custom_fields_table']} where user_id in ($user_ids)"; 1992 $res = mysql_query($sql, $conn); 1993 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 1994 1995 while ($row = mysql_fetch_assoc($res)){ 1996 if(isset($PHORUM["PROFILE_FIELDS"][$row['type']])) { 1997 if($PHORUM["PROFILE_FIELDS"][$row['type']]['html_disabled']) { 1998 $users[$row["user_id"]][$PHORUM["PROFILE_FIELDS"][$row['type']]['name']] = htmlspecialchars($row["data"]); 1999 } else { // not html-disabled 2000 if(substr($row["data"],0,6) == 'P_SER:') { 2001 // P_SER (PHORUM_SERIALIZED) is our marker telling this field is serialized 2002 $users[$row["user_id"]][$PHORUM["PROFILE_FIELDS"][$row['type']]['name']] = unserialize(substr($row["data"],6)); 2003 } else { 2004 $users[$row["user_id"]][$PHORUM["PROFILE_FIELDS"][$row['type']]['name']] = $row["data"]; 2005 } 2006 } 2007 } 2008 } 2009 2010 } 2011 2012 if(is_array($user_id)){ 2013 return $users; 2014 } else { 2015 return isset($users[$user_id]) ? $users[$user_id] : NULL; 2016 } 2017 2018 } 2019 2020 /* 2021 * Generic function to retrieve a couple of fields from the user-table 2022 * for a couple of users or only one of them 2023 * 2024 * result is always an array with one or more users in it 2025 */ 2026 2027 function phorum_db_user_get_fields($user_id, $fields) 2028 { 2029 $PHORUM = $GLOBALS["PHORUM"]; 2030 2031 $conn = phorum_db_mysql_connect(); 2032 2033 phorum_db_sanitize_mixed($user_id, "int"); 2034 2035 if(is_array($user_id)){ 2036 $user_ids=implode(",", $user_id); 2037 } else { 2038 $user_ids=(int)$user_id; 2039 } 2040 2041 if(!is_array($fields)) { 2042 $fields = array($fields); 2043 } 2044 2045 $users = array(); 2046 2047 foreach($fields as $key=>$field){ 2048 if(!phorum_db_validate_field($field)){ 2049 unset($fields[$key]); 2050 } 2051 } 2052 2053 2054 $sql = "select user_id, ".implode(",", $fields)." from {$PHORUM['user_table']} where user_id in ($user_ids)"; 2055 2056 $res = mysql_query($sql, $conn); 2057 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2058 2059 if (mysql_num_rows($res)){ 2060 while($rec=mysql_fetch_assoc($res)){ 2061 $users[$rec["user_id"]] = $rec; 2062 } 2063 } 2064 2065 return $users; 2066 2067 } 2068 2069 /** 2070 * Get a list of all users of a certain type. 2071 * 2072 * @param $type - what type of list to retrieve. 2073 * 0 = all users 2074 * 1 = all active users 2075 * 2 = all inactive users 2076 * @return array - key: userid, value: array (username, displayname) 2077 */ 2078 function phorum_db_user_get_list($type = 0){ 2079 $PHORUM = $GLOBALS["PHORUM"]; 2080 2081 settype($type, "int"); 2082 2083 $conn = phorum_db_mysql_connect(); 2084 2085 $where = ''; 2086 if ($type == 1) $where = "where active = 1"; 2087 elseif ($type == 2) $where = "where active != 1"; 2088 2089 $users = array(); 2090 $sql = "select user_id, username from {$PHORUM['user_table']} $where order by username asc"; 2091 $res = mysql_query($sql, $conn); 2092 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2093 2094 while ($row = mysql_fetch_assoc($res)){ 2095 $users[$row["user_id"]] = array("username" => $row["username"], "displayname" => $row["username"]); 2096 } 2097 2098 return $users; 2099 } 2100 2101 /** 2102 * This function executes a query to select data about a user including 2103 * his permission data and returns that in an array. 2104 */ 2105 2106 function phorum_db_user_check_pass($username, $password, $temp_password=false){ 2107 $PHORUM = $GLOBALS["PHORUM"]; 2108 2109 $conn = phorum_db_mysql_connect(); 2110 2111 settype($temp_password, "bool"); 2112 2113 $username = mysql_escape_string($username); 2114 2115 $password = mysql_escape_string($password); 2116 2117 $pass_field = ($temp_password) ? "password_temp" : "password"; 2118 2119 $sql = "select user_id from {$PHORUM['user_table']} where username='$username' and $pass_field='$password'"; 2120 2121 $res = mysql_query($sql, $conn); 2122 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2123 2124 return ($res && mysql_num_rows($res)) ? mysql_result($res, 0, "user_id") : 0; 2125 } 2126 2127 /** 2128 * This function executes a query to check for the given field in the 2129 * user tableusername and return the user_id of the user it matches or 0 2130 * if no match is found. 2131 * 2132 * The parameters can be arrays. If they are, all must be passed and all 2133 * must have the same number of values. 2134 * 2135 * If $return_array is true, an array of all matching rows will be returned. 2136 * Otherwise, only the first user_id from the results will be returned. 2137 */ 2138 2139 function phorum_db_user_check_field($field, $value, $operator="=", $return_array=false) 2140 { 2141 $PHORUM = $GLOBALS["PHORUM"]; 2142 2143 $ret = 0; 2144 2145 settype($return_array, "bool"); 2146 2147 $conn = phorum_db_mysql_connect(); 2148 2149 if(!is_array($field)){ 2150 $field=array($field); 2151 } 2152 2153 if(!is_array($value)){ 2154 $value=array($value); 2155 } 2156 2157 if(!is_array($operator)){ 2158 $operator=array($operator); 2159 } 2160 2161 if(count($field)!=count($value) || count($field)!=count($operator) || count($operator)!=count($value)){ 2162 return $ret; 2163 } 2164 2165 2166 2167 $valid_operators = array("=", "<>", "!=", ">", "<", ">=", "<="); 2168 2169 foreach($field as $key=>$name){ 2170 if(in_array($operator[$key], $valid_operators) && phorum_db_validate_field($name)){ 2171 $value[$key] = mysql_escape_string($value[$key]); 2172 $clauses[]="$name $operator[$key] '$value[$key]'"; 2173 } 2174 } 2175 2176 $sql = "select user_id from {$PHORUM['user_table']} where ".implode(" and ", $clauses); 2177 2178 $res = mysql_query($sql, $conn); 2179 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2180 2181 if ($res && mysql_num_rows($res)){ 2182 if($return_array){ 2183 $ret=array(); 2184 while($row=mysql_fetch_assoc($res)){ 2185 $ret[$row["user_id"]]=$row["user_id"]; 2186 } 2187 } else { 2188 $ret = mysql_result($res, 0, "user_id"); 2189 } 2190 } 2191 2192 return $ret; 2193 } 2194 2195 2196 /** 2197 * This function executes a query to add the given user data to the 2198 * database and returns the userid or 0 2199 */ 2200 2201 function phorum_db_user_add($userdata){ 2202 $PHORUM = $GLOBALS["PHORUM"]; 2203 2204 $conn = phorum_db_mysql_connect(); 2205 2206 if (isset($userdata["forum_permissions"]) && !empty($userdata["forum_permissions"])){ 2207 $forum_perms = $userdata["forum_permissions"]; 2208 unset($userdata["forum_permissions"]); 2209 } 2210 2211 if (isset($userdata["user_data"]) && !empty($userdata["user_data"])){ 2212 $user_data = $userdata["user_data"]; 2213 unset($userdata["user_data"]); 2214 } 2215 2216 2217 $sql = "insert into {$PHORUM['user_table']} set "; 2218 2219 $values = array(); 2220 2221 foreach($userdata as $key => $value){ 2222 if (phorum_db_validate_field($key)){ 2223 if (!is_numeric($value)){ 2224 $value = mysql_escape_string($value); 2225 $values[] = "$key='$value'"; 2226 }else{ 2227 $values[] = "$key=$value"; 2228 } 2229 } 2230 } 2231 2232 $sql .= implode(", ", $values); 2233 2234 $res = mysql_query($sql, $conn); 2235 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2236 2237 $user_id = 0; 2238 if ($res){ 2239 $user_id = mysql_insert_id($conn); 2240 } 2241 2242 if ($res){ 2243 if(isset($forum_perms)) { 2244 // storing forum-permissions 2245 foreach($forum_perms as $fid => $p){ 2246 $sql = "insert into {$PHORUM['user_permissions_table']} set user_id=$user_id, forum_id=$fid, permission=$p"; 2247 $res = mysql_query($sql, $conn); 2248 if ($err = mysql_error($conn)){ 2249 phorum_db_mysql_error("$err: $sql"); 2250 break; 2251 } 2252 } 2253 } 2254 if(isset($user_data)) { 2255 /* storing custom-fields */ 2256 foreach($user_data as $key => $val){ 2257 if(is_array($val)) { /* arrays need to be serialized */ 2258 $val = 'P_SER:'.serialize($val); 2259 /* P_SER: (PHORUM_SERIALIZED is our marker telling this Field is serialized */ 2260 } else { /* other vars need to be escaped */ 2261 $val = mysql_escape_string($val); 2262 } 2263 $sql = "insert into {$PHORUM['user_custom_fields_table']} (user_id,type,data) VALUES($user_id,$key,'$val')"; 2264 $res = mysql_query($sql, $conn); 2265 if ($err = mysql_error($conn)){ 2266 phorum_db_mysql_error("$err: $sql"); 2267 break; 2268 } 2269 } 2270 } 2271 } 2272 2273 return $user_id; 2274 } 2275 2276 2277 /** 2278 * This function executes a query to update the given user data in the 2279 * database and returns the true or false 2280 */ 2281 function phorum_db_user_save($userdata){ 2282 $PHORUM = $GLOBALS["PHORUM"]; 2283 2284 $conn = phorum_db_mysql_connect(); 2285 2286 if(isset($userdata["permissions"])){ 2287 unset($userdata["permissions"]); 2288 } 2289 2290 if (isset($userdata["forum_permissions"])){ 2291 $forum_perms = $userdata["forum_permissions"]; 2292 unset($userdata["forum_permissions"]); 2293 } 2294 2295 if (isset($userdata["groups"])){ 2296 $groups = $userdata["groups"]; 2297 unset($userdata["groups"]); 2298 unset($userdata["group_permissions"]); 2299 } 2300 if (isset($userdata["user_data"])){ 2301 $user_data = $userdata["user_data"]; 2302 unset($userdata["user_data"]); 2303 } 2304 2305 $user_id = $userdata["user_id"]; 2306 unset($userdata["user_id"]); 2307 2308 if(count($userdata)){ 2309 2310 $sql = "update {$PHORUM['user_table']} set "; 2311 2312 $values = array(); 2313 2314 foreach($userdata as $key => $value){ 2315 $values[] = "$key='".mysql_escape_string($value)."'"; 2316 } 2317 2318 $sql .= implode(", ", $values); 2319 2320 $sql .= " where user_id=$user_id"; 2321 2322 $res = mysql_query($sql, $conn); 2323 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2324 } 2325 2326 if (isset($forum_perms)){ 2327 2328 $sql = "delete from {$PHORUM['user_permissions_table']} where user_id = $user_id"; 2329 $res=mysql_query($sql, $conn); 2330 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2331 2332 foreach($forum_perms as $fid=>$perms){ 2333 $sql = "insert into {$PHORUM['user_permissions_table']} set user_id=$user_id, forum_id=$fid, permission=$perms"; 2334 $res = mysql_query($sql, $conn); 2335 if ($err = mysql_error($conn)){ 2336 phorum_db_mysql_error("$err: $sql"); 2337 } 2338 } 2339 } 2340 if(isset($user_data)) { 2341 // storing custom-fields 2342 $sql = "delete from {$PHORUM['user_custom_fields_table']} where user_id = $user_id"; 2343 $res=mysql_query($sql, $conn); 2344 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2345 2346 if(is_array($user_data)) { 2347 foreach($user_data as $key => $val){ 2348 if(is_array($val)) { /* arrays need to be serialized */ 2349 $val = 'P_SER:'.serialize($val); 2350 /* P_SER: (PHORUM_SERIALIZED is our marker telling this Field is serialized */ 2351 } else { /* other vars need to be escaped */ 2352 $val = mysql_escape_string($val); 2353 } 2354 2355 $sql = "insert into {$PHORUM['user_custom_fields_table']} (user_id,type,data) VALUES($user_id,$key,'$val')"; 2356 $res = mysql_query($sql, $conn); 2357 if ($err = mysql_error($conn)){ 2358 phorum_db_mysql_error("$err: $sql"); 2359 break; 2360 } 2361 } 2362 } 2363 } 2364 2365 return (bool)$res; 2366 } 2367 2368 /** 2369 * This function saves a users group permissions. 2370 */ 2371 function phorum_db_user_save_groups($user_id, $groups) 2372 { 2373 $PHORUM = $GLOBALS["PHORUM"]; 2374 2375 settype($user_id, "int"); 2376 2377 if (!$user_id > 0){ 2378 return false; 2379 } 2380 2381 // erase the group memberships they have now 2382 $conn = phorum_db_mysql_connect(); 2383 $sql = "delete from {$PHORUM['user_group_xref_table']} where user_id = $user_id"; 2384 $res=mysql_query($sql, $conn); 2385 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2386 2387 foreach($groups as $group_id => $group_perm){ 2388 $group_id = (int)$group_id; 2389 $group_perm = (int)$group_perm; 2390 $sql = "insert into {$PHORUM['user_group_xref_table']} set user_id=$user_id, group_id=$group_id, status=$group_perm"; 2391 mysql_query($sql, $conn); 2392 if ($err = mysql_error($conn)){ 2393 phorum_db_mysql_error("$err: $sql"); 2394 break; 2395 } 2396 } 2397 return (bool)$res; 2398 } 2399 2400 /** 2401 * This function executes a query to subscribe a user to a forum/thread. 2402 */ 2403 2404 function phorum_db_user_subscribe($user_id, $forum_id, $thread, $type) 2405 { 2406 $PHORUM = $GLOBALS["PHORUM"]; 2407 2408 settype($user_id, "int"); 2409 settype($forum_id, "int"); 2410 settype($thread, "int"); 2411 settype($type, "int"); 2412 2413 $conn = phorum_db_mysql_connect(); 2414 2415 $sql = "replace into {$PHORUM['subscribers_table']} set user_id=$user_id, forum_id=$forum_id, sub_type=$type, thread=$thread"; 2416 2417 $res = mysql_query($sql, $conn); 2418 2419 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2420 2421 return (bool)$res; 2422 } 2423 2424 /** 2425 * This function increases the post-counter for a user by one 2426 */ 2427 function phorum_db_user_addpost() { 2428 2429 $conn = phorum_db_mysql_connect(); 2430 2431 $sql="UPDATE ".$GLOBALS['PHORUM']['user_table']." SET posts=posts+1 WHERE user_id = ".$GLOBALS['PHORUM']['user']['user_id']; 2432 $res=mysql_query($sql,$conn); 2433 2434 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2435 2436 return (bool)$res; 2437 } 2438 2439 /** 2440 * This function executes a query to unsubscribe a user to a forum/thread. 2441 */ 2442 2443 function phorum_db_user_unsubscribe($user_id, $thread, $forum_id=0) 2444 { 2445 $PHORUM = $GLOBALS["PHORUM"]; 2446 2447 settype($user_id, "int"); 2448 settype($forum_id, "int"); 2449 settype($thread, "int"); 2450 2451 $conn = phorum_db_mysql_connect(); 2452 2453 $sql = "DELETE FROM {$PHORUM['subscribers_table']} WHERE user_id=$user_id AND thread=$thread"; 2454 if($forum_id) $sql.=" and forum_id=$forum_id"; 2455 2456 $res = mysql_query($sql, $conn); 2457 2458 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2459 2460 return (bool)$res; 2461 } 2462 2463 /** 2464 * This function will return a list of groups the user 2465 * is a member of, as well as the users permissions. 2466 */ 2467 function phorum_db_user_get_groups($user_id) 2468 { 2469 $PHORUM = $GLOBALS["PHORUM"]; 2470 $groups = array(); 2471 2472 settype($user_id, "int"); 2473 2474 if (!$user_id > 0){ 2475 return $groups; 2476 } 2477 2478 $conn = phorum_db_mysql_connect(); 2479 $sql = "SELECT group_id, status FROM {$PHORUM['user_group_xref_table']} WHERE user_id = $user_id ORDER BY status DESC"; 2480 2481 $res = mysql_query($sql, $conn); 2482 2483 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2484 2485 while($row = mysql_fetch_assoc($res)){ 2486 $groups[$row["group_id"]] = $row["status"]; 2487 } 2488 2489 return $groups; 2490 } 2491 2492 /** 2493 * This function executes a query to select data about a user including 2494 * his permission data and returns that in an array. 2495 * If $search is empty, all users should be returned. 2496 */ 2497 2498 function phorum_db_search_users($search) 2499 { 2500 $PHORUM = $GLOBALS["PHORUM"]; 2501 2502 $conn = phorum_db_mysql_connect(); 2503 2504 $users = array(); 2505 2506 $search = mysql_escape_string(trim($search)); 2507 2508 $sql = "select user_id, username, email, active, posts, date_last_active from {$PHORUM['user_table']} where username like '%$search%' or email like '%$search%'order by username"; 2509 2510 $res = mysql_query($sql, $conn); 2511 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2512 2513 if (mysql_num_rows($res)){ 2514 while ($user = mysql_fetch_assoc($res)){ 2515 $users[$user["user_id"]] = $user; 2516 } 2517 } 2518 2519 return $users; 2520 } 2521 2522 2523 /** 2524 * This function gets the users that await approval 2525 */ 2526 2527 function phorum_db_user_get_unapproved() 2528 { 2529 $PHORUM = $GLOBALS["PHORUM"]; 2530 2531 $conn = phorum_db_mysql_connect(); 2532 2533 $sql="select user_id, username, email from {$PHORUM['user_table']} where active in(".PHORUM_USER_PENDING_BOTH.", ".PHORUM_USER_PENDING_MOD.") order by username"; 2534 $res=mysql_query($sql, $conn); 2535 2536 if ($err = mysql_error($conn)){ 2537 phorum_db_mysql_error("$err: $sql"); 2538 } 2539 2540 $users=array(); 2541 if($res){ 2542 while($rec=mysql_fetch_assoc($res)){ 2543 $users[$rec["user_id"]]=$rec; 2544 } 2545 } 2546 2547 return $users; 2548 2549 } 2550 /** 2551 * This function deletes a user completely 2552 * - entry in the users-table 2553 * - entries in the permissions-table 2554 * - entries in the newflags-table 2555 * - entries in the subscribers-table 2556 * - entries in the group_xref-table 2557 * - entries in the private-messages-table 2558 * - entries in the files-table 2559 * - sets entries in the messages-table to anonymous 2560 * 2561 */ 2562 function phorum_db_user_delete($user_id) { 2563 $PHORUM = $GLOBALS["PHORUM"]; 2564 2565 // how would we check success??? 2566 $ret = true; 2567 2568 settype($user_id, "int"); 2569 2570 $conn = phorum_db_mysql_connect(); 2571 // user-table 2572 $sql = "delete from {$PHORUM['user_table']} where user_id=$user_id"; 2573 $res = mysql_query($sql, $conn); 2574 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2575 2576 // permissions-table 2577 $sql = "delete from {$PHORUM['user_permissions_table']} where user_id=$user_id"; 2578 $res = mysql_query($sql, $conn); 2579 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2580 2581 // newflags-table 2582 $sql = "delete from {$PHORUM['user_newflags_table']} where user_id=$user_id"; 2583 $res = mysql_query($sql, $conn); 2584 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2585 2586 // subscribers-table 2587 $sql = "delete from {$PHORUM['subscribers_table']} where user_id=$user_id"; 2588 $res = mysql_query($sql, $conn); 2589 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2590 2591 // group-xref-table 2592 $sql = "delete from {$PHORUM['user_group_xref_table']} where user_id=$user_id"; 2593 $res = mysql_query($sql, $conn); 2594 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2595 2596 // private messages 2597 $sql = "select * from {$PHORUM["pm_xref_table"]} where user_id=$user_id"; 2598 $res = mysql_query($sql, $conn); 2599 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2600 while ($row = mysql_fetch_assoc($res)) { 2601 $folder = $row["pm_folder_id"] == 0 ? $row["special_folder"] : $row["pm_folder_id"]; 2602 phorum_db_pm_delete($row["pm_message_id"], $folder, $user_id); 2603 } 2604 2605 // pm_buddies 2606 $sql = "delete from {$PHORUM["pm_buddies_table"]} where user_id=$user_id or buddy_user_id=$user_id"; 2607 $res = mysql_query($sql, $conn); 2608 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2609 2610 // private message folders 2611 $sql = "delete from {$PHORUM["pm_folders_table"]} where user_id=$user_id"; 2612 $res = mysql_query($sql, $conn); 2613 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2614 2615 // files-table 2616 $sql = "delete from {$PHORUM['files_table']} where user_id=$user_id and message_id=0 and link='" . PHORUM_LINK_USER . "'"; 2617 $res = mysql_query($sql, $conn); 2618 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2619 2620 // custom-fields-table 2621 $sql = "delete from {$PHORUM['user_custom_fields_table']} where user_id=$user_id"; 2622 $res = mysql_query($sql, $conn); 2623 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2624 2625 // messages-table 2626 if(PHORUM_DELETE_CHANGE_AUTHOR) { 2627 $sql = "update {$PHORUM['message_table']} set user_id=0,email='',author='".mysql_escape_string($PHORUM['DATA']['LANG']['AnonymousUser'])."' where user_id=$user_id"; 2628 } else { 2629 $sql = "update {$PHORUM['message_table']} set user_id=0,email='' where user_id=$user_id"; 2630 } 2631 $res = mysql_query($sql, $conn); 2632 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2633 2634 return $ret; 2635 } 2636 2637 2638 /** 2639 * This function gets the users file list 2640 */ 2641 2642 function phorum_db_get_user_file_list($user_id) 2643 { 2644 $PHORUM = $GLOBALS["PHORUM"]; 2645 2646 $conn = phorum_db_mysql_connect(); 2647 2648 settype($user_id, "int"); 2649 2650 $files=array(); 2651 2652 $sql="select file_id, filename, filesize, add_datetime from {$PHORUM['files_table']} where user_id=$user_id and message_id=0 and link='" . PHORUM_LINK_USER . "'"; 2653 2654 $res = mysql_query($sql, $conn); 2655 2656 if ($err = mysql_error($conn)){ 2657 phorum_db_mysql_error("$err: $sql"); 2658 } 2659 2660 if($res){ 2661 while($rec=mysql_fetch_assoc($res)){ 2662 $files[$rec["file_id"]]=$rec; 2663 } 2664 } 2665 2666 return $files; 2667 } 2668 2669 2670 /** 2671 * This function gets the message's file list 2672 */ 2673 2674 function phorum_db_get_message_file_list($message_id) 2675 { 2676 $PHORUM = $GLOBALS["PHORUM"]; 2677 2678 settype($message_id, "int"); 2679 2680 $conn = phorum_db_mysql_connect(); 2681 2682 $files=array(); 2683 2684 $sql="select file_id, filename, filesize, add_datetime from {$PHORUM['files_table']} where message_id=$message_id and link='" . PHORUM_LINK_MESSAGE . "'"; 2685 2686 $res = mysql_query($sql, $conn); 2687 2688 if ($err = mysql_error($conn)){ 2689 phorum_db_mysql_error("$err: $sql"); 2690 } 2691 2692 if($res){ 2693 while($rec=mysql_fetch_assoc($res)){ 2694 $files[$rec["file_id"]]=$rec; 2695 } 2696 } 2697 2698 return $files; 2699 } 2700 2701 2702 /** 2703 * This function retrieves a file from the db 2704 */ 2705 2706 function phorum_db_file_get($file_id) 2707 { 2708 $PHORUM = $GLOBALS["PHORUM"]; 2709 2710 $conn = phorum_db_mysql_connect(); 2711 2712 settype($file_id, "int"); 2713 2714 $file=array(); 2715 2716 $sql="select * from {$PHORUM['files_table']} where file_id=$file_id"; 2717 2718 $res = mysql_query($sql, $conn); 2719 2720 if ($err = mysql_error($conn)){ 2721 phorum_db_mysql_error("$err: $sql"); 2722 } 2723 2724 if($res){ 2725 $file=mysql_fetch_assoc($res); 2726 } 2727 2728 return $file; 2729 } 2730 2731 2732 /** 2733 * This function saves a file to the db 2734 */ 2735 2736 function phorum_db_file_save($user_id, $filename, $filesize, $buffer, $message_id=0, $link=null) 2737 { 2738 $PHORUM = $GLOBALS["PHORUM"]; 2739 2740 $conn = phorum_db_mysql_connect(); 2741 2742 $file_id=0; 2743 2744 settype($user_id, "int"); 2745 settype($message_id, "int"); 2746 settype($filesize, "int"); 2747 2748 if (is_null($link)) { 2749 $link = $message_id ? PHORUM_LINK_MESSAGE : PHORUM_LINK_USER; 2750 } else { 2751 $link = mysql_escape_string($link); 2752 } 2753 2754 $filename=mysql_escape_string($filename); 2755 $buffer=mysql_escape_string($buffer); 2756 2757 $sql="insert into {$PHORUM['files_table']} set user_id=$user_id, message_id=$message_id, link='$link', filename='$filename', filesize=$filesize, file_data='$buffer', add_datetime=".time(); 2758 2759 $res = mysql_query($sql, $conn); 2760 2761 if ($err = mysql_error($conn)){ 2762 phorum_db_mysql_error("$err: $sql"); 2763 } 2764 2765 if($res){ 2766 $file_id=mysql_insert_id($conn); 2767 } 2768 2769 return $file_id; 2770 } 2771 2772 2773 /** 2774 * This function saves a file to the db 2775 */ 2776 2777 function phorum_db_file_delete($file_id) 2778 { 2779 $PHORUM = $GLOBALS["PHORUM"]; 2780 2781 $conn = phorum_db_mysql_connect(); 2782 2783 settype($file_id, "int"); 2784 2785 $sql="delete from {$PHORUM['files_table']} where file_id=$file_id"; 2786 2787 $res = mysql_query($sql, $conn); 2788 2789 if ($err = mysql_error($conn)){ 2790 phorum_db_mysql_error("$err: $sql"); 2791 } 2792 2793 return $res; 2794 } 2795 2796 /** 2797 * This function links a file to a specific message 2798 */ 2799 2800 function phorum_db_file_link($file_id, $message_id, $link = null) 2801 { 2802 $PHORUM = $GLOBALS["PHORUM"]; 2803 2804 $conn = phorum_db_mysql_connect(); 2805 2806 settype($file_id, "int"); 2807 settype($message_id, "int"); 2808 2809 if (is_null($link)) { 2810 $link = $message_id ? PHORUM_LINK_MESSAGE : PHORUM_LINK_USER; 2811 } else { 2812 $link = mysql_escape_string($link); 2813 } 2814 2815 $sql="update {$PHORUM['files_table']} " . 2816 "set message_id=$message_id, link='$link' " . 2817 "where file_id=$file_id"; 2818 2819 $res = mysql_query($sql, $conn); 2820 2821 if ($err = mysql_error($conn)){ 2822 phorum_db_mysql_error("$err: $sql"); 2823 } 2824 2825 return $res; 2826 } 2827 2828 /** 2829 * This function reads the current total size of all files for a user 2830 */ 2831 2832 function phorum_db_get_user_filesize_total($user_id) 2833 { 2834 $PHORUM = $GLOBALS["PHORUM"]; 2835 2836 $conn = phorum_db_mysql_connect(); 2837 2838 settype($user_id, "int"); 2839 2840 $total=0; 2841 2842 $sql="select sum(filesize) as total from {$PHORUM['files_table']} where user_id=$user_id and message_id=0 and link='" . PHORUM_LINK_USER . "'"; 2843 2844 $res = mysql_query($sql, $conn); 2845 2846 if ($err = mysql_error($conn)){ 2847 phorum_db_mysql_error("$err: $sql"); 2848 } 2849 2850 if($res){ 2851 $total=mysql_result($res, 0,"total"); 2852 } 2853 2854 return $total; 2855 2856 } 2857 2858 /** 2859 * This function is used for cleaning up stale files from the 2860 * database. Stale files are files that are not linked to 2861 * anything. These can for example be caused by users that 2862 * are writing a message with attachments, but never post 2863 * it. 2864 * @param live_run - If set to false (default), the function 2865 * will return a list of files that will 2866 * be purged. If set to true, files will 2867 * be purged. 2868 */ 2869 function phorum_db_file_purge_stale_files($live_run = false) 2870 { 2871 $PHORUM = $GLOBALS["PHORUM"]; 2872 2873 settype($live_run, "bool"); 2874 2875 $conn = phorum_db_mysql_connect(); 2876 2877 $where = "link='" . PHORUM_LINK_EDITOR. "' " . 2878 "and add_datetime<". (time()-PHORUM_MAX_EDIT_TIME); 2879 2880 // Purge files. 2881 if ($live_run) { 2882 2883 // Delete files that are linked to the editor and are 2884 // added a while ago. These are from abandoned posts. 2885 $sql = "delete from {$PHORUM['files_table']} " . 2886 "where $where"; 2887 $res = mysql_query($sql, $conn); 2888 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2889 2890 return true; 2891 2892 // Only select a list of files that can be purged. 2893 } else { 2894 2895 // Select files that are linked to the editor and are 2896 // added a while ago. These are from abandoned posts. 2897 $sql = "select file_id, filename, filesize, add_datetime " . 2898 "from {$PHORUM['files_table']} " . 2899 "where $where"; 2900 2901 $res = mysql_query($sql, $conn); 2902 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2903 2904 $purge_files = array(); 2905 if (mysql_num_rows($res) > 0) { 2906 while ($row = mysql_fetch_assoc($res)) { 2907 $row["reason"] = "Stale editor file"; 2908 $purge_files[$row["file_id"]] = $row; 2909 } 2910 } 2911 2912 return $purge_files; 2913 } 2914 } 2915 2916 /** 2917 * This function returns the newinfo-array for markallread 2918 */ 2919 2920 function phorum_db_newflag_allread($forum_id=0) 2921 { 2922 $PHORUM = $GLOBALS['PHORUM']; 2923 $conn = phorum_db_mysql_connect(); 2924 2925 settype($forum_id, "int"); 2926 2927 if(empty($forum_id)) $forum_id=$PHORUM["forum_id"]; 2928 2929 // delete all newflags for this user and forum 2930 phorum_db_newflag_delete(0,$forum_id); 2931 2932 // get the maximum message-id in this forum 2933 $sql = "select max(message_id) from {$PHORUM['message_table']} where forum_id in ($forum_id, {$PHORUM['vroot']})"; 2934 $res = mysql_query($sql, $conn); 2935 if ($err = mysql_error($conn)){ 2936 phorum_db_mysql_error("$err: $sql"); 2937 }elseif (mysql_num_rows($res) > 0){ 2938 $row = mysql_fetch_row($res); 2939 if($row[0] > 0) { 2940 // set this message as min-id 2941 phorum_db_newflag_add_read(array(0=>array('id'=>$row[0],'forum'=>$forum_id))); 2942 } 2943 } 2944 2945 } 2946 2947 2948 /** 2949 * This function returns the read messages for the current user and forum 2950 * optionally for a given forum (for the index) 2951 */ 2952 function phorum_db_newflag_get_flags($forum_id=0) 2953 { 2954 $PHORUM = $GLOBALS["PHORUM"]; 2955 2956 settype($forum_id, "int"); 2957 2958 $read_msgs=array('min_id'=>0); 2959 2960 if(empty($forum_id)) $forum_id=$PHORUM["forum_id"]; 2961 2962 $sql="SELECT message_id,forum_id FROM ".$PHORUM['user_newflags_table']." WHERE user_id={$PHORUM['user']['user_id']} AND forum_id IN({$forum_id},{$PHORUM['vroot']})"; 2963 2964 $conn = phorum_db_mysql_connect(); 2965 $res = mysql_query($sql, $conn); 2966 2967 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 2968 2969 while($row=mysql_fetch_row($res)) { 2970 // set the min-id if given flag is set 2971 if($row[1] != $PHORUM['vroot'] && ($read_msgs['min_id']==0 || $row[0] < $read_msgs['min_id'])) { 2972 $read_msgs['min_id']=$row[0]; 2973 } else { 2974 $read_msgs[$row[0]]=$row[0]; 2975 } 2976 } 2977 2978 return $read_msgs; 2979 } 2980 2981 2982 /** 2983 * This function returns the count of unread messages the current user and forum 2984 * optionally for a given forum (for the index) 2985 */ 2986 function phorum_db_newflag_get_unread_count($forum_id=0) 2987 { 2988 $PHORUM = $GLOBALS["PHORUM"]; 2989 2990 settype($forum_id, "int"); 2991 2992 if(empty($forum_id)) $forum_id=$PHORUM["forum_id"]; 2993 2994 // get the read message array 2995 $read_msgs = phorum_db_newflag_get_flags($forum_id); 2996 2997 if($read_msgs["min_id"]==0) return array(0,0); 2998 2999 $sql="SELECT count(*) as count FROM ".$PHORUM['message_table']." WHERE message_id NOT in (".implode(",", $read_msgs).") and message_id > {$read_msgs['min_id']} and forum_id in ({$forum_id},{$PHORUM['vroot']}) and status=".PHORUM_STATUS_APPROVED." and not ".PHORUM_SQL_MOVEDMESSAGES; 3000 3001 $conn = phorum_db_mysql_connect(); 3002 $res = mysql_query($sql, $conn); 3003 3004 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3005 3006 $counts[] = mysql_result($res, 0, "count"); 3007 3008 $sql="SELECT count(*) as count FROM ".$PHORUM['message_table']." WHERE message_id NOT in (".implode(",", $read_msgs).") and message_id > {$read_msgs['min_id']} and forum_id in ({$forum_id},{$PHORUM['vroot']}) and parent_id=0 and status=".PHORUM_STATUS_APPROVED." and not ".PHORUM_SQL_MOVEDMESSAGES; 3009 3010 $conn = phorum_db_mysql_connect(); 3011 $res = mysql_query($sql, $conn); 3012 3013 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3014 3015 $counts[] = mysql_result($res, 0, "count"); 3016 3017 return $counts; 3018 } 3019 3020 3021 /** 3022 * This function marks a message as read 3023 */ 3024 function phorum_db_newflag_add_read($message_ids) { 3025 $PHORUM = $GLOBALS["PHORUM"]; 3026 3027 $num_newflags=phorum_db_newflag_get_count(); 3028 3029 // maybe got just one message 3030 if(!is_array($message_ids)) { 3031 $message_ids=array(0=>(int)$message_ids); 3032 } 3033 3034 // deleting messages which are too much 3035 $num_end=$num_newflags+count($message_ids); 3036 if($num_end > PHORUM_MAX_NEW_INFO) { 3037 phorum_db_newflag_delete($num_end - PHORUM_MAX_NEW_INFO); 3038 } 3039 // building the query 3040 $values=array(); 3041 $cnt=0; 3042 3043 foreach($message_ids as $id=>$data) { 3044 if(is_array($data)) { 3045 $data["forum"] = (int)$data["forum"]; 3046 $data["id"] = (int)$data["id"]; 3047 $values[]="({$PHORUM['user']['user_id']},{$data['forum']},{$data['id']})"; 3048 } else { 3049 $data = (int)$data; 3050 $values[]="({$PHORUM['user']['user_id']},{$PHORUM['forum_id']},$data)"; 3051 } 3052 $cnt++; 3053 } 3054 if($cnt) { 3055 $insert_sql="INSERT IGNORE INTO ".$PHORUM['user_newflags_table']." (user_id,forum_id,message_id) VALUES".join(",",$values); 3056 3057 // fire away 3058 $conn = phorum_db_mysql_connect(); 3059 $res = mysql_query($insert_sql, $conn); 3060 3061 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $insert_sql"); 3062 } 3063 } 3064 3065 /** 3066 * This function returns the number of newflags for this user and forum 3067 */ 3068 function phorum_db_newflag_get_count($forum_id=0) 3069 { 3070 $PHORUM = $GLOBALS["PHORUM"]; 3071 3072 settype($forum_id, "int"); 3073 3074 if(empty($forum_id)) $forum_id=$PHORUM["forum_id"]; 3075 3076 $sql="SELECT count(*) FROM ".$PHORUM['user_newflags_table']." WHERE user_id={$PHORUM['user']['user_id']} AND forum_id={$forum_id}"; 3077 3078 // fire away 3079 $conn = phorum_db_mysql_connect(); 3080 $res = mysql_query($sql, $conn); 3081 3082 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3083 3084 $row=mysql_fetch_row($res); 3085 3086 return $row[0]; 3087 } 3088 3089 /** 3090 * This function removes a number of newflags for this user and forum 3091 */ 3092 function phorum_db_newflag_delete($numdelete=0,$forum_id=0) 3093 { 3094 $PHORUM = $GLOBALS["PHORUM"]; 3095 3096 settype($forum_id, "int"); 3097 settype($numdelete, "int"); 3098 3099 if(empty($forum_id)) $forum_id=$PHORUM["forum_id"]; 3100 3101 if($numdelete>0) { 3102 $lvar=" ORDER BY message_id ASC LIMIT $numdelete"; 3103 } else { 3104 $lvar=""; 3105 } 3106 // delete the number of newflags given 3107 $del_sql="DELETE FROM ".$PHORUM['user_newflags_table']." WHERE user_id={$PHORUM['user']['user_id']} AND forum_id={$forum_id}".$lvar; 3108 // fire away 3109 $conn = phorum_db_mysql_connect(); 3110 $res = mysql_query($del_sql, $conn); 3111 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $del_sql"); 3112 } 3113 3114 3115 /** 3116 * This function executes a query to fix any newflags that are assigned to the wrong forum 3117 */ 3118 3119 function phorum_db_newflag_update_forum($message_ids) { 3120 3121 if(!is_array($message_ids)) { 3122 return; 3123 } 3124 3125 phorum_db_sanitize_mixed($message_ids, "int"); 3126 3127 $ids_str=implode(",",$message_ids); 3128 3129 // then doing the update to newflags 3130 $sql="UPDATE IGNORE {$GLOBALS['PHORUM']['user_newflags_table']} as flags, {$GLOBALS['PHORUM']['message_table']} as msg SET flags.forum_id=msg.forum_id where flags.message_id=msg.message_id and flags.message_id IN ($ids_str)"; 3131 $conn = phorum_db_mysql_connect(); 3132 $res = mysql_query($sql, $conn); 3133 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3134 3135 } 3136 3137 /** 3138 * This function executes a query to get the user ids of the users 3139 * subscribed to a forum/thread. 3140 */ 3141 3142 function phorum_db_get_subscribed_users($forum_id, $thread, $type){ 3143 $PHORUM = $GLOBALS["PHORUM"]; 3144 3145 settype($forum_id, "int"); 3146 settype($thread, "int"); 3147 settype($type, "int"); 3148 3149 $conn = phorum_db_mysql_connect(); 3150 3151 $userignore=""; 3152 if ($PHORUM["DATA"]["LOGGEDIN"]) 3153 $userignore="and b.user_id != {$PHORUM['user']['user_id']}"; 3154 3155 $sql = "select DISTINCT(b.email),user_language from {$PHORUM['subscribers_table']} as a,{$PHORUM['user_table']} as b where a.forum_id=$forum_id and (a.thread=$thread or a.thread=0) and a.sub_type=$type and b.user_id=a.user_id $userignore"; 3156 3157 $res = mysql_query($sql, $conn); 3158 3159 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3160 3161 $arr=array(); 3162 3163 while ($rec = mysql_fetch_row($res)){ 3164 if(!empty($rec[1])) // user-language is set 3165 $arr[$rec[1]][] = $rec[0]; 3166 else // no user-language is set 3167 $arr[$PHORUM['language']][]= $rec[0]; 3168 } 3169 3170 return $arr; 3171 } 3172 3173 /** 3174 * This function executes a query to get the subscriptions of a user-id, 3175 * together with the forum-id and subjects of the threads 3176 */ 3177 3178 function phorum_db_get_message_subscriptions($user_id,$days=2) 3179 { 3180 $PHORUM = $GLOBALS["PHORUM"]; 3181 3182 settype($user_id, "int"); 3183 settype($days, "int"); 3184 3185 $conn = phorum_db_mysql_connect(); 3186 3187 $userignore=""; 3188 if ($PHORUM["DATA"]["LOGGEDIN"]) 3189 $userignore="and b.user_id != {$PHORUM['user']['user_id']}"; 3190 3191 if($days > 0) { 3192 $timestr=" AND (".time()." - b.modifystamp) <= ($days * 86400)"; 3193 } else { 3194 $timestr=""; 3195 } 3196 3197 $sql = "select a.thread, a.forum_id, a.sub_type, b.subject,b.modifystamp,b.author,b.user_id,b.email from {$PHORUM['subscribers_table']} as a,{$PHORUM['message_table']} as b where a.user_id=$user_id and b.message_id=a.thread and (a.sub_type=".PHORUM_SUBSCRIPTION_MESSAGE." or a.sub_type=".PHORUM_SUBSCRIPTION_BOOKMARK.")"."$timestr ORDER BY b.modifystamp desc"; 3198 3199 $res = mysql_query($sql, $conn); 3200 3201 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3202 3203 $arr=array(); 3204 $forum_ids=array(); 3205 3206 while ($rec = mysql_fetch_assoc($res)){ 3207 $unsub_url=phorum_get_url(PHORUM_CONTROLCENTER_URL, "panel=".PHORUM_CC_SUBSCRIPTION_THREADS, "unsub_id=".$rec['thread'], "unsub_forum=".$rec['forum_id'], "unsub_type=".$rec['sub_type']); 3208 $rec['unsubscribe_url']=$unsub_url; 3209 $arr[] = $rec; 3210 $forum_ids[]=$rec['forum_id']; 3211 } 3212 $arr['forum_ids']=$forum_ids; 3213 3214 return $arr; 3215 } 3216 3217 /** 3218 * This function executes a query to find out if a user is subscribed to a thread 3219 */ 3220 3221 function phorum_db_get_if_subscribed($forum_id, $thread, $user_id, $type=PHORUM_SUBSCRIPTION_MESSAGE) 3222 { 3223 $PHORUM = $GLOBALS["PHORUM"]; 3224 3225 settype($forum_id, "int"); 3226 settype($thread, "int"); 3227 settype($user_id, "int"); 3228 settype($type, "int"); 3229 3230 $conn = phorum_db_mysql_connect(); 3231 3232 $sql = "select user_id from {$PHORUM['subscribers_table']} where forum_id=$forum_id and thread=$thread and user_id=$user_id and sub_type=$type"; 3233 3234 $res = mysql_query($sql, $conn); 3235 3236 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3237 3238 if (mysql_num_rows($res) > 0){ 3239 $retval = true; 3240 }else{ 3241 $retval = false; 3242 } 3243 3244 return $retval; 3245 } 3246 3247 3248 /** 3249 * This function retrieves the banlists for the current forum 3250 */ 3251 3252 function phorum_db_get_banlists($ordered=false) { 3253 $PHORUM = $GLOBALS["PHORUM"]; 3254 3255 settype($ordered, "bool"); 3256 3257 $retarr = array(); 3258 $forumstr = ""; 3259 3260 $conn = phorum_db_mysql_connect(); 3261 3262 if(isset($PHORUM['forum_id']) && !empty($PHORUM['forum_id'])) 3263 $forumstr = "WHERE forum_id = {$PHORUM['forum_id']} OR forum_id = 0"; 3264 3265 if(isset($PHORUM['vroot']) && !empty($PHORUM['vroot'])) 3266 $forumstr .= " OR forum_id = {$PHORUM['vroot']}"; 3267 3268 3269 3270 $sql = "SELECT * FROM {$PHORUM['banlist_table']} $forumstr"; 3271 3272 if($ordered) { 3273 $sql.= " ORDER BY type, string"; 3274 } 3275 3276 $res = mysql_query($sql, $conn); 3277 3278 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3279 3280 if (mysql_num_rows($res) > 0){ 3281 while($row = mysql_fetch_assoc($res)) { 3282 $retarr[$row['type']][$row['id']]=array('pcre'=>$row['pcre'],'string'=>$row['string'],'forum_id'=>$row['forum_id']); 3283 } 3284 } 3285 return $retarr; 3286 } 3287 3288 3289 /** 3290 * This function retrieves one item from the banlists 3291 */ 3292 3293 function phorum_db_get_banitem($banid) { 3294 $PHORUM = $GLOBALS["PHORUM"]; 3295 3296 $retarr = array(); 3297 3298 $conn = phorum_db_mysql_connect(); 3299 3300 settype($banid, "int"); 3301 3302 $sql = "SELECT * FROM {$PHORUM['banlist_table']} WHERE id = $banid"; 3303 3304 $res = mysql_query($sql, $conn); 3305 3306 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3307 3308 if (mysql_num_rows($res) > 0){ 3309 while($row = mysql_fetch_assoc($res)) { 3310 $retarr=array('pcre'=>$row['pcre'],'string'=>$row['string'],'forum_id'=>$row['forum_id'],'type'=>$row['type']); 3311 } 3312 } 3313 return $retarr; 3314 } 3315 3316 3317 /** 3318 * This function deletes one item from the banlists 3319 */ 3320 3321 function phorum_db_del_banitem($banid) { 3322 $PHORUM = $GLOBALS["PHORUM"]; 3323 3324 settype($banid, "int"); 3325 3326 $conn = phorum_db_mysql_connect(); 3327 3328 $sql = "DELETE FROM {$PHORUM['banlist_table']} WHERE id = $banid"; 3329 3330 $res = mysql_query($sql, $conn); 3331 3332 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3333 3334 if(mysql_affected_rows($conn) > 0) { 3335 return true; 3336 } else { 3337 return false; 3338 } 3339 } 3340 3341 3342 /** 3343 * This function adds or modifies a banlist-entry 3344 */ 3345 3346 function phorum_db_mod_banlists($type,$pcre,$string,$forum_id,$id=0) { 3347 $PHORUM = $GLOBALS["PHORUM"]; 3348 3349 $retarr = array(); 3350 3351 $conn = phorum_db_mysql_connect(); 3352 3353 settype($type, "int"); 3354 settype($pcre, "int"); 3355 settype($forum_id, "int"); 3356 settype($id, "int"); 3357 3358 if($id > 0) { // modifying an entry 3359 $sql = "UPDATE {$PHORUM['banlist_table']} SET forum_id = $forum_id, type = $type, pcre = $pcre, string = '".mysql_escape_string($string)."' where id = $id"; 3360 } else { // adding an entry 3361 $sql = "INSERT INTO {$PHORUM['banlist_table']} (forum_id,type,pcre,string) VALUES($forum_id,$type,$pcre,'".mysql_escape_string($string)."')"; 3362 } 3363 3364 $res = mysql_query($sql, $conn); 3365 3366 if ($err = mysql_error($conn)) { 3367 phorum_db_mysql_error("$err: $sql"); 3368 return false; 3369 } else { 3370 return true; 3371 } 3372 } 3373 3374 3375 3376 /** 3377 * This function lists all private messages in a folder. 3378 * @param folder - The folder to use. Either a special folder 3379 * (PHORUM_PM_INBOX or PHORUM_PM_OUTBOX) or the 3380 * id of a user's custom folder. 3381 * @param user_id - The user to retrieve messages for or NULL 3382 * to use the current user (default). 3383 * @param reverse - If set to a true value (default), sorting 3384 * of messages is done in reverse (newest first). 3385 */ 3386 3387 function phorum_db_pm_list($folder, $user_id = NULL, $reverse = true) 3388 { 3389 $PHORUM = $GLOBALS["PHORUM"]; 3390 3391 settype($reverse, "bool"); 3392 3393 $conn = phorum_db_mysql_connect(); 3394 3395 if ($user_id == NULL) $user_id = $PHORUM['user']['user_id']; 3396 settype($user_id, "int"); 3397 3398 $folder_sql = "user_id = $user_id AND "; 3399 if (is_numeric($folder)) { 3400 $folder_sql .= "pm_folder_id=$folder"; 3401 } elseif ($folder == PHORUM_PM_INBOX || $folder == PHORUM_PM_OUTBOX) { 3402 $folder_sql .= "pm_folder_id=0 AND special_folder='$folder'"; 3403 } else { 3404 die ("Illegal folder '$folder' requested for user id '$user_id'"); 3405 } 3406 3407 $sql = "SELECT m.pm_message_id, from_user_id, from_username, subject, " . 3408 "datestamp, meta, pm_xref_id, user_id, pm_folder_id, " . 3409 "special_folder, read_flag, reply_flag " . 3410 "FROM {$PHORUM['pm_messages_table']} as m, {$PHORUM['pm_xref_table']} as x " . 3411 "WHERE $folder_sql " . 3412 "AND x.pm_message_id = m.pm_message_id " . 3413 "ORDER BY x.pm_message_id " . ($reverse ? "DESC" : "ASC"); 3414 $res = mysql_query($sql, $conn); 3415 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3416 3417 $list = array(); 3418 if (mysql_num_rows($res) > 0){ 3419 while($row = mysql_fetch_assoc($res)) { 3420 3421 // Add the recipient information unserialized to the message.. 3422 $meta = unserialize($row['meta']); 3423 $row['recipients'] = $meta['recipients']; 3424 3425 $list[$row["pm_message_id"]]=$row; 3426 } 3427 } 3428 3429 return $list; 3430 } 3431 3432 /** 3433 * This function retrieves a private message from the database. 3434 * @param pm_id - The id for the private message to retrieve. 3435 * @param user_id - The user to retrieve messages for or NULL 3436 * to use the current user (default). 3437 * @param folder_id - The folder to retrieve the message from or 3438 * NULL if the folder does not matter. 3439 */ 3440 3441 function phorum_db_pm_get($pm_id, $folder = NULL, $user_id = NULL) 3442 { 3443 $PHORUM = $GLOBALS["PHORUM"]; 3444 3445 $conn = phorum_db_mysql_connect(); 3446 3447 if ($user_id == NULL) $user_id = $PHORUM['user']['user_id']; 3448 settype($user_id, "int"); 3449 settype($pm_id, "int"); 3450 3451 if (is_null($folder)) { 3452 $folder_sql = ''; 3453 } elseif (is_numeric($folder)) { 3454 $folder_sql = "pm_folder_id=$folder AND "; 3455 } elseif ($folder == PHORUM_PM_INBOX || $folder == PHORUM_PM_OUTBOX) { 3456 $folder_sql = "pm_folder_id=0 AND special_folder='$folder' AND "; 3457 } else { 3458 die ("Illegal folder '$folder' requested for message id '$pm_id'"); 3459 } 3460 3461 $sql = "SELECT * " . 3462 "FROM {$PHORUM['pm_messages_table']} as m, {$PHORUM['pm_xref_table']} as x " . 3463 "WHERE $folder_sql x.pm_message_id = $pm_id AND x.user_id = $user_id " . 3464 "AND x.pm_message_id = m.pm_message_id"; 3465 3466 $res = mysql_query($sql, $conn); 3467 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3468 3469 if (mysql_num_rows($res) > 0){ 3470 $row = mysql_fetch_assoc($res); 3471 3472 // Add the recipient information unserialized to the message.. 3473 $meta = unserialize($row['meta']); 3474 $row['recipients'] = $meta['recipients']; 3475 3476 return $row; 3477 } else { 3478 return NULL; 3479 } 3480 } 3481 3482 /** 3483 * This function creates a new folder for a user. 3484 * @param foldername - The name of the folder to create. 3485 * @param user_id - The user to create the folder for or 3486 * NULL to use the current user (default). 3487 */ 3488 function phorum_db_pm_create_folder($foldername, $user_id = NULL) 3489 { 3490 $PHORUM = $GLOBALS["PHORUM"]; 3491 3492 $conn = phorum_db_mysql_connect(); 3493 3494 if ($user_id == NULL) $user_id = $PHORUM['user']['user_id']; 3495 settype($user_id, "int"); 3496 3497 $sql = "INSERT INTO {$PHORUM['pm_folders_table']} SET " . 3498 "user_id=$user_id, " . 3499 "foldername='".mysql_escape_string($foldername)."'"; 3500 3501 $res = mysql_query($sql, $conn); 3502 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3503 3504 $folder_id = 0; 3505 if ($res){ 3506 $folder_id = mysql_insert_id($conn); 3507 } 3508 3509 return $folder_id; 3510 } 3511 3512 /** 3513 * This function renames a folder for a user. 3514 * @param folder_id - The id of the folder to rename. 3515 * @param newname - The new name for the folder. 3516 * @param user_id - The user to rename the folder for or 3517 * NULL to use the current user (default). 3518 */ 3519 function phorum_db_pm_rename_folder($folder_id, $newname, $user_id = NULL) 3520 { 3521 $PHORUM = $GLOBALS["PHORUM"]; 3522 3523 $conn = phorum_db_mysql_connect(); 3524 3525 if ($user_id == NULL) $user_id = $PHORUM['user']['user_id']; 3526 settype($user_id, "int"); 3527 settype($folder_id, "int"); 3528 3529 $sql = "UPDATE {$PHORUM['pm_folders_table']} " . 3530 "SET foldername = '".mysql_escape_string($newname)."' " . 3531 "WHERE pm_folder_id = $folder_id AND user_id = $user_id"; 3532 3533 $res = mysql_query($sql, $conn); 3534 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3535 return $res; 3536 } 3537 3538 3539 3540 /** 3541 * This function deletes a folder for a user. Along with the 3542 * folder, all contained messages are deleted as well. 3543 * @param folder_id - The id of the folder to delete. 3544 * @param user_id - The user to delete the folder for or 3545 * NULL to use the current user (default). 3546 */ 3547 function phorum_db_pm_delete_folder($folder_id, $user_id = NULL) 3548 { 3549 $PHORUM = $GLOBALS["PHORUM"]; 3550 3551 $conn = phorum_db_mysql_connect(); 3552 3553 if ($user_id == NULL) $user_id = $PHORUM['user']['user_id']; 3554 settype($user_id, "int"); 3555 settype($folder_id, "int"); 3556 3557 // Get messages in this folder and delete them. 3558 $list = phorum_db_pm_list($folder_id, $user_id); 3559 foreach ($list as $id => $data) { 3560 phorum_db_pm_delete($id, $folder_id, $user_id); 3561 } 3562 3563 // Delete the folder itself. 3564 $sql = "DELETE FROM {$PHORUM['pm_folders_table']} " . 3565 "WHERE pm_folder_id = $folder_id AND user_id = $user_id"; 3566 $res = mysql_query($sql, $conn); 3567 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3568 return $res; 3569 } 3570 3571 /** 3572 * This function retrieves the list of folders for a user. 3573 * @param user_id - The user to retrieve folders for or NULL 3574 * to use the current user (default). 3575 * @param count_messages - Count the number of messages for the 3576 * folders. Default, this is not done. 3577 */ 3578 function phorum_db_pm_getfolders($user_id = NULL, $count_messages = false) 3579 { 3580 $PHORUM = $GLOBALS["PHORUM"]; 3581 3582 $conn = phorum_db_mysql_connect(); 3583 3584 if ($user_id == NULL) $user_id = $PHORUM['user']['user_id']; 3585 settype($user_id, "int"); 3586 3587 settype($count_messages, "bool"); 3588 3589 // Setup the list of folders. Our special folders are 3590 // not in the database, so these are added here. 3591 $folders = array( 3592 PHORUM_PM_INBOX => array( 3593 'id' => PHORUM_PM_INBOX, 3594 'name' => $PHORUM["DATA"]["LANG"]["INBOX"], 3595 ), 3596 ); 3597 3598 // Select all custom folders for the user. 3599 $sql = "SELECT * FROM {$PHORUM['pm_folders_table']} " . 3600 "WHERE user_id = $user_id ORDER BY foldername"; 3601 $res = mysql_query($sql, $conn); 3602 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3603 3604 // Add them to the folderlist. 3605 if (mysql_num_rows($res) > 0){ 3606 while (($row = mysql_fetch_assoc($res))) { 3607 $folders[$row["pm_folder_id"]] = array( 3608 'id' => $row["pm_folder_id"], 3609 'name' => $row["foldername"], 3610 ); 3611 } 3612 } 3613 3614 // Add the outgoing box. 3615 $folders[PHORUM_PM_OUTBOX] = array( 3616 'id' => PHORUM_PM_OUTBOX, 3617 'name' => $PHORUM["DATA"]["LANG"]["SentItems"], 3618 ); 3619 3620 // Count messages if requested. 3621 if ($count_messages) 3622 { 3623 // Initialize counters. 3624 foreach ($folders as $id => $data) { 3625 $folders[$id]["total"] = $folders[$id]["new"] = 0; 3626 } 3627 3628 // Collect count information. 3629 $sql = "SELECT pm_folder_id, special_folder, " . 3630 "count(*) as total, (count(*) - sum(read_flag)) as new " . 3631 "FROM {$PHORUM['pm_xref_table']} " . 3632 "WHERE user_id = $user_id " . 3633 "GROUP BY pm_folder_id, special_folder"; 3634 $res = mysql_query($sql, $conn); 3635 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3636 3637 // Add counters to the folderlist. 3638 if (mysql_num_rows($res) > 0){ 3639 while (($row = mysql_fetch_assoc($res))) { 3640 $folder_id = $row["pm_folder_id"] ? $row["pm_folder_id"] : $row["special_folder"]; 3641 // If there are stale messages, we do not want them 3642 // to create non-existant mailboxes in the list. 3643 if (isset($folders[$folder_id])) { 3644 $folders[$folder_id]["total"] = $row["total"]; 3645 $folders[$folder_id]["new"] = $row["new"]; 3646 } 3647 } 3648 } 3649 } 3650 3651 return $folders; 3652 } 3653 3654 /** 3655 * This function computes the number of private messages a user has 3656 * and returns both the total and the number unread. 3657 * @param folder - The folder to use. Either a special folder 3658 * (PHORUM_PM_INBOX or PHORUM_PM_OUTBOX), the 3659 * id of a user's custom folder or 3660 * PHORUM_PM_ALLFOLDERS for all folders. 3661 * @param user_id - The user to retrieve messages for or NULL 3662 * to use the current user (default). 3663 */ 3664 3665 function phorum_db_pm_messagecount($folder, $user_id = NULL) 3666 { 3667 $PHORUM = $GLOBALS["PHORUM"]; 3668 3669 $conn = phorum_db_mysql_connect(); 3670 3671 if ($user_id == NULL) $user_id = $PHORUM['user']['user_id']; 3672 settype($user_id, "int"); 3673 3674 if (is_numeric($folder)) { 3675 $folder_sql = "pm_folder_id=$folder AND"; 3676 } elseif ($folder == PHORUM_PM_INBOX || $folder == PHORUM_PM_OUTBOX) { 3677 $folder_sql = "pm_folder_id=0 AND special_folder='$folder' AND"; 3678 } elseif ($folder == PHORUM_PM_ALLFOLDERS) { 3679 $folder_sql = ''; 3680 } else { 3681 die ("Illegal folder '$folder' requested for user id '$user_id'"); 3682 } 3683 3684 $sql = "SELECT count(*) as total, (count(*) - sum(read_flag)) as new " . 3685 "FROM {$PHORUM['pm_xref_table']} " . 3686 "WHERE $folder_sql user_id = $user_id"; 3687 3688 $messagecount=array("total" => 0, "new" => 0); 3689 3690 $res = mysql_query($sql, $conn); 3691 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3692 3693 if (mysql_num_rows($res) > 0){ 3694 $row = mysql_fetch_assoc($res); 3695 $messagecount["total"] = $row["total"]; 3696 $messagecount["new"] = ($row["new"] >= 1) ? $row["new"] : 0; 3697 } 3698 3699 return $messagecount; 3700 } 3701 3702 /** 3703 * This function does a quick check if the user has new private messages. 3704 * This is useful in case you only want to know whether the user has 3705 * new messages or not and when you are not interested in the exact amount 3706 * of new messages. 3707 * 3708 * @param user_id - The user to retrieve messages for or NULL 3709 * to use the current user (default). 3710 * @return A true value, in case there are new messages available. 3711 */ 3712 function phorum_db_pm_checknew($user_id = NULL) 3713 { 3714 $PHORUM = $GLOBALS["PHORUM"]; 3715 3716 $conn = phorum_db_mysql_connect(); 3717 3718 if ($user_id == NULL) $user_id = $PHORUM['user']['user_id']; 3719 settype($user_id, "int"); 3720 3721 $sql = "SELECT user_id " . 3722 "FROM {$PHORUM['pm_xref_table']} " . 3723 "WHERE user_id = $user_id AND read_flag = 0 LIMIT 1"; 3724 $res = mysql_query($sql, $conn); 3725 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3726 3727 return mysql_num_rows($res); 3728 } 3729 3730 /** 3731 * This function inserts a private message in the database. The return value 3732 * is the pm_message_id of the created message. 3733 * @param subject - The subject for the private message. 3734 * @param message - The message text for the private message. 3735 * @param to - A single user_id or an array of user_ids for the recipients. 3736 * @param from - The user_id of the sender. The current user is used in case 3737 * the parameter is set to NULL (default). 3738 * @param keepcopy - If set to a true value, a copy of the mail will be put in 3739 * the outbox of the user. Default value is false. 3740 */ 3741 function phorum_db_pm_send($subject, $message, $to, $from=NULL, $keepcopy=false) 3742 { 3743 $PHORUM = $GLOBALS["PHORUM"]; 3744 3745 $conn = phorum_db_mysql_connect(); 3746 3747 // Prepare the sender. 3748 if ($from == NULL) $from = $PHORUM['user']['user_id']; 3749 settype($from, "int"); 3750 $fromuser = phorum_db_user_get($from, false); 3751 if (! $fromuser) die("Unknown sender user_id '$from'"); 3752 3753 // This array will be filled with xref database entries. 3754 $xref_entries = array(); 3755 3756 // Prepare the list of recipients. 3757 $rcpts = array(); 3758 if (! is_array($to)) $to = array($to); 3759 foreach ($to as $user_id) { 3760 settype($user_id, "int"); 3761 $user = phorum_db_user_get($user_id, false); 3762 if (! $user) die("Unknown recipient user_id '$user_id'"); 3763 $rcpts[$user_id] = array( 3764 'user_id' => $user_id, 3765 'username' => $user["username"], 3766 'read_flag' => 0, 3767 ); 3768 $xref_entries[] = array( 3769 'user_id' => $user_id, 3770 'pm_folder_id' => 0, 3771 'special_folder' => PHORUM_PM_INBOX, 3772 'read_flag' => 0, 3773 ); 3774 } 3775 3776 // Keep copy of this message in outbox? 3777 if ($keepcopy) { 3778 $xref_entries[] = array( 3779 'user_id' => $from, 3780 'pm_folder_id' => 0, 3781 'special_folder' => PHORUM_PM_OUTBOX, 3782 'read_flag' => 1, 3783 ); 3784 } 3785 3786 // Prepare message meta data. 3787 $meta = mysql_escape_string(serialize(array( 3788 'recipients' => $rcpts 3789 ))); 3790 3791 // Create the message. 3792 $sql = "INSERT INTO {$PHORUM["pm_messages_table"]} SET " . 3793 "from_user_id = $from, " . 3794 "from_username = '".mysql_escape_string($fromuser["username"])."', " . 3795 "subject = '".mysql_escape_string($subject)."', " . 3796 "message = '".mysql_escape_string($message)."', " . 3797 "datestamp = '".time()."', " . 3798 "meta = '$meta'"; 3799 mysql_query($sql, $conn); 3800 if ($err = mysql_error($conn)) { 3801 phorum_db_mysql_error("$err: $sql"); 3802 return; 3803 } 3804 3805 // Get the message id. 3806 $pm_message_id = mysql_insert_id($conn); 3807 3808 // Put the message in the recipient inboxes. 3809 foreach ($xref_entries as $xref) { 3810 $sql = "INSERT INTO {$PHORUM["pm_xref_table"]} SET " . 3811 "user_id = {$xref["user_id"]}, " . 3812 "pm_folder_id={$xref["pm_folder_id"]}, " . 3813 "special_folder='{$xref["special_folder"]}', " . 3814 "pm_message_id=$pm_message_id, " . 3815 "read_flag = {$xref["read_flag"]}, " . 3816 "reply_flag = 0"; 3817 mysql_query($sql, $conn); 3818 if ($err = mysql_error($conn)) { 3819 phorum_db_mysql_error("$err: $sql"); 3820 return; 3821 } 3822 3823 } 3824 3825 return $pm_message_id; 3826 } 3827 3828 /** 3829 * This function updates a flag for a private message. 3830 * @param pm_id - The id of the message to update. 3831 * @param flag - The flag to update. Options are PHORUM_PM_READ_FLAG 3832 * and PHORUM_PM_REPLY_FLAG. 3833 * @param value - The value for the flag (true or false). 3834 * @param user_id - The user to set a flag for or NULL 3835 * to use the current user (default). 3836 */ 3837 function phorum_db_pm_setflag($pm_id, $flag, $value, $user_id = NULL) 3838 { 3839 $PHORUM = $GLOBALS["PHORUM"]; 3840 3841 $conn = phorum_db_mysql_connect(); 3842 3843 settype($pm_id, "int"); 3844 3845 if ($flag != PHORUM_PM_READ_FLAG && $flag != PHORUM_PM_REPLY_FLAG) { 3846 trigger_error("Invalid value for \$flag in function phorum_db_pm_setflag(): $flag", E_USER_WARNING); 3847 return 0; 3848 } 3849 3850 $value = $value ? 1 : 0; 3851 3852 if ($user_id == NULL) $user_id = $PHORUM['user']['user_id']; 3853 settype($user_id, "int"); 3854 3855 // Update the flag in the database. 3856 $sql = "UPDATE {$PHORUM["pm_xref_table"]} " . 3857 "SET $flag = $value " . 3858 "WHERE pm_message_id = $pm_id AND user_id = $user_id"; 3859 $res = mysql_query($sql, $conn); 3860 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3861 3862 // Update message counters. 3863 if ($flag == PHORUM_PM_READ_FLAG) { 3864 phorum_db_pm_update_message_info($pm_id); 3865 } 3866 3867 return $res; 3868 } 3869 3870 /** 3871 * This function deletes a private message from a folder. 3872 * @param folder - The folder from which to delete the message 3873 * @param pm_id - The id of the private message to delete 3874 * @param user_id - The user to delete the message for or NULL 3875 * to use the current user (default). 3876 */ 3877 function phorum_db_pm_delete($pm_id, $folder, $user_id = NULL) 3878 { 3879 $PHORUM = $GLOBALS["PHORUM"]; 3880 3881 $conn = phorum_db_mysql_connect(); 3882 3883 settype($pm_id, "int"); 3884 3885 if ($user_id == NULL) $user_id = $PHORUM['user']['user_id']; 3886 settype($user_id, "int"); 3887 3888 if (is_numeric($folder)) { 3889 $folder_sql = "pm_folder_id=$folder AND"; 3890 } elseif ($folder == PHORUM_PM_INBOX || $folder == PHORUM_PM_OUTBOX) { 3891 $folder_sql = "pm_folder_id=0 AND special_folder='$folder' AND"; 3892 } else { 3893 die ("Illegal folder '$folder' requested for user id '$user_id'"); 3894 } 3895 3896 $sql = "DELETE FROM {$PHORUM["pm_xref_table"]} " . 3897 "WHERE $folder_sql " . 3898 "user_id = $user_id AND pm_message_id = $pm_id"; 3899 3900 $res = mysql_query($sql, $conn); 3901 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3902 3903 // Update message counters. 3904 phorum_db_pm_update_message_info($pm_id); 3905 3906 return $res; 3907 } 3908 3909 /** 3910 * This function moves a private message to a different folder. 3911 * @param pm_id - The id of the private message to move. 3912 * @param from - The folder to move the message from. 3913 * @param to - The folder to move the message to. 3914 * @param user_id - The user to move the message for or NULL 3915 * to use the current user (default). 3916 */ 3917 function phorum_db_pm_move($pm_id, $from, $to, $user_id = NULL) 3918 { 3919 $PHORUM = $GLOBALS["PHORUM"]; 3920 3921 $conn = phorum_db_mysql_connect(); 3922 3923 settype($pm_id, "int"); 3924 3925 if ($user_id == NULL) $user_id = $PHORUM['user']['user_id']; 3926 settype($user_id, "int"); 3927 3928 if (is_numeric($from)) { 3929 $folder_sql = "pm_folder_id=$from AND"; 3930 } elseif ($from == PHORUM_PM_INBOX || $from == PHORUM_PM_OUTBOX) { 3931 $folder_sql = "pm_folder_id=0 AND special_folder='$from' AND"; 3932 } else { 3933 die ("Illegal source folder '$from' specified"); 3934 } 3935 3936 if (is_numeric($to)) { 3937 $pm_folder_id = $to; 3938 $special_folder = 'NULL'; 3939 } elseif ($to == PHORUM_PM_INBOX || $to == PHORUM_PM_OUTBOX) { 3940 $pm_folder_id = 0; 3941 $special_folder = "'$to'"; 3942 } else { 3943 die ("Illegal target folder '$to' specified"); 3944 } 3945 3946 $sql = "UPDATE {$PHORUM["pm_xref_table"]} SET " . 3947 "pm_folder_id = $pm_folder_id, " . 3948 "special_folder = $special_folder " . 3949 "WHERE $folder_sql user_id = $user_id AND pm_message_id = $pm_id"; 3950 3951 $res = mysql_query($sql, $conn); 3952 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3953 return $res; 3954 } 3955 3956 /** 3957 * This function updates the meta information for a message. If it 3958 * detects that no xrefs are available for the message anymore, 3959 * the message will be deleted from the database. So this function 3960 * has to be called after setting the read_flag and after deleting 3961 * a message. 3962 * PMTODO maybe we need some locking here to prevent concurrent 3963 * updates of the message info. 3964 */ 3965 function phorum_db_pm_update_message_info($pm_id) 3966 { 3967 $PHORUM = $GLOBALS['PHORUM']; 3968 3969 $conn = phorum_db_mysql_connect(); 3970 3971 settype($pm_id, "int"); 3972 3973 // Find the message record. Return immediately if no message is found. 3974 $sql = "SELECT * " . 3975 "FROM {$PHORUM['pm_messages_table']} " . 3976 "WHERE pm_message_id = $pm_id"; 3977 $res = mysql_query($sql, $conn); 3978 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3979 if (mysql_num_rows($res) == 0) return $res; 3980 $pm = mysql_fetch_assoc($res); 3981 3982 // Find the xrefs for this message. 3983 $sql = "SELECT * " . 3984 "FROM {$PHORUM["pm_xref_table"]} " . 3985 "WHERE pm_message_id = $pm_id"; 3986 $res = mysql_query($sql, $conn); 3987 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3988 3989 // No xrefs left? Then the message can be fully deleted. 3990 if (mysql_num_rows($res) == 0) { 3991 $sql = "DELETE FROM {$PHORUM['pm_messages_table']} " . 3992 "WHERE pm_message_id = $pm_id"; 3993 $res = mysql_query($sql, $conn); 3994 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 3995 return $res; 3996 } 3997 3998 // Update the read flags for the recipients in the meta data. 3999 $meta = unserialize($pm["meta"]); 4000 $rcpts = $meta["recipients"]; 4001 while ($row = mysql_fetch_assoc($res)) { 4002 // Only update if available. A kept copy in the outbox will 4003 // not be in the meta list, so if the copy is read, the 4004 // meta data does not have to be updated here. 4005 if (isset($rcpts[$row["user_id"]])) { 4006 $rcpts[$row["user_id"]]["read_flag"] = $row["read_flag"]; 4007 } 4008 } 4009 $meta["recipients"] = $rcpts; 4010 4011 // Store the new meta data. 4012 $meta = mysql_escape_string(serialize($meta)); 4013 $sql = "UPDATE {$PHORUM['pm_messages_table']} " . 4014 "SET meta = '$meta' " . 4015 "WHERE pm_message_id = $pm_id"; 4016 $res = mysql_query($sql, $conn); 4017 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 4018 return $res; 4019 } 4020 4021 /* Take care of warning about deprecation of the old PM API functions. */ 4022 function phorum_db_get_private_messages($arg1, $arg2) { 4023 phorum_db_pm_deprecated('phorum_db_get_private_messages'); } 4024 function phorum_db_get_private_message($arg1) { 4025 phorum_db_pm_deprecated('phorum_db_get_private_message'); } 4026 function phorum_db_get_private_message_count($arg1) { 4027 phorum_db_pm_deprecated('phorum_db_get_private_message_count'); } 4028 function phorum_db_put_private_messages($arg1, $arg2, $arg3, $arg4, $arg5) { 4029 phorum_db_pm_deprecated('phorum_db_put_private_messages'); } 4030 function phorum_db_update_private_message($arg1, $arg2, $arg3){ 4031 phorum_db_pm_deprecated('phorum_db_update_private_message'); } 4032 function phorum_db_pm_deprecated($func) { 4033 die("$func}() has been deprecated. Please use the new private message API."); 4034 } 4035 4036 /** 4037 * This function checks if a certain user is buddy of another user. 4038 * The function return the pm_buddy_id in case the user is a buddy 4039 * or NULL in case the user isn't. 4040 * @param buddy_user_id - The user_id to check for if it's a buddy. 4041 * @param user_id - The user_id for which the buddy list must be 4042 * checked or NULL to use the current user (default). 4043 */ 4044 function phorum_db_pm_is_buddy($buddy_user_id, $user_id = NULL) 4045 { 4046 $PHORUM = $GLOBALS['PHORUM']; 4047 $conn = phorum_db_mysql_connect(); 4048 settype($buddy_user_id, "int"); 4049 if (is_null($user_id)) $user_id = $PHORUM["user"]["user_id"]; 4050 settype($user_id, "int"); 4051 4052 $sql = "SELECT pm_buddy_id FROM {$PHORUM["pm_buddies_table"]} " . 4053 "WHERE user_id = $user_id AND buddy_user_id = $buddy_user_id"; 4054 4055 $res = mysql_query($sql, $conn); 4056 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 4057 if (mysql_num_rows($res)) { 4058 $row = mysql_fetch_array($res); 4059 return $row[0]; 4060 } else { 4061 return NULL; 4062 } 4063 } 4064 4065 /** 4066 * This function adds a buddy for a user. It will return the 4067 * pm_buddy_id for the new buddy. If the buddy already exists, 4068 * it will return the existing pm_buddy_id. If a non existant 4069 * user_id is used for the buddy_user_id, the function will 4070 * return NULL. 4071 * @param buddy_user_id - The user_id that has to be added as a buddy. 4072 * @param user_id - The user_id the buddy has to be added for or 4073 * NULL to use the current user (default). 4074 */ 4075 function phorum_db_pm_buddy_add($buddy_user_id, $user_id = NULL) 4076 { 4077 $PHORUM = $GLOBALS['PHORUM']; 4078 $conn = phorum_db_mysql_connect(); 4079 settype($buddy_user_id, "int"); 4080 if (is_null($user_id)) $user_id = $PHORUM["user"]["user_id"]; 4081 settype($user_id, "int"); 4082 4083 // Check if the buddy_user_id is a valid user_id. 4084 $valid = phorum_db_user_get($buddy_user_id, false); 4085 if (! $valid) return NULL; 4086 4087 $pm_buddy_id = phorum_db_pm_is_buddy($buddy_user_id); 4088 if (is_null($pm_buddy_id)) { 4089 $sql = "INSERT INTO {$PHORUM["pm_buddies_table"]} SET " . 4090 "user_id = $user_id, " . 4091 "buddy_user_id = $buddy_user_id"; 4092 $res = mysql_query($sql, $conn); 4093 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 4094 $pm_buddy_id = mysql_insert_id($conn); 4095 } 4096 4097 return $pm_buddy_id; 4098 } 4099 4100 /** 4101 * This function deletes a buddy for a user. 4102 * @param buddy_user_id - The user_id that has to be deleted as a buddy. 4103 * @param user_id - The user_id the buddy has to be delete for or 4104 * NULL to use the current user (default). 4105 */ 4106 function phorum_db_pm_buddy_delete($buddy_user_id, $user_id = NULL) 4107 { 4108 $PHORUM = $GLOBALS['PHORUM']; 4109 $conn = phorum_db_mysql_connect(); 4110 settype($buddy_user_id, "int"); 4111 if (is_null($user_id)) $user_id = $PHORUM["user"]["user_id"]; 4112 settype($user_id, "int"); 4113 4114 $sql = "DELETE FROM {$PHORUM["pm_buddies_table"]} WHERE " . 4115 "buddy_user_id = $buddy_user_id AND user_id = $user_id"; 4116 $res = mysql_query($sql, $conn); 4117 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 4118 return $res; 4119 } 4120 4121 /** 4122 * This function retrieves a list of buddies for a user. 4123 * @param user_id - The user_id for which to retrieve the buddies 4124 * or NULL to user the current user (default). 4125 * @param find_mutual - Wheter to find mutual buddies or not (default not). 4126 */ 4127 function phorum_db_pm_buddy_list($user_id = NULL, $find_mutual = false) 4128 { 4129 $PHORUM = $GLOBALS['PHORUM']; 4130 $conn = phorum_db_mysql_connect(); 4131 if (is_null($user_id)) $user_id = $PHORUM["user"]["user_id"]; 4132 settype($user_id, "int"); 4133 4134 // Get all buddies for this user. 4135 $sql = "SELECT buddy_user_id FROM {$PHORUM["pm_buddies_table"]} " . 4136 "WHERE user_id = $user_id"; 4137 $res = mysql_query($sql, $conn); 4138 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 4139 4140 $buddies = array(); 4141 if (mysql_num_rows($res)) { 4142 while ($row = mysql_fetch_array($res)) { 4143 $buddies[$row[0]] = array ( 4144 'user_id' => $row[0] 4145 ); 4146 } 4147 } 4148 4149 // If we do not have to lookup mutual buddies, we're done. 4150 if (! $find_mutual) return $buddies; 4151 4152 // Initialize mutual buddy value. 4153 foreach ($buddies as $id => $data) { 4154 $buddies[$id]["mutual"] = false; 4155 } 4156 4157 // Get all mutual buddies. 4158 $sql = "SELECT DISTINCT a.buddy_user_id " . 4159 "FROM {$PHORUM["pm_buddies_table"]} as a, {$PHORUM["pm_buddies_table"]} as b " . 4160 "WHERE a.user_id=$user_id " . 4161 "AND b.user_id=a.buddy_user_id " . 4162 "AND b.buddy_user_id=$user_id"; 4163 $res = mysql_query($sql, $conn); 4164 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 4165 4166 if (mysql_num_rows($res)) { 4167 while ($row = mysql_fetch_array($res)) { 4168 $buddies[$row[0]]["mutual"] = true; 4169 } 4170 } 4171 4172 return $buddies; 4173 } 4174 4175 /** 4176 * This function returns messages or threads which are newer or older 4177 * than the given timestamp 4178 * 4179 * $time - holds the timestamp the comparison is done against 4180 * $forum - get Threads from this forum 4181 * $mode - should we compare against datestamp (1) or modifystamp (2) 4182 * 4183 */ 4184 function phorum_db_prune_oldThreads($time,$forum=0,$mode=1) { 4185 4186 $PHORUM = $GLOBALS['PHORUM']; 4187 4188 settype($time, "int"); 4189 settype($forum, "int"); 4190 settype($mode, "int"); 4191 4192 $conn = phorum_db_mysql_connect(); 4193 $numdeleted=0; 4194 4195 $compare_field = "datestamp"; 4196 if($mode == 2) { 4197 $compare_field = "modifystamp"; 4198 } 4199 4200 $forummode=""; 4201 if($forum > 0) { 4202 $forummode=" AND forum_id = $forum"; 4203 } 4204 4205 // retrieving which threads to delete 4206 $sql = "select thread from {$PHORUM['message_table']} where $compare_field < $time AND parent_id=0 $forummode"; 4207 4208 $res = mysql_query($sql, $conn); 4209 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 4210 4211 $ret=array(); 4212 while($row=mysql_fetch_row($res)) { 4213 $ret[]=$row[0]; 4214 } 4215 4216 $thread_ids=implode(",",$ret); 4217 4218 if(count($ret)) { 4219 // deleting the messages/threads 4220 $sql="delete from {$PHORUM['message_table']} where thread IN ($thread_ids)"; 4221 $res = mysql_query($sql, $conn); 4222 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 4223 4224 $numdeleted = mysql_affected_rows($conn); 4225 if($numdeleted < 0) { 4226 $numdeleted=0; 4227 } 4228 4229 // deleting the associated notification-entries 4230 $sql="delete from {$PHORUM['subscribers_table']} where thread IN ($thread_ids)"; 4231 $res = mysql_query($sql, $conn); 4232 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 4233 4234 4235 // optimizing the message-table 4236 $sql="optimize table {$PHORUM['message_table']}"; 4237 $res = mysql_query($sql, $conn); 4238 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 4239 } 4240 4241 return $numdeleted; 4242 } 4243 4244 /** 4245 * split thread 4246 */ 4247 function phorum_db_split_thread($message, $forum_id) 4248 { 4249 settype($message, "int"); 4250 settype($forum_id, "int"); 4251 4252 if($message > 0 && $forum_id > 0){ 4253 // get message tree for update thread id 4254 $tree =phorum_db_get_messagetree($message, $forum_id); 4255 $queries =array(); 4256 $queries[0]="UPDATE {$GLOBALS['PHORUM']['message_table']} SET thread='$message', parent_id='0' WHERE message_id ='$message'"; 4257 $queries[1]="UPDATE {$GLOBALS['PHORUM']['message_table']} SET thread='$message' WHERE message_id IN ($tree)"; 4258 phorum_db_run_queries($queries); 4259 } 4260 } 4261 4262 /** 4263 * This function returns the maximum message-id in the database 4264 */ 4265 function phorum_db_get_max_messageid() { 4266 $PHORUM = $GLOBALS["PHORUM"]; 4267 4268 $conn = phorum_db_mysql_connect(); 4269 $maxid = 0; 4270 4271 $sql="SELECT max(message_id) from ".$PHORUM["message_table"]; 4272 $res = mysql_query($sql, $conn); 4273 4274 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 4275 4276 if (mysql_num_rows($res) > 0){ 4277 $row = mysql_fetch_row($res); 4278 $maxid = $row[0]; 4279 } 4280 4281 return $maxid; 4282 } 4283 4284 /** 4285 * This function increments the viewcount for a post 4286 */ 4287 4288 function phorum_db_viewcount_inc($message_id) { 4289 if($message_id < 1 || !is_numeric($message_id)) { 4290 return false; 4291 } 4292 4293 $conn = phorum_db_mysql_connect(); 4294 $sql="UPDATE ".$GLOBALS['PHORUM']['message_table']." SET viewcount=viewcount+1 WHERE message_id=$message_id"; 4295 $res = mysql_query($sql, $conn); 4296 4297 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 4298 4299 4300 return true; 4301 4302 } 4303 4304 4305 function phorum_db_get_custom_field_users($field_id,$field_content,$match) { 4306 4307 4308 $field_id=(int)$field_id; 4309 $field_content=mysql_escape_string($field_content); 4310 4311 $conn = phorum_db_mysql_connect(); 4312 4313 if($match) { 4314 $compval="LIKE"; 4315 } else { 4316 $compval="="; 4317 } 4318 4319 $sql = "select user_id from {$GLOBALS['PHORUM']['user_custom_fields_table']} where type=$field_id and data $compval '$field_content'"; 4320 $res = mysql_query($sql, $conn); 4321 4322 if ($err = mysql_error($conn)) phorum_db_mysql_error("$err: $sql"); 4323 4324 if(mysql_num_rows($res)) { 4325 $retval=array(); 4326 while ($row = mysql_fetch_row($res)){ 4327 $retval[$row[0]]=$row[0]; 4328 } 4329 } else { 4330 $retval=NULL; 4331 } 4332 4333 return $retval; 4334 4335 } 4336 4337 /** 4338 * Translates a message searching meta query into a real SQL WHERE 4339 * statement for this database backend. The meta query can be used to 4340 * define extended SQL queries, based on a meta description of the 4341 * search that has to be performed on the database. 4342 * 4343 * The meta query is an array, containing: 4344 * - query conditions 4345 * - grouping using "(" and ")" 4346 * - AND/OR specifications using "AND" and "OR". 4347 * 4348 * The query conditions are arrays, containing the following elements: 4349 * 4350 * - condition 4351 * 4352 * A description of a condition. The syntax for this is: 4353 * <field name to query> <operator> <match specification> 4354 * 4355 * The <field name to query> is a field in the message query that 4356 * we are running in this function. 4357 * 4358 * The <operator> can be one of "=", "!=", "<", "<=", ">", ">=". 4359 * Note that there is nothing like "LIKE" or "NOT LIKE". If a "LIKE" 4360 * query has to be done, then that is setup through the 4361 * <match specification> (see below). 4362 * 4363 * The <match specification> tells us with what the field should be 4364 * matched. The string "QUERY" inside the specification is preserved to 4365 * specify at which spot in the query the "query" element from the 4366 * condition array should be inserted. If "QUERY" is not available in 4367 * the specification, then a match is made on the exact value in the 4368 * specification. To perform "LIKE" searches (case insensitive wildcard 4369 * searches), you can use the "*" wildcard character in the specification 4370 * to do so. 4371 * 4372 * - query 4373 * 4374 * The data to use in the query, in case the condition element has a 4375 * <match specification> that uses "QUERY" in it. 4376 * 4377 * Example: 4378 * 4379 * $metaquery = array( 4380 * array( 4381 * "condition" => "field1 = *QUERY*", 4382 * "query" => "test data" 4383 * ), 4384 * "AND", 4385 * "(", 4386 * array("condition" => "field2 = whatever"), 4387 * "OR", 4388 * array("condition" => "field2 = something else"), 4389 * ")" 4390 * ); 4391 * 4392 * For MySQL, this would be turned into the MySQL WHERE statement: 4393 * ... WHERE field1 LIKE '%test data%' 4394 * AND (field2 = 'whatever' OR field2 = 'something else') 4395 * 4396 * @param $metaquery - A meta query description array. 4397 * @return $return - An array containing two elements. The first element 4398 * is either true or false, based on the success state 4399 * of the function call (false means that there was an 4400 * error). The second argument contains either a 4401 * WHERE statement or an error message. 4402 */ 4403 function phorum_db_metaquery_compile($metaquery) 4404 { 4405 $where = ''; 4406 4407 $expect_condition = true; 4408 $expect_groupstart = true; 4409 $expect_groupend = false; 4410 $expect_combine = false; 4411 $in_group = 0; 4412 4413 foreach ($metaquery as $part) 4414 { 4415 // Found a new condition. 4416 if ($expect_condition && is_array($part)) 4417 { 4418 $cond = trim($part["condition"]); 4419 if (preg_match('/^([\w_\.]+)\s+(!?=|<=?|>=?)\s+(\S*)$/', $cond, $m)) 4420 { 4421 $field = $m[1]; 4422 $comp = $m[2]; 4423 $match = $m[3]; 4424 4425 $matchtokens = preg_split( 4426 '/(\*|QUERY|NULL)/', 4427 $match, -1, 4428 PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY 4429 ); 4430 4431 $matchsql = "'"; 4432 $is_like_query = false; 4433 foreach ($matchtokens as $m) { 4434 if ($m == '*') { 4435 $is_like_query = true; 4436 $matchsql .= '%'; 4437 } elseif ($m == 'QUERY') { 4438 $matchsql .= mysql_escape_string($part["query"]); 4439 } else { 4440 $matchsql .= mysql_escape_string($m); 4441 } 4442 } 4443 $matchsql .= "'"; 4444 4445 if ($is_like_query) 4446 { 4447 if ($comp == '=') { $comp = ' LIKE '; } 4448 elseif ($comp == '!=') { $comp = ' NOT LIKE '; } 4449 else return array( 4450 false, 4451 "Illegal metaquery token " . htmlspecialchars($cond) . 4452 ": wildcard match does not combine with $comp operator" 4453 ); 4454 } 4455 4456 $where .= "$field $comp $matchsql "; 4457 } else { 4458 return array( 4459 false, 4460 "Illegal metaquery token " . htmlspecialchars($cond) . 4461 ": condition does not match the required format" 4462 ); 4463 } 4464 4465 $expect_condition = false; 4466 $expect_groupstart = false; 4467 $expect_groupend = $in_group; 4468 $expect_combine = true; 4469 } 4470 // Found a new group start. 4471 elseif ($expect_groupstart && $part == '(') 4472 { 4473 $where .= "("; 4474 $in_group ++; 4475 4476 $expect_condition = true; 4477 $expect_groupstart = false; 4478 $expect_groupend = false; 4479 $expect_combine = false; 4480 } 4481 // Found a new group end. 4482 elseif ($expect_groupend && $part == ')') 4483 { 4484 $where .= ") "; 4485 $in_group --; 4486 4487 $expect_condition = false; 4488 $expect_groupstart = false; 4489 $expect_groupend = $in_group; 4490 $expect_combine = true; 4491 } 4492 // Found a combine token (AND or OR). 4493 elseif ($expect_combine && preg_match('/^(OR|AND)$/i', $part, $m)) 4494 { 4495 $where .= strtoupper($m[1]) . " "; 4496 4497 $expect_condition = true; 4498 $expect_groupstart = true; 4499 $expect_groupend = false; 4500 $expect_combine = false; 4501 } 4502 // Unexpected or illegal token. 4503 else 4504 { 4505 die("Internal error: unexpected token in metaquery description: " . 4506 (is_array($part) ? "condition" : htmlspecialchars($part))); 4507 } 4508 } 4509 4510 if ($expect_groupend) die ("Internal error: unclosed group in metaquery"); 4511 4512 // If the metaquery is empty, then provide a safe true WHERE statement. 4513 if ($where == '') { $where = "1 = 1"; } 4514 4515 return array(true, $where); 4516 } 4517 4518 /** 4519 * Run a search on the messages, using a metaquery. See the documentation 4520 * for the phorum_db_metaquery_compile() function for more info on the 4521 * metaquery syntax. 4522 * 4523 * The query that is run here, does create a view on the messages, which 4524 * includes some thread and user info. This is used so these can also 4525 * be taken into account when selecting messages. For the condition elements 4526 * in the meta query, you can use fully qualified field names for the 4527 * <field name to query>. You can use message.*, user.* and thread.* for this. 4528 * 4529 * The primary goal for this function is to provide a backend for the 4530 * message pruning interface. 4531 * 4532 * @param $metaquery - A metaquery array. 4533 * @return $messages - An array of message records. 4534 */ 4535 function phorum_db_metaquery_messagesearch($metaquery) 4536 { 4537 $PHORUM = $GLOBALS["PHORUM"]; 4538 4539 // Compile the metaquery into a where statement. 4540 list($success, $where) = phorum_db_metaquery_compile($metaquery); 4541 if (!$success) die($where); 4542 4543 // Build the SQL query. 4544 $sql = " 4545 SELECT message.message_id, 4546 message.thread, 4547 message.parent_id, 4548 message.forum_id, 4549 message.subject, 4550 message.author, 4551 message.datestamp, 4552 message.body, 4553 message.ip, 4554 message.status, 4555 message.user_id, 4556 user.username user_username, 4557 thread.closed thread_closed, 4558 thread.modifystamp thread_modifystamp, 4559 thread.thread_count thread_count 4560 FROM {$PHORUM["message_table"]} as thread, 4561 {$PHORUM["message_table"]} as message 4562 LEFT JOIN {$PHORUM["user_table"]} user 4563 ON message.user_id = user.user_id 4564 WHERE message.thread = thread.message_id AND 4565 ($where) 4566 ORDER BY message_id ASC 4567 "; 4568 4569 $conn = phorum_db_mysql_connect(); 4570 $res = mysql_query($sql, $conn); 4571 if ($err = mysql_error($conn)) { 4572 phorum_db_mysql_error("$err: $sql"); 4573 return NULL; 4574 } else { 4575 $messages = array(); 4576 if(mysql_num_rows($res)) { 4577 while ($row = mysql_fetch_assoc($res)) { 4578 $messages[$row["message_id"]] = $row; 4579 } 4580 } 4581 return $messages; 4582 } 4583 } 4584 4585 4586 /** 4587 * This function creates the tables needed in the database. 4588 */ 4589 4590 function phorum_db_create_tables() 4591 { 4592 $PHORUM = $GLOBALS["PHORUM"]; 4593 4594 $conn = phorum_db_mysql_connect(); 4595 4596 $retmsg = ""; 4597 4598 $queries = array( 4599 4600 // create tables 4601 "CREATE TABLE {$PHORUM['forums_table']} ( forum_id int(10) unsigned NOT NULL auto_increment, name varchar(50) NOT NULL default '', active smallint(6) NOT NULL default 0, description text NULL, template varchar(50) NOT NULL default '', folder_flag tinyint(1) NOT NULL default '0', parent_id int(10) unsigned NOT NULL default '0', list_length_flat int(10) unsigned NOT NULL default '0', list_length_threaded int(10) unsigned NOT NULL default '0', moderation int(10) unsigned NOT NULL default '0', threaded_list tinyint(4) NOT NULL default '0', threaded_read tinyint(4) NOT NULL default '0', float_to_top tinyint(4) NOT NULL default '0', check_duplicate tinyint(4) NOT NULL default '0', allow_attachment_types varchar(100) NOT NULL default '', max_attachment_size int(10) unsigned NOT NULL default '0', max_totalattachment_size int(10) unsigned NOT NULL default '0', max_attachments int(10) unsigned NOT NULL default '0', pub_perms int(10) unsigned NOT NULL default '0', reg_perms int(10) unsigned NOT NULL default '0', display_ip_address smallint(5) unsigned NOT NULL default '1', allow_email_notify smallint(5) unsigned NOT NULL default '1', language varchar(100) NOT NULL default 'english', email_moderators tinyint(1) NOT NULL default '0', message_count int(10) unsigned NOT NULL default '0', sticky_count int(10) unsigned NOT NULL default '0', thread_count int(10) unsigned NOT NULL default '0', last_post_time int(10) unsigned NOT NULL default '0', display_order int(10) unsigned NOT NULL default '0', read_length int(10) unsigned NOT NULL default '0', vroot int(10) unsigned NOT NULL default '0', edit_post tinyint(1) NOT NULL default '1',template_settings text NULL, count_views tinyint(1) unsigned NOT NULL default '0', display_fixed tinyint(1) unsigned NOT NULL default '0', reverse_threading tinyint(1) NOT NULL default '0',inherit_id int(10) unsigned NULL default NULL, PRIMARY KEY (forum_id), KEY name (name), KEY active (active,parent_id), KEY group_id (parent_id)) TYPE=MyISAM", 4602 "CREATE TABLE {$PHORUM['message_table']} ( message_id int(10) unsigned NOT NULL auto_increment, forum_id int(10) unsigned NOT NULL default '0', thread int(10) unsigned NOT NULL default '0', parent_id int(10) unsigned NOT NULL default '0', author varchar(37) NOT NULL default '', subject varchar(255) NOT NULL default '', body text NOT NULL, email varchar(100) NOT NULL default '', ip varchar(255) NOT NULL default '', status tinyint(4) NOT NULL default '2', msgid varchar(100) NOT NULL default '', modifystamp int(10) unsigned NOT NULL default '0', user_id int(10) unsigned NOT NULL default '0', thread_count int(10) unsigned NOT NULL default '0', moderator_post tinyint(3) unsigned NOT NULL default '0', sort tinyint(4) NOT NULL default '2', datestamp int(10) unsigned NOT NULL default '0', meta mediumtext NULL, viewcount int(10) unsigned NOT NULL default '0', closed tinyint(4) NOT NULL default '0', PRIMARY KEY (message_id), KEY thread_message (thread,message_id), KEY thread_forum (thread,forum_id), KEY special_threads (sort,forum_id), KEY status_forum (status,forum_id), KEY list_page_float (forum_id,parent_id,modifystamp), KEY list_page_flat (forum_id,parent_id,thread), KEY post_count (forum_id,status,parent_id), KEY dup_check (forum_id,author,subject,datestamp), KEY forum_max_message (forum_id,message_id,status,parent_id), KEY last_post_time (forum_id,status,modifystamp), KEY next_prev_thread (forum_id,status,thread), KEY user_id (user_id) ) TYPE=MyISAM", 4603 "CREATE TABLE {$PHORUM['settings_table']} ( name varchar(255) NOT NULL default '', type enum('V','S') NOT NULL default 'V', data text NOT NULL, PRIMARY KEY (name)) TYPE=MyISAM", 4604 "CREATE TABLE {$PHORUM['subscribers_table']} ( user_id int(10) unsigned NOT NULL default '0', forum_id int(10) unsigned NOT NULL default '0', sub_type int(10) unsigned NOT NULL default '0', thread int(10) unsigned NOT NULL default '0', PRIMARY KEY (user_id,forum_id,thread), KEY forum_id (forum_id,thread,sub_type)) TYPE=MyISAM", 4605 "CREATE TABLE {$PHORUM['user_permissions_table']} ( user_id int(10) unsigned NOT NULL default '0', forum_id int(10) unsigned NOT NULL default '0', permission int(10) unsigned NOT NULL default '0', PRIMARY KEY (user_id,forum_id), KEY forum_id (forum_id,permission) ) TYPE=MyISAM", 4606 "CREATE TABLE {$PHORUM['user_table']} ( user_id int(10) unsigned NOT NULL auto_increment, username varchar(50) NOT NULL default '', password varchar(50) NOT NULL default '',cookie_sessid_lt varchar(50) NOT NULL default '', sessid_st varchar(50) NOT NULL default '', sessid_st_timeout int(10) unsigned NOT NULL default 0, password_temp varchar(50) NOT NULL default '', email varchar(100) NOT NULL default '', email_temp varchar(110) NOT NULL default '', hide_email tinyint(1) NOT NULL default '0', active tinyint(1) NOT NULL default '0', user_data text NULL, signature text NULL, threaded_list tinyint(4) NOT NULL default '0', posts int(10) NOT NULL default '0', admin tinyint(1) NOT NULL default '0', threaded_read tinyint(4) NOT NULL default '0', date_added int(10) unsigned NOT NULL default '0', date_last_active int(10) unsigned NOT NULL default '0', last_active_forum int(10) unsigned NOT NULL default '0', hide_activity tinyint(1) NOT NULL default '0',show_signature TINYINT( 1 ) DEFAULT '0' NOT NULL, email_notify TINYINT( 1 ) DEFAULT '0' NOT NULL, pm_email_notify TINYINT ( 1 ) DEFAULT '1' NOT NULL, tz_offset TINYINT( 2 ) DEFAULT '-99' NOT NULL,is_dst TINYINT( 1 ) DEFAULT '0' NOT NULL ,user_language VARCHAR( 100 ) NOT NULL default '',user_template VARCHAR( 100 ) NOT NULL default '', moderator_data text NULL, moderation_email tinyint(2) unsigned not null default 1, PRIMARY KEY (user_id), UNIQUE KEY username (username), KEY active (active), KEY userpass (username,password), KEY sessid_st (sessid_st), KEY cookie_sessid_lt (cookie_sessid_lt), KEY activity (date_last_active,hide_activity,last_active_forum), KEY date_added (date_added), KEY email_temp (email_temp) ) TYPE=MyISAM", 4607 "CREATE TABLE {$PHORUM['user_newflags_table']} ( user_id int(11) NOT NULL default '0', forum_id int(11) NOT NULL default '0', message_id int(11) NOT NULL default '0', PRIMARY KEY (user_id,forum_id,message_id) ) TYPE=MyISAM", 4608 "CREATE TABLE {$PHORUM['groups_table']} ( group_id int(11) NOT NULL auto_increment, name varchar(255) NOT NULL default '0', open tinyint(3) NOT NULL default '0', PRIMARY KEY (group_id) ) TYPE=MyISAM", 4609 "CREATE TABLE {$PHORUM['forum_group_xref_table']} ( forum_id int(11) NOT NULL default '0', group_id int(11) NOT NULL default '0', permission int(10) unsigned NOT NULL default '0', PRIMARY KEY (forum_id,group_id), KEY group_id (group_id) ) TYPE=MyISAM", 4610 "CREATE TABLE {$PHORUM['user_group_xref_table']} ( user_id int(11) NOT NULL default '0', group_id int(11) NOT NULL default '0', status tinyint(3) NOT NULL default '1', PRIMARY KEY (user_id,group_id) ) TYPE=MyISAM", 4611 "CREATE TABLE {$PHORUM['files_table']} ( file_id int(11) NOT NULL auto_increment, user_id int(11) NOT NULL default '0', filename varchar(255) NOT NULL default '', filesize int(11) NOT NULL default '0', file_data mediumtext NOT NULL, add_datetime int(10) unsigned NOT NULL default '0', message_id int(10) unsigned NOT NULL default '0', link varchar(10) NOT NULL default '', PRIMARY KEY (file_id), KEY add_datetime (add_datetime), KEY message_id_link (message_id,link)) TYPE=MyISAM", 4612 "CREATE TABLE {$PHORUM['banlist_table']} ( id int(11) NOT NULL auto_increment, forum_id int(11) NOT NULL default '0', type tinyint(4) NOT NULL default '0', pcre tinyint(4) NOT NULL default '0', string varchar(255) NOT NULL default '', PRIMARY KEY (id), KEY forum_id (forum_id)) TYPE=MyISAM", 4613 "CREATE TABLE {$PHORUM['search_table']} ( message_id int(10) unsigned NOT NULL default '0', forum_id int(10) unsigned NOT NULL default '0',search_text mediumtext NOT NULL, PRIMARY KEY (message_id), KEY forum_id (forum_id), FULLTEXT KEY search_text (search_text) ) TYPE=MyISAM", 4614 "CREATE TABLE {$PHORUM['user_custom_fields_table']} ( user_id INT DEFAULT '0' NOT NULL , type INT DEFAULT '0' NOT NULL , data TEXT NULL, PRIMARY KEY ( user_id , type )) TYPE=MyISAM", 4615 "CREATE TABLE {$PHORUM['pm_messages_table']} ( pm_message_id int(10) unsigned NOT NULL auto_increment, from_user_id int(10) unsigned NOT NULL default '0', from_username varchar(50) NOT NULL default '', subject varchar(100) NOT NULL default '', message text NOT NULL, datestamp int(10) unsigned NOT NULL default '0', meta mediumtext NULL, PRIMARY KEY(pm_message_id)) TYPE=MyISAM", 4616 "CREATE TABLE {$PHORUM['pm_folders_table']} ( pm_folder_id int(10) unsigned NOT NULL auto_increment, user_id int(10) unsigned NOT NULL default '0', foldername varchar(20) NOT NULL default '', PRIMARY KEY (pm_folder_id)) TYPE=MyISAM", 4617 "CREATE TABLE {$PHORUM['pm_xref_table']} ( pm_xref_id int(10) unsigned NOT NULL auto_increment, user_id int(10) unsigned NOT NULL default '0', pm_folder_id int(10) unsigned NOT NULL default '0', special_folder varchar(10), pm_message_id int(10) unsigned NOT NULL default '0', read_flag tinyint(1) NOT NULL default '0', reply_flag tinyint(1) NOT NULL default '0', PRIMARY KEY (pm_xref_id), KEY xref (user_id,pm_folder_id,pm_message_id), KEY read_flag (read_flag)) TYPE=MyISAM", 4618 "CREATE TABLE {$PHORUM['pm_buddies_table']} ( pm_buddy_id int(10) unsigned NOT NULL auto_increment, user_id int(10) unsigned NOT NULL default '0', buddy_user_id int(10) unsigned NOT NULL default '0', PRIMARY KEY pm_buddy_id (pm_buddy_id), UNIQUE KEY userids (user_id, buddy_user_id), KEY buddy_user_id (buddy_user_id)) TYPE=MyISAM", 4619 4620 ); 4621 foreach($queries as $sql){ 4622 $res = mysql_query($sql, $conn); 4623 if ($err = mysql_error($conn)){ 4624 $retmsg = "$err<br />"; 4625 phorum_db_mysql_error("$err: $sql"); 4626 break; 4627 } 4628 } 4629 4630 return $retmsg; 4631 } 4632 4633 // uses the database-dependant functions to escape a string 4634 function phorum_db_escape_string($str) { 4635 $str_tmp=mysql_real_escape_string($str); 4636 4637 return $str_tmp; 4638 } 4639 4640 /** 4641 * This function goes through an array of queries and executes them 4642 */ 4643 4644 function phorum_db_run_queries($queries){ 4645 $PHORUM = $GLOBALS["PHORUM"]; 4646 4647 $conn = phorum_db_mysql_connect(); 4648 4649 $retmsg = ""; 4650 4651 foreach($queries as $sql){ 4652 $res = mysql_query($sql, $conn); 4653 if ($err = mysql_error($conn)){ 4654 // skip duplicate column name errors 4655 if(!stristr($err, "duplicate column")){ 4656 $retmsg.= "$err<br />"; 4657 phorum_db_mysql_error("$err: $sql"); 4658 } 4659 } 4660 } 4661 4662 return $retmsg; 4663 } 4664 4665 /** 4666 * This function checks that a database connection can be made. 4667 */ 4668 4669 function phorum_db_check_connection(){ 4670 $conn = @phorum_db_mysql_connect(); 4671 4672 return ($conn) ? true : false; 4673 } 4674 4675 /** 4676 * handy little connection function. This allows us to not connect to the 4677 * server until a query is actually run. 4678 * NOTE: This is not a required part of abstraction 4679 */ 4680 4681 function phorum_db_mysql_connect(){ 4682 $PHORUM = $GLOBALS["PHORUM"]; 4683 4684 static $conn; 4685 if (empty($conn)){ 4686 $conn = mysql_connect($PHORUM["DBCONFIG"]["server"], $PHORUM["DBCONFIG"]["user"], $PHORUM["DBCONFIG"]["password"], true); 4687 mysql_select_db($PHORUM["DBCONFIG"]["name"], $conn); 4688 } 4689 return $conn; 4690 } 4691 4692 /** 4693 * error handling function 4694 * NOTE: This is not a required part of abstraction 4695 */ 4696 4697 function phorum_db_mysql_error($err){ 4698 4699 if(isset($GLOBALS['PHORUM']['error_logging'])) { 4700 $logsetting = $GLOBALS['PHORUM']['error_logging']; 4701 } else { 4702 $logsetting = ""; 4703 } 4704 $adminemail = $GLOBALS['PHORUM']['system_email_from_address']; 4705 $cache_dir = $GLOBALS['PHORUM']['cache']; 4706 4707 if (!defined("PHORUM_ADMIN")){ 4708 if($logsetting == 'mail') { 4709 include_once ("./include/email_functions.php"); 4710 4711 $data=array('mailmessage'=>"An SQL-error occured in your phorum-installation.\n\nThe error-message was:\n$err\n\n", 4712 'mailsubject'=>'Phorum: an SQL-error occured'); 4713 phorum_email_user(array($adminemail),$data); 4714 4715 } elseif($logsetting == 'file') { 4716 $fp = fopen($cache_dir."/phorum-sql-errors.log",'a'); 4717 fputs($fp,time().": $err\n"); 4718 fclose($fp); 4719 4720 } else { 4721 echo htmlspecialchars($err); 4722 } 4723 exit(); 4724 }else{ 4725 echo "<!-- $err -->"; 4726 } 4727 } 4728 4729 /** 4730 * This function will sanitize a mixed variable of data based on type 4731 * 4732 * @param $var The variable to be sanitized. Passed by reference. 4733 * @param $type Either int or not int. 4734 * @return null 4735 * 4736 */ 4737 function phorum_db_sanitize_mixed(&$var, $type){ 4738 if(is_array($var)){ 4739 foreach($var as $id => $val){ 4740 if($type=="int"){ 4741 $var[$id] = (int)$val; 4742 } else { 4743 $var[$id] = mysql_escape_string($val); 4744 } 4745 } 4746 } else { 4747 if($type=="int"){ 4748 $var = (int)$var; 4749 } else { 4750 $var = mysql_escape_string($var); 4751 } 4752 } 4753 } 4754 4755 /** 4756 * Checks that a value to be used as a field name contains only characters 4757 * that would appear in a field name. 4758 * 4759 * @param $field_name string to be checked 4760 * @return bool 4761 * 4762 */ 4763 function phorum_db_validate_field($field_name){ 4764 return (bool)preg_match('!^[a-zA-Z0-9_]+$!', $field_name); 4765 } 4766 4767 /** 4768 * This function is used by the sanity checking system in the 4769 * admin interface to determine how much data can be transferred 4770 * in one query. This is used to detect problems with uploads that 4771 * are larger than the database server can handle. 4772 * The function returns the size in bytes. For database implementations 4773 * which do not have this kind of limit, NULL can be returned. 4774 */ 4775 function phorum_db_maxpacketsize () 4776 { 4777 $conn = phorum_db_mysql_connect(); 4778 $res = mysql_query("SELECT @@global.max_allowed_packet",$conn); 4779 if (!$res) return NULL; 4780 if (mysql_num_rows($res)) { 4781 $row = mysql_fetch_array($res); 4782 return $row[0]; 4783 } 4784 return NULL; 4785 } 4786 4787 /** 4788 * This function is used by the sanity checking system to let the 4789 * database layer do sanity checks of its own. This function can 4790 * be used by every database layer to implement specific checks. 4791 * 4792 * The return value for this function should be exactly the same 4793 * as the return value expected for regular sanity checking 4794 * function (see include/admin/sanity_checks.php for information). 4795 * 4796 * There's no need to load the sanity_check.php file for the needed 4797 * constants, because this function should only be called from the 4798 * sanity checking system. 4799 */ 4800 function phorum_db_sanitychecks() 4801 { 4802 $PHORUM = $GLOBALS["PHORUM"]; 4803 4804 // Retrieve the MySQL server version. 4805 $conn = phorum_db_mysql_connect(); 4806 $res = mysql_query("SELECT @@global.version",$conn); 4807 if (!$res) return array( 4808 PHORUM_SANITY_WARN, 4809 "The database layer could not retrieve the version of the 4810 running MySQL server", 4811 "This probably means that you are running a really old MySQL 4812 server, which does not support \"SELECT @@global.version\" 4813 as an SQL command. If you are not running a MySQL server 4814 with version 4.0.18 or higher, then please upgrade your 4815 MySQL server. Else, contact the Phorum developers to see 4816 where this warning is coming from" 4817 ); 4818 4819 if (mysql_num_rows($res)) 4820 { 4821 $row = mysql_fetch_array($res); 4822 $verstr = preg_replace('/-.*$/', '', $row[0]); 4823 $ver = explode(".", $verstr); 4824 4825 // Version numbering format which is not recognized. 4826 if (count($ver) != 3) return array( 4827 PHORUM_SANITY_WARN, 4828 "The database layer was unable to recognize the MySQL server's 4829 version number \"" . htmlspecialchars($row[0]) . "\". Therefore, 4830 checking if the right version of MySQL is used is not possible.", 4831 "Contact the Phorum developers and report this specific 4832 version number, so the checking scripts can be updated." 4833 ); 4834 4835 settype($ver[0], 'int'); 4836 settype($ver[1], 'int'); 4837 settype($ver[2], 'int'); 4838 4839 // MySQL before version 4. 4840 if ($ver[0] < 4) return array( 4841 PHORUM_SANITY_CRIT, 4842 "The MySQL database server that is used is too old. The 4843 running version is \"" . htmlspecialchars($row[0]) . "\", 4844 while MySQL version 4.0.18 or higher is recommended.", 4845 "Upgrade your MySQL server to a newer version. If your 4846 website is hosted with a service provider, please contact 4847 the service provider to upgrade your MySQL database." 4848 ); 4849 4850 // MySQL before version 4.0.18, with full text search enabled. 4851 if (isset($PHORUM["DBCONFIG"]["mysql_use_ft"]) && 4852 $PHORUM["DBCONFIG"]["mysql_use_ft"] && 4853 $ver[0] == 4 && $ver[1] == 0 && $ver[2] < 18) return array( 4854 PHORUM_SANITY_WARN, 4855 "The MySQL database server that is used does not 4856 support all Phorum features. The running version is 4857 \"" . htmlspecialchars($row[0]) . "\", while MySQL version 4858 4.0.18 or higher is recommended.", 4859 "Upgrade your MySQL server to a newer version. If your 4860 website is hosted with a service provider, please contact 4861 the service provider to upgrade your MySQL database." 4862 ); 4863 4864 // All checks are okay. 4865 return array (PHORUM_SANITY_OK, NULL); 4866 } 4867 4868 return array( 4869 PHORUM_SANITY_CRIT, 4870 "An unexpected problem was found in running the sanity 4871 check function phorum_db_sanitychecks().", 4872 "Contact the Phorum developers to find out what the problem is." 4873 ); 4874 } 4875 4876 ?>
titre
Description
Corps
titre
Description
Corps
titre
Description
Corps
titre
Corps
| Généré le : Thu Nov 29 12:22:27 2007 | par Balluche grâce à PHPXref 0.7 |
|
|