PHP download

PHP code example of squirrelphp / queries

1. Go to this page and download the library: Download squirrelphp/queries 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/ */

squirrelphp / queries example snippets


$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\Queries\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` > ?) ...