PHP code example of codethereal / sqlite-php

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

codethereal / sqlite-php example snippets

$sqlite = new Codethereal\Database\Driver\Sqlite('data.db');
$db = $sqlite->getQueryBuilder();

$resultSingle = $db->select('name,email,password')->getSingle('users'); // SELECT name,email,password FROM posts LIMIT 1
$result = $db->select('name,email,password')->get('users'); // SELECT name,email,password FROM posts
  
# For mode 1: it returns you an array with db column keys
# For mode 2: it returns you an array with index numbers
# For mode 3: it returns you an array with both column keys and index numbers
###*** You must use while loop on returned result, if you want you get only one record ***###
while ($row = $result->fetchArray(1)) {
    echo "<pre>";
    print_r($row);
    echo "</pre>";
}

# Allowed where operators are: ['=', '>', '<', '>=', '<=', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN']
$db->where('id', 2)->get('users'); // SELECT * FROM users WHERE id = 2
$db->where('id', '>', 2)->get('users'); // SELECT * FROM users WHERE id > 2
$db
    ->where('id', '>', 2)
    ->where('name', 'codethereal')
    ->get('users'); // SELECT * FROM users WHERE id > 2 AND name = 'codethereal'

$db->select('name,email')
    ->where('id', 5)
    ->orWhere('name', 'codethereal')
    ->getSingle('users'); // SELECT name,email FROM users WHERE id = 5 OR name = 'dogukan' LIMIT 1

$db->select('name,email')
    ->where('id', 5)
    ->orWhere(function (\Codethereal\Database\Builder\Query $query) {
        return $query
            ->where('name', 'codethereal')
            ->where('email', '[email protected]');
    })
    ->orWhere('name', 'codethereal')
    ->getSingle('users'); // SELECT name,email FROM users WHERE id = 5 OR (name = 'dogukan' AND email = '[email protected]') OR name = 'codethereal' LIMIT 1

$db->in('id', [1, 2])->get('users'); // SELECT * FROM users WHERE id IN (1,2)
$db->notIn('id', [1, 2])->get('users'); // SELECT * FROM users WHERE id NOT IN (1,2)

$db->where('name', 'LIKE', 'Dogukan%')->get('users'); // SELECT * FROM users WHERE name LIKE 'Dogukan%'
$db->where('name', 'NOT LIKE', '%Codethereal%')->get('users'); // SELECT * FROM users WHERE name LIKE '%Codethereal%'

$db->orderBy('name', 'ASC')->get('users'); // SELECT * FROM users ORDER BY name ASC

# Available join methods for sqlite are: ['INNER', 'CROSS', 'LEFT (OUTER)']
$db->select('users.name as userName, posts.name as postName')->join('users', 'users.id = posts.user_id', 'CROSS')->get('posts');
$db->select('users.name as userName, posts.name as postName')->join('users', 'users.id = posts.user_id', 'INNER')->get('posts');

$db->where('views', '>', 10)->count('posts'); // SELECT COUNT(*) as count FROM posts

$db->insert('users', ['name' => 'Dogukan Akkaya', 'email' => '[email protected]']); // INSERT INTO users (name, email) VALUES ('Dogukan Akkaya', '[email protected]') | Returns insert id on success

$db->where('id', 1)->update('users', ['name' => 'Dogukan Akkaya | Codethereal', 'email' => '[email protected]']); // UPDATE users SET name = 'Dogukan Akkaya | Codethereal', email = '[email protected]' WHERE id = 1

$db->where('id', 1)->delete('users'); // DELETE FROM users WHERE id = 1

# You don't have to wrap with try-catch block. It will rollback on any error
$sqlite->transBegin();
$id = $db->insert('users', ['name' => 'Codethereal', 'email' => '[email protected]']);
$db->insert('postss', ['name' => 'New post', 'user_id' => $id]);
$sqlite->transCommit();