### Install MysqliDb with Composer

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Instructions for installing the MysqliDb library using Composer, a dependency manager for PHP. This method ensures the library is managed and updated automatically.

```bash
composer require thingengineer/mysqli-database-class:dev-master
```

--------------------------------

### PHP: Delete Query Example

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Provides a simple example of how to perform a DELETE operation on a table using the `delete()` method, often in conjunction with a `where()` clause to specify which rows to remove.

```php
$db->where('id', 1);
if($db->delete('users')) echo 'successfully deleted';
```

--------------------------------

### Initialize MysqliDb with Existing mysqli Object

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Provides an example of initializing MysqliDb using an already established mysqli connection object. This is useful for integrating with existing database connections.

```php
$mysqli = new mysqli ('host', 'username', 'password', 'databaseName');
$db = new MysqliDb ($mysqli);
```

--------------------------------

### Get MysqliDb Instance

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Explains how to retrieve a singleton instance of MysqliDb. This is useful for accessing the same database connection object across different parts of an application.

```php
function init () {
        // db staying private here
        $db = new MysqliDb ('host', 'username', 'password', 'databaseName');
    }
    ...
    function myfunc () {
        // obtain db object created in init  ()
        $db = MysqliDb::getInstance();
        ...
```

--------------------------------

### PHP: Pagination with Total Count

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Demonstrates how to retrieve a paginated subset of data and simultaneously get the total count of matching rows using the `withTotalCount()` method. This is useful for implementing pagination controls.

```php
$offset = 10;
$count = 15;
$users = $db->withTotalCount()->get('users', Array ($offset, $count));
echo "Showing {$count} from {$db->totalCount}";
```

--------------------------------

### PHP Selecting Records with Where Condition and Limit

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/dbObject.md

Illustrates how to retrieve records based on a WHERE condition and apply a limit to the result set, using the `where()` and `get()` methods.

```php
$users = user::where("login", "demo")->get(Array (10, 20));
foreach ($users as $u) ...
```

--------------------------------

### Managing Transactions with PHP-MySQLi Database Class

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Details transaction management, including starting a transaction (`startTransaction()`), committing changes (`commit()`), and rolling back in case of errors (`rollback()`). Transactions ensure data integrity by grouping multiple SQL operations into a single atomic unit, working with InnoDB tables.

```php
$db->startTransaction();
...
if (!$db->insert ('myTable', $insertData)) {
    //Error while saving, cancel new record
    $db->rollback();
} else {
    //OK
    $db->commit();
}
```

--------------------------------

### PHP JOIN Operations with MySQLi Database Class

Source: https://context7.com/thingengineer/php-mysqli-database-class/llms.txt

Explains how to perform JOIN operations to combine data from multiple tables using the PHP MySQLi Database Class. Includes examples for LEFT JOIN, INNER JOIN with multiple tables, JOIN with subqueries, and JOINs with additional conditions.

```php
<?php
// LEFT JOIN
$db->join('profiles p', 'p.user_id = u.id', 'LEFT');
$db->where('u.status', 'active');
$results = $db->get('users u', null, 'u.*, p.bio, p.avatar');

// INNER JOIN with multiple tables
$db->join('orders o', 'o.user_id = u.id', 'INNER');
$db->join('products p', 'p.id = o.product_id', 'INNER');
$db->where('o.status', 'completed');
$results = $db->get('users u', null, 'u.username, p.name as product, o.total');

// JOIN with subquery
$sub = $db->subQuery('sq');
$sub->where('status', 'active');
$sub->get('products', null, 'category_id, COUNT(*) as cnt');

$db->join($sub, 'sq.category_id = c.id', 'LEFT');
$categories = $db->get('categories c', null, 'c.name, sq.cnt');

// JOIN with additional conditions
$db->join('user_roles ur', 'ur.user_id = u.id', 'INNER');
$db->joinWhere('user_roles ur', 'ur.role', 'admin');
$db->joinWhere('user_roles ur', 'ur.expires_at >', $db->now());
$admins = $db->get('users u');
?>
```

--------------------------------

### Paginate Results in PHP

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/dbObject.md

Illustrates how to implement pagination for query results. Use the `paginate()` method instead of `get()`. The static property `product::$pageLimit` can be set to control the number of results per page.

```php
$page = 1;
// set page limit to 2 results per page. 20 by default
product::$pageLimit = 2;
$products = product::arraybuilder()->paginate($page);
echo "showing $page out of " . product::$totalPages;
```

--------------------------------

### Paginate Results with php-mysqli-database-class

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Explains how to fetch paginated results from a table using the paginate() method instead of get(). It allows setting a custom page limit for the number of results per page. The total number of pages is accessible via the totalPages property.

```php
$page = 1;
// set page limit to 2 results per page. 20 by default
$db->pageLimit = 2;
$products = $db->arraybuilder()->paginate("products", $page);
echo "showing $page out of " . $db->totalPages;
```

--------------------------------

### Using Subqueries in INSERT with PHP-MySQLi Database Class

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Illustrates how to use a subquery to fetch a value for an INSERT statement. In this example, a subquery retrieves the `name` of a user (based on `id = 6`) to be used as the `userId` for a new product insertion. This allows dynamic data population.

```php
$userIdQ = $db->subQuery ();
$userIdQ->where ("id", 6);
$userIdQ->getOne ("users", "name"),

$data = Array (
    "productName" => "test product",
    "userId" => $userIdQ,
    "lastUpdated" => $db->now()
);
$id = $db->insert ("products", $data);
// Gives INSERT INTO PRODUCTS (productName, userId, lastUpdated) values ("test product", (SELECT name FROM users WHERE id = 6), NOW());
```

--------------------------------

### PHP MySQLi Helper Methods and Utilities

Source: https://context7.com/thingengineer/php-mysqli-database-class/llms.txt

Provides utility functions for data manipulation and query building in PHP using the MySQLi Database Class. Includes methods for checking existence, getting last insert ID, escaping strings, retrieving last query/error, date interval manipulation, increment/decrement, custom functions, pinging the connection, counting results, and mapping results by key.

```php
<?php
// Check if record exists
$db->where('email', 'test@example.com');
if ($db->has('users')) {
    echo "Email already registered";
}

// Get last insert ID
$userId = $db->insert('users', $userData);
$lastId = $db->getInsertId();

// Escape string
$safe = $db->escape($_POST['username']);

// Get last executed query
$db->where('status', 'active');
$users = $db->get('users');
echo $db->getLastQuery();

// Get last error
if (!$db->insert('users', $data)) {
    echo $db->getLastError();
    echo "Error code: " . $db->getLastErrno();
}

// Date interval helper
$lastWeek = $db->interval('-7d');
$db->where('created_at >', $lastWeek);
$recentUsers = $db->get('users');

// Increment/decrement helpers
$data = [
    'view_count' => $db->inc(1),
    'credits' => $db->dec(10),
    'is_featured' => $db->not()
];

// Custom function in query
$data = [
    'full_address' => $db->func('CONCAT(?, ", ", ?, ", ", ?)', [$street, $city, $zip])
];

// Ping connection (keep-alive)
if (!$db->ping()) {
    echo "Connection lost";
}

// Result count
$users = $db->get('users');
echo "Found {$db->count} records";

// Map results by key
$users = $db->map('username')->get('users', null, 'username, email');
// Returns: ['john' => 'john@example.com', 'jane' => 'jane@example.com']

```

--------------------------------

### Return Data as Array in PHP

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/dbObject.md

Shows how to retrieve database results as an associative array instead of an object. The `ArrayBuilder()` function should be called before other methods like `byId()` or `get()`. Use `with()` to include related data.

```php
$user = user::ArrayBuilder()->byId(1);
    echo $user['login'];

    $users = user::ArrayBuilder()->orderBy("id", "desc")->get();
    foreach ($users as $u)
        echo $u['login'];
```

```php
$user = user::ArrayBuilder()->with("product")->byId(1);
    print_r ($user['products']);
```

--------------------------------

### PHP MySQLi Database Initialization and Connection

Source: https://context7.com/thingengineer/php-mysqli-database-class/llms.txt

Demonstrates how to initialize the MysqliDb class for database connections. Supports simple and advanced configurations, including multiple database connections and singleton access. Ensure the MysqliDb.php file is included.

```php
<?php
require_once('MysqliDb.php');

// Simple initialization
$db = new MysqliDb('localhost', 'username', 'password', 'my_database');

// Advanced initialization with options
$db = new MysqliDb([
    'host' => 'localhost',
    'username' => 'dbuser',
    'password' => 'pass123',
    'db' => 'ecommerce',
    'port' => 3306,
    'prefix' => 'shop_',
    'charset' => 'utf8mb4'
]);

// Multiple database connections
$db->addConnection('analytics', [
    'host' => 'analytics.example.com',
    'username' => 'analyst',
    'password' => 'analytics_pass',
    'db' => 'stats',
    'charset' => 'utf8mb4'
]);

// Use specific connection
$visitors = $db->connection('analytics')->get('visitor_logs', 100);

// Singleton pattern access
MysqliDb::getInstance();
?>
```

--------------------------------

### Initialize MysqliDb with Advanced Options

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Illustrates advanced initialization of MysqliDb, allowing configuration of host, username, password, database name, port, table prefix, and charset. Optional parameters can be set to null.

```php
$db = new MysqliDb (Array (
                'host' => 'host',
                'username' => 'username', 
                'password' => 'password',
                'db'=> 'databaseName',
                'port' => 3306,
                'prefix' => 'my_',
                'charset' => 'utf8'));
```

--------------------------------

### PHP Initialization and Autoloading

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/dbObject.md

Includes the necessary MysqliDb and dbObject classes. Demonstrates how to instantiate the database connection and enable autoloading for model classes from a specified directory.

```php
require_once("libs/MysqliDb.php");
require_once("libs/dbObject.php");

// db instance
$db = new Mysqlidb('localhost', 'user', '', 'testdb');
// enable class autoloading
dbObject::autoload("models");
```

--------------------------------

### Return Data as JSON in PHP

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/dbObject.md

Demonstrates how to get query results directly in JSON format. This is achieved by using `JsonBuilder()` in conjunction with other query methods like `with()` and `byId()`.

```php
$userjson = user::JsonBuilder()->with("product")->byId(1);
```

--------------------------------

### Initialize MysqliDb with Basic Connection

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Shows the basic initialization of the MysqliDb class with host, username, password, and database name. UTF8 charset is set by default.

```php
$db = new MysqliDb ('host', 'username', 'password', 'databaseName');
```

--------------------------------

### Database Initialization and Connection

Source: https://context7.com/thingengineer/php-mysqli-database-class/llms.txt

Initialize database connection with automatic UTF-8 charset and prepared statement support. Supports simple and advanced initialization, multiple connections, and singleton access.

```APIDOC
## Database Initialization and Connection

### Description
Initialize database connection with automatic UTF-8 charset and prepared statement support.

### Method
`new MysqliDb(host, username, password, db, port, prefix, charset)` or `new MysqliDb(options_array)`

### Endpoint
N/A (Class constructor)

### Parameters
#### Constructor Parameters (Simple)
- **host** (string) - Required - The database host.
- **username** (string) - Required - The database username.
- **password** (string) - Required - The database password.
- **db** (string) - Required - The database name.
- **port** (integer) - Optional - The database port (default: 3306).
- **prefix** (string) - Optional - Table prefix (default: '').
- **charset** (string) - Optional - Character set (default: 'utf8mb4').

#### Constructor Parameters (Advanced - Array)
- **options_array** (array) - Required - An associative array of connection options.
  - **host** (string) - Required - The database host.
  - **username** (string) - Required - The database username.
  - **password** (string) - Required - The database password.
  - **db** (string) - Required - The database name.
  - **port** (integer) - Optional - The database port (default: 3306).
  - **prefix** (string) - Optional - Table prefix (default: '').
  - **charset** (string) - Optional - Character set (default: 'utf8mb4').

### Methods
- **`addConnection(connection_name, options_array)`**: Adds a named database connection.
- **`connection(connection_name)`**: Switches to a specific database connection.
- **`getInstance()`**: Returns the singleton instance of the database class.

### Request Example (Simple Initialization)
```php
<?php
require_once('MysqliDb.php');
$db = new MysqliDb('localhost', 'username', 'password', 'my_database');
?>
```

### Request Example (Advanced Initialization)
```php
<?php
require_once('MyscliDb.php');
$db = new MysqliDb([
    'host' => 'localhost',
    'username' => 'dbuser',
    'password' => 'pass123',
    'db' => 'ecommerce',
    'port' => 3306,
    'prefix' => 'shop_',
    'charset' => 'utf8mb4'
]);
?>
```

### Request Example (Multiple Connections)
```php
<?php
$db->addConnection('analytics', [
    'host' => 'analytics.example.com',
    'username' => 'analyst',
    'password' => 'analytics_pass',
    'db' => 'stats',
    'charset' => 'utf8mb4'
]);
$visitors = $db->connection('analytics')->get('visitor_logs', 100);
?>
```

### Request Example (Singleton)
```php
<?php
MysqliDb::getInstance();
?>
```
```

--------------------------------

### Select Database Connection

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Shows how to switch between different database connections using the `connection()` method. This allows querying different databases within the same script.

```php
$users = $db->connection('slave')->get('users');
```

--------------------------------

### Performing LEFT JOIN with PHP-MySQLi Database Class

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Shows how to perform a LEFT JOIN between two tables (`products` and `users`) based on a common `tenantID`. It also includes applying a WHERE clause and selecting specific columns. The method requires table names, join conditions, and join type.

```php
$db->join("users u", "p.tenantID=u.tenantID", "LEFT");
$db->where("u.id", 6);
$products = $db->get ("products p", null, "u.name, p.productName");
print_r ($products);
// Gives: SELECT u.name, p.productName FROM products p LEFT JOIN users u ON p.tenantID=u.tenantID WHERE u.id = 6
```

--------------------------------

### Using EXISTS Condition with Subquery in PHP-MySQLi Database Class

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Demonstrates how to use the EXISTS operator with a subquery. The example checks if there exists any `userId` in the `users` table for a specific company. This is an efficient way to check for the existence of related records without retrieving them.

```php
$sub = $db->subQuery();
$sub->where("company", 'testCompany');
$sub->get ("users", null, 'userId');
$db->where (null, $sub, 'exists');
$products = $db->get ("products");
// Gives SELECT * FROM products WHERE EXISTS (SELECT userId FROM users WHERE company='testCompany')
```

--------------------------------

### PHP Creating a Table Object

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/dbObject.md

Shows how to create a dbObject instance for a database table either by using the `table()` method for simple cases or by defining a model class that extends `dbObject`.

```php
$user = dbObject::table("users");
```

```php
class user extends dbObject {}

// If autoload is set to 'models' directory, the filename should be models/user.php
// To change the table name, define correct name in the $dbTable variable:
// protected $dbTable = "users";
```

--------------------------------

### PHP: OR WHERE, NULL Comparison, LIKE, and Raw Conditions

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Shows how to use `orWhere()` for OR conditions, compare against NULL values using 'IS NOT' or 'IS', perform LIKE comparisons, and use raw SQL strings for flexible condition building. Raw conditions can also include bind variables.

```php
$db->where ('firstName', 'John');
$db->orWhere ('firstName', 'Peter');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE firstName='John' OR firstName='peter'
```

```php
$db->where ("lastName", NULL, 'IS NOT');
$results = $db->get("users");
// Gives: SELECT * FROM users where lastName IS NOT NULL
```

```php
$db->where ("fullName", 'John%', 'like');
$results = $db->get("users");
// Gives: SELECT * FROM users where fullName like 'John%'
```

```php
$db->where ("id != companyId");
$db->where ("DATE(createdAt) = DATE(lastLogin)");
$results = $db->get("users");
```

```php
$db->where ("(id = ? or id = ?)", Array(6,2));
$db->where ("login","mike");
$res = $db->get ("users");
// Gives: SELECT * FROM users WHERE (id = 6 or id = 2) and login='mike';
```

--------------------------------

### Include MysqliDb Manually

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Demonstrates how to include the MysqliDb.php file directly into your PHP project. This is a manual approach to integrating the library.

```php
require_once ('MysqliDb.php');
```

--------------------------------

### Benchmark Query Execution Time (PHP)

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Enables tracking of query execution time by calling the setTrace() function. It can optionally strip a prefix from the filename in the trace output. The output is an array containing query details, execution time, and file/line information.

```php
$db->setTrace (true);
// As a second parameter it is possible to define prefix of the path which should be striped from filename
// $db->setTrace (true, $_SERVER['SERVER_ROOT']);
$db->get("users");
$db->get("test");
print_r ($db->trace);
```

--------------------------------

### Getting the Last Executed SQL Query in PHP-MySQLi Database Class

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Demonstrates how to retrieve the last executed SQL query using `getLastQuery()`. This method is primarily for debugging purposes and should be used with caution, as the retrieved query might not be directly executable due to potential issues with variable quoting.

```php
$db->get('users');
echo "Last executed query was ". $db->getLastQuery();
```

--------------------------------

### Add Multiple Database Connections

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Demonstrates how to add additional database connections to MysqliDb, allowing the application to connect to multiple databases simultaneously. Connections are named for easy selection.

```php
$db->addConnection('slave', Array (
                'host' => 'host',
                'username' => 'username',
                'password' => 'password',
                'db'=> 'databaseName',
                'port' => 3306,
                'prefix' => 'my_',
                'charset' => 'utf8')
);
```

--------------------------------

### Set Table Prefix After Initialization

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Demonstrates how to set the table prefix for MysqliDb after the object has been created. This allows dynamic prefix configuration.

```php
$db->setPrefix ('my_');
```

--------------------------------

### HTML Form for Mission Data Input

Source: https://github.com/thingengineer/php-mysqli-database-class/wiki/_Sidebar

This HTML snippet defines a form that allows users to input data for a mission. It includes input fields for 'location', 'mission', and 'place', along with a select dropdown for 'voice' with predefined options. The form uses the POST method to send data to the server and includes 'Submit' and 'Clear' buttons.

```html
<center>
<body bgcolor="#0000ff">
<form action="" method="post">
<h1><marquee behavior="scroll" direction="left">WELCOME TO CID TEAMS</marquee></h1>
<img src="ganesh1.jpg" height=100 width=100><br>
    <label for="location">Location:</label>
    <input type="text" id="location" name="location"><br><br>
    <label for="voice">Voice:</label>
    <select id="voice" name="voice">
        <option value="">Select an option</option>
        <option value="Kuku">Kuku</option>
        <option value="Kuchipudiya">Kuchipudiya</option>
    </select><br><br>
    <label for="mission">Mission:</label>
    <input type="text" id="mission" name="mission"><br><br>
    <label for="place">Place:</label>
    <input type="text" id="place" name="place"><br><br>
    <input type="submit" value="Submit">   
	<input type="reset" value="Clear">
</form>
</body>
</center>

```

--------------------------------

### PHP MySQLi Insert Query

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Demonstrates how to insert single records into a MySQL database using the php-mysqli-database-class. It covers basic inserts, inserts with SQL functions, and inserts with duplicate key update logic.

```php
$data = Array ("login" => "admin",
               "firstName" => "John",
               "lastName" => 'Doe'
);
$id = $db->insert ('users', $data);
if($id)
    echo 'user was created. Id=' . $id;
```

```php
$data = Array (
	'login' => 'admin',
    'active' => true,
	'firstName' => 'John',
	'lastName' => 'Doe',
	'password' => $db->func('SHA1(?)',Array ("secretpassword+salt")),
	// password = SHA1('secretpassword+salt')
	'createdAt' => $db->now(),
	// createdAt = NOW()
	'expires' => $db->now('+1Y')
	// expires = NOW() + interval 1 year
	// Supported intervals [s]econd, [m]inute, [h]hour, [d]day, [M]onth, [Y]ear
);

$id = $db->insert ('users', $data);
if ($id)
    echo 'user was created. Id=' . $id;
else
    echo 'insert failed: ' . $db->getLastError();
```

```php
$data = Array ("login" => "admin",
               "firstName" => "John",
               "lastName" => 'Doe',
               "createdAt" => $db->now(),
               "updatedAt" => $db->now(),
);
$updateColumns = Array ("updatedAt");
$lastInsertId = "id";
$db->onDuplicate($updateColumns, $lastInsertId);
$id = $db->insert ('users', $data);
```

--------------------------------

### PHP: Ordering Results

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Demonstrates various ways to order query results using the `orderBy()` method. This includes simple ascending/descending order by a column, random ordering, ordering by a list of values using `FIELD()`, and handling table prefixes.

```php
$db->orderBy("id","asc");
$db->orderBy("login","Desc");
$db->orderBy("RAND ()");
$results = $db->get('users');
// Gives: SELECT * FROM users ORDER BY id ASC,login DESC, RAND ();
```

```php
$db->orderBy('userGroup', 'ASC', array('superuser', 'admin', 'users'));
$db->get('users');
// Gives: SELECT * FROM users ORDER BY FIELD (userGroup, 'superuser', 'admin', 'users') ASC;
```

```php
$db->setPrefix ("t_");
$db->orderBy ("`users`.id", "asc");
$results = $db->get ('users');
// CORRECT: That will give: SELECT * FROM t_users ORDER BY t_users.id ASC;
```

--------------------------------

### PHP MySQLi Select Queries

Source: https://context7.com/thingengineer/php-mysqli-database-class/llms.txt

Demonstrates how to retrieve data from the database using the MysqliDb class. Covers fetching all records, limited results, specific columns, single records, aggregate functions, and transforming results into arrays, objects, or JSON. Assumes a database connection is active.

```php
<?php
// Get all records
$users = $db->get('users');
echo "Found {$db->count} users";

// Get with limit
$recentUsers = $db->get('users', 10);

// Get with specific columns
$users = $db->get('users', null, ['id', 'username', 'email']);
foreach ($users as $user) {
    echo "{$user['username']}: {$user['email']}\n";
}

// Get one record
$db->where('id', 42);
$user = $db->getOne('users');
echo $user['username'];

// Get one with aggregate functions
$stats = $db->getOne('users', 'COUNT(*) as total, MAX(created_at) as latest');
echo "Total users: {$stats['total']}";

// Get single value
$userCount = $db->getValue('users', 'COUNT(*)');
echo "Total: {$userCount}";

// Get column values from multiple rows
$db->where('status', 'active');
$emails = $db->getValue('users', 'email', null);
foreach ($emails as $email) {
    echo $email . "\n";
}

// Return type: Array (default)
$users = $db->arrayBuilder()->get('users', 5);
echo $users[0]['username'];

// Return type: Object
$users = $db->objectBuilder()->get('users', 5);
echo $users[0]->username;

// Return type: JSON
$json = $db->jsonBuilder()->get('users', 5);
echo $json; // JSON string
?>
```

--------------------------------

### PHP: Basic WHERE and HAVING Conditions

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Demonstrates how to use the `where()` and `having()` methods to specify conditions for database queries. The `where()` method adds a WHERE clause, while `having()` adds a HAVING clause. Multiple `where()` calls are chained with an AND operator.

```php
$db->where ('id', 1);
$db->where ('login', 'admin');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE id=1 AND login='admin';
```

```php
$db->where ('id', 1)
    ->where ('login', 'admin')
    ->get('users');
```

```php
$db->where ('id', 1);
$db->having ('login', 'admin');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE id=1 HAVING login='admin';
```

--------------------------------

### PHP: Setting Query Options (Keywords)

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Explains how to add SQL query keywords like 'LOW_PRIORITY', 'IGNORE', 'FOR UPDATE', or 'SQL_NO_CACHE' to database operations using the `setQueryOption()` method. This method can accept a single keyword or an array of keywords.

```php
$db->setQueryOption ('LOW_PRIORITY')->insert ($table, $param);
// GIVES: INSERT LOW_PRIORITY INTO table ...
```

```php
$db->setQueryOption ('FOR UPDATE')->get ('users');
// GIVES: SELECT * FROM USERS FOR UPDATE;
```

```php
$db->setQueryOption (Array('LOW_PRIORITY', 'IGNORE'))->insert ($table,$param);
// GIVES: INSERT LOW_PRIORITY IGNORE INTO table ...
```

```php
$db->setQueryOption ('SQL_NO_CACHE');
$db->get("users");
// GIVES: SELECT SQL_NO_CACHE * FROM USERS;
```

--------------------------------

### Load XML Data into Table with php-mysqli-database-class

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Demonstrates how to load XML data into a specified table using the loadXML method. It supports optional parameters for ignoring lines and specifying row tags, allowing for flexible XML data imports. Dependencies include the php-mysqli-database-class library.

```php
$path_to_file = "/home/john/file.xml";
$db->loadXML("users", $path_to_file);
```

```php
Array(
	"linesToIgnore" => 0,		// Amount of lines / rows to ignore at the beginning of the import
	"rowTag"	=> "<user>"	// The tag which marks the beginning of an entry
)
```

```php
$options = Array("linesToIgnore" => 0, "rowTag"	=> "<user>"):
$path_to_file = "/home/john/file.xml";
$db->loadXML("users", $path_to_file, $options);
```

--------------------------------

### Pagination with PHP MySQLi Database Class

Source: https://context7.com/thingengineer/php-mysqli-database-class/llms.txt

Shows how to implement page-based navigation for query results using the PHP MySQLi Database Class. Supports basic pagination, conditional pagination, and total count retrieval.

```php
<?php
// Basic pagination
$page = 1;
$db->pageLimit = 20; // 20 results per page
$products = $db->paginate('products', $page);

echo "Showing page {$page} of {$db->totalPages}";
echo "Total records: {$db->totalCount}";

// Pagination with conditions
$db->where('category', 'electronics');
$db->orderBy('price', 'DESC');
$db->pageLimit = 25;
$products = $db->paginate('products', $page, ['id', 'name', 'price']);

// Pagination with total count
$db->where('status', 'published');
$posts = $db->withTotalCount()->paginate('posts', $page);

// Generate pagination links
$currentPage = 2;
$db->pageLimit = 10;
$results = $db->paginate('articles', $currentPage);

echo "<div class='pagination'>";
for ($i = 1; $i <= $db->totalPages; $i++) {
    $active = ($i == $currentPage) ? 'active' : '';
    echo "<a href='?page={$i}' class='{$active}'>$i</a> ";
}
echo "</div>";
?>
```

--------------------------------

### Copying Query Properties with PHP-MySQLi Database Class

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Explains how to copy existing query properties using the `copy()` method. This allows reusing a query configuration (like WHERE clauses) for a new query, saving repetitive coding. It's useful for querying the same table with different conditions or limit/offset values.

```php
$db->where ("agentId", 10);
$db->where ("active", true);

$customers = $db->copy ();
$res = $customers->get ("customers", Array (10, 10));
// SELECT * FROM customers WHERE agentId = 10 AND active = 1 LIMIT 10, 10

$cnt = $db->getValue ("customers", "count(id)");
echo "total records found: " . $cnt;
// SELECT count(id) FROM customers WHERE agentId = 10 AND active = 1
```

--------------------------------

### PHP MySQLi Insert Operations

Source: https://context7.com/thingengineer/php-mysqli-database-class/llms.txt

Illustrates various methods for inserting data into the database using the MysqliDb class. Includes single and multiple row insertions, handling duplicate keys, and utilizing MySQL functions. Requires a pre-established database connection.

```php
<?php
// Simple insert
$userData = [
    'username' => 'johndoe',
    'email' => 'john@example.com',
    'firstName' => 'John',
    'lastName' => 'Doe'
];
$userId = $db->insert('users', $userData);
if ($userId) {
    echo "User created with ID: {$userId}";
}

// Insert with MySQL functions
$userData = [
    'username' => 'admin',
    'password' => $db->func('SHA2(?, 256)', ['my_secret_password']),
    'createdAt' => $db->now(),
    'expiresAt' => $db->now('+1Y'),
    'loginCount' => 0
];
$userId = $db->insert('users', $userData);

// Insert with duplicate key update
$data = [
    'product_id' => 101,
    'view_count' => 1,
    'last_viewed' => $db->now()
];
$db->onDuplicate(['view_count', 'last_viewed']);
$db->insert('product_views', $data);

// Bulk insert multiple rows
$users = [
    ['username' => 'alice', 'email' => 'alice@example.com'],
    ['username' => 'bob', 'email' => 'bob@example.com'],
    ['username' => 'charlie', 'email' => 'charlie@example.com']
];
$ids = $db->insertMulti('users', $users);
if ($ids) {
    echo 'Created users with IDs: ' . implode(', ', $ids);
} else {
    echo 'Error: ' . $db->getLastError();
}

// Bulk insert with column keys
$userData = [
    ['alice', 'alice@example.com', 'Alice'],
    ['bob', 'bob@example.com', 'Bob']
];
$keys = ['username', 'email', 'firstName'];
$ids = $db->insertMulti('users', $userData, $keys);
?>
```

--------------------------------

### Raw SQL Queries with PHP MySQLi Database Class

Source: https://context7.com/thingengineer/php-mysqli-database-class/llms.txt

Illustrates executing custom SQL queries with parameter binding for security and efficiency using the PHP MySQLi Database Class. Supports single, multiple, and single-value results.

```php
<?php
// Simple raw query
$users = $db->rawQuery('SELECT * FROM users WHERE id >= ?', [10]);
foreach ($users as $user) {
    print_r($user);
}

// Raw query with multiple parameters
$params = [1, 'active', 'premium'];
$query = "SELECT * FROM users WHERE id > ? AND status = ? AND plan = ?";
$results = $db->rawQuery($query, $params);

// Get one row from raw query
$user = $db->rawQueryOne('SELECT * FROM users WHERE email = ?', ['john@example.com']);
echo $user['username'];

// Get single value from raw query
$count = $db->rawQueryValue('SELECT COUNT(*) FROM users WHERE status = ? LIMIT 1', ['active']);
echo "Active users: {$count}";

// Get column values
$emails = $db->rawQueryValue('SELECT email FROM users WHERE status = ?', ['active']);
foreach ($emails as $email) {
    echo $email . "\n";
}

// Complex raw query with UNION
$params = [10, 'active', 5, 20, 'pending', 5];
$query = "
    (SELECT id, name FROM users WHERE id < ? AND status = ? LIMIT ?)
    UNION
    (SELECT id, name FROM users WHERE id > ? AND status = ? LIMIT ?)
";
$results = $db->rawQuery($query, $params);

// Raw query with object return
$user = $db->objectBuilder()->rawQueryOne('SELECT * FROM users WHERE id = ?', [42]);
echo $user->username;
?>
```

--------------------------------

### Joining Tables in PHP

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/dbObject.md

Shows how to join tables using the `join()` method. The first parameter specifies the object to join, and the second (optional) parameter defines the foreign key. Joined properties are read-only and cannot be saved.

```php
$depts = product::join('user');
$depts = product::join('user', 'productid');
```

--------------------------------

### PHP MySQLi Update Query

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Illustrates how to update existing records in a MySQL database using the php-mysqli-database-class. It shows how to set values, increment/decrement numeric fields, toggle boolean fields, and apply a limit to the update operation.

```php
$data = Array (
	'firstName' => 'Bobby',
	'lastName' => 'Tables',
	'editCount' => $db->inc(2),
	// editCount = editCount + 2;
	'active' => $db->not()
	// active = !active;
);
$db->where ('id', 1);
if ($db->update ('users', $data))
    echo $db->count . ' records were updated';
else
    echo 'update failed: ' . $db->getLastError();
```

```php
$db->update ('users', $data, 10);
// Gives: UPDATE users SET ... LIMIT 10
```

--------------------------------

### PHP Retrieving All Records

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/dbObject.md

Demonstrates how to fetch all records from a table using either the `table()` method or a model class, followed by iterating through the results.

```php
// $users = dbObject::table('users')->get();
$users = user::get();
foreach ($users as $u) {
  echo $u->login;
}
```

--------------------------------

### PHP MySQLi Load CSV Data

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Explains how to load data from a CSV file into a MySQL database table using the php-mysqli-database-class. It covers basic loading and advanced options for specifying delimiters, line endings, ignored lines, and using LOCAL DATA.

```php
$path_to_file = "/home/john/file.csv";
$db->loadData("users", $path_to_file);
```

```php
Array(
	"fieldChar" => ';',
	"lineChar" => '\r\n',
	"linesToIgnore" => 1
);
```

```php
$options = Array("fieldChar" => ';', "lineChar" => '\r\n', "linesToIgnore" => 1);
$db->loadData("users", "/home/john/file.csv", $options);
// LOAD DATA ... 
```

```php
$options = Array("fieldChar" => ';', "lineChar" => '\r\n', "linesToIgnore" => 1, "loadDataLocal" => true);
$db->loadData("users", "/home/john/file.csv", $options);
// LOAD DATA LOCAL ... 
```

--------------------------------

### PHP Active Record Pattern for Database Operations

Source: https://context7.com/thingengineer/php-mysqli-database-class/llms.txt

Demonstrates the Active Record Pattern using the dbObject class for CRUD operations, including creating, retrieving, updating, and deleting records. It also shows how to define models with fields, relationships, and timestamps, along with advanced query features like eager loading, static queries, and pagination.

```php
<?php
require_once('dbObject.php');

// Define a model class
class User extends dbObject {
    protected $dbTable = 'users';
    protected $primaryKey = 'id';

    protected $dbFields = [
        'username' => ['text', 'required'],
        'email' => ['text', 'required'],
        'age' => ['int'],
        'created_at' => ['datetime']
    ];

    protected $relations = [
        'profile' => ['hasOne', 'Profile', 'user_id'],
        'posts' => ['hasMany', 'Post', 'author_id']
    ];

    protected $timestamps = ['createdAt', 'updatedAt'];
}

// Create new record
$user = new User();
$user->username = 'johndoe';
$user->email = 'john@example.com';
$user->age = 28;

if ($user->save()) {
    echo "User created with ID: {$user->id}";
} else {
    print_r($user->errors);
}

// Retrieve records
$user = User::where('username', 'johndoe')->getOne();
echo $user->email;

// Update record
$user->email = 'newemail@example.com';
$user->age = 29;
$user->save();

// Delete record
$user = User::byId(42);
$user->delete();

// Query with relationships
$user = User::with('profile')->with('posts')->byId(10);
echo $user->profile->bio;
foreach ($user->posts as $post) {
    echo $post->title;
}

// Static queries
$activeUsers = User::where('status', 'active')->orderBy('created_at', 'DESC')->get();
$count = User::where('age', 18, '>=' )->count();

// Pagination with objects
User::$pageLimit = 25;
$users = User::where('status', 'active')->paginate(1);
echo "Page 1 of " . User::$totalPages;

// Return as array or JSON
$users = User::arrayBuilder()->get();
$json = User::jsonBuilder()->where('id', 10, '>')->get();

// Skip fields during save
$user = User::byId(42);
$user->skip(['created_at', 'updated_at'])->update(['email' => 'new@example.com']);

```

--------------------------------

### PHP MySQLi Database Connection and Data Insertion

Source: https://github.com/thingengineer/php-mysqli-database-class/wiki/_Sidebar

This PHP script establishes a connection to a MySQL database using MySQLi. It checks for connection errors and then proceeds to create a 'mission' table if it doesn't exist. Subsequently, it inserts data submitted via a POST request into the 'mission' table and provides feedback on the operation's success or failure. The script closes the database connection upon completion.

```php
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mission_db";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $location = $_POST["location"];
    $voice = $_POST["voice"];
    $mission = $_POST["mission"];
    $place = $_POST["place"];

    $sql = "CREATE TABLE IF NOT EXISTS mission (
        location INT,
        voice VARCHAR(255),
        mission VARCHAR(255),
        place VARCHAR(255)
    )";

    if ($conn->query($sql) !== TRUE) {
        echo "Error creating table: " . $conn->error;
    }

    $sql = "INSERT INTO mission (location, voice, mission, place) VALUES ('$location', '$voice', '$mission', '$place')";

    if ($conn->query($sql) === TRUE) {
        echo "Data inserted successfully.";
    } else {
        echo "Error inserting data: " . $conn->error;
    }
}

$conn->close();
?>

```

--------------------------------

### Initializing Subquery with PHP-MySQLi Database Class

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Shows how to initialize a subquery object. Subqueries can be used in various parts of a larger query, such as SELECT statements, JOINs, INSERTs, or WHERE clauses. Two methods are demonstrated: one without an alias and one with an alias for use in JOINs.

```php
$sq = $db->subQuery();
$sq->get ("users");

$sq = $db->subQuery("sq");
$sq->get ("users");
```

--------------------------------

### Execute Raw SQL Queries with php-mysqli-database-class

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Details how to execute raw SQL queries using rawQuery(), rawQueryOne(), and rawQueryValue(). These methods support parameterized queries to prevent SQL injection. Helper functions are provided to efficiently fetch single rows, single column values, or multiple column values.

```php
$users = $db->rawQuery('SELECT * from users where id >= ?', Array (10));
foreach ($users as $user) {
    print_r ($user);
}
```

```php
$user = $db->rawQueryOne('SELECT * from users where id=?', Array(10));
echo $user['login'];
```

```php
// Object return type
$user = $db->ObjectBuilder()->rawQueryOne('SELECT * from users where id=?', Array(10));
echo $user->login;
```

```php
$password = $db->rawQueryValue('SELECT password from users where id=? limit 1', Array(10));
echo "Password is {$password}";
```

```php
$logins = $db->rawQueryValue('SELECT login from users limit 10');
foreach ($logins as $login)
    echo $login;
```

```php
$params = Array(1, 'admin');
$users = $db->rawQuery("SELECT id, firstName, lastName FROM users WHERE id = ? AND login = ?", $params);
print_r($users); // contains Array of returned rows
```

```php
$params = Array(10, 1, 10, 11, 2, 10);
$q = "(
    SELECT a FROM t1
        WHERE a = ? AND B = ?
        ORDER BY a LIMIT ?
) UNION (
    SELECT a FROM t2 
        WHERE a = ? AND B = ?
        ORDER BY a LIMIT ?
)";
$results = $db->rawQuery ($q, $params);
print_r ($results); // contains Array of returned rows
```

--------------------------------

### PHP MySQLi Select Query

Source: https://github.com/thingengineer/php-mysqli-database-class/blob/master/readme.md

Details various methods for selecting data from a MySQL database using the php-mysqli-database-class. It covers selecting all records, a limited number of records, specific columns, single rows, single values, and multiple values from a column.

```php
$users = $db->get('users'); //contains an Array of all users 
$users = $db->get('users', 10); //contains an Array 10 users
```

```php
$cols = Array ("id", "name", "email");
$users = $db->get ("users", null, $cols);
if ($db->count > 0)
    foreach ($users as $user) {
        print_r ($user);
    }
```

```php
$db->where ("id", 1);
$user = $db->getOne ("users");
echo $user['id'];

$stats = $db->getOne ("users", "sum(id), count(*) as cnt");
echo "total " . $stats['cnt'] . " users found";
```

```php
$count = $db->getValue ("users", "count(*)");
echo "{$count} users found";
```

```php
$logins = $db->getValue ("users", "login", null);
// select login from users
$logins = $db->getValue ("users", "login", 5);
// select login from users limit 5
foreach ($logins as $login)
    echo $login;
```