PHP SQLite3::lastInsertRowID方法代码示例

 /**
  * 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 createTransaction($user, $group_id, $purpose, $transaction_map)
{
    $db = new SQLite3('development.sqlite3');
    $stmt = $db->prepare('INSERT INTO group_transactions (user_id, group_id,
    purpose) VALUES (:user_id, :group_id, :purpose);');
    $stmt->bindValue('user_id', $user['id'], SQLITE3_INTEGER);
    $stmt->bindValue('group_id', $group_id, SQLITE3_INTEGER);
    $stmt->bindValue('purpose', $purpose, SQLITE3_TEXT);
    $result = $stmt->execute();
    if ($result) {
        $group_transaction_id = $db->lastInsertRowID();
        foreach ($transaction_map as $user_id => $amount) {
            $stmt = $db->prepare('INSERT INTO user_transactions (user_id,
        other_user_id, group_transaction_id, amount VALUES (:user_id,
        :other_user_id, :group_transaction_id, :amount);');
            $stmt->bindValue('user_id', $user['id'], SQLITE3_INTEGER);
            $stmt->bindValue('other_user_id', $user_id, SQLITE3_INTEGER);
            $stmt->bindValue('group_transaction_id', $group_transaction_id, SQLITE3_INTEGER);
            $stmt->bindValue('amount', $amount, SQLITE3_INTEGER);
            $result = $stmt->execute();
            if (!$result) {
                return null;
            }
        }
        return array('user_id' => $user['id'], 'group_id' => $group_id, 'purpose' => $purpose);
    } else {
        return null;
    }
}

    function insert_album($name, $description, $location, $date)
    {
        date_default_timezone_set('Europe/London');
        $created_at = date("Y-m-d H:i:s");
        $sql = <<<EOF
\t\t\tINSERT INTO album
\t\t\t(name, description, location, date, created_at)
\t\t\tVALUES 
\t\t\t("{$name}", "{$description}" , "{$location}", "{$date}", "{$created_at}") 
EOF;
        $result = $this->exec($sql);
        return SQLite3::lastInsertRowID();
    }

 /**
  * Publish a message to the queue
  *
  * @param Message $message
  * @return void
  */
 public function submit(Message $message)
 {
     if ($message->getIdentifier() !== NULL) {
         $preparedStatement = $this->connection->prepare('SELECT rowid FROM queue WHERE msgid=:msgid');
         $preparedStatement->bindValue(':msgid', $message->getIdentifier());
         $result = $preparedStatement->execute();
         if ($result->fetchArray(SQLITE3_NUM) !== FALSE) {
             return;
         }
     }
     $encodedMessage = $this->encodeMessage($message);
     $preparedStatement = $this->connection->prepare('INSERT INTO queue (msgid, payload) VALUES (:msgid, :payload);');
     $preparedStatement->bindValue(':msgid', $message->getIdentifier());
     $preparedStatement->bindValue(':payload', $encodedMessage);
     $preparedStatement->execute();
     $message->setIdentifier($this->connection->lastInsertRowID());
     $message->setState(Message::STATE_SUBMITTED);
 }

 /**
  * @param string $query
  * @return array|bool|int
  */
 public function query($query)
 {
     $this->logger->log($query);
     $query_type = $this->determine_query_type($query);
     $data = array();
     if ($query_type == SQL_SELECT || $query_type == SQL_SHOW) {
         $SqliteResult = $this->executeQuery($query);
         while ($row = $SqliteResult->fetchArray(SQLITE3_ASSOC)) {
             $data[] = $row;
         }
         return $data;
     } else {
         $this->executeQuery($query);
         if ($query_type == SQL_INSERT) {
             return $this->sqlite3->lastInsertRowID();
         }
         return true;
     }
 }

    /**
     * Insert Entity
     * @param  int $album_ID      Album ID
     * @param  String $type          Nature of the file
     * @param  String $extension     Extension
     * @param  String $description   
     * @param  String $original_name  Full name of the original file, for download
     * @param  Timestamp $taken_date  Date and time of the file when created
     * @return int                entity ID
     */
    function insert_entity($album_ID, $type, $extension, $description, $original_name, $taken_date, $orientation)
    {
        date_default_timezone_set('Europe/London');
        $created_at = date("Y-m-d H:i:s");
        $updated_at = date("Y-m-d H:i:s");
        $sql = <<<EOF
\t\t\tINSERT INTO entity
\t\t\t\t(album_ID, 
\t\t\t\t\ttype, 
\t\t\t\t\textension, 
\t\t\t\t\tdescription, 
\t\t\t\t\toriginal_file_name, 
\t\t\t\t\ttaken_date, 
\t\t\t\t\torientation, 
\t\t\t\t\tcreated_at, 
\t\t\t\t\tupdated_at
\t\t\t\t)
\t\t\tVALUES 
\t\t\t\t("{$album_ID}", 
\t\t\t\t\t"{$type}" , 
\t\t\t\t\t"{$extension}", 
\t\t\t\t\t"{$description}", 
\t\t\t\t\t"{$original_name}", 
\t\t\t\t\t"{$taken_date}", 
\t\t\t\t\t"{$orientation}", 
\t\t\t\t\t"{$created_at}", 
\t\t\t\t\t"{$updated_at}") 
EOF;
        $return = $this->exec($sql);
        return SQLite3::lastInsertRowID();
        /*if(!$return){
        	    	echo $this->lastErrorMsg();
        		} else {
        	    	echo "Records created successfully\n";
        		}*/
    }

 private function add_happiness()
 {
     try {
         // Save happiness data...
         // We expect:
         // _ location (INT)
         // _ happiness (INT)
         // _ unhappiness (INT)
         ensure(array_key_exists('location', $_POST), 'Location required');
         ensure(array_key_exists('happiness', $_POST), 'Happiness required');
         ensure(array_key_exists('unhappiness', $_POST), 'Unhappiness required');
         list($location, $happiness, $unhappiness) = array(intval($_POST['location']), intval($_POST['happiness']), intval($_POST['unhappiness']));
     } catch (Exception $e) {
         $this->bad_request($e);
     }
     // http://uk2.php.net/manual/en/sqlite3.open.php
     $db = new SQLite3('./happy-balls.db', SQLITE3_OPEN_READWRITE);
     $sql = sprintf('INSERT INTO happiness(location, happiness, unhappiness) VALUES (%d, %d, %d);', $location, $happiness, $unhappiness);
     $db->exec($sql);
     $id = $db->lastInsertRowID();
     $db->close();
     print "OK, id={$id}\n";
 }

     $user['avatar_url'] = $session['authinfo']['profile']['photo'];
 }
 $sqdb = new SQLite3('demo.sqlite');
 $sqdb->exec('CREATE TABLE IF NOT EXISTS users ' . '(id INTEGER PRIMARY KEY AUTOINCREMENT, user_name STRING, first_name STRING, last_name STRING, ' . 'email STRING, profile_url STRING, avatar_url STRING, phone STRING, company STRING)');
 $sqdb->exec('CREATE TABLE IF NOT EXISTS user_map (id INTEGER NOT NULL, identifier STRING, provider STRING)');
 $sqdb->exec('CREATE TABLE IF NOT EXISTS user_posts (id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL, comment STRING)');
 $identifier = $sqdb->querySingle('SELECT id FROM user_map WHERE identifier = \'' . $user['identifier'] . '\'');
 if (empty($identifier)) {
     $query = 'INSERT INTO users (user_name) VALUES (\'\')';
     $insert = $sqdb->exec($query);
     if (!$insert) {
         $stat = 'fail';
         $debug[] = $query;
         $debug[] = $sqdb->lastErrorMsg();
     } else {
         $user['id'] = $sqdb->lastInsertRowID();
         $query = 'INSERT INTO user_map (id, identifier, provider) VALUES (\'' . $user['id'] . '\',\'' . $user['identifier'] . '\',\'' . $user['provider'] . '\')';
         $insert = $sqdb->exec($query);
         if (!$insert) {
             $stat = 'fail';
             $debug[] = $query;
             $debug[] = $sqdb->lastErrorMsg();
         }
     }
 } else {
     $user['id'] = $identifier;
 }
 if (!empty($user['id']) && $stat == 'ok') {
     $query = 'SELECT * FROM users WHERE id = \'' . $user['id'] . '\'';
     $db_user = $sqdb->querySingle($query, true);
     foreach ($db_user as $key => $val) {

             $column12 = @$db->escapeString(trim($column12));
             $column13 = @$db->escapeString(trim($column13));
             $column14 = @$db->escapeString(trim($column14));
             $column15 = @$db->escapeString(trim($column15));
             $search = strip_tags($line);
             $search = mb_strtolower($search, 'utf-8');
             $search = explode(' ', $search);
             $search = array_unique($search);
             $search = implode(' ', $search);
             $search = @$db->escapeString($search);
             $add = @$db->exec("INSERT INTO pages (key, ufu, column1, column2, column3, column4, column5, column6, column7, column8, column9, column10, column11, column12, column13, column14, column15, search) VALUES ('" . $line . "', '" . $ufu . "', '" . $column1 . "', '" . $column2 . "', '" . $column3 . "', '" . $column4 . "', '" . $column5 . "', '" . $column6 . "', '" . $column7 . "', '" . $column8 . "', '" . $column9 . "', '" . $column10 . "', '" . $column11 . "', '" . $column12 . "', '" . $column13 . "', '" . $column14 . "', '" . $column15 . "', '" . $search . "');");
         }
     }
     $db->exec('COMMIT;');
     // номер последней вставленной страницы:
     $lastnum = (int) $db->lastInsertRowID();
     // проверяем достижение лимита количества страниц:
     if ($lastnum > $maxpage) {
         file_put_contents('log/' . $host . '.log', $maxpage);
     }
     // обновляем маркер текущей страницы
     if ($ufurl == '1') {
         $update = $db->exec("UPDATE pages SET keys='1' WHERE ufu='" . $p . "';");
     } else {
         $update = $db->exec("UPDATE pages SET keys='1' WHERE id='" . $p . "';");
     }
 }
 // конец парсинга ключевиков
 // парсинг контента
 if ($content == '' or $content == 'a:0:{}') {
     $badchar = array("\n", "\r", "\t", ' ', '«', '»', '"', '←', '→', ''', '–', ' ', ' ', '—', '…', ''', '→', '—', '>', '<', '{', '}', '#', '"', '—', '\'');

/**
* Insert PHP make test results in SQLite database
*
* The following structure must be used as first array : 
*  [status]    => enum(failed, success)
*  [version]   => string   - example: 5.4.1-dev
*  [userEmail] => mangled
*  [date]      => unix timestamp
*  [phpinfo]   => string  - phpinfo() output (CLI)
*  [buildEnvironment] => build environment
*  [failedTest] => array: list of failed test. Example: array('/Zend/tests/declare_001.phpt')
*  [expectedFailedTest] => array of expected failed test (same format as failedTest)
*  [succeededTest] => array of successfull tests. Provided only when parsing ci.qa results (for now)
*  [tests] => array
       testName => array (
           'output' => string("Current output of test")
           'diff'   => string("Diff with expected output of this test")
* @param array array to insert
* @param array releases we accept (so that we don't accept a report that claims to be PHP 8.1 for example)
* @return boolean success or not (for the moment, error leads to a call to 'exit;' ... not good I know)
*/
function insertToDb_phpmaketest($array, $QA_RELEASES = array())
{
    if (!is_array($array)) {
        // impossible to fetch data. We'll record this error later ...
    } else {
        if (strtolower($array['status']) == 'failed') {
            $array['status'] = 0;
        } elseif (strtolower($array['status']) == 'success') {
            $array['status'] = 1;
        } else {
            die('status unknown: ' . $array['status']);
        }
        if (!is_valid_php_version($array['version'], $QA_RELEASES)) {
            exit('invalid version');
        }
        $dbFile = dirname(__FILE__) . '/db/' . $array['version'] . '.sqlite';
        $queriesCreate = array('failed' => 'CREATE TABLE IF NOT EXISTS failed (
                  `id` integer PRIMARY KEY AUTOINCREMENT,
                  `id_report` bigint(20) NOT NULL,
                  `test_name` varchar(128) NOT NULL,
                  `output` STRING NOT NULL,
                  `diff` STRING NOT NULL,
                  `signature` binary(16) NOT NULL
                )', 'expectedfail' => 'CREATE TABLE IF NOT EXISTS expectedfail (
                  `id` integer PRIMARY KEY AUTOINCREMENT,
                  `id_report` bigint(20) NOT NULL,
                  `test_name` varchar(128) NOT NULL
                )', 'success' => 'CREATE TABLE IF NOT EXISTS success (
                  `id` integer PRIMARY KEY AUTOINCREMENT,
                  `id_report` bigint(20) NOT NULL,
                  `test_name` varchar(128) NOT NULL
                )', 'reports' => 'CREATE TABLE IF NOT EXISTS reports (
                  id integer primary key AUTOINCREMENT,
                  date datetime NOT NULL,
                  status smallint(1) not null,
                  nb_failed unsigned int(10)  NOT NULL,
                  nb_expected_fail unsigned int(10)  NOT NULL,
                  success unsigned int(10) NOT NULL,
                  build_env STRING NOT NULL,
                  phpinfo STRING NOT NULL,
                  user_email varchar(64) default null
            )');
        if (!file_exists($dbFile)) {
            //Create DB
            $dbi = new SQLite3($dbFile, SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE);
            foreach ($queriesCreate as $table => $query) {
                $dbi->exec($query);
                if ($dbi->lastErrorCode() != '') {
                    echo "ERROR when creating table " . $table . ": " . $dbi->lastErrorMsg() . "\n";
                    exit;
                }
            }
            $dbi->close();
        }
        $dbi = new SQLite3($dbFile, SQLITE3_OPEN_READWRITE) or exit('cannot open DB to record results');
        // handle tests with no success
        if (!isset($array['succeededTest'])) {
            $array['succeededTest'] = array();
        }
        $query = "INSERT INTO `reports` (`id`, `date`, `status`, \n        `nb_failed`, `nb_expected_fail`, `success`, `build_env`, `phpinfo`, user_email) VALUES    (null, \n        datetime(" . (int) $array['date'] . ", 'unixepoch', 'localtime'), \n        " . (int) $array['status'] . ", \n        " . count($array['failedTest']) . ", \n        " . count($array['expectedFailedTest']) . ", \n        " . count($array['succeededTest']) . ", \n        ('" . $dbi->escapeString($array['buildEnvironment']) . "'), \n        ('" . $dbi->escapeString($array['phpinfo']) . "'),\n        " . (!$array['userEmail'] ? "NULL" : "'" . $dbi->escapeString($array['userEmail']) . "'") . "\n        )";
        $dbi->query($query);
        if ($dbi->lastErrorCode() != '') {
            echo "ERROR: " . $dbi->lastErrorMsg() . "\n";
            exit;
        }
        $reportId = $dbi->lastInsertRowID();
        foreach ($array['failedTest'] as $name) {
            if (substr($name, 0, 1) != '/') {
                $name = '/' . $name;
            }
            $test = $array['tests'][$name];
            $query = "INSERT INTO `failed` \n            (`id`, `id_report`, `test_name`, signature, `output`, `diff`) VALUES    (null, \n            '" . $reportId . "', '" . $name . "', \n            X'" . md5($name . '__' . $test['diff']) . "',\n            ('" . $dbi->escapeString($test['output']) . "'), ('" . $dbi->escapeString($test['diff']) . "'))";
            @$dbi->query($query);
            if ($dbi->lastErrorCode() != '') {
                echo "ERROR when inserting failed test : " . $dbi->lastErrorMsg() . "\n";
                exit;
            }
        }
        foreach ($array['expectedFailedTest'] as $name) {
//.........这里部分代码省略.........

<?php

$db = new SQLite3(':memory:');
echo "Creating Table\n";
var_dump($db->exec('CREATE TABLE test (time INTEGER, id STRING)'));
echo "Inserting data\n";
var_dump($db->exec('INSERT INTO test (time, id) VALUES(2, 1)'));
echo "Request last inserted id\n";
try {
    $db->lastInsertRowID("");
} catch (Exception $ex) {
    var_dump($ex->getMessage());
}
echo "Closing database\n";
var_dump($db->close());
echo "Done";

<form action="upload.php" method="post" enctype="multipart/form-data">
    <input type="file" name="file">
    <input type="submit" value="submit">
</form>
<?php 
    exit;
}
?>
<pre>
<?php 
$db = new SQLite3('monitor.db');
$hashed = sha1_file($_FILES['file']['tmp_name']);
$row = $db->query("SELECT id, person, result FROM image WHERE hash = '{$hashed}';")->fetchArray();
if (!$row) {
    $db->exec("INSERT INTO image (hash, person, result) VALUES ('{$hashed}', '<unnamed>', '<unnamed>');");
    $imgid = $db->lastInsertRowID();
    $imgprs = '<unnamed>';
    $imgres = '<unnamed>';
    move_uploaded_file($_FILES['file']['tmp_name'], "image/{$hashed}.jpg");
} else {
    $imgid = $row['id'];
    $imgprs = $row['person'];
    $imgres = $row['result'];
}
$stmt = $db->prepare("INSERT INTO record (image, person, result) VALUES (?, ?, ?);");
$stmt->bindParam(1, $imgid, SQLITE3_INTEGER);
$stmt->bindParam(2, $imgprs, SQLITE3_TEXT);
$stmt->bindParam(3, $imgres, SQLITE3_TEXT);
if (!$stmt->execute()) {
    die('INSERT fail');
}

 public function lastUsedID()
 {
     return $this->dbHandle->lastInsertRowID();
 }

 /**
  * Get last insert Id
  *
  * @return int
  */
 public function getLastInsertId()
 {
     return $this->sqlite->lastInsertRowID();
 }

 public function insert_id()
 {
     return $this->handler->lastInsertRowID();
 }