PHP code example of theodorejb / peachy-sql

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

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

    

theodorejb / peachy-sql example snippets

use DevTheorem\PeachySQL\PeachySql;

$connection = new PDO('sqlsrv:server=(local)', $username, $password, [
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE => true,
    'Database' => 'someDbName',
]);

$db = new PeachySql($connection);

$sql = "UPDATE Users SET fname = ? WHERE user_id = ?";
$stmt = $db->prepare($sql, [&$fname, &$id]);

$nameUpdates = [
    3 => 'Theodore',
    7 => 'Luke',
];

foreach ($nameUpdates as $id => $fname) {
    $stmt->execute();
}

$stmt->close();

$sql = 'SELECT * FROM Users WHERE fname LIKE ? AND lname LIKE ?';
$result = $db->query($sql, ['theo%', 'b%']);
echo json_encode($result->getAll());

// select all columns and rows in a table, ordered by last name and then first name
$rows = $db->selectFrom("SELECT * FROM Users")
    ->orderBy(['lname', 'fname'])
    ->query()->getAll();

// select from multiple tables with conditions and pagination
$rows = $db->selectFrom("SELECT * FROM Users u INNER JOIN Customers c ON c.CustomerID = u.CustomerID")
    ->where(['c.CustomerName' => 'Amazing Customer'])
    ->orderBy(['u.fname' => 'desc', 'u.lname' => 'asc'])
    ->offset(0, 50) // page 1 with 50 rows per page
    ->query()->getIterator();

use DevTheorem\PeachySQL\QueryBuilder\SqlParams;

$sql = "
    WITH UserVisits AS (
        SELECT user_id, COUNT(*) AS recent_visits
        FROM UserHistory
        WHERE date > ?
        GROUP BY user_id
    )
    SELECT u.fname, u.lname, uv.recent_visits
    FROM Users u
    INNER JOIN UserVisits uv ON uv.user_id = u.user_id";

$date = (new DateTime('2 months ago'))->format('Y-m-d');

$rows = $db->select(new SqlParams($sql, [$date]))
    ->where(['u.status' => 'verified'])
    ->query()->getIterator();

$userData = [
    'fname' => 'Donald',
    'lname' => 'Chamberlin'
];

$id = $db->insertRow('Users', $userData)->id;

$userData = [
    [
        'fname' => 'Grace',
        'lname' => 'Hopper'
    ],
    [
        'fname' => 'Douglas',
        'lname' => 'Engelbart'
    ],
    [
        'fname' => 'Margaret',
        'lname' => 'Hamilton'
    ]
];

$result = $db->insertRows('Users', $userData);
$ids = $result->ids; // e.g. [64, 65, 66]
$affected = $result->affected; // 3
$queries = $result->queryCount; // 1

$result = $db->insertRows('Users', $userData, 2);
$ids = $result->ids; // e.g. [64, 66, 68]

// update the user with user_id 4
$newData = ['fname' => 'Raymond', 'lname' => 'Boyce'];
$db->updateRows('Users', $newData, ['user_id' => 4]);

// delete users with IDs 1, 2, and 3
$userTable->deleteFrom('Users', ['user_id' => [1, 2, 3]]);

$db->insertRow('Users', [
    'fname' => 'Tony',
    'lname' => 'Hoare',
    'uuid' => $db->makeBinaryParam(Uuid::uuid4()->getBytes()),
]);