PHP DBLayer類代碼示例

/**
 * This function is beign used to change the users emailaddress info.
 * It will first check if the user who executed this function is the person of whom the emailaddress is or if it's a mod/admin. If this is not the case the page will be redirected to an error page.
 * The emailaddress will be validated first. If the checking was successful the email will be updated and the settings template will be reloaded. Errors made by invalid data will be shown
 * also after reloading the template.
 * @author Daan Janssens, mentored by Matthew Lagoe
 */
function userRegistration()
{
    try {
        //if logged in
        if (WebUsers::isLoggedIn()) {
            $dbl = new DBLayer("lib");
            $dbl->update("settings", array('Value' => $_POST['userRegistration']), "`Setting` = 'userRegistration'");
            $result['target_id'] = $_GET['id'];
            global $SITEBASE;
            require_once $SITEBASE . '/inc/settings.php';
            $pageElements = settings();
            $pageElements = array_merge(settings(), $result);
            $pageElements['permission'] = unserialize($_SESSION['ticket_user'])->getPermission();
            // pass error and reload template accordingly
            helpers::loadtemplate('settings', $pageElements);
            throw new SystemExit();
        } else {
            //ERROR: user is not logged in
            header("Location: index.php");
            throw new SystemExit();
        }
    } catch (PDOException $e) {
        //go to error page or something, because can't access website db
        print_r($e);
        throw new SystemExit();
    }
}

/**
 * This function is used in deactivating plugins.
 * This can be done by providing id using $_GET global variable of the plugin which
 * we want to activate. After getting id we update the respective plugin with status
 * deactivate which here means '0'.
 *
 * @author Shubham Meena, mentored by Matthew Lagoe
 */
function deactivate_plugin()
{
    // if logged in
    if (WebUsers::isLoggedIn()) {
        if (isset($_GET['id'])) {
            // id of plugin to deactivate
            $id = filter_var($_GET['id'], FILTER_SANITIZE_FULL_SPECIAL_CHARS);
            $db = new DBLayer('lib');
            $result = $db->update("plugins", array('Status' => '0'), "Id = {$id}");
            if ($result) {
                // if result is successfull it redirects and shows success message
                header("Cache-Control: max-age=1");
                header("Location: index.php?page=plugins&result=5");
                throw new SystemExit();
            } else {
                // if result is unsuccessfull it redirects and shows success message
                header("Cache-Control: max-age=1");
                header("Location: index.php?page=plugins&result=6");
                throw new SystemExit();
            }
        } else {
            //if $_GET variable is not set it redirects and shows error
            header("Cache-Control: max-age=1");
            header("Location: index.php?page=plugins&result=6");
            throw new SystemExit();
        }
    }
}

/**
 * This function is used in deleting plugins.
 * It removes the plugin from the codebase as well as
 * from the Database. When user request to delete a plugin
 * id of that plugin is sent in $_GET global variable.
 *
 * @author Shubham Meena, mentored by Matthew Lagoe
 */
function delete_plugin()
{
    // if logged in
    if (WebUsers::isLoggedIn()) {
        if (isset($_GET['id'])) {
            // id of plugin to delete after filtering
            $id = filter_var($_GET['id'], FILTER_SANITIZE_FULL_SPECIAL_CHARS);
            $db = new DBLayer('lib');
            $sth = $db->selectWithParameter("FileName", "plugins", array('id' => $id), "Id=:id");
            $name = $sth->fetch();
            if (is_dir("{$name['FileName']}")) {
                // removing plugin directory from the code base
                if (Plugincache::rrmdir("{$name['FileName']}")) {
                    $db->delete('plugins', array('id' => $id), "Id=:id");
                    //if result	successfull redirect and show success message
                    header("Cache-Control: max-age=1");
                    header("Location: index.php?page=plugins&result=2");
                    throw new SystemExit();
                } else {
                    // if result unsuccessfull redirect and show error message
                    header("Cache-Control: max-age=1");
                    header("Location: index.php?page=plugins&result=0");
                    throw new SystemExit();
                }
            }
        } else {
            // if result unsuccessfull redirect and show error message
            header("Cache-Control: max-age=1");
            header("Location: index.php?page=plugins&result=0");
            throw new SystemExit();
        }
    }
}

/**
* This function is beign used to load info that's needed for the settings page.
* check if the person who wants to view this page is a mod/admin or the user to whom te settings belong himself, if this is not the case, he will be redirected to an error page.
* it will return a lot of information of that user, that's being used for loading the template.
* @author Daan Janssens, mentored by Matthew Lagoe
*/
function settings()
{
    if (WebUsers::isLoggedIn()) {
        //in case id-GET param set it's value as target_id, if no id-param is given, ue the session id.
        if (isset($_GET['id'])) {
            if ($_GET['id'] != $_SESSION['id'] && !Ticket_User::isMod(unserialize($_SESSION['ticket_user']))) {
                //ERROR: No access!
                $_SESSION['error_code'] = "403";
                header("Cache-Control: max-age=1");
                header("Location: index.php?page=error");
                throw new SystemExit();
            } else {
                $webUser = new Webusers($_GET['id']);
                $result = $webUser->getInfo();
                if (Ticket_User::isMod(unserialize($_SESSION['ticket_user'])) && $_GET['id'] != $_SESSION['id']) {
                    $result['changesOther'] = "TRUE";
                }
                $result['target_id'] = $_GET['id'];
                $result['current_mail'] = $webUser->getEmail();
                $result['target_username'] = $webUser->getUsername();
            }
        } else {
            $webUser = new Webusers($_SESSION['id']);
            $result = $webUser->getInfo();
            $result['target_id'] = $_SESSION['id'];
            $result['current_mail'] = $webUser->getEmail();
            $result['target_username'] = $webUser->getUsername();
        }
        //Sanitize Data
        $result['current_mail'] = filter_var($result['current_mail'], FILTER_SANITIZE_EMAIL);
        $result['target_username'] = filter_var($result['target_username'], FILTER_SANITIZE_STRING);
        $result['FirstName'] = filter_var($result['FirstName'], FILTER_SANITIZE_STRING);
        $result['LastName'] = filter_var($result['LastName'], FILTER_SANITIZE_STRING);
        $result['Country'] = filter_var($result['Country'], FILTER_SANITIZE_STRING);
        $result['Gender'] = filter_var($result['Gender'], FILTER_SANITIZE_NUMBER_INT);
        $result['ReceiveMail'] = filter_var($result['ReceiveMail'], FILTER_SANITIZE_NUMBER_INT);
        $result['country_array'] = getCountryArray();
        global $INGAME_WEBPATH;
        $result['ingame_webpath'] = $INGAME_WEBPATH;
        $dbl = new DBLayer("lib");
        $statement = $dbl->executeWithoutParams("SELECT * FROM settings");
        $rows = $statement->fetchAll();
        foreach ($rows as &$value) {
            $result[$value['Setting']] = $value['Value'];
        }
        return $result;
    } else {
        //ERROR: not logged in!
        header("Location: index.php");
        header("Cache-Control: max-age=1");
        throw new SystemExit();
    }
}

function isKnownUser($username, $userpass = null)
{
    global $db, $CNF;
    //    echo $username;
    if (strlen($username) > 0) {
        $db = new DBLayer($CNF["db_host"], $CNF["db_user"], $CNF["db_pass"], $CNF["db_name"]);
        $db->query("SET NAMES utf8");
        $sql_pass = $userpass != null ? "`status_id` !=4 AND `pass`='{$userpass}'" : '`status_id` !=4';
        $query = $db->query("SELECT `uid`,`login`,`lastname`,`firstname`,`middlename` FROM users WHERE `login`='{$username}' AND {$sql_pass}");
        if ($db->num_rows($query) > 0) {
            $auth = $db->fetch_assoc($query);
            return $auth;
        }
    }
    return false;
}

 private function getTableDescription()
 {
     $tname = $this->__get_entity_name();
     if (!isset($_SESSION['makiavelo']['t_descriptions'][$tname])) {
         $_SESSION['makiavelo']['t_descriptions'][$tname] = DBLayer::describeTable($tname);
     }
     return $_SESSION['makiavelo']['t_descriptions'][$tname];
 }

 public static function query($sql)
 {
     $db = DBLayer::connect();
     $return = mysql_query($sql, $db);
     if (!$return) {
         Makiavelo::info("Error on MYSQL Query:: " . mysql_error());
     }
     return $return;
 }

 /**
  * Constructor.
  * will fetch the correct elements that match to a specific page (specified by the $_GET['pagenum'] variable). The query has to be passed as a string to the function
  * that way it will only load the specific elements that are related to the pagenumber. The $params, parameter is optional and is used to pass the parameters for the query.
  * The result class will be used to instantiate the found elements with, their set() function will be called. The class  its getters can be later used to get the info out of the object.
  * @param $query the query to be paginated
  * @param $db the db on which the query should be performed
  * @param $nrDisplayed the amount of elements that should be displayed /page
  * @param $resultClass the elements that should be returned should be of that specific class.
  * @param $params the parameters used by the query (optional)
  */
 function __construct($query, $db, $nrDisplayed, $resultClass, $params = array())
 {
     if (!isset($_GET['pagenum'])) {
         $this->current = 1;
     } else {
         $this->current = $_GET['pagenum'];
     }
     //Here we count the number of results
     $db = new DBLayer($db);
     $rows = $db->execute($query, $params)->rowCount();
     $this->amountOfRows = $rows;
     //the array hat will contain all users
     if ($rows > 0) {
         //This is the number of results displayed per page
         $page_rows = $nrDisplayed;
         //This tells us the page number of our last page
         $this->last = ceil($rows / $page_rows);
         //this makes sure the page number isn't below one, or more than our maximum pages
         if ($this->current < 1) {
             $this->current = 1;
         } else {
             if ($this->current > $this->last) {
                 $this->current = $this->last;
             }
         }
         //This sets the range to display in our query
         $max = 'limit ' . ($this->current - 1) * $page_rows . ',' . $page_rows;
         //This is your query again, the same one... the only difference is we add $max into it
         $data = $db->execute($query . " " . $max, $params);
         $this->element_array = array();
         //This is where we put the results in a resultArray to be sent to smarty
         while ($row = $data->fetch(PDO::FETCH_ASSOC)) {
             $element = new $resultClass();
             $element->set($row);
             $this->element_array[] = $element;
         }
     }
 }

function checkInputValues()
{
    global $fdb;
    // Check connection
    $conn = @mysql_connect($_SESSION['hostname'], $_SESSION['username'], $_SESSION['password']);
    if (!$conn) {
        myerror('Unable to connect to MySQL server. Please check your settings again.<br><br><a href="?page=settings">Go back to settings</a>');
    }
    // Check databases
    if (!@mysql_select_db($_SESSION['php_db_clean'], $conn)) {
        // Fetch database list
        $list = '';
        $result = @mysql_query('SHOW databases', $conn);
        while ($ob = mysql_fetch_row($result)) {
            $list .= ' &nbsp <a href="?page=settings&newdb=' . $ob[0] . '">' . $ob[0] . '</a><br>' . "\n";
        }
        // Close connection and show message
        mysql_close($conn);
        myerror('Unable to select database.' . '<br><br>Found these databases:<br><font color="gray">' . $list . '</font>' . '<br><a href="?page=settings">Go back to settings</a>');
    }
    mysql_close($conn);
    // Include FORUM's config file
    include './' . $_SESSION['forum'] . '/_config.php';
    // Check prefix
    $fdb = new DBLayer($_SESSION['hostname'], $_SESSION['username'], $_SESSION['password'], $_SESSION['php_db_clean'], $_SESSION['php_prefix'], false);
    $res = $fdb->query('SELECT count(*) FROM ' . $_SESSION['php'] . $tables['Users']);
    if (intval($fdb->result($res, 0)) == 0) {
        // Select a list of tables
        $list = array();
        $res = $fdb->query('SHOW TABLES IN ' . $_SESSION['php_db']);
        while ($ob = $fdb->fetch_row($res)) {
            $list[] = $ob[0];
        }
        // check list size
        sizeof($list) == 0 ? $list[] = 'None' : null;
        // Get list of "proabable" prefixes
        $prefix_list = '';
        $res = $fdb->query('SHOW TABLES FROM ' . $_SESSION['php_db'] . ' LIKE \'%' . $tables['Posts'] . '\'') or myerror('Unable to fetch table list', __FILE__, __LINE__, $fdb->error());
        //			$res = $fdb->query('SHOW TABLES FROM '.$_SESSION['php_db'].' LIKE \'%'.$tables['Users'].'\'') or myerror('Unable to fetch table list', __FILE__, __LINE__, $fdb->error());
        while ($ob = $fdb->fetch_row($res)) {
            $prefix = substr($ob[0], 0, strlen($ob[0]) - strlen($tables['Users']));
            $prefix_list .= ' &nbsp; <a href="?page=settings&newprefix=' . $prefix . '">' . $prefix . '</a><br>' . "\n";
        }
        // Print message
        $prefix = $_SESSION['php_prefix'] == '' ? 'no' : '\'' . $_SESSION['php_prefix'] . '\'';
        myerror('Unable to find ' . $_SESSION['forum'] . ' tables! (using prefix: <i>' . $prefix . '</i>)' . '<br><br>Go back to settings and choose another prefix, or select one of these prefixes:<br><font color="gray">' . $prefix_list . '</font>' . '<br>These are the tables in the selected database:<br><font color="gray"> &nbsp; ' . implode("<br> &nbsp; ", $list) . '</font>' . '<br><br><a href="?page=settings">Go back to settings</a>');
    }
}

/**
 * This function is used in installing updates for plugins.
 * It takes id of the plugin whose update is available using
 * $_GET global variable and then extract the update details
 * from db and then install it in the plugin.
 *
 * @author Shubham Meena, mentored by Matthew Lagoe
 */
function update_plugin()
{
    // if logged in
    if (WebUsers::isLoggedIn()) {
        if (isset($_GET['id'])) {
            // id of plugin to update
            $id = filter_var($_GET['id'], FILTER_SANITIZE_FULL_SPECIAL_CHARS);
            $db = new DBLayer('lib');
            $sth = $db->executeWithoutParams("SELECT * FROM plugins INNER JOIN updates ON plugins.Id=updates.PluginId Where plugins.Id={$id}");
            $row = $sth->fetch();
            // replacing update in the  database
            Plugincache::rrmdir($row['FileName']);
            Plugincache::zipExtraction($row['UpdatePath'], rtrim($row['FileName'], strtolower($row['Name'])));
            $db->update("plugins", array('Info' => $row['UpdateInfo']), "Id={$row['Id']}");
            // deleting the previous update
            $db->delete("updates", array('id' => $row['s.no']), "s.no=:id");
            // if update is installed succesffully redirect to show success message
            header("Cache-Control: max-age=1");
            header("Location: index.php?page=plugins&result=8");
            throw new SystemExit();
        }
    }
}

 public function execute($params)
 {
     Makiavelo::info("Creating Database...");
     $sql_folder_path = ROOT_PATH . Makiavelo::SQL_CREATE_TABLES_FOLDER;
     Makiavelo::puts("Creating database...");
     $conn = DBLayer::connect();
     $db_name = DBLayer::getDBName();
     $sql = "CREATE DATABASE `{$db_name}`";
     if (!mysql_query($sql, $conn)) {
         Makiavelo::info("ERROR creating db: " . mysql_error());
     }
     //We also have to create the migrations table
     $sql_migrations = "CREATE TABLE migrations ( migration INT PRIMARY KEY);";
     mysql_select_db($db_name);
     if (!mysql_query($sql_migrations, $conn)) {
         Makiavelo::info("ERROR creating migrations table:: " . mysql_error());
     }
     DBLayer::disconnect($conn);
 }

 private function loadEntities()
 {
     $sql_folder_path = ROOT_PATH . Makiavelo::SQL_CREATE_TABLES_FOLDER;
     $d = dir($sql_folder_path);
     while (($item = $d->read()) != false) {
         if ($item != "create_db.sql" && substr($item, 0, 1) != ".") {
             $file_path = $sql_folder_path . "/" . $item;
             $fp = fopen($file_path, "r");
             if ($fp) {
                 Makiavelo::puts("Loading entity: {$item} ...");
                 $conn = DBLayer::connect();
                 $sql = fread($fp, filesize($file_path));
                 fclose($fp);
                 $res = mysql_query($sql, $conn);
                 if (!$res && mysql_errno($conn) == 1050) {
                     Makiavelo::puts("---- Entity already loaded, ignoring");
                 }
                 DBLayer::disconnect($conn);
             }
         }
     }
 }

 /**
 Allows the dev to modify the structure of a table:
 Supported operations:
 
 - add_field
 - drop_field
 */
 protected function alter_table($tname, $params)
 {
     global $__db_conn;
     foreach ($params as $operation => $parms) {
         switch ($operation) {
             case "add_field":
                 $keys = array_keys($parms);
                 $new_field = $keys[0];
                 $type = $this->sql_types_mapping[$parms[$new_field]];
                 $sql = "ALTER TABLE {$tname} ADD COLUMN {$new_field} {$type}";
                 break;
             case "drop_field":
                 $new_field = $parms;
                 $sql = "ALTER TABLE {$tname} drop column {$new_field} ";
                 break;
             default:
                 break;
         }
         Makiavelo::info("Altering table :: " . $sql);
         DBLayer::query($sql);
     }
 }

public function saveRecipeWork()
{
$dbl=new DBLayer();
$dbl->setCollectionObj($this->RecipeColname);
$obj=$this->CreateRecipeArray();//prepare_array_Recipework();
$dbl->InsertCollection($obj);
//$cursor = $dbl->get_CollectionObject($this->RecipeColname);//,$this->objID);
}

        $arr[$i]['cl'] = $result['class'];
        $arr[$i]['race'] = $result['race'];
        $arr[$i]['level'] = $char_data[$UNIT_FIELD_LEVEL];
        $arr[$i]['gender'] = $char_gender[3];
        $arr[$i]['Extention'] = $Extention;
        $arr[$i]['leaderGuid'] = isset($groups[$char_data[0]]) ? $groups[$char_data[0]] : 0;
        $i++;
    }
    $mangos_db->close();
    usort($arr, "sort_players");
    $arr = array_merge($Count, $arr);
    $res['online'] = $arr;
} else {
    $res['online'] = NULL;
}
if ($show_status) {
    $mangos_db = new DBLayer($mangos[$royaume]['host'], $mangos[$royaume]['user'], $mangos[$royaume]['password'], $mangos[$royaume]['db']);
    $mangos_db->query("SET NAMES " . $mangos[$royaume]['encoding'] . "");
    $query = $mangos_db->query("SELECT `starttime`,`maxplayers` FROM `uptime` WHERE `starttime`=(SELECT MAX(`starttime`) FROM `uptime`)");
    if ($result = $mangos_db->fetch_assoc($query)) {
        $status['uptime'] = time() - $result['starttime'];
        $status['maxplayers'] = $result['maxplayers'];
        $status['online'] = test_realm() ? 1 : 0;
    }
    $mangos_db->close();
} else {
    $status = NULL;
}
unset($mangos_db);
$res['status'] = $status;
$_RESULT = $res;