README

This is a simple abstraction library for SQLite DML operations.

For example:

SQLite->from(string $table)
  ->where(?array ...$conditions)
  ->order(?array $order_by)
  ->limit(?int $limit = null, ?int $offset = null)
  ->select(string|array|null $columns = null): SQLiteResult|bool;

which would be equivalent to the following in SQLite:

SELECT `columns` FROM `table` WHERE `filter` ORDER BY `order_by` LIMIT `limit`;

All methods can be chained in any order (even multiple times) after a from() as long as a select(), insert(), update(), or delete() is the last method.
Chaining the same method more than once will override its previous value. Passing null to any method that accepts it will unset its value completely.

Install from composer

composer require vlw/sqlite

use vlw\SQLite\SQLite;

[!IMPORTANT] This library requires the SQLite3 extension and PHP 8.0 or newer.

Example / Documentation

Example table name: beverages id|beverage_type|beverage_name|beverage_size --|--|--|-- 0|coffee|cappuccino|10 1|coffee|black|15 2|tea|green|10 3|tea|black|15

use vlw\SQLite\SQLite;

// Inherits: https://www.php.net/manual/en/sqlite3.construct.php
$db = new SQLite($db);

All executor methods select(), update(), and insert() will return a SQLiteResult object or boolean. SQLiteResult is an proxy (essentially an extension) of SQLite3Result.

FROM

SQLite->from(
  string $table
): self;

All queries start by chaining the from(string $table) method. This will define which database table the current query should be executed on.

Example:

SQLite("beverages.db")
  ->from("beverages")
  ->select("beverage_type");

SELECT

Chain SQLite->select() anywhere after a SQLite->from() to retrieve columns from a database table.

Pass an associative array of strings, CSV string, or null to this method to filter columns.

SQLite->select(
  string|array|null $columns
): SQLiteResult|bool;

In most cases you probably want to select with a constraint. Chain the where() method before select() to filter the query

Example

SQLite("beverages.db")
  ->from("beverages")
  ->select(["beverage_name", "beverage_size"]);

INSERT

Chain SQLite->insert() anywhere after a SQLite->from() to append a new row to a database table.

Passing a sequential array to insert() will assume that you wish to insert data for all defined columns in the table. Pass an associative array of [column_name => value] to INSERT data for specific columns (assuming the other columns have a DEFAULT value defined).

SQLite->insert(
  // Array of values to INSERT
  array $values
): SQLiteResult

Example

SQLite("beverages.db")
  ->from("beverages")
  ->insert([
    null,
    "coffee",
    "latte",
    10
  ]);

[!NOTE] You can also pass an associative array of column names and values

DELETE

Chain SQLite->delete() anywhere after a SQLite->from() to remove a row or rows from the a database table.

SQLite->delete(
  array ...$conditions
): SQLiteResult
// Returns true if at least one row was deleted

This method takes at least one SQLite->where()-syntaxed argument to determine which row or rows to delete. Refer to the SQLite->where() section for more information.

Example

SQLite("beverages.db")
  ->from("beverages")
  ->delete([
    "beverage_name" => "coffee",
  ]);

UPDATE

Chain SQLite->update() anywhere after a SQLite->from() to modify existing rows in a database table.

SQLite->update(
  // Key, value array of column names and values to update
  array $fields,
): SQLiteResult|bool;

Example

SQLite("beverages.db")
  ->from("beverages")
  ->update([
    "beverage_size" => 10
  ]);

In most cases you probably want to UPDATE against a constaint. Chain a where() method before SQLite->update() to set constraints

WHERE

Filter a SQLite->select() or SQLite->update() method by chaining the SQLite->where() method anywhere before it. The SQLite->delete() executor method also uses the same syntax for its arguments.

Each key, value pair will be AND constrained against each other.

SQLite->where(
  ?array ...$conditions
): self;

Example

SQLite("beverages.db")
  ->from("beverages")
  ->where([
    "beverage_type" => "coffee"
  ])
  ->select([
    "beverage_name", 
    "beverage_size"
  ]);
// SELECT `beverage_name`, `beverage_size` FROM `beverages` WHERE (`beverage_type` = "coffee");

Capture groups

AND

Add additional key value pairs to an array passed to where() and they will all be compared as AND with each other.

SQLite->where([
  "beverage_type" => "coffee",
  "beverage_size" => 15
]);

WHERE (`beverage_type` = 'coffee' AND `beverage_size` = 15)

OR

Passing an additional array of key values as an argument will OR it with all other arrays passed.

$filter1 = [
  "beverage_type" => "coffee",
  "beverage_size" => 15
];

$filter2 = [
  "beverage_type" => "tea",
  "beverage_name" => "black"
];

SQLite->where($filter1, $filter2, ...);

WHERE (`beverage_type` = 'coffee' AND `beverage_size` = 15) OR (`beverage_type` = 'tea' AND `beverage_name` = 'black')

Define custom operators

By default, all values in an the assoc array passed to where() will be treated as an EQUALS (=) operator.

SQLite->where(["column" => "euqals_this_value"]);

Setting the value of any key to another assoc array will allow for more "advanced" filtering by defining your own Operators.

The key of this subarray can be any SQLite operator string, or the ->value of any case in the Operators enum.

SQLite->where([
  "beverage_name" => [
    Operators::LIKE->value => "%wildcard_contains%"
  ]
]);

ORDER BY

Chain the SQLite->order() method before a SQLite->select() statement to order by a specific column

SQLite->order(
  ?array $order_by
): self;

SQLite("beverages.db")
  ->from("beverages")
  ->order([
    "beverage_name" => "ASC"
  ])
  ->select([
    "beverage_name", "beverage_size"
  ]);
// SELECT `beverage_name`, `beverage_size` FROM `beverages` ORDER BY `beverage_name` ASC

LIMIT

Chain the limit() method before a SQLite->select() statement to limit the amount of columns returned

SQLite->limit(
  ?int $limit,
  ?int $offset = null
): self;

Passing a single integer argument

This will simply LIMIT the results returned to the integer passed

SQLite("beverages.db")
  ->from("beverages")
  ->limit(1)
  ->select([
    "beverage_name",
    "beverage_size"
  ]);
// SELECT `beverage_name`, `beverage_size` FROM `beverages` WHERE `beverage_type` = "coffee" LIMIT 1

Passing two integer arguments

This will OFFSET and LIMIT the results returned. The first argument will be the LIMIT and the second argument will be its OFFSET.

SQLite("beverages.db")
  ->from("beverages")
  ->limit(3, 2)
  ->select([
    "beverage_name",
    "beverage_size"
  ]);
// SELECT `beverage_name`, `beverage_size` FROM `beverages` LIMIT 3 OFFSET 2

SQLiteResult

All methods return the clas vlw/SQLite/SQLiteResult which is a superset of SQLite3Result. It implements the following quality of life methods:

SQLiteResult->assoc()

Returns an array of associative arrays for each matched row for a resuly. Essentially a polyfill for mysqli's mysqli_result->fetch_all(MYSQLI_ASSOC)

Example:

SQLite("beverages.db")
  ->from("beverages")
  ->select(["beverage_name", "beverage_size"])
  ->assoc();

[
  [
    "beverage_name" => "tea",
    "beverage_size" => 10
  ],
  [
    "beverage_name" => "tea",
    "beverage_size" => 15
  ]
  // ..etc
]

vlw / sqlite

Maintainers

Package info

Statistics

Security

README

Install from composer

Example / Documentation

FROM

SELECT

Example

INSERT

Example

DELETE

Example

UPDATE

Example

WHERE

Example

Capture groups

AND

OR

Define custom operators

ORDER BY

LIMIT

Passing a single integer argument

Passing two integer arguments

SQLiteResult

SQLiteResult->assoc()