PHP SQLite3::query方法代码示例

 public function read($params, AccountWriter $writer)
 {
     $args = new FormattedArgumentMap($params);
     $folder = $args->opt("i", "plugins/SimpleAuth");
     if (!is_dir($folder)) {
         throw new \InvalidArgumentException("Input database {$folder} not found or is not a directory");
     }
     $path = rtrim($folder, "/\\") . "/players.db";
     if (!is_file($path)) {
         return;
     }
     $this->setStatus("Opening database");
     $db = new \SQLite3($path);
     $result = $db->query("SELECT COUNT(*) AS cnt FROM players");
     $total = $result->fetchArray(SQLITE3_ASSOC)["cnt"];
     $result->finalize();
     $this->setStatus("Preparing data");
     $result = $db->query("SELECT name,registerdate,logindate,lastip,hash FROM players");
     $i = 0;
     while (is_array($row = $result->fetchArray(SQLITE3_ASSOC))) {
         $i++;
         $info = AccountInfo::defaultInstance($row["name"], $this->defaultOpts);
         $info->lastIp = $row["lastip"];
         $info->registerTime = $row["registerdate"];
         $info->lastLogin = $row["logindate"];
         $info->passwordHash = hex2bin($row["hash"]);
         $writer->write($info);
         $this->setProgress($i / $total);
     }
     $db->close();
 }

function getEvents()
{
    global $db;
    $token = trim(file_get_contents('config/token.txt'));
    $mainPage = pullUrl("https://techspring.nationbuilder.com/api/v1/sites/v2/pages/events?starting=" . date("Y-m-d") . "&access_token=" . $token . "");
    $obj = json_decode($mainPage);
    //echo "<pre>" . json_encode($obj, JSON_PRETTY_PRINT) . "</pre>";
    if ($db = new SQLite3('local_db.sql')) {
        $q = @$db->query('CREATE TABLE IF NOT EXISTS events (eid INTEGER, start_time TEXT, end_time TEXT, name TEXT, description TEXT, PRIMARY KEY(eid))');
        $q = @$db->query('CREATE TABLE IF NOT EXISTS rsvp (eid INTEGER, uid INTEGER)');
    }
    foreach ($obj->results as $eventObj) {
        echo "<br/>{$eventObj->id} - {$eventObj->name} - {$eventObj->start_time} - {$eventObj->end_time} - {$eventObj->intro} <br />";
        $startTime = strtotime($eventObj->start_time);
        $endTime = strtotime($eventObj->end_time);
        echo "Date: " . date("l, F jS", $startTime);
        echo "<br />Time:" . date("g:i a", $startTime) . " - " . date("g:i a", $endTime) . "<br />";
        @$db->query("INSERT OR IGNORE INTO `events` (eid, start_time, end_time, name, description) VALUES " . "('" . $eventObj->id . "'," . "'" . $eventObj->start_time . "'," . "'" . $eventObj->end_time . "'," . "'" . $eventObj->name . "'," . "'" . $eventObj->intro . "');");
    }
    $eventRes = @$db->query("SELECT * FROM `events`");
    while ($event = $eventRes->fetchArray()) {
        $json = pullUrl("https://techspring.nationbuilder.com/api/v1/sites/v2/pages/events/" . $event['eid'] . "/rsvps?limit=10&__proto__=&access_token=" . $token);
        $rsvpData = json_decode($json);
        foreach ($rsvpData->results as $rsvp) {
            @$db->query("INSERT OR IGNORE INTO `rsvp` (eid, uid) VALUES ('" . $event['eid'] . "','" . $rsvp->person_id . "')");
        }
    }
}

 public function tearDown()
 {
     $sql = 'DELETE FROM merchants';
     self::$dbConnection->query($sql);
     $sql = 'DELETE FROM transactions';
     self::$dbConnection->query($sql);
 }

    /**
     * Create Tables needed for storing information about apps.
     *
     * @throws DBAppsException
     *  If a create table query fails.
     */
    protected function createTables()
    {
        $sqls = array(<<<'EOF'
-- Table for listing top free apps from google.
CREATE TABLE IF NOT EXISTS topApps
(
    package TEXT, -- The package name of the app.
    rank INT, -- The rank of the app in the top apps list.
    PRIMARY KEY(package)
);
EOF
, <<<'EOF'
-- Table for listing all apps from google.
CREATE TABLE IF NOT EXISTS Apps
(
    package TEXT, -- The package name of the app.
    title TEXT, -- The title of the app (human readable).
    author TEXT, -- The author of the app.
    description TEXT, -- The description text of the app.
    PRIMARY KEY(package)
);
EOF
);
        foreach ($sqls as $sql) {
            if ($this->db->query($sql) === FALSE) {
                throw new DBAppsException(_('Failed to create tables.'));
            }
        }
    }

 /**
  * Query
  * 
  * @param mixed $query
  */
 function sql_query($query)
 {
     if (empty($query)) {
         core::dprint('Empty sql_query call');
         return false;
     }
     if (is_array($query)) {
         $query = vsprintf($query[0], array_slice($query, 1));
     }
     $query = trim($query);
     if (empty($query)) {
         return false;
     }
     if (!$this->_connect()) {
         return false;
     }
     ++$this->_counter;
     $this->_sqlite_fixes($query);
     $this->_last_query = $query;
     $is_select = preg_match('@^(SELECT|PRAGMA)@i', $query);
     $microtime = microtime(1);
     // how the fuck to catch errors in query?
     $this->query_result = $is_select ? @$this->_connect_id->query($query) : @$this->_connect_id->exec($query);
     $this->_last_query_time = microtime(1) - $microtime;
     core::dprint(array('[SQL%0d|%.5f : %s', $this->_counter, $this->_last_query_time, $query), core::E_SQL);
     if (!$this->query_result) {
         $err = $this->sql_error();
         core::dprint('[SQL FAIL] ' . $err['message'] . ' : ' . $err['code'], core::E_SQL);
     }
     return $this->query_result;
 }

 /**
  * The primary method a driver needs to implement is the execute method, which takes an array of query options.
  * The options in the array varies, but the key type will always be supplied, which will be either SELECT, UPDATE,
  * INSERT, REPLACE or DELETE.
  *
  * @param array $options An array of options that were generated through use of the Query class.
  * @return object It is expected to return an instance of an \Queryer\Driver\DatabaseDriverResult class.
  * @see \Queryer\Query, \Queryer\Driver\DatabaseDriverResult
  */
 public function execute(array $options)
 {
     $query = self::generateQuery($options);
     $query = DatabaseTools::replaceVariables($query, $options['variables']);
     $result = $this->sqlite->query($query);
     return new Sqlite3DriverResult($result, $this->sqlite->changes(), $this->sqlite->lastInsertRowID(), $result === false ? $this->sqlite->lastErrorCode() : null, $result === false ? $this->sqlite->lastErrorMsg() : null, $query);
 }

function get_mbtiles_data($file_path)
{
    $db_mbtiles = new SQLite3($file_path);
    // Min Zoom and Max Zoom
    $zoom_range_query = "select min(zoom_level), max(zoom_level) from tiles";
    $zoom_range_results = $db_mbtiles->query($zoom_range_query);
    $zoom_range = $zoom_range_results->fetchArray();
    $min_zoom = $zoom_range[0];
    $max_zoom = $zoom_range[1];
    // Get zoom, column, and row for each tile
    $query = "select zoom_level, avg(tile_column), avg(tile_row) from tiles group by zoom_level";
    $results = $db_mbtiles->query($query);
    $rows = array();
    while ($row = $results->fetchArray()) {
        $rows[] = $row;
    }
    $center_tile = $rows[floor(0.5 * count($rows))];
    $center_tile_zoom = $center_tile[0];
    $center_tile_x = floor($center_tile[1]);
    // Column
    $center_tile_y = round(pow(2, $center_tile_zoom) - $center_tile[2] - 1);
    // Converted Row
    $mbtiles_data = array("min_zoom" => $min_zoom, "max_zoom" => $max_zoom, "center_coordinates" => array("zoom" => $center_tile_zoom, "x" => intval($center_tile_x), "y" => intval($center_tile_y)));
    return $mbtiles_data;
}

function save_pois($jsonStr, $dbname)
{
    //create or open the channel database
    $db = new SQLite3($dbname, SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE);
    //create the POIs table
    $db->query("CREATE TABLE IF NOT EXISTS POIsM (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, description TEXT, phoneNumber TEXT, homepage TEXT, iconURL TEXT, thumbnailURL TEXT, imageURL TEXT,\n                videoURL TEXT, soundURL TEXT, modelURL TEXT, latitude REAL, longitude REAL, altitude REAL) ;");
    $db->query("DELETE FROM POIsM;");
    //required because we bulk-replace all POIs on updates to the channel
    $db->query("UPDATE SQLITE_SEQUENCE SET seq='0' WHERE name='POIsM';");
    //reset the id count to start from 1 again; Else, the id count starts from the previously used highest id for the table
    //Decode the json string to UTF-8 encoding as "json_decode" only works for UTF-8 encoding
    $poisJson = json_decode(utf8_decode($jsonStr));
    $objects = $poisJson->pois;
    //parse the input json for POI information
    foreach ($objects as $obj) {
        $description = "";
        $phoneNumber = "";
        $icon = "";
        $thumbnail = "";
        print_r($obj);
        $homepage = "";
        $imageUrl = "";
        $movieUrl = "";
        $soundUrl = "";
        //Check if attributes needed for POI are present in JSON
        if (isset($obj->description)) {
            $description = $obj->description;
        }
        if (isset($obj->phoneNumber)) {
            $phoneNumber = $obj->phoneNumber;
        }
        if (isset($obj->iconURL)) {
            $icon = $obj->iconURL;
        } else {
            $icon = "http://channels.excel.junaio.com/resources/icon_thumbnail.png";
        }
        if (isset($obj->thumbnailURL)) {
            $thumbnail = $obj->thumbnailURL;
        } else {
            $thumbnail = "http://channels.excel.junaio.com/resources/icon_thumbnail.png";
        }
        if (isset($obj->homepage)) {
            $homepage = $obj->homepage;
        }
        if (isset($obj->imageURL)) {
            $imageUrl = $obj->imageURL;
        }
        if (isset($obj->video)) {
            $movieUrl = $obj->video;
        }
        if (isset($obj->sound)) {
            $soundUrl = $obj->sound;
        }
        //insert each POI to the db
        $query = "INSERT INTO POIsM (title, description, phoneNumber, homepage, iconURL, thumbnailURL, imageURL, videoURL,\n        soundURL, modelURL, latitude, longitude, altitude) VALUES ('" . $obj->title . "', '" . $description . "',\n        '" . $phoneNumber . "', '" . $homepage . "', '" . $icon . "', '" . $thumbnail . "', '" . $imageUrl . "', '" . $movieUrl . "', '" . $soundUrl . "', '', '" . $obj->latitude . "', '" . $obj->longitude . "', '" . $obj->altitude . "');";
        $db->query($query);
    }
    $db->close();
}

 /**
  * 
  * @param string $query
  * @return array
  */
 public function sendSelectQuery($query)
 {
     $result = $this->db->query($query);
     while ($fetch = $result->fetchArray(SQLITE3_ASSOC)) {
         $resultList[] = $fetch;
     }
     return is_array($resultList) ? $resultList : [];
 }

 protected function fetchPlayer($name)
 {
     $result = $this->db->query("SELECT * FROM selected_tools WHERE player = {$this->escape($name)};");
     $data = [];
     while (is_array($dat = $result->fetchArray(SQLITE3_ASSOC))) {
         $data[] = $dat;
     }
     return $data;
 }

 /**
  * Query and get results
  *
  * @param string $sql
  * @param bool   $parse Parse or return as is
  * @param bool   $debug
  * @return array
  */
 public function query($sql, $parse = true, $debug = false)
 {
     $result = array();
     $statement = $this->db->query($sql);
     while ($row =& $statement->fetchArray(SQLITE3_ASSOC)) {
         $result[] = $row;
     }
     return $result;
 }

 public function getArray($sSql)
 {
     $aReturn = [];
     $oQuery = @$this->__db->query($sSql);
     if ($oQuery !== false) {
         while ($aRow = $oQuery->fetchArray(SQLITE3_ASSOC)) {
             array_push($aReturn, $aRow);
         }
     }
     return $aReturn;
 }

 /**
  * Function fetches all rows out of the DB and returns an array of Model/Company objects
  * @return \Model\Company[]
  */
 public function getAll()
 {
     $retArr = array();
     $sql = 'SELECT * FROM companyDetails';
     $rs = $this->db->query($sql);
     while ($row = $rs->fetchArray()) {
         $model = new \Model\Company($row);
         $retArr[$row['proposedCode']] = $model;
     }
     return $retArr;
 }

 public function getStats($playerName)
 {
     $playerName = $this->db->escapeString(trim(strtolower($playerName)));
     $result = $this->db->query("SELECT * FROM tntstats WHERE name = '" . $playerName . "'");
     if ($result instanceof \SQLiteResult) {
         $assoc = $result->fetch(SQLITE_ASSOC);
         if (isset($assoc["name"]) and $assoc["name"] === $playerName) {
             return $assoc;
         }
     }
     return null;
 }

 public static function findByPostalCode($postalCode, $countryCode)
 {
     static::initialize();
     $query = 'SELECT * FROM geodb WHERE postalCode = "' . $postalCode . '"';
     if ($countryCode !== NULL) {
         $query .= ' AND countryCode = "' . $countryCode . '"';
     }
     $result = static::$connection->query($query);
     if ($result instanceof \SQLite3Result) {
         return $result->fetchArray(SQLITE3_ASSOC);
     }
     return array();
 }