PHP dibi::query方法代码示例

 public function renderDefault($url)
 {
     $res = dibi::query('
   SELECT
         workId,
         url as file,
         title,
         text,', Model::sqlCategory() . ' as category,
         authorUrl,
         CONCAT_WS(" ", name, surname) as authorName,
         year,', Model::sqlWorkClassName() . 'as workClass,', 'award,
         type, 
         pages,
         words,
         characters,
         [read],
         added,
         edited             
         FROM [works] 
         join [authors] on author = authorId
         WHERE `url`=%s', $url)->fetchAll();
     //$res[0]['award'] = ($res[0]['award'] != 99) ? $res[0]['award'] . ". místo" : "nominaci";
     //2010-08-19 22:43:25
     $res[0]['added'] = preg_replace('/(\\d{4})-(\\d{2})-(\\d{2}) (\\d{2}):(\\d{2}):(\\d{2})/', '$3.$2.$1 $4:$5', $res[0]['added']);
     $res[0]['edited'] = preg_replace('/(\\d{4})-(\\d{2})-(\\d{2}) (\\d{2}):(\\d{2}):(\\d{2})/', '$3.$2.$1 $4:$5', $res[0]['edited']);
     $this->template->data = $res[0];
     $this->template->files = FileModel::getFiles($res[0]['workId']);
     Model::increaseRead($res[0]['workId']);
 }

 /**
  * Performs an authentication
  *
  * @param  array
  * @return void
  * @throws AuthenticationException
  */
 public function authenticate(array $credentials)
 {
     $username = $credentials[self::USERNAME];
     $password = $credentials[self::PASSWORD];
     $sql = dibi::query('SELECT * FROM [' . TABLE_USERS . '] WHERE name=%s;', $username);
     $sql->setType('permission', Dibi::BOOL);
     $row = $sql->fetch();
     /*if (count($sql)==0) {
           throw new AuthenticationException('Unknown user', self::IDENTITY_NOT_FOUND);
       }*/
     /*if ($row->password !== md5($password)) {
           throw new AuthenticationException('Password not match', self::INVALID_CREDENTIAL);
       }*/
     if (count($sql) == 0 or $row->password !== md5($password)) {
         throw new AuthenticationException('Incorrect password or name', self::INVALID_CREDENTIAL);
     }
     // get roles
     $sql = dibi::query('SELECT r.key_name
                             FROM [' . TABLE_ROLES . '] AS r
                             RIGHT JOIN [' . TABLE_USERS_ROLES . '] AS us ON r.id=us.role_id
                             WHERE us.user_id=%i;', $row->id);
     $roles = $sql->fetchPairs();
     unset($row->password);
     return new Identity($username, $roles, $row);
 }

/**
 * Metainformation catalogue
 * --------------------------------------------------
 *
 * MICKA_LIB_INSERT.PHP for MicKa
 *
 * @link       http://www.bnhelp.cz
 * @package    Micka
 * @category   Metadata
 * @version    20101120
 * @authors		 DZ
 */
function deleteMd($user, $type, $value, $mode, $par = NULL)
{
    setMickaLog('micka_lib_insert.php (deleteMd)', 'DEBUG', "user={$user}, {$type}={$value}, mode={$mode}, par={$par}");
    $rs = FALSE;
    // autorizace
    if ($user == 'guest' || !canAction()) {
        return $rs;
    }
    $record = getMdHeader($type, $value, $col = '', $fetch = array('all', '='));
    if (isset($record[0]['RECNO']) && $record[0]['RECNO'] > -1) {
        if (!getMdRight('edit', $user, $record[0]['DATA_TYPE'], $record[0]['CREATE_USER'], $record[0]['EDIT_GROUP'], $record[0]['VIEW_GROUP'])) {
            return $rs;
        } else {
            $sql = array();
            if ($mode == 'all') {
                array_push($sql, 'DELETE FROM [md_values] WHERE [recno]=%i;', $record[0]['RECNO']);
                array_push($sql, 'DELETE FROM [md] WHERE [recno]=%i;', $record[0]['RECNO']);
            } elseif ($mode == 'value') {
                array_push($sql, 'DELETE FROM [md_values] WHERE [recno]=%i  AND md_id<>38;', $record[0]['RECNO']);
            }
            dibi::begin();
            try {
                dibi::query($sql);
                dibi::commit();
                $rs = TRUE;
            } catch (DibiException $e) {
                setMickaLog($e, 'ERROR', 'micka_lib_insert.php (deleteMd)');
                dibi::rollback();
            }
        }
    }
    //Debug::dump($rs);
    setMickaLog('micka_lib_insert.php (deleteMd)', 'DEBUG', "return={$rs}");
    return $rs;
}

 /**
  *
  */
 public function indexAction()
 {
     die('todo');
     $overwrite = !is_null(Core_Request::getInstance()->getArgv(4)) ? Core_Request::getInstance()->getArgv(4) : false;
     foreach (dibi::getDatabaseInfo()->getTables() as $table_data) {
         if ($table_data->getName() == Migration_MigrationModel::getTableName()) {
             continue;
         }
         $ddl_data = dibi::query('SHOW CREATE TABLE ' . $table_data->getName())->fetch()->toArray();
         $ddl_query = $ddl_data['Create Table'];
         $migration_time = time();
         $migration_name = 'Create' . ucfirst($table_data->getName());
         $migration_name = str_replace(' ', '', $migration_name);
         $migration_name = str_replace('-', '', $migration_name);
         $migration_name = str_replace('_', '', $migration_name);
         $filename = cfg()->migration_path . $migration_name . '.php';
         if (Core_Files::fileSize($filename) && !$overwrite) {
             echo PHP_EOL . 'Migration "Create ' . ucfirst($table_data->getName()) . '" Exists' . PHP_EOL;
             continue;
         }
         $template_data = Core_Files::getContent(cfg()->migration_path . Migration_FilesHelper::TEMPLATE_FILE_NAME);
         $template_data = str_replace('Template', $migration_name, $template_data);
         $template_data = str_replace('__NAME__', $migration_name, $template_data);
         $template_data = str_replace('__CREATED_AT__', $migration_time, $template_data);
         $template_data = str_replace('__CREATED_CFG__', Core_Request::getInstance()->getArgv(1), $template_data);
         $template_data = preg_replace('#//__UP_ACTION__#', $ddl_query, $template_data);
         $down_query = 'DROP TABLE IF EXISTS `' . $table_data->getName() . '`';
         $template_data = preg_replace('#//__DOWN_ACTION__#', $down_query, $template_data);
         Core_Files::putContent($filename, $template_data);
         echo PHP_EOL . 'Migration ' . $filename . ' created' . PHP_EOL;
     }
 }

 public function loadGroupsByUserId($id)
 {
     $query = "SELECT `id`, `pid`, `title`, `description` FROM\n\t\t\t`" . Config::DB_PREFIX . "user_groups` ug\n\t\t\tJOIN  `" . Config::DB_PREFIX . "groups` g ON g.`id` = ug.`group_id`\n\t\t\tWHERE ug.`user_id` = " . $id;
     $result = dibi::query($query)->fetchAssoc('id');
     $this->importRecord($result);
     return $this;
 }

    function renderXml_old()
    {
        //$this->id_lang
        $this->template->items = dibi::query('
SELECT 
product.id_product AS id, 
product_lang.name, 
product_lang.meta_description, 
product_lang.link_rewrite AS product_link,
category_product.id_category AS id_category,
category_lang.name AS category_name,
category_lang.link_rewrite AS category_link

FROM `product`
LEFT JOIN product_lang USING (id_product)
LEFT JOIN category_product USING (id_product)
LEFT JOIN category_lang USING (id_category)
WHERE product_lang.id_lang = 1 AND
 category_lang.id_lang = 1
GROUP BY (id_product)')->fetchAll();
        $this->template->baseUri = 'http://' . $_SERVER['HTTP_HOST'];
        $this->id_lang = 1;
        for ($i = 0; $i < count($this->template->items); $i++) {
            $this->template->items[$i]['url'] = $this->link(':Front:Product:default', array('id' => $this->template->items[$i]['id'], 'id_category' => $this->template->items[$i]['id_category']));
            $this->template->items[$i]['image'] = ProductModel::getImage($this->template->items[$i]['id']);
            $this->template->items[$i]['price_vat'] = round($productWithLowestPrice['price'], 2);
            $this->template->items[$i]['price'] = round($this->template->items[$i]['price_vat'] / 1.2, 2);
            $this->template->items[$i]['vat'] = '0.20';
            $this->template->items[$i]['size'] = $productWithLowestPrice['size'];
            $this->template->items[$i]['material'] = $productWithLowestPrice['material'];
        }
        dde($this->template->items);
    }

 private function loadAll()
 {
     // @todo odchytavat vyjimky z dibi pro pripad neexistujici tabulky.
     $query = "SELECT `module_functions_id`, `webinstance_id`, `pageid_id`, `command` FROM `" . Config::DB_PREFIX . 'routedynamic_' . $this->langSymbol . "`";
     $record = dibi::query($query)->fetchAll();
     $this->importRecord($record);
 }

 public function __construct($app)
 {
     $app->post('/rest/login/', function () use($app) {
         $user = json_decode($app->request->getBody(), true);
         if (!isset($user['username']) || empty($user['username'])) {
             $app->error();
         }
         if (!isset($user['password']) || empty($user['password'])) {
             $app->error();
         }
         $result = \dibi::query('select username FROM ::user where username = %s and password = %s', $user['username'], md5($user['password']))->fetchAll();
         if (sizeof($result) != 1) {
             return $app->error();
         }
         $oldToken = \dibi::query('select token from ::oauth where username = %s', $user['username'])->fetch();
         $token = bin2hex(openssl_random_pseudo_bytes(16));
         $auth = array();
         $auth["username"] = $user['username'];
         $auth["token"] = $token;
         if ($oldToken["token"]) {
             \dibi::query('update ::oauth set token = %s where username = %s', $token, $user['username']);
         } else {
             \dibi::query('insert into ::oauth', $auth);
         }
         echo json_encode($token);
     });
 }

 public function beforeRender()
 {
     $filters = $this->getUniqueValues(array('works' => array('year', 'award', 'type'), 'authors' => array('class')));
     $temp = $filters['authors'];
     unset($filters['authors']);
     //$filters['works']['category'] = Model::palicky();// + array(5=>'palice');    // 'category'=>'Kategorie',
     $filters['works']['grade'] = Model::rocniky();
     $filters['palicka']['category'] = Model::palicky();
     $filters['authors'] = $temp;
     //{link :Front:Default:works "category"=>palicka}
     $this->template->filters = $filters;
     $this->template->schoolYear = Model::getSchoolYear();
     $newest = dibi::query('SELECT
       url,
       title,
       CONCAT_WS(" ", name, surname) as authorName          
       FROM [works] 
       join [authors] on author = authorId', 'ORDER BY %by', array('added' => 'desc'), 'LIMIT %i', 2)->fetchAll();
     $this->template->newest = $newest;
     $mostread = dibi::query('SELECT
       url,
       title,
       CONCAT_WS(" ", name, surname) as authorName          
       FROM [works] 
       join [authors] on author = authorId', 'ORDER BY %by', array('read' => 'desc'), 'LIMIT %i', 3)->fetchAll();
     $this->template->mostread = $mostread;
     $user = Environment::getUser();
     if ($user->isLoggedIn()) {
         $this->template->adminMode = true;
     }
 }

 function saveArticle($values, $id_node)
 {
     $tmp = dibi::fetch("SELECT * FROM article WHERE id_node=%i", $id_node);
     unset($values['id_node']);
     //odstranenie premennych ktore niesu v databaze
     $values = Tools::getValuesForTable('article', $values);
     $name = NStrings::webalize($values['url_identifier']);
     $newname = "";
     $i = 0;
     while ($i < 50) {
         if ($i == 0) {
             $newname = $name;
         } else {
             $newname = NStrings::webalize($name . $i);
         }
         $s = dibi::fetchSingle("SELECT COUNT(id_node) FROM article WHERE url_identifier=%s", $newname, " AND id_node!=%i", $id_node);
         if ($s == 0) {
             break;
         } else {
             $i++;
         }
     }
     $values['url_identifier'] = $newname;
     dibi::query("UPDATE article SET ", $values, " WHERE id_node=%i", $id_node);
     NEnvironment::getService('Article')->invalidateCache();
 }

 /**
  * Nacte data.
  *
  * @return Bobr_Page_Page
  * @throws Bobr_Page_PageIAException Pokud neni vyplnena vlastnosti id.
  * @throws Bobr_Page_PageException Pokud nenajde zadne data.
  */
 public function load()
 {
     if (0 > $this->id) {
         throw new Bobr_Page_PageIAException('Neni zadano pageId ktere se ma nacist.');
     }
     if (!$this->loadFromCache()) {
         $query = 'SELECT `container_id`, `block_id`, `weight`
             FROM `' . Config::DB_PREFIX . 'pageid_container_block`
             WHERE `pageid_id` = ' . $this->id . ' ORDER BY `container_id`, `weight`';
         $data = dibi::query($query)->fetchAssoc('container_id,block_id');
         if (empty($data)) {
             throw new Bobr_Page_PageException('Zadana stranka nema zadne data.');
         }
         // Naimportujem data.
         $this->importRecord($data);
         try {
             $this->setPageId(new Bobr_Page_PageId($this->id));
         } catch (Bobr_Page_PageIdException $e) {
             // Nelze nacist PageId nelze sestavit Page.
             throw new Bobr_Page_PageException($e->getMessage());
         }
         // Ulozime Page do cache.
         $this->saveToCache();
     }
     return $this;
 }

    public function renderXml()
    {
        $this->template->items = dibi::query('
SELECT 
product.id_product AS id, 
product_lang.name, 
product_lang.description, 
product_lang.link_rewrite AS product_link,
category_product.id_category AS id_category,
category_lang.name AS category_name,
category_lang.link_rewrite AS category_link
FROM `product`
LEFT JOIN product_lang USING (id_product)
LEFT JOIN category_product USING (id_product)
LEFT JOIN category_lang USING (id_category)
WHERE product_lang.id_lang = 1 AND
 category_lang.id_lang = 1
GROUP BY (id_product)')->fetchAll();
        $this->template->baseUri = 'http://www.matrace-rosty.sk';
        $this->id_lang = 1;
        for ($i = 0; $i < count($this->template->items); $i++) {
            $productWithLowestPrice = dibi::query('SELECT * FROM product_param WHERE id_product = %i', $this->template->items[$i]['id'], ' ORDER BY price LIMIT 0,1')->fetch();
            $this->template->items[$i]['url'] = $this->getPresenter()->link('Eshop:current', array('categories' => $this->template->items[$i]['category_link'], 'url_identifier' => NStrings::webalize($this->template->items[$i]['product_link'])));
            $this->template->items[$i]['image'] = ProductModel::getImage($this->template->items[$i]['id']);
            $this->template->items[$i]['price_vat'] = round($productWithLowestPrice['price'], 2);
            $this->template->items[$i]['price'] = round($this->template->items[$i]['price_vat'] / 1.2, 2);
            $this->template->items[$i]['vat'] = '0.20';
            $this->template->items[$i]['size'] = $productWithLowestPrice['size'];
            $this->template->items[$i]['material'] = $productWithLowestPrice['material'];
        }
    }

 public static function show()
 {
     if (isset($_GET['ajax_action_section']) and $_GET['ajax_action_section'] == 'save_description') {
         dibi::query("\n  \t\t\tUPDATE [newsletter_emails] \n  \t\t\tSET description = %s", $_POST['description'], "WHERE id_newsletter_emails = %i", $_POST['id_newsletter_emails']);
         exit;
     }
     MT::addTemplate(APP_DIR . '/require_modules/newsletter/default.phtml', 'newsletter');
     $ds = NewsletterModel::getDatasource();
     if (isset($_GET['id_newsletter_delete'])) {
         NewsletterModel::delete($_GET['id_newsletter_delete']);
         header('location: admin.php?section=newsletter');
         exit;
     }
     if (isset($_GET['export'])) {
         $dse = clone $ds;
         MT::addVar('newsletter', 'export', $dse->where('active = 1 AND id_newsletter_emails IN %l', $_GET['id'])->fetchAll());
     }
     if (@$_GET['order_by'] == 'email') {
         $ds->orderBy('email');
     } else {
         $ds->orderBy('adddate');
     }
     //$vp = new MyPaginator($ds, 10);
     MT::addVar('newsletter', 'emails', $ds->fetchAll());
     //  	MT::addVar('newsletter', 'vp', $vp);
 }

 public function getToken()
 {
     session_start();
     $code = $_REQUEST["code"];
     if (empty($code)) {
         $_SESSION['state'] = md5(uniqid(rand(), TRUE));
         //CSRF protection
         $dialog_url = "https://www.facebook.com/dialog/oauth?client_id=" . APP_ID . "&redirect_uri=" . urlencode(REDIRECT_URI) . "&scope=" . PERMISSIONS . "&state=" . $_SESSION['state'];
         echo "<script> top.location.href='" . $dialog_url . "'</script>";
     }
     if ($_SESSION['state'] && $_SESSION['state'] === $_REQUEST['state']) {
         $token_url = "https://graph.facebook.com/oauth/access_token?" . "client_id=" . APP_ID . "&redirect_uri=" . urlencode(REDIRECT_URI) . "&client_secret=" . APP_SECRET . "&code=" . $code;
         $response = file_get_contents($token_url);
         $params = null;
         parse_str($response, $params);
         $date = new DateTime();
         $date->add(new DateInterval('PT' . $params["expires"] . 'S'));
         $arr = array('token' => $params['access_token'], 'expiration' => $date);
         dibi::query('TRUNCATE tokens');
         dibi::query('INSERT INTO tokens', $arr);
         echo "Thanks for your token :)";
     } else {
         echo "The state does not match. You may be a victim of CSRF.";
     }
 }

 protected function _writeMethodsForEavAttributes($model, $table)
 {
     $query = 'SELECT attribute_code as Field
         FROM eav_attribute a
         JOIN eav_entity_type t ON t.entity_type_id = a.entity_type_id
         WHERE entity_model = %s';
     $this->_writeMethodsForFields($model, $table, \dibi::query($query, $this->_classIdentifiers[$model]), 'eav');
 }