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 createDatabaseSchema($schemaName)
{
$dbConnection = new DbConnection(NULL);
$mySQLi = $dbConnection->getMySQLi();
$queryResult = $mySQLi->query("DROP DATABASE IF EXISTS `{$schemaName}`");
if ($queryResult === FALSE) {
throw new Exception("Error creating database '{$schemaName}' - " . $mySQLi->error);
}
$queryResult = $mySQLi->query("CREATE DATABASE IF NOT EXISTS `{$schemaName}`");
if ($queryResult === FALSE) {
throw new Exception("Error creating database '{$schemaName}' - " . $mySQLi->error);
}
$queryResult->close();
$dbConnection->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;
}
}
$stmt = $pdo->conn->prepare($sql);
$exe = $stmt->execute($params);
if ($exe) {
echo returnJson(true, 'บ ันทึกสำเร็จ', 'บันทึกสำเร็จ', './index.php?page=list-material_recipe');
} else {
echo returnJson(false, 'เกิดข้อผิดพลาด', 'บันทึก ไม่สำเร็จ [ ' . $sql . ' ]', '');
}
}
break;
case 'delete':
// delete ลบข้อมูล
try {
$pdo->conn = $pdo->open();
$sql = 'DELETE FROM material_recipe WHERE recipe_id =:id';
$stmt = $pdo->conn->prepare($sql);
$exe = $stmt->execute(array(':id' => $_POST['id']));
if ($exe) {
echo returnJson(true, 'ลบข้อมูล', 'ลบสำเร็จ', './index.php?page=list-material_recipe');
} else {
echo returnJson(false, 'เกิดข้อผิดพลาด', 'ลบ ไม่สำเร็จ [ ' . $sql . ' ]', '');
}
} catch (Exception $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die;
}
$pdo->close();
break;
default:
break;
}
/**
* mixed XMLData(string $db, string $table, array $options = null)
*
* Returns the content of a table in XML format (Propel model more or less)
*
* @param string $db database name
* @param string $table table name
* @param array $options (optional) (from, to, start_table, end_table)
* @return mixed false if error occurs, string if ok
* @access public
* @static
* @see DUMP_CRLF
*/
public static function XMLData($db, $table, $options = null)
{
$localConn = new DbConnection();
if (!$localConn->connect()) {
return false;
}
$localQuery = 'SHOW COLUMNS FROM ' . self::backQuote($table) . ' FROM ' . self::backQuote($db);
if (!$localConn->exec($localQuery)) {
return false;
}
for ($i = 0; $row = $localConn->fetchRow(MYSQL_ASSOC); $i++) {
$fields[$i] = $row['Field'];
$types[$i] = $row['Type'];
$nulls[$i] = strtoupper($row['Null']) == 'YES' ? 'true' : 'false';
$keys[$i] = $row['Key'];
$defaults[$i] = $row['Default'];
$extras[$i] = $row['Extra'];
}
$numFields = count($fields);
// Defines the offsets to use
$limitClause = isset($options['to']) && $options['to'] > 0 && (isset($options['from']) && $options['from'] >= 0) ? ' LIMIT ' . ($options['from'] > 0 ? $options['from'] . ', ' : '') . $options['to'] : '';
$localQuery = 'SELECT * FROM ' . self::backQuote($db) . '.' . self::backQuote($table) . $limitClause;
if (!$localConn->exec($localQuery)) {
return false;
}
if ($localConn->numRows() == 0) {
return '';
}
$buffer = ' <table name="' . $table . '">' . DUMP_CRLF;
while ($record = $localConn->fetchRow(MYSQL_ASSOC)) {
$buffer .= ' <row>' . DUMP_CRLF;
for ($i = 0; $i < $numFields; $i++) {
$element = ' name="' . $fields[$i] . '"' . ' type="' . $types[$i] . '"' . ($nulls[$i] ? ' null="' . $nulls[$i] . '"' : '') . ($keys[$i] ? ' key="' . $keys[$i] . '"' : '') . ($defaults[$i] ? ' default="' . $defaults[$i] . '"' : '') . ($extras[$i] ? ' extra="' . $extras[$i] . '"' : '');
if (!is_null($record[$fields[$i]])) {
$buffer .= ' <column' . $element . '>' . htmlspecialchars($record[$fields[$i]]) . '</column>' . DUMP_CRLF;
} else {
$buffer .= ' <column' . $element . ' />' . DUMP_CRLF;
}
}
$buffer .= ' </row>' . DUMP_CRLF;
}
$localConn->close();
$buffer .= ' </table>' . DUMP_CRLF;
return $buffer;
}
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();
}
/**
* Controlling vars
*/
$tab = "admin";
$nav = "dump";
/**
* Checking permissions
*/
require_once "../auth/login_check.php";
loginCheck(OPEN_PROFILE_ADMINISTRATOR, false);
// Not in DEMO to prevent users' malice
require_once "../lib/Dump.php";
@set_time_limit(OPEN_EXEC_TIME_LIMIT);
$auxConn = new DbConnection();
if (!$auxConn->connect()) {
$auxConn->close();
Error::connection($auxConn);
}
$localQuery = 'SHOW TABLE STATUS FROM ' . Dump::backQuote(OPEN_DATABASE);
if (!$auxConn->exec($localQuery)) {
$auxConn->close();
Error::connection($auxConn);
}
/**
* Show page
*/
$title = _("Optimize Database");
require_once "../layout/header.php";
/**
* Breadcrumb
*/
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;
}
