PHP code example of jpi / query

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

jpi / query example snippets


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


// 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 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;


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


// 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;