/** * Returns all relevant articles for a FAQ record with the same language * * @param integer $recordId FAQ ID * @param string $question FAQ title * @param string $keywords FAQ keywords * * @return array */ public function getAllRelatedById($recordId, $question, $keywords) { $terms = str_replace('-', ' ', $question) . $keywords; $search = PMF_Search_Factory::create($this->_config, array('database' => PMF_Db::getType())); $search->setTable(PMF_Db::getTablePrefix() . 'faqdata AS fd')->setResultColumns(array('fd.id AS id', 'fd.lang AS lang', 'fcr.category_id AS category_id', 'fd.thema AS question', 'fd.content AS answer'))->setJoinedTable(PMF_Db::getTablePrefix() . 'faqcategoryrelations AS fcr')->setJoinedColumns(array('fd.id = fcr.record_id', 'fd.lang = fcr.record_lang'))->setConditions(array('fd.active' => "'yes'", 'fd.lang' => "'" . $this->_config->getLanguage()->getLanguage() . "'"))->setMatchingColumns(array('fd.thema', 'fd.content', 'fd.keywords')); $result = $search->search($terms); return $this->_config->getDb()->fetchAll($result); }
/** * Get an array with minimalistic attachment meta data * * @return array */ public function getBreadcrumbs() { $retval = array(); $query = sprintf("\n SELECT\n fa.id AS ID,\n fa.record_id AS record_id,\n fa.record_lang AS record_lang,\n fa.filename AS filename,\n fa.filesize AS filesize,\n fa.mime_type AS mime_type,\n fd.thema AS thema\n FROM\n %s fa\n JOIN\n %s fd\n ON\n fa.record_id = fd.id\n GROUP BY\n fa.id", PMF_Db::getTablePrefix() . 'faqattachment', PMF_Db::getTablePrefix() . 'faqdata'); $result = $this->config->getDb()->query($query); if ($result) { $retval = $this->config->getDb()->fetchAll($result); } return $retval; }
/** * Generates a huge array for the report * @return array */ public function getReportingData() { $report = []; $query = sprintf("\n SELECT\n fd.id AS id,\n fd.lang AS lang,\n fcr.category_id AS category_id,\n c.name as category_name,\n c.parent_id as parent_id,\n fd.sticky AS sticky,\n fd.thema AS question,\n fd.author AS original_author,\n fd.datum AS creation_date,\n fv.visits AS visits,\n u.display_name AS last_author\n FROM\n %sfaqdata fd\n LEFT JOIN\n %sfaqcategoryrelations fcr\n ON\n (fd.id = fcr.record_id AND fd.lang = fcr.record_lang)\n LEFT JOIN\n %sfaqvisits fv\n ON\n (fd.id = fv.id AND fd.lang = fv.lang)\n LEFT JOIN\n %sfaqchanges as fc\n ON\n (fd.id = fc.id AND fd.lang = fc.lang)\n LEFT JOIN\n %sfaquserdata as u\n ON\n (u.user_id = fc.usr)\n LEFT JOIN\n %sfaqcategories as c\n ON\n (c.id = fcr.category_id AND c.lang = fcr.record_lang)\n ORDER BY\n fd.id\n ASC", PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix()); $result = $this->_config->getDb()->query($query); $lastId = 0; while ($row = $this->_config->getDb()->fetchObject($result)) { if ($row->id == $lastId) { $report[$row->id]['faq_translations'] += 1; } else { $report[$row->id] = array('faq_id' => $row->id, 'faq_language' => $row->lang, 'category_id' => $row->category_id, 'category_parent' => $row->parent_id, 'category_name' => $row->category_name, 'faq_translations' => 0, 'faq_sticky' => $row->sticky, 'faq_question' => $row->question, 'faq_org_author' => $row->original_author, 'faq_creation' => PMF_Date::createIsoDate($row->creation_date), 'faq_visits' => $row->visits, 'faq_last_author' => $row->last_author); } $lastId = $row->id; } return $report; }
/** * retrieves stored link state and validates timestamp * * @param int $id * @param string $artlang * @param boolean $checkDate * * @return boolean|string */ public function getEntryState($id = 0, $artlang = '', $checkDate = false) { $interval = $this->getURLValidateInterval(); $query = sprintf("\n SELECT \n links_state, links_check_date \n FROM \n %sfaqdata \n WHERE \n id = %d \n AND \n lang = '%s'", PMF_Db::getTablePrefix(), $id, $this->_config->getDb()->escape($artlang)); if ($result = $this->_config->getDb()->query($query)) { while ($row = $this->_config->getDb()->fetchObject($result)) { $_linkState = $row->links_state; if (trim($_linkState) == "") { $_linkState = true; } if ($row->links_check_date > $interval) { return $_linkState; } else { if ($checkDate == false) { return $_linkState; } else { return true; } } } } else { return false; } }
/** * Removes all users from the group $groupId. * Returns true on success, otherwise false. * * @param integer $groupId Group ID * @return bool */ public function removeAllUsersFromGroup($groupId) { if ($groupId <= 0 or !is_numeric($groupId)) { return false; } // remove all user from group $delete = sprintf("\n DELETE FROM\n %sfaquser_group\n WHERE\n group_id = %d", PMF_Db::getTablePrefix(), $groupId); $res = $this->config->getDb()->query($delete); if (!$res) { return false; } return true; }
/** * Check if a table is filled with data * * @param string $tableName Table name * * @return boolean true, if table is empty, otherwise false */ public static function checkOnEmptyTable($tableName) { if (self::$instance->numRows(self::$instance->query('SELECT * FROM ' . PMF_Db::getTablePrefix() . $tableName)) < 1) { return true; } else { return false; } }
/** * Returns an array of country codes for a specific FAQ record ID, * specific category ID or all languages used by FAQ records , categories * * @param integer $id ID * @param string $table Specifies table * * @return array */ public function languageAvailable($id, $table = 'faqdata') { $output = []; if (isset($id)) { if ($id == 0) { // get languages for all ids $distinct = ' DISTINCT '; $where = ''; } else { // get languages for specified id $distinct = ''; $where = " WHERE id = " . $id; } $query = sprintf("\n SELECT %s\n lang\n FROM\n %s%s\n %s", $distinct, PMF_Db::getTablePrefix(), $table, $where); $result = $this->config->getDb()->query($query); if ($this->config->getDb()->numRows($result) > 0) { while ($row = $this->config->getDb()->fetchObject($result)) { $output[] = $row->lang; } } } return $output; }
/** * Refuses all user rights. * Returns true on success, otherwise false. * * @param integer $user_id User ID * @return boolean */ public function refuseAllUserRights($user_id) { $delete = sprintf("\n DELETE FROM\n %sfaquser_right\n WHERE\n user_id = %d", PMF_Db::getTablePrefix(), $user_id); $res = $this->config->getDb()->query($delete); if (!$res) { return false; } return true; }
/** * Updates all configuration items * * @param array $newConfigs Array with new configuration values * * @return bool */ public function update(array $newConfigs) { $runtimeConfigs = array('core.database', 'core.instance', 'core.language', 'core.ldap', 'core.ldapConfig'); if (is_array($newConfigs)) { foreach ($newConfigs as $name => $value) { if ($name != 'main.phpMyFAQToken' && !in_array($name, $runtimeConfigs)) { $update = sprintf("\n UPDATE\n %s%s\n SET\n config_value = '%s'\n WHERE\n config_name = '%s'", PMF_Db::getTablePrefix(), $this->_tableName, $this->getDb()->escape(trim($value)), $name); $this->getDb()->query($update); if (isset($this->config[$name])) { unset($this->config[$name]); } } } return true; } return false; }
/** * Sets login succuess/failure * * @param boolean $success * * @return boolean */ protected function setSuccess($success) { $this->loginState = (int) $success; $update = sprintf("\n UPDATE\n %sfaquser\n SET\n success = %d\n WHERE\n user_id = %d", PMF_Db::getTablePrefix(), $this->loginState, $this->getUserId()); return $this->config->getDb()->query($update); }
* @author Alexander M. Turek <*****@*****.**> * @copyright 2005-2014 phpMyFAQ Team * @license http://www.mozilla.org/MPL/2.0/ Mozilla Public License Version 2.0 * @link http://www.phpmyfaq.de * @since 2013-02-05 */ if (!defined('IS_VALID_PHPMYFAQ')) { $protocol = 'http'; if (isset($_SERVER['HTTPS']) && strtoupper($_SERVER['HTTPS']) === 'ON') { $protocol = 'https'; } header('Location: ' . $protocol . '://' . $_SERVER['HTTP_HOST'] . dirname($_SERVER['SCRIPT_NAME'])); exit; } $faqTableInfo = $faqConfig->getDb()->getTableStatus(); $templateVars = array('PMF_LANG' => $PMF_LANG, 'dashboardArticles' => $faqTableInfo[PMF_Db::getTablePrefix() . "faqdata"], 'dashboardComments' => $faqTableInfo[PMF_Db::getTablePrefix() . "faqcomments"], 'dashboardNews' => $faqTableInfo[PMF_Db::getTablePrefix() . "faqnews"], 'dashboardOpenQuestions' => $faqTableInfo[PMF_Db::getTablePrefix() . "faqquestions"], 'dashboardUsers' => $faqTableInfo[PMF_Db::getTablePrefix() . 'faquser'] - 1, 'dashboardVisits' => $faqTableInfo[PMF_Db::getTablePrefix() . 'faqsessions'], 'enableUserTracking' => $faqConfig->get('main.enableUserTracking'), 'inMaintenanceMode' => $faqConfig->get('main.maintenanceMode'), 'onlineVerificationActive' => false, 'onlineVerificationError' => false, 'updateCheckActive' => false); if ($faqConfig->get('main.enableUserTracking')) { $session = new PMF_Session($faqConfig); $visits = $session->getLast30DaysVisits(); $templateVars['visitsData'] = implode(',', $visits); unset($session, $visits); } // Perform update check $version = PMF_Filter::filterInput(INPUT_POST, 'param', FILTER_SANITIZE_STRING); if (!is_null($version) && $version == 'version') { $json = file_get_contents('http://www.phpmyfaq.de/api/version'); $result = json_decode($json); if ($result instanceof stdClass) { $installed = $faqConfig->get('main.currentVersion'); $available = $result->stable; $templateVars['updateCheckActive'] = true;
<div class="dashboard-stat span2"> <span><a href="?action=news"><?php echo $PMF_LANG["msgNews"]; ?> </a></span> <?php echo $faqTableInfo[PMF_Db::getTablePrefix() . "faqnews"]; ?> </div> <div class="dashboard-stat span2"> <span><a href="?action=user&user_action=listallusers"><?php echo $PMF_LANG['admin_mainmenu_users']; ?> </a></span> <?php echo $faqTableInfo[PMF_Db::getTablePrefix() . 'faquser'] - 1; ?> </div> </section> <?php if ($faqConfig->get('main.enableUserTracking')) { ?> <section class="row-fluid"> <div class="span12"> <header> <h3><?php echo $PMF_LANG["ad_stat_report_visits"]; ?> </h3>
/** * Remove meta data from the db * * @return null */ protected function deleteMeta() { $sql = sprintf("DELETE FROM %sfaqattachment WHERE id = %d", PMF_Db::getTablePrefix(), $this->id); $this->db->query($sql); }
<div class="controls"> <ul class="adminAttachments"> <?php $attList = PMF_Attachment_Factory::fetchByRecordId($faqConfig, $faqData['id']); foreach ($attList as $att) { printf('<li><a href="../%s">%s</a> ', $att->buildUrl(), $att->getFilename()); if ($permission['delattachment']) { printf('<a class="label label-important" href="?action=delatt&record_id=%d&id=%d&lang=%s"><i class="icon-trash icon-white"></i></a>', $faqData['id'], $att->getId(), $faqData['lang']); } echo "</li>\n"; } ?> </ul> <?php if (0 === $faqData['id']) { $faqData['id'] = $faqConfig->getDb()->nextId(PMF_Db::getTablePrefix() . 'faqdata', 'id'); } printf('<a class="btn btn-success" onclick="addAttachment(\'attachment.php?record_id=%d&record_lang=%s\', \'Attachment\');">%s</a>', $faqData['id'], $faqData['lang'], $PMF_LANG['ad_att_add']); ?> </div> </div> <?php } ?> <!-- Tags --> <div class="control-group"> <label class="control-label" for="tags"><?php echo $PMF_LANG['ad_entry_tags']; ?> :</label> <div class="controls">
/** * The main search function for the full text search * * @param string $searchTerm Text/Number (solution id) * @param boolean $allLanguages true to search over all languages * * @return array */ public function search($searchTerm, $allLanguages = true) { $fdTable = PMF_Db::getTablePrefix() . 'faqdata'; $fcrTable = PMF_Db::getTablePrefix() . 'faqcategoryrelations'; $condition = array($fdTable . '.active' => "'yes'"); $search = PMF_Search_Factory::create($this->_config, array('database' => PMF_Db::getType())); if (!is_null($this->getCategoryId()) && 0 < $this->getCategoryId()) { if ($this->getCategory() instanceof PMF_Category) { $children = $this->getCategory()->getChildNodes($this->getCategoryId()); $selectedCategory = array($fcrTable . '.category_id' => array_merge((array) $this->getCategoryId(), $children)); } else { $selectedCategory = array($fcrTable . '.category_id' => $this->getCategoryId()); } $condition = array_merge($selectedCategory, $condition); } if (!$allLanguages && !is_numeric($searchTerm)) { $selectedLanguage = array($fdTable . '.lang' => "'" . $this->_config->getLanguage()->getLanguage() . "'"); $condition = array_merge($selectedLanguage, $condition); } $search->setTable($fdTable)->setResultColumns(array($fdTable . '.id AS id', $fdTable . '.lang AS lang', $fdTable . '.solution_id AS solution_id', $fcrTable . '.category_id AS category_id', $fdTable . '.thema AS question', $fdTable . '.content AS answer'))->setJoinedTable($fcrTable)->setJoinedColumns(array($fdTable . '.id = ' . $fcrTable . '.record_id', $fdTable . '.lang = ' . $fcrTable . '.record_lang'))->setConditions($condition); if (is_numeric($searchTerm)) { $search->setMatchingColumns(array($fdTable . '.solution_id')); } else { $search->setMatchingColumns(array($fdTable . '.thema', $fdTable . '.content', $fdTable . '.keywords')); } $result = $search->search($searchTerm); if (!$this->_config->getDb()->numRows($result)) { return []; } else { return $this->_config->getDb()->fetchAll($result); } }
/** * Returns an array of all users found in the database. By default, the * anonymous User will not be returned. The returned array contains the * user ID as key, the values are login name, account status, authentication * source and the user creation date. * * @param boolean $withoutAnonymous Without anonymous? * @return array */ public function getAllUserData($withoutAnonymous = true) { $select = sprintf("\n SELECT\n user_id, login, account_status, auth_source, member_since\n FROM\n %sfaquser\n %s\n ORDER BY\n login ASC", PMF_Db::getTablePrefix(), $withoutAnonymous ? 'WHERE user_id <> -1' : ''); $res = $this->config->getDb()->query($select); if (!$res) { return []; } $result = []; while ($row = $this->config->getDb()->fetchArray($res)) { $result[$row['user_id']] = $row; } return $result; }
/** * Calculates the rating of the user votings * * @param integer $id * * @return string */ function getVotingResult($id) { $query = sprintf(' SELECT (vote/usr) as voting, usr FROM %sfaqvoting WHERE artikel = %d', PMF_Db::getTablePrefix(), $id); $result = $this->_config->getDb()->query($query); if ($this->_config->getDb()->numRows($result) > 0) { $row = $this->_config->getDb()->fetchObject($result); return sprintf(' %s (' . $this->plr->GetMsg('plmsgVotes', $row->usr) . ')', round($row->voting, 2)); } else { return '0 (' . $this->plr->GetMsg('plmsgVotes', 0) . ')'; } }
/** * Fetch all record attachments * * @param PMF_Configuration $config * @param integer $recordId ID of the record * * @return array */ public static function fetchByRecordId(PMF_Configuration $config, $recordId) { $retval = array(); $sql = sprintf("\n SELECT\n id\n FROM\n %sfaqattachment\n WHERE\n record_id = %d\n AND\n record_lang = '%s'", PMF_Db::getTablePrefix(), $recordId, PMF_Language::$language); $result = $config->getDb()->fetchAll($config->getDb()->query($sql)); if ($result) { foreach ($result as $item) { $retval[] = self::create($item->id); } } reset($retval); return $retval; }
$query[] = "COMMIT"; } elseif ('sqlite3' === $DB['type']) { $query[] = "ALTER TABLE " . PMF_Db::getTablePrefix() . "faquser ADD COLUMN success INT(1) NULL DEFAULT 1"; } else { $query[] = "ALTER TABLE " . PMF_Db::getTablePrefix() . "faquser ADD success INT(1) NULL DEFAULT 1"; } } // Always the last step: Update version number if (version_compare($version, PMF_System::getVersion(), '<')) { $faqConfig->update(array('main.currentVersion' => PMF_System::getVersion())); } // optimize tables if possible switch ($DB['type']) { case 'mysqli': // Get all table names $faqConfig->getDb()->getTableNames(PMF_Db::getTablePrefix()); foreach ($faqConfig->getDb()->tableNames as $tableName) { $query[] = 'OPTIMIZE TABLE ' . $tableName; } break; case 'pgsql': $query[] = "VACUUM ANALYZE;"; break; } // Perform the queries for optimizing the database if (isset($query)) { echo '<div class="center">'; foreach ($query as $executeQuery) { $result = $faqConfig->getDb()->query($executeQuery); printf('<span title="%s">.</span>', $executeQuery); if (!$result) {
/** * Deletes logging data older than 30 days * * @return boolean */ public function delete() { $query = sprintf("DELETE FROM\n %sfaqadminlog\n WHERE\n time < %d", PMF_Db::getTablePrefix(), $_SERVER['REQUEST_TIME'] - 30 * 86400); if ($this->_config->getDb()->query($query)) { return true; } return false; }
/** * Calculates the number of visits per day the last 30 days * * @returns array */ public function getLast30DaysVisits() { $stats = []; $visits = []; $startDate = strtotime('-1 month'); $endDate = $_SERVER['REQUEST_TIME']; $query = sprintf("\n SELECT\n sid, time\n FROM\n %sfaqsessions\n WHERE\n time > %d\n AND\n time < %d;", PMF_Db::getTablePrefix(), $startDate, $endDate); $result = $this->_config->getDb()->query($query); while ($row = $this->_config->getDb()->fetchObject($result)) { $visits[] = $row->time; } for ($date = $startDate; $date <= $endDate; $date += 86400) { $stats[date('Y-m-d', $date)] = 0; foreach ($visits as $visitDate) { if (date('Y-m-d', $date) === date('Y-m-d', $visitDate)) { $stats[date('Y-m-d', $date)]++; } } } return $stats; }
/** * Adds a configuration item for the database * * @param string $name * @param mixed $value * * @return boolean */ public function addConfig($name, $value) { $insert = sprintf("INSERT INTO\n %sfaqinstances_config\n VALUES\n (%d, '%s', '%s')", PMF_Db::getTablePrefix(), $this->getId(), $this->config->getDb()->escape(trim($name)), $this->config->getDb()->escape(trim($value))); return $this->config->getDb()->query($insert); }
/** * Deletes a news entry identified by its ID * * @todo check if there are comments attached to the deleted news * * @param integer $id News ID * * @return boolean */ function deleteNews($id) { $query = sprintf("DELETE FROM\n %sfaqnews\n WHERE\n id = %d\n AND\n lang = '%s'", PMF_Db::getTablePrefix(), $id, $this->_config->getLanguage()->getLanguage()); if (!$this->_config->getDb()->query($query)) { return false; } return true; }
/** * Checks the number of entries of given login name * * @param string $login Loginname * @param array $optionalData Optional data * * @return integer */ public function checkLogin($login, array $optionalData = null) { $check = sprintf("\n SELECT\n login\n FROM\n %sfaquserlogin\n WHERE\n login = '******'", PMF_Db::getTablePrefix(), $this->db->escape($login)); $check = $this->db->query($check); $error = $this->db->error(); if (strlen($error) > 0) { $this->errors[] = $error; return 0; } return $this->db->numRows($check); }
/** * Deletes an item and definition into the database * * @param integer $id Glossary ID * * @return boolean */ public function deleteGlossaryItem($id) { $query = sprintf("\n DELETE FROM\n %sfaqglossary\n WHERE\n id = %d AND lang = '%s'", PMF_Db::getTablePrefix(), (int) $id, $this->config->getLanguage()->getLanguage()); if ($this->config->getDb()->query($query)) { return true; } return false; }
/** * Create a matrix for representing categories and faq records * * @return array */ public function getCategoryRecordsMatrix() { $matrix = []; $query = sprintf(' SELECT fcr.category_id AS id_cat, fd.id AS id FROM %sfaqdata fd INNER JOIN %sfaqcategoryrelations fcr ON fd.id = fcr.record_id AND fd.lang = fcr.category_lang ORDER BY fcr.category_id, fd.id', PMF_Db::getTablePrefix(), PMF_Db::getTablePrefix()); $result = $this->_config->getDb()->query($query); if ($this->_config->getDb()->numRows($result) > 0) { while ($row = $this->_config->getDb()->fetchObject($result)) { $matrix[$row->id_cat][$row->id] = true; } } return $matrix; }
/** * Saves remember me token in the database * * @param string $rememberMe * * @return boolean */ protected function setRememberMe($rememberMe) { $update = sprintf("\n UPDATE\n %sfaquser\n SET\n remember_me = '%s'\n WHERE\n user_id = %d", PMF_Db::getTablePrefix(), $this->config->getDb()->escape($rememberMe), $this->getUserId()); return $this->config->getDb()->query($update); }
/** * Constructor * * @param PMF_Configuration $config * * @return PMF_Stopwords */ public function __construct(PMF_Configuration $config) { $this->_config = $config; $this->table_name = PMF_Db::getTablePrefix() . "faqstopwords"; }
/** * Deletes the user-data entry for the given user-ID $userId. * Returns true on success, otherwise false. * * @param integer $userId User ID * @return bool */ public function delete($userId) { $userId = (int) $userId; if ($userId <= 0 && $userId != -1) { return false; } $this->userId = $userId; $delete = sprintf("\n DELETE FROM\n %sfaquserdata\n WHERE\n user_id = %d", PMF_Db::getTablePrefix(), $this->userId); $res = $this->config->getDb()->query($delete); if (!$res) { return false; } $this->data = array(); return true; }
/** * Delete old captcha records. * * During normal use the <b>faqcaptcha</b> table would be empty, on average: * each record is created when a captcha image is showed to the user * and deleted upon a successful matching, so, on average, a record * in this table is probably related to a spam attack. * * @param int $time The time (sec) to define a captcha code old and ready * to be deleted (default: 1 week) * @return void */ private function garbageCollector($time = 604800) { $delete = sprintf("\n DELETE FROM \n %sfaqcaptcha \n WHERE \n captcha_time < %d", PMF_Db::getTablePrefix(), $_SERVER['REQUEST_TIME'] - $time); $this->_config->getDb()->query($delete); $delete = sprintf("\n DELETE FROM\n %sfaqcaptcha\n WHERE\n useragent = '%s' AND language = '%s' AND ip = '%s'", PMF_Db::getTablePrefix(), $this->userAgent, $this->_config->getLanguage()->getLanguage(), $this->ip); $this->_config->getDb()->query($delete); }