PHP SQL::query方法代码示例

/**
 * calculate creature health, mana and armor
 * 
 * kinda crappy way, but works
 * 
 * if $type is used:
 * 1 -> returns health
 * 2 -> returns mana
 * 3 -> returns armor
 * 0 -> returns array(health,mana,armor)      
 */
function get_additional_data($entryid, $type = 0)
{
    global $world_db, $realm_id;
    if (!is_numeric($entryid)) {
        return array(0, 0, 0);
    }
    $sqlw = new SQL();
    $sqlw->connect($world_db[$realm_id]['addr'], $world_db[$realm_id]['user'], $world_db[$realm_id]['pass'], $world_db[$realm_id]['name']);
    $q = $sqlw->query("\n\t\tSELECT \n\t\t\t(SELECT unit_class \n\t\t\tFROM creature_template \n\t\t\tWHERE entry = " . $entryid . ") AS class, \n\t\t\t\t(SELECT FLOOR(minlevel + (RAND() * (maxlevel - minlevel + 1))) \n\t\t\t\tFROM creature_template \n\t\t\t\tWHERE entry = " . $entryid . ") AS level, \n\t\t\t\t(SELECT exp \n\t\t\t\tFROM creature_template \n\t\t\t\tWHERE entry = " . $entryid . ") AS exp;");
    $data = $sqlw->fetch_assoc($q);
    if ($sqlw->num_rows($q) == 0) {
        return array(0, 0, 0);
    }
    $q = "\n\t\t\tSELECT \n\t\t\t\t((SELECT Health_Mod \n\t\t\t\tFROM creature_template \n\t\t\t\tWHERE entry = " . $entryid . ")\n\t\t\t\t\t*(SELECT basehp" . $data['exp'] . " \n\t\t\t\t\tFROM creature_classlevelstats \n\t\t\t\t\tWHERE level = " . $data['level'] . " AND class = " . $data['class'] . ")+0.5), \n\t\t\t\t((SELECT Mana_Mod \n\t\t\t\tFROM creature_template \n\t\t\t\tWHERE entry = " . $entryid . ")\n\t\t\t\t\t*(SELECT basemana \n\t\t\t\t\tFROM creature_classlevelstats \n\t\t\t\t\tWHERE level = " . $data['level'] . " AND class = " . $data['class'] . ")+0.5),\n\t\t\t\t((SELECT Armor_Mod \n\t\t\t\tFROM creature_template \n\t\t\t\tWHERE entry = " . $entryid . ")\n\t\t\t\t*(SELECT basearmor \n\t\t\t\tFROM creature_classlevelstats \n\t\t\t\tWHERE level = " . $data['level'] . " AND class = " . $data['class'] . ")+0.5);";
    if ($type == 1) {
        $q = "\n\t\t\tSELECT \n\t\t\t\t((SELECT Health_Mod \n\t\t\t\tFROM creature_template \n\t\t\t\tWHERE entry = " . $entryid . ")\n\t\t\t\t\t*(SELECT basehp" . $data['exp'] . " \n\t\t\t\t\tFROM creature_classlevelstats \n\t\t\t\t\tWHERE level = " . $data['level'] . " AND class = " . $data['class'] . ")+0.5);";
    }
    if ($type == 2) {
        $q = "\n\t\t\tSELECT \n\t\t\t\t((SELECT Mana_Mod \n\t\t\t\tFROM creature_template \n\t\t\t\tWHERE entry = " . $entryid . ")\n\t\t\t\t\t*(SELECT basemana \n\t\t\t\t\tFROM creature_classlevelstats \n\t\t\t\t\tWHERE level = " . $data['level'] . " AND class = " . $data['class'] . ")+0.5);";
    }
    if ($type == 3) {
        $q = "\n\t\t\tSELECT \n\t\t\t\t((SELECT Armor_Mod \n\t\t\t\tFROM creature_template \n\t\t\t\tWHERE entry = " . $entryid . ")\n\t\t\t\t\t*(SELECT basearmor \n\t\t\t\t\tFROM creature_classlevelstats \n\t\t\t\t\tWHERE level = " . $data['level'] . " AND class = " . $data['class'] . ")+0.5);";
    }
    $query = $sqlw->query($q);
    $result = $sqlw->fetch_row($query);
    $sqlw->close();
    unset($sql);
    if ($type == 2 && $result[0] == 0.5) {
        return 0;
    }
    if ($type == 0 && $result[1] == 0.5) {
        return array($result[0], 0, $result[2]);
    }
    return $type > 0 ? $result[0] : $result;
}

function search()
{
    global $lang_global, $output, $itemperpage, $item_datasite, $mangos_db, $characters_db, $realm_id, $sql_search_limit;
    wowhead_tt();
    if (!isset($_GET['search_value']) || !isset($_GET['search_by'])) {
        redirect("mail_on.php?error=2");
    }
    $sql = new SQL();
    $sql->connect($characters_db[$realm_id]['addr'], $characters_db[$realm_id]['user'], $characters_db[$realm_id]['pass'], $characters_db[$realm_id]['name']);
    $search_value = $sql->quote_smart($_GET['search_value']);
    $search_by = $sql->quote_smart($_GET['search_by']);
    $search_menu = array('sender', 'receiver');
    // if (!array_key_exists($search_by, $search_menu)) $search_by = 'sender';
    $start = isset($_GET['start']) ? $sql->quote_smart($_GET['start']) : 0;
    $order_by = isset($_GET['order_by']) ? $sql->quote_smart($_GET['order_by']) : "id";
    $dir = isset($_GET['dir']) ? $sql->quote_smart($_GET['dir']) : 1;
    $order_dir = $dir ? "ASC" : "DESC";
    $dir = $dir ? 0 : 1;
    $temp = $sql->query("SELECT guid FROM `characters` WHERE name like '%{$search_value}%'");
    $search_value = $sql->result($temp, 0, 'guid');
    $query_1 = $sql->query("SELECT count(*) FROM `mail`");
    $query = $sql->query("SELECT a.id, a.messageType, a.sender, a.receiver, a.subject, a.itemTextId, a.has_items, a.money, a.cod, a.checked, b.item_template\r\n            FROM mail a\r\n            INNER JOIN mail_items b ON a.id = b.mail_id\r\n            WHERE {$search_by} = {$search_value}\r\n            ORDER BY {$order_by} {$order_dir} LIMIT {$start}, {$itemperpage}");
    $this_page = $sql->num_rows($query);
    $all_record = $sql->result($query_1, 0);
    $total_found = $sql->num_rows($query);
    //==========================top page navigation starts here========================
    $output .= "<center><table class=\"top_hidden\">\r\n    <tr><td>\r\n            <table class=\"hidden\">\r\n                <tr><td>\r\n            <form action=\"mail_on.php\" method=\"get\" name=\"form\">\r\n            <input type=\"hidden\" name=\"action\" value=\"search\" />\r\n            <input type=\"hidden\" name=\"error\" value=\"4\" />\r\n            <input type=\"text\" size=\"45\" name=\"search_value\" />\r\n            <select name=\"search_by\">\r\n                <option value=\"sender\">Sender</option>\r\n                <option value=\"receiver\">Receiver</option>\r\n            </select></form></td><td>";
    makebutton($lang_global['search'], "javascript:do_submit()", 80);
    $output .= "</td></tr></table>\r\n            <td align=\"right\">";
    $output .= generate_pagination("mail_on.php?action=search&amp;order_by={$order_by}&amp;dir=" . !$dir, $all_record, $itemperpage, $start);
    $output .= "</td></tr></table>";
    //==========================top page navigation ENDS here ========================
    $output .= "<table class=\"lined\">\r\n  <tr>\r\n    <th width=\"5%\">ID</th>\r\n    <th width=\"5%\">Source</th>\r\n    <th width=\"10%\">Sender</th>\r\n    <th width=\"10%\">Receiver</th>\r\n    <th width=\"15%\">Subject</th>\r\n    <th width=\"5%\">Has Items</th>\r\n    <th width=\"25%\">Text</th>\r\n    <th width=\"15%\">Money</th>\r\n    <th width=\"5%\">Checked</th>\r\n  </tr>";
    while ($mail = $sql->fetch_array($query)) {
        $g = floor($mail[7] / 10000);
        $mail[7] -= $g * 10000;
        $s = floor($mail[7] / 100);
        $mail[7] -= $s * 100;
        $c = $mail[7];
        $money = "";
        if ($mail[7] > 0) {
            $money = $g . "<img src=\"./img/gold.gif\" /> " . $s . "<img src=\"./img/silver.gif\" /> " . $c . "<img src=\"./img/copper.gif\" /> ";
        }
        $output .= "<tr valign=top>\r\n                    <td>{$mail['0']}</td>\r\n                    <td>" . get_mail_source($mail[1]) . "</td>\r\n                    <td><a href=\"char.php?id={$mail['2']}\">" . get_char_name($mail[2]) . "</a></td>\r\n                    <td><a href=\"char.php?id={$mail['3']}\">" . get_char_name($mail[3]) . "</a></td>\r\n                    <td>{$mail['4']}</td>\r\n            ";
        $output .= "<td>";
        $output .= "\r\n                    <a style=\"padding:2px;\" href=\"{$item_datasite}{$mail[10]}\" target=\"_blank\">\r\n                      <img class=\"bag_icon\" src=\"" . get_item_icon($mail[10]) . "\" alt=\"\" />\r\n                  </a>";
        //maketooltip("<img src=\"./img/up.gif\" alt=\"\">", $item_datasite{$mail[10]}, $mail[10], "item_tooltip", "target=\"_blank\"");
        $output .= "</td>";
        $output .= "<td>" . get_mail_text($mail[5]) . "</td>\r\n                        <td>{$money}</td>\r\n        <td>" . get_check_state($mail[9]) . "</td>\r\n                   </tr>";
    }
    /*--------------------------------------------------*/
    $output .= "<tr><td colspan=\"6\" class=\"hidden\" align=\"right\">All Mails: {$all_record}</td></tr>\r\n </table></center>";
    $sql->close();
}

 private function addComment($comment, $OpenIDFields = false)
 {
     if (!isset($comment['author']['openid']) || $comment['author']['openid'] == '') {
         $author = $comment['author'];
         $author['openid'] = '';
     } else {
         $author = array('name' => $OpenIDFields['nickname'], 'email' => $OpenIDFields['email'], 'website' => $comment['author']['website']);
         $e = explode('http://', $comment['author']['openid']);
         $author['openid'] = end($e);
         if (preg_match('#(.*?)\\/$#s', $author['openid'])) {
             $author['openid'] = substr($author['openid'], 0, -1);
         }
     }
     if ($author['website'] == 'http://' || $author['website'] == '') {
         if (trim($comment['author']['openid']) == '') {
             $author['website'] = '';
         } else {
             $author['website'] = trim($comment['author']['openid']);
         }
     }
     if ($result = !SQL::query("SELECT MAX([comment_number]) AS [max_number] FROM [blog_comments]p WHERE ([post_id] == " . (int) $comment['post_id'] . ")")) {
         $number = $result->fetchOne();
         $number = (int) $number->max_number;
     } else {
         $number = 0;
     }
     if (!SQL::exec("\r\nINSERT INTO [blog_comments]p\r\n(\r\n[post_id],\r\n[comment_number],\r\n[comment_date],\r\n[comment_content],\r\n[comment_author_openid],\r\n[comment_author_name],\r\n[comment_author_email],\r\n[comment_author_website],\r\n[comment_author_user_agent],\r\n[comment_author_os]\r\n) VALUES (\r\n" . (int) $comment['post_id'] . ",\r\n" . $number . ",\r\n" . time() . ",\r\n'" . SQL::escape(trim($comment['content'])) . "',\r\n'" . SQL::escape(trim($author['openid'])) . "',\r\n'" . SQL::escape(trim($author['name'])) . "',\r\n'" . SQL::escape(strtolower(trim($author['email']))) . "',\r\n'" . SQL::escape(trim($author['website'])) . "',\r\n'" . SQL::escape($_SERVER['HTTP_USER_AGENT']) . "',\r\n'" . '' . "'\r\n)")) {
         throw new Exception('The comment could not be added.');
     }
     $uri = SITE_ROOT_PATH . str_replace(array('%id', '%slug'), array($comment['post_id'], $comment['post_slug']), CFG_URL_BLOG_POST) . '#comments';
     header('Location: ' . $uri);
     echo '<a href="' . $uri . '">' . $uri . '</a>';
 }

 /**
  * cascade to children
  *
  * @param string referencing of the changed anchor
  * @param string rights to be cascaded (e.g., 'Y', 'R' or 'N')
  */
 public static function cascade($reference, $active)
 {
     global $context;
     // only sections may have sub-sections
     if (strpos($reference, 'section:') === 0) {
         // cascade to sub-sections
         if ($items = Sections::list_for_anchor($reference, 'raw')) {
             // cascade to each section individually
             foreach ($items as $id => $item) {
                 // limit actual rights
                 $item['active'] = Anchors::ceil_rights($active, $item['active_set']);
                 $query = "UPDATE " . SQL::table_name('sections') . " SET active='" . SQL::escape($item['active']) . "' WHERE id = " . SQL::escape($id);
                 SQL::query($query);
                 // cascade to children
                 Anchors::cascade('section:' . $item['id'], $item['active']);
             }
         }
     }
     // only categories may have sub-categories
     if (strpos($reference, 'category:') === 0) {
         // cascade to sub-categories
         if ($items = Categories::list_for_anchor($reference, 'raw')) {
             // cascade to each section individually
             foreach ($items as $id => $item) {
                 // limit actual rights
                 $item['active'] = Anchors::ceil_rights($active, $item['active_set']);
                 $query = "UPDATE " . SQL::table_name('categories') . " SET active='" . SQL::escape($item['active']) . "' WHERE id = " . SQL::escape($id);
                 SQL::query($query);
                 // cascade to children
                 Anchors::cascade('category:' . $item['id'], $item['active']);
             }
         }
     }
     // only sections may have articles
     if (strpos($reference, 'section:') === 0) {
         // cascade to articles --up to 3000
         if ($items =& Articles::list_for_anchor_by('edition', $reference, 0, 3000, 'raw')) {
             // cascade to each section individually
             foreach ($items as $id => $item) {
                 // limit actual rights
                 $item['active'] = Anchors::ceil_rights($active, $item['active_set']);
                 $query = "UPDATE " . SQL::table_name('articles') . " SET active='" . SQL::escape($item['active']) . "' WHERE id = " . SQL::escape($id);
                 SQL::query($query);
                 // cascade to children
                 Anchors::cascade('article:' . $item['id'], $item['active']);
             }
         }
     }
     // cascade to files --up to 3000
     if ($items = Files::list_by_date_for_anchor($reference, 0, 3000, 'raw')) {
         // cascade to each section individually
         foreach ($items as $id => $item) {
             // limit actual rights
             $item['active'] = Anchors::ceil_rights($active, $item['active_set']);
             $query = "UPDATE " . SQL::table_name('files') . " SET active='" . SQL::escape($item['active']) . "' WHERE id = " . SQL::escape($id);
             SQL::query($query);
         }
     }
 }

function html_header()
{
    if (!defined('SITE_ROOT')) {
        define('SITE_ROOT', './');
    }
    require SITE_ROOT . 'portal_config.php';
    require_once SITE_ROOT . 'include/database.class.php';
    $currentUserID = $_SESSION['current_userID'];
    $db = new SQL(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME, false);
    $db->query("SELECT * FROM forum_users WHERE id='{$currentUserID}'");
    if ($row = $db->fetch_array()) {
        $username = $row['username'];
        $realname = $row['realname'];
    }
    $db->close();
    if ($realname !== null) {
        $displayname = $realname;
    } else {
        $displayname = $username;
    }
    echo '
<div id="wrap">
<div class="navbar navbar-top"><div class="navbar-inner"><div class="container">
<a class="brand" href="#index.php"><div class="logo-ip"></div></a>
<div class="btn-group pull-left">
<a href="../forum" class="btn btn-inverse"><i class="icon-rss"></i> Forum Ishare</a>
</div>

<div class="btn-group">
  <button class="btn dropdown-toggle" data-toggle="dropdown"><i class="icon-list muted"></i> KampusLinks <span class="caret"></span></button>
  <ul class="dropdown-menu">
    <li><a href="http://mpp.eng.usm.my/">MPPUSMKKj Official Blog</a></li>
    <li><a href="http://hepp.eng.usm.my/">BHEPP USMKKj</a></li>
    <li><a href="http://infodesk.eng.usm.my/">Infodesk PPKT USMKKj</a></li>
    <li><a href="http://www.eng.usm.my/php/blockedIP/">Blocked Port List</a></li>
    <li><a href="http://elearning.usm.my/">e-Learning Portal</a></li>
    <li><a href="http://campusonline.usm.my/">CampusOnline Portal</a></li>
    <li><a href="http://www.tcom.usm.my/">Sistem Direktori Telefon USM</a></li>
    <li><a href="http://www.facebook.com/ppkt.eng.usm">Facebook PPKT USMKKj</a></li>
    <li class="divider"></li>
    <li><a href="http://hik3.net/refcode"><i class="icon-bookmark"></i> RefCode (Snippets)</a></li>
  </ul>
</div>
            
<div class="btn-group pull-right">
<a href="index.php" class="btn btn-primary"><i class="icon-home icon-white"></i> Home</a>
<a href="profile.php?id=' . $currentUserID . '" class="btn btn-inverse"><i class="icon-user"></i> ' . $displayname . '</a>
<button class="btn btn-danger dropdown-toggle" data-toggle="dropdown"><span class="caret"></span></button>
  <ul class="dropdown-menu">
    <li><a href="edit_profile.php"><i class="icon-edit muted"></i> Edit Profile</a></li>
    <li><a href="edit_sharerlink.php"><i class="icon-hdd muted"></i> Edit Sharerlink</a></li>
    <li class="divider"></li>
    <li><a href="../forum/login.php?action=out&id=' . $currentUserID . '"><i class="icon-off muted"></i> Logout</a></li>
  </ul>
</div>
            
</div></div></div>
';
}

 public function set($data)
 {
     $userId = $_SESSION["userId"];
     $dailyCalories = filter_var($data["daily_calories"], FILTER_SANITIZE_NUMBER_INT);
     $query = "UPDATE users SET daily_calories = " . $dailyCalories . " WHERE id = " . $userId . ";";
     $result = SQL::query($query);
     return $result;
 }

function get_realm_name($realm_id)
{
    global $realm_db;
    $sqlr = new SQL();
    $sqlr->connect($realm_db['addr'], $realm_db['user'], $realm_db['pass'], $realm_db['name']);
    $result = $sqlr->query("SELECT name FROM `realmlist` WHERE id = '{$realm_id}'");
    $realm_name = $sqlr->result($result, 0);
    return $realm_name;
}

	/**
	 * @param int $group_id
	 * @return bool|mixed|null
	 */
	public static function getUserList($group_id) {
		$sql = 'SELECT user_id FROM ' . self::getFullTableName() . ' WHERE group_id = :id';

		$result = SQL::query(self::getConnection(), $sql, array("id" => $group_id));

		if($result !== false)
			return $result;
		return false;
	}

function e1_insert_into_conges_config($DEBUG = FALSE)
{
    $sql_insert_1 = "INSERT INTO `conges_config` VALUES ('calendrier_select_all_groups', 'FALSE', '10_Gestion par groupes', 'boolean', 'config_comment_calendrier_select_all_groups')";
    $result_insert_1 = SQL::query($sql_insert_1);
    $sql_insert_2 = "INSERT INTO `conges_config` VALUES ('consult_calendrier_sans_auth', 'FALSE', '04_Authentification', 'boolean', 'config_comment_consult_calendrier_sans_auth')";
    $result_insert_2 = SQL::query($sql_insert_2);
    $sql_insert_3 = "INSERT INTO `conges_config` VALUES ('resp_ajoute_conges', 'TRUE', '06_Responsable', 'boolean', 'config_comment_resp_ajoute_conges')";
    $result_insert_3 = SQL::query($sql_insert_3);
}

	/**
	 * @param bool|int $limit
	 * @return mixed|null
	 */
	public static function getAll($limit = false) {
		$parms = array();
		if($limit)
			$parms = array("limit" => (int) $limit);

		$sql = "SELECT * FROM " . self::getFullTableName() . ($limit) ? " LIMIT :limit" : "";

		return SQL::query(self::getConnection(), $sql, $parms);
	}

function populate_requestbox()
{
    $db = new SQL(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME, false);
    $db->query("SELECT COUNT(id) FROM ip_requests");
    $total_request = implode($db->fetch_assoc());
    $db->query("SELECT COUNT(id) FROM ip_reply");
    $total_reply = implode($db->fetch_assoc());
    $db->close();
    echo '<div class="alert alert-info">This is <strong>User\'s Request</strong> section (currently contained <strong>' . $total_request . '</strong> request shouts and <strong>' . $total_reply . '</strong> replies). Just use <code>!request</code> code in your shout to make them appear here. Please note that not all your requests will be replied. Lucky if you have!</div>';
    echo '<div id="containerx">';
    echo '<div class="data"></div>';
    echo '<div class="pagination"></div>';
    echo '</div>';
    echo '
    <script>
    var replyID;
    
    $(document).ready(function () { // START DOCUMENT.READY
    
    $(".tip-top").tooltip();
    
    function loadData(page){
      $("#containerx").html("<div class=\\"loader\\" style=\\"margin-top:10px\\"></div>").fadeIn("fast");
      $.ajax({
        type: "GET",
        url: "subfiles/requestbox_more.php?page="+page,
        success: function(msg){
          $("#containerx").html(msg);
        }
      });
    }
    loadData(1);  // For first time page load default results
    $("#containerx .pagination li.enx").live("click",function(e){
      e.preventDefault();
      var page = $(this).attr("p");
      loadData(page);
    });
    
    }); // END DCOUMENT.READY
    
    </script>
    ';
}

	/**
	 * @param $id
	 * @return mixed|null
	 */
	public static function get_by_id($id) {
		$sql = 'SELECT * FROM ' . self::getFullTableName() . ' WHERE user_id = :id LIMIT 1;';

		return SQL::query(
			self::getConnection(),
			$sql,
			array(
				"id" => $id
			)
		);
	}

 public function deleteById($id)
 {
     $selectId = filter_var($id, FILTER_SANITIZE_NUMBER_INT);
     $calories = new Calories();
     $foodExistsInCaloriesList = $calories->foodExistsInCaloriesList($selectId);
     if ($foodExistsInCaloriesList) {
         return array("success" => false);
     } else {
         $result = SQL::query("DELETE FROM foods WHERE id = " . $selectId . ";");
         return array("success" => $result);
     }
 }

function smarty_resource_style_timestamp($tpl_name, &$tpl_timestamp, &$smarty)
{
    // do database call here to populate $tpl_timestamp.
    $sql = new SQL();
    $sql->query("select tpl_timestamp\n                   from my_table\n                  where tpl_name='{$tpl_name}'");
    if ($sql->num_rows) {
        $tpl_timestamp = $sql->record['tpl_timestamp'];
        return true;
    } else {
        return false;
    }
}

 /**
  * list profiles
  *
  * @param int the offset from the start of the list; usually, 0 or 1 - default is 0
  * @param int the number of items to display - default is 10
  */
 public static function list_by_hits($offset = 0, $count = 10)
 {
     global $context;
     // the list of profiles
     $query = "SELECT * FROM " . SQL::table_name('profiles') . " " . "ORDER BY total_hits DESC LIMIT " . $offset . ', ' . $count;
     if ($result = SQL::query($query)) {
         while ($row = SQL::fetch($result)) {
             $rows[] = array('left=' . $row['url'], 'left=' . $row['total_hits'], 'left=' . round($row['total_time'] / $row['total_hits'], 3), 'left=' . $row['minimum_time'], 'left=' . $row['maximum_time'], 'left=' . $row['total_time']);
         }
     }
     return $rows;
 }