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