PHP database::fetchAll方法代码示例

 public static function auth($email, $password)
 {
     $database = new database();
     $query = "select * from users where email = '{$email}' " . "and password = '{$password}'" . " and active = 1";
     $database->performQuery($query);
     return $database->fetchAll();
 }

 public function reset()
 {
     $token_handler = new security();
     $token_handler->check_token();
     $content = '';
     // resets users password
     // sends an email containing a link + token with 6h validity
     // from this link, access this same method, but with confirm=yes in url
     if (isset($_GET['confirm'])) {
         if ($_GET['confirm'] == 'yes') {
             // check token with database
             // will arrive here from user's mail - show form to enter new password and UPDATE it in the database
         }
     } else {
         // send email to user with link to reset, redirecting here
         // ?controller=users&action=reset&confirm=yes&token=ETC
         // 1st, check if user + email exist in database
         $connection = new database();
         $sql = "SELECT username, email FROM users WHERE username=?";
         $data[] = $_POST['username'];
         $user_results = $connection->fetchAll($sql, $data);
         if ($connection->row_count = 1) {
             // ok, found one user with this username
             // but, does he/she has an email?
             if ($_POST['email'] != '') {
                 if ($user_results[0]['email'] == $_POST['email']) {
                     // send email with proper link to reset password
                     $content .= "<p>Dear {$_POST['username']}, an email was sent to {$_POST['email']} with instructions on how to reset your password.";
                     $content .= "<p>It should arrive momentarily; if not, check your spam box or contact the administrator.";
                     // TODO: send email to reset password.
                     // Contains a link with a token that redirects to a special page - this only confirms that user has acces to the concerned email
                 } else {
                     $content .= "<p>Email not found or invalid. Please, try again.";
                     $content .= "<p>Contact the administrator if you think you do not have a registered email.";
                 }
             } else {
                 $content .= "<p>Email is obligatory. Please, try again.";
             }
         } else {
             $content .= "User not found. Please, try again!";
         }
     }
     $output['page'] = 'views/forgot.php';
     $output['content'] = $content;
     return $output;
 }

<?php

require 'libraries/database.php';
$idN = $_REQUEST['idN'];
$data = new database("timphongtro");
$sql1 = "select * from taikhoan_o_phongtro where IDTaiKhoan='{$idN}'";
$data->query($sql1);
if ($data->num_rows() != 0) {
    $result1 = $data->fetch();
    $idP = $result1['IDPhongTro'];
    $sql2 = "select * from phongtro where IDPhongTro='{$idP}'";
    $data->query($sql2);
    $result2 = $data->fetch();
    $sql3 = "select * from taikhoan_o_phongtro, taikhoan where taikhoan_o_phongtro.IDTaiKhoan=taikhoan.IDTaiKhoan and IDPhongTro='{$idP}'";
    $data->query($sql3);
    $result3 = $data->fetchAll('user');
    header('Content-type: text/xml');
    echo "<result>";
    echo "<idP>" . $idP . "</idP>";
    echo "<dienTich>" . $result2['DienTich'] . "</dienTich>";
    echo "<gia>" . $result2['GiaPhong'] . "</gia>";
    echo "<khuVuc>" . $result2['KhuVuc'] . "</khuVuc>";
    echo "<diaChi>" . $result2['DiaChi'] . "</diaChi>";
    echo "<boSung>" . $result2['ThongTinBoSung'] . "</boSung>";
    echo "<tinhTrang>" . $result2['TinhTrang'] . "</tinhTrang>";
    echo "<soNguoiO>" . $result2['SoNguoiTrongPhong'] . "</soNguoiO>";
    echo "<danhSachNguoiO>";
    foreach ($result3 as $index => $user) {
        if (is_array($user)) {
            foreach ($user as $key => $value) {
                echo "<" . $key . ">";

 public static function get_all_roles()
 {
     return database::fetchAll(database::select("groups"));
 }

 public static function get_blocks_by_path($url, $place = null)
 {
     $menu_url = page::menu_get_array_key($url);
     $condition = "(visibility like '%%url%' OR visibility like '%%urk%' OR visibility IS NULL)";
     $params = array("%url" => $url, "%urk" => str_replace("%", "\\%", $menu_url));
     if ($place != null) {
         $params['%place'] = $place;
         $condition .= " AND position='%place'";
     }
     return database::fetchAll(database::select("blocks", array(), $condition, $params));
 }

 public static function load_all_groups_permissions($permission)
 {
     return database::fetchAll(database::select("permission_role", array("gid"), "permission='%permission'", array("%permission" => $permission)), pdo::FETCH_COLUMN);
 }

 /**
  * @return mixed
  */
 public function set_values_form()
 {
     // BUG in function: does not work when pre selecting values from joined tables
     // WORKAROUND: set manually the $result value with method set_values_form_manually ($result)
     if (isset($_GET['id'])) {
         if (is_numeric($_GET['id'])) {
             $id = $_GET['id'];
             $data = array($id);
             // for PDO prepared statement, even if it's a single value, needs to be an array
             $cols_str = $this->cols[0];
             // removes the [0] from $this->cols = array(0 => 'nom, prenom, nom_khmer etc');
             $sql = 'SELECT ' . $cols_str . ' FROM ' . $this->table_name . ' WHERE ' . $this->id_column . '=?';
             $connection = new database();
             $result = $connection->fetchAll($sql, $data);
             if ($connection->get_row_num() == 1) {
                 $this->form_values = $result[0];
                 // this removes the [0] from $result array and sets the property form_values
             } else {
                 return false;
             }
         } else {
             return false;
         }
     } else {
         return false;
     }
 }

 public function submit()
 {
     $output = array();
     if (isset($_POST['username']) and isset($_POST['password'])) {
         $username = trim($_POST['username']);
         $password = trim($_POST['password']);
         // TODO: implement password hashing check
         //
         $sql = 'SELECT user_id, email, profile_id, password FROM users WHERE username=?';
         $data = array($username);
         $connection = new database();
         $result = $connection->fetchAll($sql, $data);
         $hash = $result[0]['password'];
         /* REQUIRES php >= 5.5.0
         
                     if (password_verify($password, $hash)) {
                         echo "ok";
                     } else {
                         echo "not ok";
                     }
                     //die();
                     */
         // for PHP <=5.5.0
         //if ($connection->get_row_num()==1 ) {
         if (crypt($password, MY_SALT) == $hash) {
             /* GATEWAY: define here all session variables based on user:
              * User Variables:
              *      1. css_username
              *      2. css_user_id
              *      3. css_email
              *      4. css_profile_id
              * System variables:
              *      1. main_menu
              *      2. upright_menu
              *      3. last_login
              *      4. controllers->actions (array)
              *          [controller][action][permission] where [profile_id]=[user_profile_id]
              *      5. current_school_year -> max school year. If NO school year is configured, insert current year and select it.
              */
             $_SESSION['css_username'] = $username;
             $_SESSION['log'] .= new timestamp("user {$username} has logged in");
             $_SESSION['css_user_id'] = $result[0]['user_id'];
             $_SESSION['css_email'] = $result[0]['email'];
             $_SESSION['css_profile_id'] = $result[0]['profile_id'];
             $_SESSION['user_ip'] = $_SERVER['REMOTE_ADDR'];
             // small security control
             // Requires PECL  extension to work
             //$country = geoip_country_name_by_name($_SESSION['user_ip']);
             $sql = "INSERT INTO login_activity (user_id, profile_id, username, email, ip_address) VALUES ('" . $result[0]['user_id'] . "', '" . $result[0]['profile_id'] . "', '" . $username . "', '" . $result[0]['email'] . "', '" . $_SESSION['user_ip'] . "')";
             $login_activity = $connection->query($sql);
             // TODO: acl structure
             // results comes as:
             // $acl_results = array (
             //      0 => array (
             //          'controller' => 'about',
             //          'c_action'   => 'index',
             //          'active_id'  => 1),
             //      1 => array (....
             // Refactor to:
             // $acl_results_refactored = array(
             //      'about' => array('index'       => 1),
             //
             //      'admin' => array('index'       => 1,
             //                       'log'         => 1,
             //                       'users_index' => 1));
             // 1. extract all controllers from DB which corresponds to user
             // 2. foreach $controllers['controller'] add $c_action and corresponding permission
             $sql = "SELECT controllers.controller, controllers.c_action, acl.active_id\n                        FROM acl\n                        JOIN controllers ON controllers.controller_id = acl.controller_id\n                        WHERE acl.profile_id=?\n                        GROUP BY controllers.controller_id ASC\n                        ";
             $data = array($_SESSION['css_profile_id']);
             $acl_results = $connection->fetchAll($sql, $data);
             //var_dump($acl_results);
             $acl_map = array();
             $i = 0;
             foreach ($acl_results as $row) {
                 $acl_map[$row['controller'] . '.' . $row['c_action']] = $row['active_id'];
                 // preferable way to add a single row to an existing array
                 $i++;
             }
             //var_dump ($acl_map);
             $_SESSION['acl_map'] = $acl_map;
             $date = new DateTime();
             $_SESSION['last_login'] = $date->format('U');
             $sql = "SELECT school_year_id, school_year\n                        FROM school_years\n                        ORDER BY school_year DESC\n                        LIMIT 1";
             $school_years_result = $connection->query($sql);
             if ($connection->get_row_num() == 0) {
                 // no school year has been registered, INSERT INTO school_years the current school year
                 $date = new DateTime();
                 $current_year = $date->format('Y');
                 $current_month = $date->format('m');
                 if ($current_month >= 9 and $current_month <= 12) {
                     $current_school_year = $current_year . '/' . ($current_year + 1);
                 } else {
                     $current_school_year = $current_year - 1 . '/' . $current_year;
                 }
                 $current_school_year = strval($current_school_year);
                 $insert_school_year_sql = "INSERT INTO school_years (school_year)\n                                               VALUES ('" . $current_school_year . "')";
                 $connection->query($insert_school_year_sql);
                 // Get last school_year_id and assign to $_SESSION['current....
                 $_SESSION['current_school_year_id'] = $connection->last_Inserted_id();
                 $_SESSION['current_school_year'] = $current_school_year;
//.........这里部分代码省略.........

 public static function list_of_declared_themes()
 {
     $res = database::fetchAll(database::select("theme_manager"));
     return is_array($res) ? $res : array();
 }

 public static function get_all_menus()
 {
     return database::fetchAll(database::select("menu"));
 }

 public static function list_of_declared_modules()
 {
     $res = database::fetchAll(database::select("module_manager", array(), "1 ORDER BY module_type, module_name"));
     return $res;
 }

 public static function get_all_permissions()
 {
     return database::fetchAll(database::select("permission"));
 }

                     echo "</" . $key . ">";
                 }
             }
         }
         echo "</result>";
     } else {
         header('Content-type: text/xml');
         echo "<result>" . "false" . "</result>";
     }
 }
 $sql4 = "select * from phongdexuat where KhuVuc='{$khuVuc}' and DienTich>='{$dienTichMin}' and DienTich<='{$dienTichMax}' and GiaPhong>='{$giaMin}' and GiaPhong<='{$giaMax}'";
 $data2->query($sql4);
 $n4 = $data2->num_rows();
 if ($kieuPhong == "deXuat") {
     if ($n4 != 0) {
         $result4 = $data2->fetchAll("phong");
         header('Content-type: text/xml');
         echo "<result>";
         foreach ($result4 as $index => $room) {
             if (is_array($room)) {
                 foreach ($room as $key => $value) {
                     echo "<" . $key . ">";
                     if (is_array($value)) {
                         echo "<idP>" . $value['IDPhongDeXuat'] . "</idP>";
                         echo "<kieu>" . "deXuat" . "</kieu>";
                         echo "<khuVuc>" . $value['KhuVuc'] . "</khuVuc>";
                         echo "<dienTich>" . $value['DienTich'] . "</dienTich>";
                         echo "<gia>" . $value["GiaPhong"] . "</gia>";
                     }
                     echo "</" . $key . ">";
                 }

 public function setschoolyear()
 {
     // set school year : comes from drop down $_POST
     if (isset($_POST['school_year_id'])) {
         if (is_numeric($_POST['school_year_id'])) {
             $school_year_handle = new database();
             $sql = "SELECT school_year FROM school_years WHERE school_year_id=?";
             $data = array($_POST['school_year_id']);
             $result = $school_year_handle->fetchAll($sql, $data);
             if ($school_year_handle->get_row_num() == 1) {
                 $_SESSION['current_school_year_id'] = $_POST['school_year_id'];
                 $_SESSION['current_school_year'] = $result[0]['school_year'];
             } else {
             }
         }
     }
     //echo $_SESSION['school_year'].'<br>';
     //var_dump ($result);
     //die();
     header("Location: http://" . WEBSITE_URL . "/index.php?controller={$_GET['controller']}&action=index");
 }

 public static function node_load_all()
 {
     return database::fetchAll(database::select("node", array(), "1 ORDER BY date DESC"));
 }