PHP code example of jpi / query

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

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

    

jpi / query example snippets

$queryBuilder = new \JPI\Database\Query\Builder($database, $table);

table(string $table, string|null $alias): static

column(string $column, string|null $alias): static

join(): static

// Join with a single expression, but can add more to the 2nd parameter
$queryBuilder->join("orders", "users.id = orders.user_id");

// Nicer syntax adding multiple expressions
$queryBuilder->join(
    $queryBuilder->newJoinClause("orders")
        ->on("users.id = orders.user_id")
        ->on("orders.status = 'completed'")
);

$queryBuilder->where("status = 'active'");
$queryBuilder->where("created_at > NOW()");

$queryBuilder->where("status", "=", "active");
$queryBuilder->where("age", ">", 18);
$queryBuilder->where("name", "LIKE", "%john%");

// If you need to control the parameter name yourself (for example, to reuse it across multiple
// conditions), prefix the placeholder with `:` and then bind it explicitly using `param()`:
$queryBuilder->where("status", "=", ":status_value");
$queryBuilder->param("status_value", "active");

$queryBuilder->where("status", "IN", ["active", "pending"]);
$queryBuilder->where("id", "NOT IN", [1, 2, 3]);

// age BETWEEN 18 AND 65
$queryBuilder->where("age", "BETWEEN", [18, 65]);

$queryBuilder->where("deleted_at", "IS NULL");
$queryBuilder->where("email", "IS NOT NULL");

// id IN (SELECT customer_id FROM orders WHERE status = 'completed')
$subQuery = new \JPI\Database\Query\Builder($database, "orders");
$subQuery
    ->column("customer_id")
    ->where("status", "=", "completed");
$queryBuilder->where("id", "IN", $subQuery);

orderBy(string $column, bool $ascDirection = true): static

limit(int $limit, int|null $page): static

page(int $page): static

// (status = "active" AND age > 18)
$andCondition = $queryBuilder->newAndCondition()
    ->where("status", "=", "active")
    ->where("age", ">", 18);

// (status = "active" OR role = "admin")
$orCondition = $queryBuilder->newOrCondition()
    ->where("status", "=", "active")
    ->where("role", "=", "admin");

// status = 'active' AND (role = 'admin' OR type = 'premium')
$queryBuilder
    ->where("status", "=", "active")
    ->where(
        $queryBuilder->newOrCondition()
            ->where("role", "=", "admin")
            ->where("type", "=", "premium")
    );

// ((status = 'active' AND age > 18) OR type = 'premium')
$queryBuilder
    ->where(
        $queryBuilder->newOrCondition()
            ->where(
                $queryBuilder->newAndCondition()
                    ->where("status", "=", "active")
                    ->where("age", ">", 18)
            )
            ->where("type", "=", "premium")
    );

// SELECT * FROM users;
$collection = $queryBuilder->select();
/**
$collection = [
    [
        "id" => 1,
        "first_name" => "Jahidul",
        "last_name" => "Islam",
        "email" => "[email protected]",
        "password" => "password123",
        ...
    ],
    [
        "id" => 2,
        "first_name" => "Test",
        "last_name" => "Example",
        "email" => "[email protected]",
        "password" => "password123",
        ...
    ],
    ...
];
*/

// SELECT first_name, last_name FROM users;
$collection = $queryBuilder
    ->column("first_name")
    ->column("last_name")
    ->select();
/**
$collection = [
    [
        "first_name" => "Jahidul",
        "last_name" => "Islam",
    ],
    [
        "first_name" => "Test",
        "last_name" => "Example",
    ],
    ...
];
*/

// SELECT * FROM users WHERE status = "active";
$collection = $queryBuilder
    ->where("status", "=", "active")
    ->select();
/**
$collection = [
    [
        "id" => 1,
        "first_name" => "Jahidul",
        "last_name" => "Islam",
        "email" => "[email protected]",
        "password" => "password123",
        "status" => "active",
        ...
    ],
    [
        "id" => 3,
        "first_name" => "Test",
        "last_name" => "Example",
        "email" => "[email protected]",
        "password" => "password123",
        "status" => "active",
        ...
    ],
    ...
];
*/

// SELECT * FROM users WHERE status = "active" ORDER BY last_name ASC;
$collection = $queryBuilder
    ->where("status", "=", "active")
    ->orderBy("last_name")
    ->select();
/**
$collection = [
    [
        "id" => 3,
        "first_name" => "Test",
        "last_name" => "Example",
        "email" => "[email protected]",
        "password" => "password123",
        "status" => "active",
        ...
    ],
    [
        "id" => 1,
        "first_name" => "Jahidul",
        "last_name" => "Islam",
        "email" => "[email protected]",
        "password" => "password123",
        "status" => "active",
        ...
    ],
    ...
];
*/

// SELECT * FROM users WHERE status = "active" ORDER BY first_name ASC LIMIT 10 OFFSET 20;
$collection = $queryBuilder
    ->where("status", "=", "active")
    ->orderBy("first_name")
    ->limit(10, 3)
    ->select();
/**
$collection = [
    [
        "id" => 31,
        "first_name" => "Jahidul",
        "last_name" => "Islam",
        "email" => "[email protected]",
        "password" => "password123",
        "status" => "active",
        ...
    ],
    [
        "id" => 30,
        "first_name" => "Test",
        "last_name" => "Example",
        "email" => "[email protected]",
        "password" => "password123",
        "status" => "active",
        ...
    ],
    ...
];
*/

// SELECT * FROM users WHERE first_name LIKE "%jahidul%" LIMIT 1;
$row = $queryBuilder
    ->where("first_name", "LIKE", "%jahidul%")
    ->limit(1)
    ->select();
/**
$row = [
    "id" => 1,
    "first_name" => "Jahidul",
    "last_name" => "Islam",
    "email" => "[email protected]",
    "password" => "password",
    ...
];
*/

/**
SELECT * FROM users
INNER JOIN user_logins ON user_id = login_user_user_id;
*/
$queryBuilder->join("user_logins", "user_id = login_user_user_id");
$collection = $queryBuilder->select();
/**
$collection = [
    [
        "id" => 1,
        "first_name" => "Jahidul",
        "login_user_id" => 1,
        "login_user_user_id" => 1,
        "login_user_date" => "2025-10-29 10:00:00",
        ...
    ],
    [
        "id" => 1,
        "first_name" => "Jahidul",
        "login_user_id" => 2,
        "login_user_user_id" => 1,
        "login_user_date" => "2025-11-01 12:00:00",
        ...
    ],
];

/**
SELECT * FROM users
INNER JOIN user_logins ON user_id = login_user_user_id AND login_user_date > '2025-11-01';
 */
$queryBuilder->join(
    $queryBuilder->newJoinClause("user_logins")
        ->on("user_id = login_user_user_id")
        ->on("login_user_date > '2025-11-01'")
);
$queryBuilder->select();
/**
$collection = [
    [
        "id" => 1,
        "first_name" => "Jahidul",
        "login_user_id" => 2,
        "login_user_user_id" => 1,
        "login_user_date" => "2025-11-01 12:00:00",
        ...
    ],
];

// SELECT COUNT(*) as count FROM users;
$count = $queryBuilder->count();
// $count = 10;

// SELECT COUNT(*) as count FROM users WHERE status = "active";
$count = $queryBuilder
    ->where("status", "=", "active")
    ->count();
// $count = 5;

// SELECT COUNT(email) as count FROM users;
// Using column parameter to count non-NULL values in the email column
$count = $queryBuilder->count("email");
// $count = 10;

// SELECT COUNT(DISTINCT status) as count FROM users;
// Can use expressions in the column parameter
$count = $queryBuilder->count("DISTINCT status");
// $count = 2;

// Single row insert
// INSERT INTO users (first_name, last_name, email, password) VALUES ("Jahidul", "Islam", "[email protected]", "password");
$id = $queryBuilder->insert([
    "first_name" => "Jahidul",
    "last_name" => "Islam",
    "email" => "[email protected]",
    "password" => "password",
]);
// $id = 3;

// Multi-row insert
// INSERT INTO users (first_name, last_name, email, password) VALUES ("Jahidul", "Islam", "[email protected]", "password"), ("Test", "User", "[email protected]", "password123");
$rowCount = $queryBuilder->insert([
    [
        "first_name" => "Jahidul",
        "last_name" => "Islam",
        "email" => "[email protected]",
        "password" => "password",
    ],
    [
        "first_name" => "Test",
        "last_name" => "User",
        "email" => "[email protected]",
        "password" => "password123",
    ],
]);
// $rowCount = 2 (number of rows inserted)

// UPDATE users SET status = "inactive";
$numberOrRowsUpdated = $queryBuilder->update([
    "status" => "inactive",
]);
// $numberOrRowsUpdated = 10;

// UPDATE users SET first_name = "Pabel" WHERE id = 1;
$numberOrRowsUpdated = $queryBuilder
    ->where("id", "=", 1)
    ->update([
        "first_name" => "Pabel",
    ]);
// $numberOrRowsUpdated = 1;

// DELETE FROM users;
$numberOrRowsDeleted = $queryBuilder->delete();
// $numberOrRowsDeleted = 10;

// DELETE FROM users WHERE id = 1;
$numberOrRowsDeleted = $queryBuilder
    ->where("id", "=", 1)
    ->delete();
// $numberOrRowsDeleted = 1;