PHP Query::sql方法代码示例

 function updateDate($catalogue_id, $msgid)
 {
     $q = new Query();
     $q->sql("UPDATE {messages} SET updated_at = NOW() WHERE msgid=? AND catalogue_id=?", $msgid, $catalogue_id)->execute();
     //var_dump($catalogue_id);
     //echo "true";
 }

 function updateMessage($catalogue_id, $msgid, $comments, $msgstr, $fuzzy)
 {
     $q = new Query();
     $flags = $fuzzy ? 'fuzzy' : '';
     $q->sql("UPDATE {messages} SET comments=?, msgstr=?, flags=? WHERE msgid=? AND catalogue_id=?", $comments, $msgstr, $flags, $msgid, $catalogue_id)->execute();
     var_dump($catalogue_id);
     echo "true";
 }

 /**
  * Запрос к базе данных
  *
  * @param Query|string $query Запрос
  * @throws \Exception
  * @return \mysqli_result
  */
 public function query($query)
 {
     if ($this->check_connection) {
         if (!parent::ping()) {
             $this->establish();
         }
     }
     if ($query instanceof query) {
         $this->query = $query->sql();
     } else {
         $this->query = $query;
     }
     $this->query_list[] = $this->query;
     $this->result = parent::query($this->query);
     if ($this->error) {
         throw new \Exception("MYSQL ERROR: {$this->error}.\nSQL: {$this->query}");
     }
     return $this->result;
 }

 public function clean($keys)
 {
     $todel = "";
     $unused = "";
     $used = "";
     $i = 0;
     # Get database keys
     $q = new Query();
     $msgs = $q->sql("SELECT msgid,msgstr FROM {messages} WHERE catalogue_id = ?", $this->catalogue_id)->fetchAll();
     # Compare database and file keys
     if (is_array($msgs)) {
         foreach ($msgs as $msg) {
             if ($msg["msgid"] == 'login_account') {
                 //var_dump($msg["msgid"]);
                 //var_dump($keys);
             }
             #TODO handle special char on msgid (like " or ,)
             if (!isset($keys['"' . $msg["msgid"] . '"'])) {
                 if (empty($msg["msgstr"])) {
                     # delete unused key
                     $todel .= $todel == "" ? '"' . $msg["msgid"] . '"' : ',"' . $msg["msgid"] . '"';
                 } else {
                     # mark obsolete unused key
                     $unused .= $unused == "" ? '"' . $msg["msgid"] . '"' : ',"' . $msg["msgid"] . '"';
                 }
                 $i++;
             } else {
                 $used .= $used == "" ? '"' . $msg["msgid"] . '"' : ',"' . $msg["msgid"] . '"';
             }
         }
     }
     if (!empty($todel)) {
         $q->sql("DELETE FROM {messages} \n                \t WHERE  catalogue_id=? AND msgid IN ({$todel}) AND msgstr = ''", $this->catalogue_id)->execute();
     }
     if (!empty($unused)) {
         $q->sql("UPDATE {messages} \n                \t SET is_obsolete = 1 WHERE  catalogue_id=? AND msgid IN ({$unused})", $this->catalogue_id, $unused)->execute();
     }
     if (!empty($used)) {
         $q->sql("UPDATE {messages} \n                \t SET is_obsolete = 0 WHERE  catalogue_id=? AND msgid IN ({$used})", $this->catalogue_id, $used)->execute();
     }
     return $i;
 }

    /** Return unsatisfied prerequsites.
     * 
     * Note: code is an sql expression.
     */
    static function query_prerequisites($code, $completed, $taking = [], $values = [])
    {
        global $db;
        // Convert all the courses to strings.
        foreach ($completed as &$c) {
            if (is_a($c, 'Course')) {
                $c = $c->getcode();
            }
        }
        foreach ($taking as &$c) {
            if (is_a($c, 'Course')) {
                $c = $c->getcode();
            }
        }
        // Warning: Dragons Ahead!  This is a complex query. With a lot
        // of parts.
        ///// Inner query.
        // This inner query counts how many courses in $completed are
        // in the elgible group and not in the excluded group.
        $haspreq = new Query('coursegroups elg');
        $haspreq->select('count(elg.id)');
        $haspreq->where('elg.id = prerequisites.eligible');
        $haspreq->join('coursegroup_courses elgc', 'elgc.id = elg.id');
        // Where the course is in the completed group.
        // Or it can be taken concurrently and it is in the taking group.
        $haspreq->where('(
				elgc.course_code IN ' . Query::valuelistsql($completed) . '
				OR (
					elgc.course_code IN ' . Query::valuelistsql($taking) . '
					AND elgc.concurrent
				)
			)', array_merge($completed, $taking));
        // And it is not one of the courses that is excluded.
        $haspreq->where('NOT EXISTS (
				SELECT course_code
				FROM coursegroup_courses
				WHERE coursegroup_courses.id = prerequisites.excluded
				AND   coursegroup_courses.course_code = elgc.course_code
			)', []);
        ///// Outer Query
        // This query selects prerequsites where the number of credits
        // earned is less then the required credits.
        $q = new Query('prerequisites');
        $q->select('course_code');
        $q->select('eligible');
        $q->select('excluded');
        $q->where('course_code = ' . $code, $values);
        $q->where('(' . $haspreq->sql() . ') < credits', $haspreq->values());
        return $q;
    }

    function install($force)
    {
        $create_message = <<<CM
\t    CREATE TABLE IF NOT EXISTS `{messages}` (
\t      `id` int(11) NOT NULL auto_increment,
\t      `catalogue_id` int(11) NOT NULL,
\t      `msgid` text  CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
\t      `msgstr` text  CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
\t      `comments` text  CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
\t      `extracted_comments` text  CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
\t      `reference` text  CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
\t      `flags` text  CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
\t      `is_obsolete` tinyint(1) NOT NULL,
\t\t\t\t`is_header` tinyint(1) NOT NULL,
\t      `previous_untranslated_string` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
\t      `updated_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
\t      PRIMARY KEY  (`id`)
\t    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
CM;
        $create_catalogue = <<<CM
\t    CREATE TABLE IF NOT EXISTS `{catalogues}` (
\t      `id` int(11) NOT NULL auto_increment,
\t      `name` varchar(100) NOT NULL,
\t      PRIMARY KEY  (`id`),
\t      UNIQUE KEY `name` (`name`)
\t    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
CM;
        $q = new Query();
        if ($this->force) {
            $this->echo_stderr("\tForced Installation taking place...\n");
            $q->sql("DROP TABLE IF EXISTS {catalogues}, {messages}")->execute();
        }
        $q->sql($create_catalogue)->execute();
        $q->sql($create_message)->execute();
        $this->echo_stderr("\tInstallation complete!\n\n");
    }

	function order_view ($instance, $direction, &$context) {
		
		// Current ordering value
		$pos_name = $this->custom_position;
		$pos = $instance->$pos_name;
		
		// Setup query params
		$params = array($pos);
		
		$tree_field = $this->get_tree_field();
		$tree_field_filter = '';
		if ($tree_field) {
			$v = $instance->{$tree_field->name};
			$tree_field_filter = sprintf('AND %s %s %%s', escape($tree_field->name), is_null($v) ? 'IS' : '=');
			$params[] = $v;
		}
		
		// Create a raw query to get the item which will be swapped by this instance
		// Moving up:
			// look for a directly lower value, use descending order to get the closest value
		// Moving down:
			// look for a directly greater value, use ascending order to get the closest value
			// SELECT * FROM gm_cms_page WHERE pos >|< %d ORDER BY pos DESC|ASC LIMIT 1
		$sql = sprintf('SELECT * FROM %1$s WHERE %2$s %4$s %%d %5$s ORDER BY %2$s %3$s LIMIT 1',
			$this->meta->table_safe,
			escape($pos_name),
			($direction == 'up') ? 'DESC' : 'ASC',
			($direction == 'up') ? '<' : '>',
			$tree_field_filter
		);
		
		// Create a query instance
		$q = new Query($this->meta);
		// Execute and fetch the raw query
		$data = $q->sql($sql, $params);
		
		// Check for data
		if ($data) {
			
			// Data is an array of one row
			// Create an instance based on the first row
			$next = $q->create($data[0], false);
			
			// Swap position values
			$instance->$pos_name = $next->$pos_name;
			$next->$pos_name = $pos;
			
			// Save
			$instance->save();
			$next->save();
			
		}
		
		if ($_SERVER['HTTP_REFERER']) {
			redir($_SERVER['HTTP_REFERER']);
		}
		else {
			redir('../../../');
		}
	}