PHP code example of squirrelphp / queries

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

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

    

squirrelphp / queries example snippets

use Squirrel\Connection\Config\Mysql;
use Squirrel\Connection\PDO\ConnectionPDO;
use Squirrel\Queries\DBBuilder;
use Squirrel\Queries\DBInterface;
use Squirrel\Queries\DB\ErrorHandler;
use Squirrel\Queries\DB\MySQLImplementation;

// Create a squirrel connection
$connection = new ConnectionPDO(
    new Mysql(
        host: 'localhost',
        user: 'user',
        password: 'password',
        dbname: 'mydb',
    ),
);

// Create a MySQL implementation layer
$implementationLayer = new MySQLImplementation($connection);

// Create an error handler layer
$errorLayer = new ErrorHandler();

// Set implementation layer beneath the error layer
$errorLayer->setLowerLayer($implementationLayer);

// Rename our layered service - this is now our database object
$db = $errorLayer;

// $db is now useable and can be injected
// anywhere you need it. Typehint it with
// \Squirrel\Queries\DBInterface
$fetchEntry = function(DBInterface $db): array {
    return $db->fetchOne('SELECT * FROM table');
};

$fetchEntry($db);

// A builder just needs a DBInterface to be created:
$queryBuilder = new DBBuilder($db);

// The query builder generates more readable queries, and
// helps your IDE in terms of type hints / possible options
// depending on the query you are doing
$entries = $queryBuilder
    ->select()
    ->fields([
      'id',
      'name',
    ])
    ->where([
      'name' => 'Robert',
    ])
    ->getAllEntries();

// If you want to add more layers, you can create a
// class which implements DBRawInterface and 

$selectStatement = $db->select('SELECT fieldname FROM tablename WHERE restriction = ? AND restriction2 = ?', [5, 8]);
$firstRow = $db->fetch($selectStatement);
$db->clear($selectStatement);

$selectStatement = $db->select('SELECT fieldname FROM tablename WHERE restriction = 5 AND restriction2 = 8');

$firstRow = $db->fetchOne('SELECT fieldname FROM tablename WHERE restriction = ? AND restriction2 = ?', [5, 8]);

$allRows = $db->fetchAll('SELECT fieldname FROM tablename WHERE restriction = ? AND restriction2 = ?', [5, 8]);

$selectStatement = $db->select([
    'field' => 'fieldname',
    'table' => 'tablename',
    'where' => [
        'restriction' => 5,
        'restriction2' => 8,
    ],
]);
$firstRow = $db->fetch($selectStatement);
$db->clear($selectStatement);

$selectStatement = $db->select('SELECT ´fieldname´ FROM ´tablename´ WHERE ´restriction´=? AND ´restriction2´=?', [5, 8]);

$selectStatement = $db->select([
   'fields' => [
       'fufumama',
       'b.lalala',
       'result' => 'a.setting_value',
       'result2' => ':a.setting_value:+:b.blabla_value:',
   ],
   'tables' => [
       'blobs.aa_sexy a',
       ':blobs.aa_blubli: :b: LEFT JOIN :blobs.aa_blubla: :c: ON (:c.field: = :b.field5: AND :b.sexy: = ?)' => 5,
   ],
   'where' => [
       ':a.field: = :b.field:',
       'setting_id' => 'orders_xml_override',
       'boring_field_name' => [5,3,8,13],
       ':setting_value: = ? OR :setting_value2: = ?' => ['one','two'],
   ],
   'group' => [
        'a.field',
   ],
   'order' => [
        'a.field' => 'DESC',
   ],
   'limit' => 10,
   'offset' => 5,
   'lock' => true,
]);
$firstRow = $db->fetch($selectStatement);
$db->clear($selectStatement);

$selectStatement = $db->select('SELECT `fufumama`,`b`.`lalala`,`a`.`setting_value` AS "result",(`a`.`setting_value`+`b`.`blabla_value`) AS "result2" FROM `blobs`.`aa_sexy` `a`,`blobs`.`aa_blubli` `b` LEFT JOIN `blobs`.`aa_blubla` `c` ON (`c`.`field` = `b`.`field5` AND `b`.`sexy` = ?) WHERE (`a`.`field` = `b`.`field`) AND `setting_id`=? AND `boring_field_name` IN (?,?,?,?) AND (`setting_value` = ? OR `setting_value2` = ?) GROUP BY `a`.`field` ORDER BY `a`.`field` DESC LIMIT 10 OFFSET 5 FOR UPDATE', [5,'orders_xml_override',5,3,8,13,'one','two']);

$rowsAffected = $db->change('UPDATE users SET first_name = ?, last_name = ?, login_number = login_number + 1 WHERE user_id = ?', [
    'Liam', // first_name
    'Henry', // last_name
    5, // user_id
]);

$rowsAffected = $db->change('DELETE FROM users WHERE user_id = ? AND first_name = ?', [
    5, // user_id
    'Liam', // first_name
]);

$rowsAffected = $db->change('INSERT INTO users (user_id, first_name) SELECT user_id, first_name FROM users_backup');

$rowsAffected = $db->update('tablename', [
    'fieldname' => 'string',
    'locationId' => 5,
], [
    'restriction' => 5,
    'restriction2' => 8,
]);

$rowsAffected = $db->change('UPDATE ´tablename´ SET ´fieldname´=?,`locationId`=? WHERE ´restriction´=? AND ´restriction2´=?', ['string', 5, 5, 8]);

$insertId = $db->insert('yourdatabase.yourtable', [
    'tableId' => 5,
    'column1' => 'Henry',
    'other_column' => 'Liam',
], 'rowId');

$db->insertOrUpdate('users_visits', [
    'userId' => 5,
    'visit' => 1,
], [
    'userId',
], [
    ':visit: = :visit: + 1'
]);

$db->change('INSERT INTO `users_visits` (`userId`,`visit`) VALUES (?,?) ON DUPLICATE KEY UPDATE `visit` = `visit` + 1', [5, 1]);

$db->change('INSERT INTO "users_visits" ("userId","visit") VALUES (?,?) ON CONFLICT ("userId") DO UPDATE SET "visit" = "visit" + 1', [5, 1]);

$db->insertOrUpdate('users_names', [
    'userId' => 5,
    'firstName' => 'Jane',
], [
    'userId',
]);

$db->change('INSERT INTO `users_names` (`userId`,`firstName`) VALUES (?,?) ON DUPLICATE KEY UPDATE `firstName`=?, [5, 'Jane', 'Jane']);

$rowsAffected = $db->delete('users_names', [
    'userId' => 13,
]);

$rowsAffected = $db->change('DELETE FROM `users_names` WHERE `userId`=?', [13]);

$db->transaction(function() {
    // Do queries in here as much as you want, it will all be one transaction
    // and committed as soon as this function ends
});

$db->transaction(function() use ($db) {
    $tableId = $db->insert('myTable', [
        'tableName' => 'Henry',
    ], 'tableId');

    $db->update('otherTable', [
        'tableId' => $tableId,
    ], [
        'tableName' => 'Henry',
    ]);
});

$db->transaction(function() use ($db) {
    $tableId = $db->insert('myTable', [
        'tableName' => 'Henry',
    ], 'tableId');

    // This still does exactly the same as in the previous example, because the
    // function will be executed without a "new" transaction being started,
    // the existing one just continues
    $db->transaction(function() use ($db, $tableId)) {
        // If this fails, then the error handler will attempt to repeat the outermost
        // transaction function, which is what you would want / expect, so it starts
        // with the Henry insert again
        $db->update('otherTable', [
            'tableId' => $tableId,
        ], [
            'tableName' => 'Henry',
        ]);
    });
});

$db->transaction(function(string $table, string $tableName) use ($db) {
    $tableId = $db->insert('myTable', [
        'tableName' => 'Henry',
    ], 'tableId');

    $db->update('otherTable', [
        'tableId' => $tableId,
    ], [
        'tableName' => $tableName,
    ]);
}, 'myTable', 'Henry');

$rowsAffected = $db->change('UPDATE ' . $db->quoteIdentifier('users') . ' SET ' . $db->quoteIdentifier('first_name') . ')=? WHERE ' . $db->quoteIdentifier('user_id') . '=?', ['Sandra', 5]);

$rowsAffected = $db->change($db->quoteExpression('UPDATE :users: SET :first_name:=? WHERE :user_id:=?'), ['Sandra', 5]);

// $usersNumber will be an integer
$usersNumber = $dbBuilder
    ->count()
    ->inTables([
        'users u',
        'users_addresses a',
    ])
    ->where([
        ':u.userId: = :a.userId:',
        'u.zipCode' => 33769,
    ])
    ->getNumber();

$selectQuery = $dbBuilder
    ->select()
    ->fields([
        'u.userId',
        'name' => 'a.firstName',
    ])
    ->inTables([
        'users u',
        'users_addresses a',
    ])
    ->where([
        ':u.userId: = :a.userId:',
        'u.zipCode' => 33769,
    ])
    ->groupBy([
        'u.userId',
    ])
    ->orderBy([
        'u.createDate',
    ])
    ->limitTo(3)
    ->startAt(0)
    ->blocking();

foreach ($selectQuery as $result) {
    echo $result['userId'] . ' => ' . $result['name'];
}

$userResults = $dbBuilder
    ->select()
    ->fields([
        'u.userId',
        'name' => 'a.firstName',
    ])
    ->inTables([
        'users u',
        'users_addresses a',
    ])
    ->where([
        ':u.userId: = :a.userId:',
        'u.zipCode' => 33769,
    ])
    ->groupBy([
        'u.userId',
    ])
    ->orderBy([
        'u.createDate',
    ])
    ->limitTo(3)
    ->startAt(0)
    ->blocking()
    ->getAllEntries();

foreach ($userResults as $result) {
    echo $result['userId'] . ' => ' . $result['name'];
}

$result = $dbBuilder
    ->select()
    ->fields([
        'u.userId',
        'name' => 'a.firstName',
    ])
    ->inTables([
        'users u',
        'users_addresses a',
    ])
    ->where([
        ':u.userId: = :a.userId:',
        'u.zipCode' => 33769,
    ])
    ->groupBy([
        'u.userId',
    ])
    ->orderBy([
        'u.createDate',
    ])
    ->limitTo(3)
    ->startAt(0)
    ->blocking()
    ->getOneEntry();

echo $result['userId'] . ' => ' . $result['name'];

$userIds = $dbBuilder
    ->select()
    ->field('userId')
    ->inTable('users')
    ->where([
        'u.zipCode' => 33769,
    ])
    ->getFlattenedFields();

foreach ($userIds as $userId) {
    // Do something which each $userId here
}

$newUserIdFromDatabase = $dbBuilder
    ->insert()
    ->inTable('users')
    ->set([
      'userName' => 'Kjell',
    ])
    ->writeAndReturnNewId('rowId');

$rowsAffected = $dbBuilder
    ->update()
    ->inTable('users')
    ->set([
        'lastLoginDate' => time(),
        ':visits: = :visits: + 1',
    ])
    ->where([
        'userId' => 33,
    ])
    ->writeAndReturnAffectedNumber();

$rowsAffected = $dbBuilder
    ->update()
    ->inTable('users')
    ->set([
        'lastLoginDate' => time(),
        ':visits: = :visits: + 1',
    ])
    ->confirmNoWhereRestrictions()
    ->writeAndReturnAffectedNumber();

$insertBuilder
    ->insertOrUpdate()
    ->inTable('users_visits')
    ->set([
        'userId' => 5,
        'visits' => 1,
    ])
    ->index([
        'userId',
    ])
    ->setOnUpdate([
        ':visits: = :visits: + 1',
    ])
    ->write();

$rowsAffected = $dbBuilder
    ->delete()
    ->inTable('users')
    ->where([
        'userId' => 33,
    ])
    ->writeAndReturnAffectedNumber();

$rowsAffected = $dbBuilder
    ->delete()
    ->inTable('users')
    ->confirmNoWhereRestrictions()
    ->writeAndReturnAffectedNumber();

$user = $dbBuilder
    ->select()
    ->inTable('users')
    ->where([
        'user_id' => $userId, // user_id must be equal to $userId
    ])
    ->getOneEntry();

// $user now contains all table column and values:
echo $user['user_id'];

$user = $dbBuilder
    ->select()
    ->inTable('users')
    ->where([
        ':user_id: BETWEEN ? AND ?' => [15, 55],
        ':create_date: > ?' => time() - 86400,
    ])
    ->getOneEntry();

$rowsAffected = $dbBuilder
    ->update()
    ->inTable('users')
    ->set([
        'last_login_date' => time(),
        ':visits: = :visits: + 1',
        ':full_name: = CONCAT(:first_name:,:last_name:)',
        ':balance: = :balance: + ?' => $balanceIncrease,
    ])
    ->where([
        'user_id' => 33,
        ':last_login_date: < ?' => time()-86400,
    ])
    ->writeAndReturnAffectedNumber();

use Squirrel\Connection\LargeObject;

$rowsAffected = $dbBuilder
    ->update()
    ->inTable('files')
    ->set([
        'file_name' => 'someimage.jpg',
        'file_data' => new LargeObject(file_get_contents('someimage.jpg')),
    ])
    ->where([
        'file_id' => 33,
    ])
    ->write();

$file = $dbBuilder
    ->select()
    ->inTable('files')
    ->where([
        'file_id' => 33,
    ])
    ->getOneEntry();

// Use file_data in some way, like showing or writing it - it is a regular string
echo $file['file_data'];

$rowsAffected = $db->change('UPDATE sessions SET time_zone = \'Europe/Zurich\' WHERE session_id = \'zzjEe2Jpksrjxsd05m1tOwnc7LJNV4sV\'');

$rowsAffected = $db->change('UPDATE sessions SET time_zone = ? WHERE session_id = ?', [
    'Europe/Zurich',
    'zzjEe2Jpksrjxsd05m1tOwnc7LJNV4sV',
]);

sql
... WHERE (`user_id` BETWEEN ? AND ?) AND (`create_date` > ?) ...