public static function Add($category_name)
{
$con = new DbConnection();
$query = "INSERT INTO forum_categories (category_name) VALUES (?)";
$st = $con->prepare($query);
$st->bind_param("s", $category_name);
$st->execute();
$con->close();
}
public static function Update($setting)
{
if (!$setting->isNull()) {
$s_value = $setting->value;
$s_id = $setting->id;
$con = new DbConnection();
$query = "UPDATE settings SET setting_value = ? WHERE setting_id = ?";
$st = $con->prepare($query);
$st->bind_param("si", $s_value, $s_id);
$st->execute();
$con->close();
}
}
public static function GetAll()
{
$types = array();
$con = new DbConnection();
$query = "SELECT type_id, type_name FROM server_types";
$st = $con->prepare($query);
$st->bind_result($t_id, $t_name);
$st->execute();
while ($st->fetch()) {
$type = new ServerType();
$type->id = $t_id;
$type->name = $t_name;
$types[] = $type;
}
$con->close();
return $types;
}
public static function GetAll()
{
$forums = array();
$con = new DbConnection();
$query = "SELECT f.forum_id, f.forum_name, f.forum_description, f.category_id, c.category_name FROM forums f\n\t\tJOIN forum_categories c ON c.category_id = f.category_id\n\t\tORDER BY f.category_id";
$st = $con->prepare($query);
$st->bind_result($f_id, $f_name, $f_desc, $c_id, $c_name);
$st->execute();
while ($st->fetch()) {
$forum = new Forum();
$forum->id = $f_id;
$forum->name = $f_name;
$forum->description = $f_desc;
$forum->category->id = $c_id;
$forum->category->name = $c_name;
$forums[] = $forum;
}
$con->close();
return $forums;
}
public static function GetByForum($forum_id)
{
$topics = array();
$con = new DbConnection();
$query = "SELECT t.topic_id, t.forum_id, t.topic_title, t.user_id, u.username, t.created_on FROM topics t\n\t\t JOIN users u ON u.user_id = t.user_id\n\t\t WHERE forum_id = ?";
$st = $con->prepare($query);
$st->bind_param("i", $forum_id);
$st->bind_result($t_id, $f_id, $t_title, $u_id, $u_name, $t_createdOn);
$st->execute();
while ($st->fetch()) {
$topic = new Topic();
$topic->id = $t_id;
$topic->forumId = $f_id;
$topic->title = $t_title;
$topic->user->id = $u_id;
$topic->user->username;
$topic->createdOn = $t_createdOn;
$topics[] = $topic;
}
$con->close();
return $topics;
}
public static function GetAll()
{
$servers = array();
$con = new DbConnection();
$query = "SELECT s.server_id, s.server_name, s.server_address, s.server_port, s.server_capacity, st.type_id, st.type_name FROM servers s JOIN server_types st ON st.type_id = s.server_type";
$st = $con->prepare($query);
$st->bind_result($s_id, $s_name, $s_addr, $s_port, $s_capacity, $t_id, $t_name);
$st->execute();
while ($st->fetch()) {
$server = new Server();
$server->id = $s_id;
$server->name = $s_name;
$server->address = $s_addr;
$server->port = $s_port;
$server->capacity = $s_capacity;
$server->type->id = $t_id;
$server->type->name = $t_name;
$servers[] = $server;
}
$con->close();
return $servers;
}
public static function GetByTopic($topic_id, $limit = 25)
{
$posts = array();
$con = new DbConnection();
$query = "SELECT p.post_id, p.topic_id, p.post_text, p.user_id, u.username, p.posted_on, p.edited_on FROM topic_posts p\n\t\t JOIN users u ON u.user_id = p.user_id\n\t\t WHERE p.topic_id = ?\n\t\t ORDER BY p.post_id LIMIT ?";
$st = $con->prepare($query);
$st->bind_param("ii", $topic_id, $limit);
$st->bind_result($p_id, $t_id, $p_text, $u_id, $u_name, $p_postedOn, $p_editedOn);
$st->execute();
while ($st->fetch()) {
$post = new TopicPost();
$post->id = $p_id;
$post->text = $p_text;
$post->topicId = $t_id;
$post->postedOn = $p_postedOn;
$post->editedOn = $p_editedOn;
$post->user->id = $u_id;
$post->user->name = $u_name;
$posts[] = $post;
}
$con->close();
return $posts;
}
public static function Delete($p_id)
{
DbPermission::RemoveFromAllGroup($p_id);
$con = new DbConnection();
$query = "DELETE FROM permissions WHERE permission_id = ?";
$st = $con->prepare($query);
$st->bind_param("i", $p_id);
$st->execute();
$con->close();
}
public static function GetById($user_id)
{
$user = new User();
$query = "SELECT user_id, username, first_name, last_name, salt, password, hash_type, email, created_on FROM users WHERE user_id = ?";
$con = new DbConnection();
$st = $con->prepare($query);
$st->bind_param("i", $user_id);
$st->bind_result($u_id, $u_name, $u_firstName, $u_lastName, $u_salt, $u_password, $u_hashType, $u_email, $u_createdOn);
$st->execute();
if ($st->fetch()) {
$user->id = $u_id;
$user->username = $u_name;
$user->firstName = $u_firstName;
$user->lastName = $u_lastName;
$user->salt = $u_salt;
$user->password = $u_password;
$user->hashType = $u_hashType;
$user->email = $u_email;
$user->createdOn = $u_createdOn;
}
$con->close();
return $user;
}
public static function GetLastNews($count = 10)
{
$news = array();
$con = new DbConnection();
$query = "SELECT n.news_id, n.news_title, n.news_content, n.news_posted_on, n.user_id, u.username, u.first_name, u.last_name FROM news n JOIN users u ON u.user_id = n.user_id ORDER BY n.news_id DESC LIMIT ?";
$st = $con->prepare($query);
$st->bind_param("i", $count);
$st->bind_result($n_id, $n_title, $n_content, $n_postedOn, $u_id, $u_username, $u_firstName, $u_lastName);
$st->execute();
while ($st->fetch()) {
$n = new News();
$n->id = $n_id;
$n->title = $n_title;
$n->content = $n_content;
$n->postedOn = $n_postedOn;
$n->userId = $u_id;
$n->user->username = $u_username;
$n->user->firstName = $u_firstName;
$n->user->lastName = $u_lastName;
$news[] = $n;
}
$con->close();
return $news;
}
public static function GetById($g_id)
{
$group = new Group();
$con = new DbConnection();
$query = "SELECT group_id, group_name FROM groups WHERE group_id = ?";
$st = $con->prepare($query);
$st->bind_param("i", $g_id);
$st->bind_result($group_id, $g_name);
$st->execute();
if ($st->fetch()) {
$group->id = $group_id;
$group->name = $g_name;
}
$con->close();
if (!$group->isNull()) {
$con = new DbConnection();
$query = "SELECT p.permission_id, p.permission_name, p.permission_value, p.permission_description FROM groups_permissions gp JOIN permissions p ON gp.permission_id = p.permission_id WHERE gp.group_id = ?";
$st = $con->prepare($query);
$st->bind_param("i", $g_id);
$st->bind_result($p_id, $p_name, $p_value, $p_desc);
$st->execute();
while ($st->fetch()) {
$data = array('permission_id' => $p_id, 'permission_name' => $p_name, 'permission_value' => $p_value, 'permission_description' => $p_desc);
$perm = new Permission($data);
$group->permissions->add($perm);
}
$con->close();
}
return $group;
}
