PHP sqlite_query函数代码示例

function get_masters()
{
    $db = sqlite_open("database.db") or die("failed to open/create the database");
    $sql = "SELECT * FROM master";
    $res = sqlite_query($db, $sql);
    return $res;
}

 /**
  * Method to send SQL query
  *
  * @param   resource    $res_conn
  * @return  void
  */
 private function sendQuery($res_conn)
 {
     // checking query type
     // if the query return recordset or not
     if (preg_match("/^(SELECT)\\s/i", $this->sql_string)) {
         $this->res_result = @sqlite_query($res_conn, $this->sql_string);
         // error checking
         if (!$this->res_result) {
             $this->errno = sqlite_last_error($res_conn);
             $this->error = "Query failed to executed. Please check your query again. \n" . sqlite_error_string($this->errno);
         } else {
             // count number of rows
             $this->num_rows = @sqlite_num_rows($this->res_result);
         }
     } else {
         $_query = @sqlite_unbuffered_query($res_conn, $this->sql_string);
         $this->insert_id = sqlite_last_insert_rowid($res_conn);
         // error checking
         if (!$_query) {
             $this->errno = sqlite_last_error($res_conn);
             $this->error = "Query failed to executed. Please check your query again. \n" . sqlite_error_string($this->errno);
         } else {
             // get number of affected row
             $this->affected_rows = @sqlite_changes($res_conn);
         }
         // nullify query
         $_query = null;
     }
 }

 public function prepareDB($bWithData = true)
 {
     if (file_exists(dirname(__FILE__) . '/unittest.db')) {
         unlink(dirname(__FILE__) . '/unittest.db');
     }
     $db = sqlite_open(dirname(__FILE__) . '/unittest.db');
     $res = sqlite_query($db, 'CREATE TABLE people (id INTEGER PRIMARY KEY, firstName TEXT, lastName TEXT)', $sError);
     if ($res === false) {
         throw new Exception($sError);
     }
     if ($bWithData) {
         $res = sqlite_query($db, 'INSERT INTO people (id,firstName,lastName) VALUES (1, \'Jerome\', \'Piochet\')', $sError);
         if ($res === false) {
             throw new Exception($sError);
         }
         $res = sqlite_query($db, 'INSERT INTO people (id,firstName,lastName) VALUES (2, \'Tadao\', \'Poichet\')', $sError);
         if ($res === false) {
             throw new Exception($sError);
         }
         $res = sqlite_query($db, 'INSERT INTO people (id,firstName,lastName) VALUES (3, \'A\', \'B\')', $sError);
         if ($res === false) {
             throw new Exception($sError);
         }
         $res = sqlite_query($db, 'INSERT INTO people (id,firstName,lastName) VALUES (4, \'C\', \'D\')', $sError);
         if ($res === false) {
             throw new Exception($sError);
         }
     }
     sqlite_close($db);
 }

 public function executeNoresSQL($a_query)
 {
     if (!$this->db) {
         return NULL;
     }
     sqlite_query($this->db, $a_query);
 }

 public function __construct()
 {
     if (!defined("WebOrbServicesPath")) {
         $dom = new DomDocument();
         $dom->load(WebOrb . "weborb-config.xml");
         $servicePath = $dom->documentElement->getAttribute('servicePath');
         define("WebOrbServicesPath", realpath(WebOrb . $servicePath) . DIRECTORY_SEPARATOR);
     }
     if (file_exists(WebOrbServicesPath . "Weborb/Examples/Security/datbase.db")) {
         if (!($this->link = sqlite_open(WebOrbServicesPath . "Weborb/Examples/Security/datbase.db", 0666, $sqliteerror))) {
             throw new Exception($sqliteerror);
         }
     } else {
         if (!($this->link = sqlite_open(WebOrbServicesPath . "Weborb/Examples/Security/datbase.db", 0666, $sqliteerror))) {
             throw new Exception($sqliteerror);
         }
         sqlite_query($this->link, "CREATE TABLE Security ( Id INT, UserName VARCHAR(20), Password VARCHAR(20), Role VARCHAR(20) )");
         sqlite_query($this->link, "INSERT INTO Security (Id, UserName, Password, Role) VALUES ( 1, 'joe', 'flexrocks', 'examplesuser' )");
         sqlite_query($this->link, "INSERT INTO Security (Id, UserName, Password, Role) VALUES ( 2, 'bob', 'weborb', 'administrator' )");
         sqlite_query($this->link, "CREATE TABLE Products ( Id INT, Name VARCHAR(20), Price NUMERIC )");
         sqlite_query($this->link, "INSERT INTO Products (Id, Name, Price) VALUES ( 1, 'Laptop', 499 )");
         sqlite_query($this->link, "INSERT INTO Products (Id, Name, Price) VALUES ( 2, 'Mouse', 20 )");
         sqlite_query($this->link, "INSERT INTO Products (Id, Name, Price) VALUES ( 3, 'Keyboard', 10 )");
     }
 }

function populate_sqlite_db($database, $db)
{
    sqlite_query($db, "CREATE TABLE webcal_user (cal_login VARCHAR(25) NOT NULL, cal_passwd VARCHAR(32), cal_lastname VARCHAR(25), cal_firstname VARCHAR(25), cal_is_admin CHAR(1) DEFAULT 'N',cal_email VARCHAR(75) NULL,cal_enabled CHAR(1) DEFAULT 'Y',cal_telephone VARCHAR(50) NULL,cal_address VARCHAR(75) NULL,cal_title VARCHAR(75) NULL,cal_birthday INT,cal_last_login INT, PRIMARY KEY ( cal_login ))");
    sqlite_query($db, "INSERT INTO webcal_user ( cal_login, cal_passwd, cal_lastname, cal_firstname, cal_is_admin ) VALUES ( 'admin', '21232f297a57a5a743894a0e4a801fc3', 'Administrator', 'Default', 'Y' );");
    sqlite_query($db, "CREATE TABLE webcal_entry ( cal_id INT NOT NULL, cal_group_id INT NULL, cal_ext_for_id INT NULL,  cal_create_by VARCHAR(25) NOT NULL, cal_date INT NOT NULL, cal_time INT NULL, cal_mod_date INT, cal_mod_time INT, cal_duration INT NOT NULL, cal_due_date INT default NULL, cal_due_time INT default NULL, cal_location varchar(100) default NULL, cal_url varchar(100) default NULL, cal_completed INT default NULL, cal_priority INT DEFAULT 5, cal_type CHAR(1) DEFAULT 'E', cal_access CHAR(1) DEFAULT 'P', cal_name VARCHAR(80) NOT NULL, cal_description TEXT, PRIMARY KEY ( cal_id ))");
    sqlite_query($db, "CREATE TABLE webcal_entry_repeats ( cal_id INT DEFAULT 0 NOT NULL, cal_type VARCHAR(20), cal_end INT, cal_frequency INT DEFAULT 1, cal_days CHAR(7), cal_endtime int(11) default NULL, cal_bymonth varchar(50) default NULL, cal_bymonthday varchar(100) default NULL, cal_byday varchar(100) default NULL, cal_bysetpos varchar(50) default NULL, cal_byweekno varchar(50) default NULL, cal_byyearday varchar(50) default NULL, cal_wkst char(2) default 'MO', cal_count int(11) default NULL, PRIMARY KEY (cal_id))");
    sqlite_query($db, "CREATE TABLE webcal_entry_repeats_not ( cal_id INT NOT NULL, cal_date INT NOT NULL, cal_exdate INT NOT NULL default '1', PRIMARY KEY ( cal_id, cal_date ))");
    sqlite_query($db, "CREATE TABLE webcal_entry_user ( cal_id INT DEFAULT 0 NOT NULL, cal_login VARCHAR(25) NOT NULL, cal_status CHAR(1) DEFAULT 'A', cal_category INT DEFAULT NULL, cal_percent INT NOT NULL default '0', PRIMARY KEY ( cal_id, cal_login ))");
    sqlite_query($db, "CREATE TABLE webcal_entry_ext_user ( cal_id INT DEFAULT 0 NOT NULL, cal_fullname VARCHAR(50) NOT NULL, cal_email VARCHAR(75) NULL, PRIMARY KEY ( cal_id, cal_fullname ))");
    sqlite_query($db, "CREATE TABLE webcal_user_pref ( cal_login VARCHAR(25) NOT NULL, cal_setting VARCHAR(25) NOT NULL, cal_value VARCHAR(100) NULL, PRIMARY KEY ( cal_login, cal_setting ))");
    sqlite_query($db, "CREATE TABLE webcal_user_layers ( cal_layerid INT DEFAULT 0 NOT NULL, cal_login VARCHAR(25) NOT NULL, cal_layeruser VARCHAR(25) NOT NULL, cal_color VARCHAR(25) NULL, cal_dups CHAR(1) DEFAULT 'N', PRIMARY KEY ( cal_login, cal_layeruser ))");
    sqlite_query($db, "CREATE TABLE webcal_site_extras ( cal_id INT DEFAULT 0 NOT NULL, cal_name VARCHAR(25) NOT NULL, cal_type INT NOT NULL, cal_date INT DEFAULT 0, cal_remind INT DEFAULT 0, cal_data TEXT)");
    sqlite_query($db, "CREATE TABLE webcal_reminders (cal_id INT DEFAULT 0 NOT NULL,cal_date INT DEFAULT 0 NOT NULL,cal_offset INT DEFAULT 0 NOT NULL,cal_related CHAR(1) DEFAULT 'S' NOT NULL,cal_before CHAR(1) DEFAULT 'Y' NOT NULL,cal_last_sent INT DEFAULT NULL,cal_repeats INT DEFAULT 0 NOT NULL,cal_duration INT DEFAULT 0 NOT NULL,cal_times_sent INT DEFAULT 0 NOT NULL,cal_action VARCHAR(12) DEFAULT 'EMAIL' NOT NULL,PRIMARY KEY ( cal_id ))");
    sqlite_query($db, "CREATE TABLE webcal_group ( cal_group_id INT NOT NULL, cal_owner VARCHAR(25) NULL, cal_name VARCHAR(50) NOT NULL, cal_last_update INT NOT NULL, PRIMARY KEY ( cal_group_id ))");
    sqlite_query($db, "CREATE TABLE webcal_group_user ( cal_group_id INT NOT NULL, cal_login VARCHAR(25) NOT NULL, PRIMARY KEY ( cal_group_id, cal_login ))");
    sqlite_query($db, "CREATE TABLE webcal_view ( cal_view_id INT NOT NULL, cal_owner VARCHAR(25) NOT NULL, cal_name VARCHAR(50) NOT NULL, cal_view_type CHAR(1), cal_is_global CHAR(1) DEFAULT 'N' NOT NULL, PRIMARY KEY ( cal_view_id ))");
    sqlite_query($db, "CREATE TABLE webcal_view_user ( cal_view_id INT NOT NULL, cal_login VARCHAR(25) NOT NULL, PRIMARY KEY ( cal_view_id, cal_login ))");
    sqlite_query($db, "CREATE TABLE webcal_config ( cal_setting VARCHAR(50) NOT NULL, cal_value VARCHAR(100) NULL, PRIMARY KEY ( cal_setting ))");
    sqlite_query($db, "CREATE TABLE webcal_entry_log ( cal_log_id INT NOT NULL, cal_entry_id INT NOT NULL, cal_login VARCHAR(25) NOT NULL, cal_user_cal VARCHAR(25) NULL, cal_type CHAR(1) NOT NULL, cal_date INT NOT NULL, cal_time INT NULL, cal_text TEXT, PRIMARY KEY ( cal_log_id ))");
    sqlite_query($db, "CREATE TABLE webcal_categories ( cat_id INT NOT NULL, cat_owner VARCHAR(25) NULL, cat_name VARCHAR(80) NOT NULL,cat_color VARCHAR(8) NULL, PRIMARY KEY ( cat_id ))");
    sqlite_query($db, "CREATE TABLE webcal_asst ( cal_boss VARCHAR(25) NOT NULL, cal_assistant VARCHAR(25) NOT NULL, PRIMARY KEY ( cal_boss, cal_assistant ))");
    sqlite_query($db, "CREATE TABLE webcal_nonuser_cals ( cal_login VARCHAR(25) NOT NULL, cal_lastname VARCHAR(25) NULL, cal_firstname VARCHAR(25) NULL, cal_admin VARCHAR(25) NOT NULL, cal_is_public CHAR(1) DEFAULT 'N' NOT NULL, cal_url VARCHAR(255) DEFAULT NULL, PRIMARY KEY ( cal_login ))");
    sqlite_query($db, "CREATE TABLE webcal_import ( cal_import_id INT NOT NULL, cal_name VARCHAR(50) NULL, cal_date INT NOT NULL, cal_type VARCHAR(10) NOT NULL, cal_login VARCHAR(25) NULL, PRIMARY KEY ( cal_import_id ))");
    sqlite_query($db, "CREATE TABLE webcal_import_data ( cal_import_id INT NOT NULL, cal_id INT NOT NULL, cal_login VARCHAR(25) NOT NULL, cal_import_type VARCHAR(15) NOT NULL, cal_external_id VARCHAR(200) NULL, PRIMARY KEY  ( cal_id, cal_login ))");
    sqlite_query($db, "CREATE TABLE webcal_report ( cal_login VARCHAR(25) NOT NULL, cal_report_id INT NOT NULL, cal_is_global CHAR(1) DEFAULT 'N' NOT NULL, cal_report_type VARCHAR(20) NOT NULL, cal_include_header CHAR(1) DEFAULT 'Y' NOT NULL, cal_report_name VARCHAR(50) NOT NULL, cal_time_range INT NOT NULL, cal_user VARCHAR(25) NULL, cal_allow_nav CHAR(1) DEFAULT 'Y', cal_cat_id INT NULL, cal_include_empty CHAR(1) DEFAULT 'N', cal_show_in_trailer CHAR(1) DEFAULT 'N', cal_update_date INT NOT NULL, PRIMARY KEY ( cal_report_id ))");
    sqlite_query($db, "CREATE TABLE webcal_report_template ( cal_report_id INT NOT NULL, cal_template_type CHAR(1) NOT NULL, cal_template_text TEXT, PRIMARY KEY ( cal_report_id, cal_template_type ))");
    sqlite_query($db, "CREATE TABLE webcal_access_user ( cal_login VARCHAR(25) NOT NULL, cal_other_user VARCHAR(25) NOT NULL, cal_can_view INT NOT NULL DEFAULT '0', cal_can_edit INT NOT NULL DEFAULT '0', cal_can_approve INT NOT NULL DEFAULT '0', cal_can_invite CHAR(1) NOT NULL DEFAULT 'Y', cal_can_email CHAR(1) NOT NULL DEFAULT 'Y', cal_see_time_only CHAR(1) NOT NULL DEFAULT 'N', PRIMARY KEY ( cal_login, cal_other_user ))");
    sqlite_query($db, "CREATE TABLE webcal_access_function ( cal_login VARCHAR(25) NOT NULL, cal_permissions VARCHAR(64) NOT NULL, PRIMARY KEY ( cal_login ))");
    sqlite_query($db, "CREATE TABLE webcal_user_template ( cal_login VARCHAR(25) NOT NULL default '', cal_type CHAR(1) NOT NULL default '', cal_template_text text, PRIMARY KEY  (cal_login,cal_type))");
    sqlite_query($db, "CREATE TABLE webcal_entry_categories (cal_id INT NOT NULL default '0', cat_id INT NOT NULL default '0', cat_order INT NOT NULL default '0', cat_owner VARCHAR(25) default NULL)");
    sqlite_query($db, "CREATE TABLE webcal_blob ( cal_blob_id INT NOT NULL, cal_id INT NULL, cal_login VARCHAR(25) NULL, cal_name VARCHAR(30) NULL, cal_description VARCHAR(128) NULL, cal_size INT NULL, cal_mime_type VARCHAR(50) NULL, cal_type CHAR(1) NOT NULL, cal_mod_date INT NOT NULL, cal_mod_time INT NOT NULL, cal_blob BLOB, PRIMARY KEY ( cal_blob_id ))");
    sqlite_query($db, "CREATE TABLE webcal_timezones (tzid varchar(100) NOT NULL default '', dtstart varchar(25) default NULL, dtend varchar(25) default NULL, vtimezone text, PRIMARY KEY  ( tzid ))");
}

 function run_query($sql)
 {
     if (!$this->conn) {
         $this->connect();
     }
     return sqlite_query($sql, $this->conn);
 }

 function query_start($query)
 {
     // For reg expressions
     $query = trim($query);
     // Query was an insert, delete, update, replace
     if (preg_match("/^(insert|delete|update|replace)\\s+/i", $query)) {
         return false;
     }
     $this->savedqueries[] = $query;
     // Flush cached values..
     $this->flush();
     // Log how the function was called
     $this->func_call = "\$db->query_start(\"{$query}\")";
     // Keep track of the last query for debug..
     $this->last_query = $query;
     $this->result = @sqlite_query($query, $this->dbh);
     $this->num_queries++;
     // If there is an error then take note of it..
     if (!$this->result) {
         $this->print_error();
         return false;
     }
     // =======================================================
     // Take note of column info
     #$i=0;
     #foreach(@sqlite_fetch_field_array($handle) as $name)
     #{
     #   $this->col_info[$i++]->name = $name;
     #}
     $this->last_result = array();
     $this->num_rows = 0;
     // If debug ALL queries
     $this->trace || $this->debug_all ? $this->debug() : null;
     return true;
 }

 /**
  * 执行mysql query()操作
  * @param string $sql
  * @return mixed
  */
 public function query($sql)
 {
     // 是否记录 SQL log
     if (true == C('sql_log')) {
         wlog('SQL-Log', $sql);
     }
     $error_msg = '';
     $_key = strtolower(substr($sql, 0, 6));
     if ($_key == 'select') {
         $rs = sqlite_query($this->db, $sql, SQLITE_BOTH, $error_msg);
     } else {
         $rs = sqlite_exec($this->db, $sql, $error_msg);
     }
     if (!empty($rs)) {
         $GLOBALS['run_dbquery_count']++;
         return $rs;
     } else {
         if (C('show_errors')) {
             show_error('执行sqlite_query()出现错误: ' . $error_msg . '<br />原SQL: ' . $sql);
         } else {
             exit('db_sqlite2::query() error.');
         }
     }
     //return false;
 }

function increase_nb_play($id)
{
    global $db;
    $id = (int) $id;
    $query = "UPDATE fuu SET nb_play=nb_play + 1 WHERE id='{$id}'";
    return sqlite_query($db, $query);
}

 protected function _query($query = '')
 {
     if ($this->_conexion == null) {
         $this->_connect();
     }
     return sqlite_query($query, $this->_conexion, SQLITE_ASSOC);
 }

 public function set($key, $val, $immutable = false)
 {
     if ($immutable) {
         $this->immutableSet($key, $val);
     } else {
         if (empty($val)) {
             sqlite_query($this->db, "DELETE FROM is4c_local WHERE keystr='{$key}'");
         } else {
             if (is_array($val)) {
                 $temp = "";
                 foreach ($val as $v) {
                     $temp .= $v . chr(255);
                 }
                 $val = substr($temp, 0, strlen($temp) - 1);
             } elseif ($val === false) {
                 $val = 'FALSE';
             } elseif ($val === true) {
                 $val = 'TRUE';
             }
             $check = sqlite_query($this->db, "SELECT valstr FROM is4c_local WHERE keystr='{$key}'");
             if (sqlite_num_rows($check) == 0) {
                 sqlite_query($this->db, "INSERT INTO is4c_local VALUES ('{$key}','{$val}')");
             } else {
                 sqlite_query($this->db, "UPDATE is4c_local SET valstr='{$val}' WHERE keystr='{$key}'");
             }
         }
     }
     $this->debug();
 }

 public function request($s)
 {
     $h = sqlite_query($this->c, $s, SQLITE_BOTH, $this->e);
     if ($h === false) {
         throw new HException("Error while executing " . $s . " (" . $this->e . ")");
     }
     return new php_db__Sqlite_SqliteResultSet($h);
 }

 protected function _doExec($query)
 {
     if ($qI = sqlite_query($query, $this->_connection)) {
         return sqlite_changes($this->_connection);
     } else {
         throw new jException('jelix~db.error.query.bad', sqlite_error_string($this->_connection) . '(' . $query . ')');
     }
 }

 public function query($query)
 {
     $this->enhanceQuery($query);
     if ($resource = sqlite_query($this->handle, $query)) {
         return new SQLResult_sqlite2($resource);
     }
     return false;
 }