PHP Db::query方法代码示例

 public function brisi()
 {
     $db = new Db();
     $sql = "DROP TABLE IF EXISTS " . $this->studenti;
     $db->query($sql);
     $sql = "DROP TABLE IF EXISTS " . $this->kolokvij;
     $db->query($sql);
     $sql = "DROP TABLE IF EXISTS " . $this->ispit;
     $db->query($sql);
     $sql = "DROP TABLE IF EXISTS " . $this->grupe;
     $db->query($sql);
 }

 public function register($username, $password)
 {
     if ($this->userExists($username)) {
         throw new \Exception("User already exists.");
     }
     $result = $this->db->prepare("INSERT INTO users (username, password, gold , food)\n        VALUES (?, ? ,?, ?);");
     $result->execute([$username, password_hash($password, PASSWORD_DEFAULT), User::GOLD_DEFAULT, User::FOOD_DEFAULT]);
     if ($result->rowCount() > 0) {
         $userId = $this->db->lastId();
         $this->db->query("INSERT INTO userbuildings (user_id, building_id, level_id)\n            SELECT {$userId}, id, 0 FROM buildings");
         return true;
     }
     throw new \Exception("Cannot register user.");
 }

 public static function calculatePoints($killID, $tempTables = false)
 {
     $temp = $tempTables ? "_temporary" : "";
     $victim = Db::queryRow("select * from zz_participants{$temp} where killID = :killID and isVictim = 1", array(":killID" => $killID), 0);
     $kill = $victim;
     $involved = Db::query("select * from zz_participants{$temp} where killID = :killID and isVictim = 0", array(":killID" => $killID), 0);
     $vicpoints = self::getPoints($victim["groupID"]);
     $vicpoints += $kill["total_price"] / 10000000;
     $maxpoints = round($vicpoints * 1.2);
     $invpoints = 0;
     foreach ($involved as $inv) {
         $invpoints += self::getPoints($inv["groupID"]);
     }
     if ($vicpoints + $invpoints == 0) {
         return 0;
     }
     $gankfactor = $vicpoints / ($vicpoints + $invpoints);
     $points = ceil($vicpoints * ($gankfactor / 0.75));
     if ($points > $maxpoints) {
         $points = $maxpoints;
     }
     $points = round($points, 0);
     return max(1, $points);
     // a kill is always worth at least one point
 }

 function pagination2($messageParPage, $table, $id)
 {
     /*
     paginatio_array 0->Nbre d'enregistrements
     paginatio_array 1->Nbre de pages
     paginatio_array 2->Pages actuelle
     paginatio_array 3->Premiere entree
     */
     $id = Utils::anti_injection($id);
     $pagination_array = array();
     $sqlQuery = "SELECT COUNT(*) AS total FROM " . TABLE_PREFIX . $table . " where titre_txt_fr like '%" . $id . "%' or description_txtbox_fr like '%" . $id . "%'";
     $getTotal = Db::query($sqlQuery);
     $donnees_total = Db::fetch_assoc($getTotal);
     $pagination_array[0] = $donnees_total['total'];
     $pagination_array[1] = ceil($pagination_array[0] / $messageParPage);
     if (isset($_GET['page'])) {
         $pagination_array[2] = intval($_GET['page']);
         if ($pagination_array[2] > $pagination_array[1] && $pagination_array[1] > 0) {
             $pagination_array[2] = $pagination_array[1];
         }
     } else {
         $pagination_array[2] = 1;
     }
     $pagination_array[3] = ($pagination_array[2] - 1) * $messageParPage;
     return $pagination_array;
 }

 public function dispatch()
 {
     $result = Db::query("SELECT personas.id_personas\n                  , personas.nombre\n                  , personas.apellido\n                  , personas.correo\n                  , personas.cargo\n                  , personas.tel_oficina\n                  , personas.tel_oficina_int\n                  , personas.tel_celular\n                  , personas.tel_fax\n                  , personas.tel_casa\n                  , personas.foto\n                  , empresas.id_empresas\n                  , empresas.nombre AS empresa\n                  , empresas.direccion_1\n                  , empresas.direccion_2\n                  , empresas.ciudad\n                  , empresas.estado\n                  , empresas.cod_postal\n                  , empresas.web\n                  , empresas.tel_oficina AS e_tel_oficina\n                  , empresas.tel_fax AS e_tel_fax\n                  , paises.id_paises\n                  , paises.nombre AS pais\n             FROM personas\n             RIGHT JOIN empresas ON empresas.id_empresas = personas.id_empresas\n             LEFT JOIN paises ON paises.id_paises = empresas.id_paises\n             ORDER BY paises.nombre, empresas.nombre, personas.nombre");
     if ($result) {
         $i = 0;
         $row = $result[$i];
         while (isset($result[$i])) {
             $idEmpresas = $row['id_empresas'];
             $direccion = String::format("{%s}{ %s}", $row['direccion_1'], $row['direccion_2']);
             $lugar = String::format("{%s}{, %s}{ (%s)}", $row['ciudad'], $row['estado'], $row['cod_postal']);
             $this->data[$idEmpresas] = array('nombre' => $row['empresa'], 'pais' => $row['pais'], 'direccion' => $direccion, 'lugar' => $lugar, 'tel_oficina' => $row['e_tel_oficina'], 'tel_fax' => $row['e_tel_fax'], 'web' => $row['web'], 'personas' => array());
             while (isset($result[$i]) && $idEmpresas == $row['id_empresas']) {
                 $idPersonas = $row['id_personas'];
                 if ($idPersonas > 0) {
                     $nombre = String::format("{%s}{ %s}", $row['nombre'], $row['apellido']);
                     $telOficina = String::format("{%s}{ x%s}", $row['tel_oficina'], $row['tel_oficina_int']);
                     $this->data[$idEmpresas]['personas'][$idPersonas] = array('nombre' => $nombre, 'correo' => $row['correo'], 'cargo' => $row['cargo'], 'tel_oficina' => $telOficina, 'tel_celular' => $row['tel_celular'], 'tel_fax' => $row['tel_fax'], 'tel_casa' => $row['tel_casa'], 'foto' => $row['foto']);
                 }
                 $i++;
                 if (isset($result[$i])) {
                     $row = $result[$i];
                 }
             }
         }
     }
     parent::dispatch();
 }

 /**
  * 获取所有数据
  *
  * @param string $sql            
  * @return multitype:unknown
  */
 public function select($sql, $index = '')
 {
     $arr = array();
     $query = $this->db->query($sql);
     if (empty($index)) {
         while ($row = $this->db->fetchArray($query)) {
             $arr[] = $row;
         }
         return $arr;
     } else {
         while ($row = $this->db->fetchArray($query)) {
             $arr[$row[$index]] = $row;
         }
         return $arr;
     }
 }

 function changepwd()
 {
     $retour = true;
     $oldpwd = Db::escape($_POST['old']);
     $newpwd = Db::escape($_POST['new']);
     $verifpwd = Db::escape($_POST['verif']);
     $login = Db::escape($_POST['login']);
     $getLogin = Db::query("SELECT * FROM `" . TABLE_PREFIX . "admin` WHERE `login_txt` = '" . $login . "'");
     if (Db::num_rows($getLogin) > 0) {
         $this->login = $login;
         $getPwd = Db::query("SELECT * FROM `" . TABLE_PREFIX . "admin` WHERE `login_txt` = '" . $login . "'\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND `mdp_txt` = '" . md5($oldpwd) . "'");
         if (Db::num_rows($getPwd) > 0) {
             if ($newpwd != $verifpwd) {
                 $this->errors = "changeVerif";
                 $retour = false;
             } else {
                 Db::query("UPDATE " . TABLE_PREFIX . "admin SET mdp_txt = '" . md5($newpwd) . "' WHERE login_txt = '" . $login . "'");
             }
         } else {
             $this->errors = "pwd";
             $retour = false;
         }
     } else {
         $this->errors = "login";
         $retour = false;
     }
     return $retour;
 }

/**
 * count
 * @param Db $db
 * @return array
 */
function getCountRecFood(&$db)
{
    $sql = "select favor,cometrue from recommendedfood,users \n\t\t\twhere recommendedfood.user_id=users.user_id\n\t\t\tand users.school_id={$_POST['school_id']};";
    $res = $db->query($sql);
    if ($res !== false) {
        $return = array();
        $return['count'] = 0;
        $return['wish_satisfied_rate'] = 0.0;
        $return['lineover_rate'] = 0.0;
        $return['line'] = 500;
        //心愿线数量
        $return['count'] = sizeof($res);
        if ($return['count'] === 0) {
            return $return;
        }
        $linecount = 0;
        $truecount = 0;
        foreach ($res as $value) {
            if ($value['cometrue']) {
                $truecount++;
            }
            if ($value['favor'] >= 500) {
                $linecount++;
            }
        }
        $return['lineover_rate'] = $linecount / $return['count'];
        $return['wish_satisfied_rate'] = $truecount / $return['count'];
        return $return;
    } else {
        echo getJsonResponse(1, $db->error, null);
        Log::error_log('database error：' . $db->error . ' in ' . basename(__FILE__));
        //错误日志
        exit;
    }
}

 public function Get($pa_additional_query_params = null, $pa_options = null)
 {
     $ps_query = $this->request->getParameter('q', pString);
     $ps_bundle = $this->request->getParameter('bundle', pString);
     $va_tmp = explode('.', $ps_bundle);
     $vs_table = $va_tmp[0];
     $vs_field = $va_tmp[1];
     $o_dm = Datamodel::load();
     if (!($t_table = $o_dm->getInstanceByTableName($vs_table, true))) {
         // bad table name
         print _t("Invalid table name");
         return null;
     }
     if (!$t_table->hasField($vs_field) || !in_array($t_table->getFieldInfo($vs_field, 'FIELD_TYPE'), array(FT_TEXT, FT_NUMBER))) {
         // bad field name
         print _t("Invalid bundle name");
         return null;
     }
     if ($this->request->user->getBundleAccessLevel($vs_table, $vs_field) == __CA_BUNDLE_ACCESS_NONE__) {
         print _t("You do not have access to this bundle");
         return null;
     }
     $vn_max_returned_values = 50;
     $o_db = new Db();
     $qr_res = $o_db->query("\n\t\t\t\tSELECT DISTINCT {$vs_field}\n\t\t\t\tFROM {$vs_table}\n\t\t\t\tWHERE\n\t\t\t\t\t({$vs_field} LIKE ?) " . ($t_table->hasField('deleted') ? ' AND deleted = 0' : '') . "\n\t\t\t\tORDER BY\n\t\t\t\t\t{$vs_field}\n\t\t\t\tLIMIT {$vn_max_returned_values}\n\t\t\t", (string) $ps_query . '%');
     $this->view->setVar('intrinsic_value_list', $qr_res->getAllFieldValues($vs_field));
     return $this->render('ajax_intrinsic_value_list_html.php');
 }

 public function save()
 {
     if (is_numeric($this->page_id) && is_string($this->page_name)) {
         $db = new Db();
         $id = $db->quote($this->page_id);
         $category_id = $db->quote($this->category_id);
         $name = $db->quote($this->page_name);
         $url = $db->quote($this->url);
         $top_description = $db->quote($this->top_description);
         $bottem_description = $db->quote($this->bottom_description);
         $keyword = $db->quote($this->keyword);
         $title = $db->quote($this->title);
         $description = $db->quote($this->description);
         $access_type = $db->quote($this->access_type);
         $active = $db->quote($this->page_status);
         $author = $db->quote(1);
         $modified = $db->quote(1);
         $query = "INSERT INTO " . $this->tableName() . " (page_id, category_id, name, url, top_description, bottem_description, \n                Keyword, title, description, author, modified_by, access_type,  active) \n                VALUES({$id}, {$category_id},  {$name}, {$url}, {$top_description}, {$bottem_description}, {$keyword}, {$title}, {$description},\n                    {$author}, {$modified}, {$access_type}, {$active})\n                ON DUPLICATE KEY UPDATE    \n                name= {$name}, category_id={$category_id}, url={$url},top_description={$top_description}, bottem_description={$bottem_description}, \n                Keyword={$keyword}, title={$title}, description={$description}, author={$author}, modified_by={$modified}, \n                   active={$active}, access_type={$access_type}";
         if ($db->query($query)) {
             return true;
         } else {
             Error::set($db->error());
         }
     }
     return false;
 }

 /**
  * Записывает видео в топ саммых папулярных видео если оно подходит по балам или видео в топе не привышает MAX_POPULAR_VIDEO_IN_CATEGORY
  * @param $videoId
  * @param $categoriesIds
  * @param $balls
  * @throws Exception
  */
 public function setPopularVideo($videoId, $categoriesIds, $balls)
 {
     if (!empty($categoriesIds)) {
         $categories = explode(',', $categoriesIds);
         foreach ($categories as $category) {
             // Если видео есть в списке популярных то в нем просто обновляются баллы
             $this->db->query("UPDATE video_popular SET balls = :balls WHERE video_id = :videoId AND category_id = :categoryId", ['videoId' => $videoId, 'categoryId' => $category, 'balls' => $balls]);
             if ($this->db->getConnect()->affected_rows < 1) {
                 // если нет видео, получается видео с самым меньшим колличеством баллов
                 $minBallsVideo = $this->db->fetchRow("SELECT SQL_CALC_FOUND_ROWS * FROM video_popular WHERE category_id = :categoryId ORDER BY balls ASC LIMIT 1", ['categoryId' => $category]);
                 $foundRows = $this->db->fetchOne("SELECT FOUND_ROWS();");
                 $insertVideo = false;
                 // если популярных видео в категории меньше чем значение это значит что мы можем просто добавить видео в популярные
                 if ($foundRows < self::MAX_POPULAR_VIDEO_IN_CATEGORY) {
                     $insertVideo = true;
                 } else {
                     if ($minBallsVideo['balls'] < $balls) {
                         // если у текущего видео балл выше чем у видео с наименьшим балом, то заменяем видео с наименьшим балом на текущее.
                         $insertVideo = true;
                         $this->db->query("DELETE FROM video_popular WHERE video_id = :videoId AND category_id = :categoryId", ['videoId' => $minBallsVideo['video_id'], 'categoryId' => $minBallsVideo['category_id']]);
                     }
                 }
                 if ($insertVideo) {
                     $this->db->query("INSERT INTO video_popular (video_id, category_id, balls) VALUES (:videoId, :categoryId, :balls)", ['videoId' => $videoId, 'categoryId' => $category, 'balls' => $balls]);
                 }
             }
         }
     }
 }

 function pagination($messageParPage, $table, $sscategorie)
 {
     /*
     paginatio_array 0->Nbre d'enregistrements
     paginatio_array 1->Nbre de pages
     paginatio_array 2->Pages actuelle
     paginatio_array 3->Première entrée
     */
     $sscategorie = Utils::anti_injection($sscategorie);
     $pagination_array = array();
     $sqlQuery = "SELECT COUNT(*) AS total FROM " . TABLE_PREFIX . $table . " WHERE sscat_radio=" . $sscategorie;
     $getTotal = Db::query($sqlQuery);
     $donnees_total = Db::fetch_assoc($getTotal);
     $pagination_array[0] = $donnees_total['total'];
     $pagination_array[1] = ceil($pagination_array[0] / $messageParPage);
     if (isset($_GET['page'])) {
         $pagination_array[2] = intval($_GET['page']);
         if ($pagination_array[2] > $pagination_array[1] && $pagination_array[1] > 0) {
             $pagination_array[2] = $pagination_array[1];
         }
     } else {
         $pagination_array[2] = 1;
     }
     $pagination_array[3] = ($pagination_array[2] - 1) * $messageParPage;
     return $pagination_array;
 }

 function login()
 {
     $retour = false;
     $login = Db::escape($_POST['login']);
     $getAuth = Db::query("SELECT * FROM `" . TABLE_PREFIX . "admin` WHERE `login_txt` = '" . $login . "'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND `mdp_txt` = '" . Db::escape(md5($_POST['mdp'])) . "'");
     if (Db::num_rows($getAuth) > 0) {
         $_SESSION['key'] = true;
         // Mise en session de la connexion
         $entry = Db::fetch_assoc($getAuth);
         $retour = true;
     } else {
         $getLogin = Db::query("SELECT * FROM `" . TABLE_PREFIX . "admin` WHERE `login_txt` = '" . $login . "'");
         if (Db::num_rows($getLogin) > 0) {
             $this->login = $login;
             $getPwd = Db::query("SELECT * FROM `" . TABLE_PREFIX . "admin` WHERE `login_txt` = '" . $login . "'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND `mdp_txt` = '" . Db::escape(md5($_POST['mdp'])) . "'");
             if (Db::num_rows($getPwd) <= 0) {
                 $this->errors = "pwd";
             }
         } else {
             $this->errors = "login";
         }
         $retour = false;
     }
     return $retour;
 }

 function pagination($messageParPage, $sscategorie, $search, $searchColumn)
 {
     /*
     paginatio_array 0->Nbre d'enregistrements
     paginatio_array 1->Nbre de pages
     paginatio_array 2->Pages actuelle
     paginatio_array 3->Première entrée
     */
     $pagination_array = array();
     if (!empty($search)) {
         $sqlQuery = "SELECT COUNT(*) AS total FROM " . TABLE_PREFIX . CATEGORIE_NOM . " WHERE sscat_radio = '" . $sscategorie . "' AND ";
         for ($i = 0; $i < sizeof($searchColumn); $i++) {
             if ($i != 0 && $i != sizeof($searchColumn)) {
                 $sqlQuery .= "OR ";
             }
             $sqlQuery .= $searchColumn[$i] . " like '%" . Db::escape($search) . "%' ";
         }
     } else {
         $sqlQuery = "SELECT COUNT(*) AS total FROM " . TABLE_PREFIX . CATEGORIE_NOM . " WHERE sscat_radio = '" . $sscategorie . "'";
     }
     $getTotal = Db::query($sqlQuery);
     $donnees_total = Db::fetch_assoc($getTotal);
     $pagination_array[0] = $donnees_total['total'];
     $pagination_array[1] = ceil($pagination_array[0] / $messageParPage);
     if (isset($_POST['page'])) {
         $pagination_array[2] = intval($_POST['page']);
         if ($pagination_array[2] > $pagination_array[1] && $pagination_array[1] > 0) {
             $pagination_array[2] = $pagination_array[1];
         }
     } else {
         $pagination_array[2] = 1;
     }
     $pagination_array[3] = ($pagination_array[2] - 1) * $messageParPage;
     return $pagination_array;
 }

 public function update_ressources()
 {
     $sql = "SELECT income from modifiers WHERE user_id = {$this->id}";
     $req = Db::query($sql);
     $income = $req->fetchColumn();
     $this->increase_ressource(round(get_time_diff($this->last_refresh) * $income), true);
 }