1. Go to this page and download the library: Download quasilyte/ksqlite library. Choose the download type require.
2. Extract the ZIP file and open the index.php.
3. Add this code to the index.php.
<?php
require_once('vendor/autoload.php');
/* Start to develop here. Best regards https://php-download.com/ */
quasilyte / ksqlite example snippets
function exec(string $sql, array $params = []): bool
// Simple case: not bind params.
$query = 'CREATE TABLE IF NOT EXISTS languages(
lang_id INTEGER PRIMARY KEY,
lang_name TEXT NOT NULL
);'
if (!$db->exec($query)) {
handle_error($db->getLastError());
}
// Exec with named params.
// Note: a var prefix (':', '@' or '$') should be consistent
// between the query and bind params array.
$query = 'INSERT INTO languages(lang_name) VALUES(:lang_name)';
$params = [':lang_name' => 'KPHP'];
if (!$db->exec($query, $params)) {
handle_error($db->getLastError());
}
// Exec with positional params.
// Note: bind var numbers start from 1.
$query = 'DELETE FROM languages WHERE lang_name = ?1 OR lang_name = ?2';
$params = [1 => 'COBOL', 2 => 'Visual COBOL'];
if (!$db->exec($query, $params)) {
handle_error($db->getLastError());
}
function execPrepared(string $sql, $bind_params_func): bool
// Execute several inserts with different bind var sets.
$values = [10, 20, 30, 40];
$query = 'INSERT INTO fav_numbers(num_value) VALUES(?1)';
$ok = $db->execPrepared($query, function(KSQLiteParamsBinder $b) use ($values) {
if ($b->query_index >= count($values)) {
return false; // No more rows to insert, stop now
}
// Bind ?1 to the specified value.
// Use string keys, like ':num_value', to bind named params.
$b->bind(1, $values[$b->query_index]);
return true; // Parameters bound, execute the query
});
if (!$ok) {
handle_error($db->getLastError());
}
// Execute 10 inserts without bind vars.
$query = "INSERT INTO fav_events(event_time) VALUES(time('now'))";
$ok = $db->execPrepared($query, function(KSQLiteParamsBinder $b) {
return $b->query_index < 10;
});
if (!$ok) {
handle_error($db->getLastError());
}
// Prepared statement API allows you to perform N queries
// using the same statement even if you don't know the exact
// N in advance.
$query = "INSERT INTO important_data(x, y) VALUES(:x, :y)";
$ok = $db->execPrepared($query, function(KSQLiteParamsBinder $b) use ($stream) {
// Note: we're not even using $b->index here as our data stream is statefull
// and it knows which item we're processing right now.
if (!$stream->hasMore()) {
return false;
}
$stream->next();
foreach ($stream->keyValue() as $k => $v) {
$b->bind($k, $v);
}
return true;
});
if (!$ok) {
handle_error($db->getLastError());
}
// The simplest case: no bind params, default mapping function, collecting all results.
// The result rows are arrays of [x, y].
$query = 'SELECT x, y FROM tab';
[$rows, $ok] = $db->fetch($query);
if (!$ok) {
handle_error($db->getLastError());
}
foreach ($rows as $i => [$x, $y]) {
var_dump([$i => "x=$x y=$y"]);
}
// Using the same query, but building the result with assoc arrays,
// like ['x' => $x, 'y' => $y].
[$rows, $ok] = $db->fetch($query, [], function(KSQLiteQueryContext $ctx) {
return $ctx->rowDataAssoc();
});
if (!$ok) {
handle_error($db->getLastError());
}
foreach ($rows as $i => $data) {
var_dump([$i => "x=$data['x'] y=$data['y']"]);
}
// If you return a non-array value from fetch, you'll get a flat array in the final result.
$query = 'SELECT id, second_key FROM users WHERE age >= ?1';
$vars = [1 => 18];
[$ids, $ok] = $db->fetch($query, $vars, function(KSQLiteQueryContext $ctx) {
return $ctx->rowDataAssoc()['id'];
});
if (!$ok) {
handle_error($db->getLastError());
}
foreach ($ids as $i => $id) {
var_dump([$i => "id=$id"]);
}
function fetchRow(string $sql, array $params = []): tuple(mixed[], bool)
$query = 'SELECT * FROM users WHERE user_id = :id';
[$user, $ok] = $db->fetchRow($query, [':id' => $id]);
if (!$ok) {
handle_error($db->getLastError());
}
function fetchColumn(string $sql, array $params = []): tuple(mixed, bool)
$query = 'SELECT COUNT(*) FROM users';
[$num_users, $ok] = $db->fetchColumn($query);
if (!$ok) {
handle_error($db->getLastError());
}
function query(string $sql, array $params, $row_func): bool
// Implementing a fetch-like operation via query.
$result = new KSQLiteArray();
$ok = $db->query($sql, $vars, function(SQLiteQueryContext $ctx) use ($result) {
$result->values[] = $ctx->rowData();
});
if (!$ok) {
handle_error($db->getLastError());
}
$handler->processData($result->values); // Work with unboxed [K]PHP array
function queryPrepared(string $sql, $bind_params_func, $row_func): bool
$ok = $db->queryPrepared(
'SELECT * FROM fav_numbers WHERE num_id = :num_id',
function(KSQLiteParamsBinder $b) use ($ids) {
if ($b->query_index >= count($ids)) {
return false;
}
$b->bind(':num_id', $ids[$b->query_index]);
return true;
},
function(KSQLiteQueryContext $ctx) {
// $ctx->query_index is 0 for the first prepared query execution.
// The second execution will have $query_index=1 and so on.
var_dump($ctx->query_index . '=>' . $ctx->rowDataAssoc()['num_value']);
}
);
if (!$ok) {
handle_error($db->getLastError());
}