Browser docs

Db - Insert Update Delete

PowerLite PDO: Learn how to manipulate your database records using insert, update, and delete operations.

On this page

This page provides a practical guide on how to execute SQL queries using the Db class in the PowerLitePdo library. We will cover the following methods:

Loading the Db class within the DI container

To use the Db class, you first need to load it within the Dependency Injection (DI) container. Here's how you can do it:

use Migliori\PowerLitePdo\Db;

$container = require_once __DIR__ . '/../src/bootstrap.php';
$db = $container->get(Db::class);

Db::insert()

The Db::insert method inserts a new record into a specified table. The $values argument is an associative array where the keys represent the column names and the values represent the corresponding values to be inserted. The $debug argument controls whether the insert operation is executed and how debugging information is handled. It returns the number of affected rows or false on failure.

public function insert(
    string $table,
    array $values,
    bool|string $debug = false
) : bool|int

Arguments Summary

ArgumentTypeDescriptionExample
$tablestringThe name of the table into which a new record will be inserted.
'customers'
$valuesarray<string, mixed>An associative array containing the fields and values to be inserted.
['name' => 'Cathy', 'city' => 'Cardiff']
$debugbool|stringThe Debug modefalse, true or 'silent'

Examples

INSERT Db::insert()

$table = 'customers';
$values = ['id' => null, 'name' => 'John Doe', 'city' => 'New York'];

// Send the query and retrieve the number of affected rows
$result = $db->insert($table, $values);

// Get the last insert id
$lastInsertId = $db->getLastInsertId();

In this example, a new customer named 'John Doe' from 'New York' is inserted into the 'customers' table. The insert operation is executed and no debugging information is displayed.

INSERT - debug mode enabled Db::insert()

$table = 'orders';
$values = ['id' => null, 'customer_id' => 1, 'product_id' => 101, 'quantity' => 3];

// Simulate the INSERT query and retrieve the number of affected rows
$result = $db->insert($table, $values, true);

// Get the last insert id
$lastInsertId = $db->getLastInsertId();

In this example, a new order is inserted into the 'orders' table. The insert operation is not executed, but the query is displayed for debugging purposes.

Db::update()

This method is used to update records in a database table. It returns the number of affected rows or false on failure.

Method Signature

public function update(string $table,
    array $data,
    $where = null,
    bool|string $debug = false
): bool|int

Arguments Summary

ArgumentTypeDescriptionExample
$tablestringThe name of the table to update.
users
orders
$dataarrayAn associative array where the key is the column name and the value is the new value.
['name' => 'newuser', 'email' => 'newuser@example.com']
$where?arrayAn array of SQL WHERE conditions
['status' => 'active']
['users.id' => 'profiles.user_id']
['users.id >' => 10]
['users.id >' => 10, 'users.username LIKE' => '%me%']
$debugbool|stringThe Debug modefalse, true or 'silent'

Examples

UPDATE Db::update()

// Update a single record
$result = $db->update('users', ['name' => 'newuser'], 'id = 1');

// Simulate an update query using the $debug parameter for debugging purposes. The update operation is not actually executed.
$result = $db->update('orders', ['name' => 'newuser'], 'id = 1', true);

delete()

This method is used to delete records from a database table. It returns the number of affected rows or false on failure.

Method Signature

public function delete(
    string $table,
    array<int|string, mixed>|string $where,
    bool|string $debug = false
) : bool|int

Arguments Summary

ArgumentTypeDescriptionExamples
$tablestringThe name of the table to delete records from.
users
orders
$where?arrayAn array of SQL WHERE conditions
['status' => 'active']
['users.id' => 'profiles.user_id']
['users.id >' => 10]
['users.id >' => 10, 'users.username LIKE' => '%me%']
$debugbool|stringThe Debug modefalse, true or 'silent'

Examples

DELETE Db::delete()

// Delete a user with id 1
$db->delete('users', ['id' => 1]);

// Simulate a deletion with the $debug parameter for debugging purposes. The delete operation is not actually executed.
$db->delete('orders', 'id = 100', true);