JsonSQL PHP Class

Example:

require_once 'JsonSQL.php';

try {
    // If 'my_app_data.json' does not exist in the current directory, it will be created.
    $db = new JsonSQL('my_app_data.json');
    echo "JsonSQL instance ready.";
} catch (Exception $e) {
    die("Error initializing JsonSQL: " . $e->getMessage());
}

This is the primary method for handling password keys. It can operate in two modes:

• 'generate': Takes a plain-text password ($input) and returns an array containing the original key and its securely hashed version. You should store the returned hash.
• 'verify': Takes a plain-text password ($input) and a stored hash ($hash) to verify if they match. Returns true or false.

Example (Generating a key and hash):

$password = "MySuperSecretPassword123";
$keyData = $db->key($password, null, 'generate');

// $keyData['key'] is your original password "MySuperSecretPassword123"
// $keyData['hash'] is the secure hash you should store in a database or config file
echo "Store this hash: " . $keyData['hash'];

Example (Verifying a key against a hash):

$storedHash = '...the hash you stored from the generate step...';
$loginPassword = "MySuperSecretPassword123"; // From user input

if ($db->key($loginPassword, $storedHash, 'verify')) {
    echo "Password is correct!";
} else {
    echo "Incorrect password.";
}

Encrypts the entire contents of the current JSON file using the provided $key. It saves the encrypted output to a new file named data.enc in the same directory.

Example:

$encryptionKey = "MySuperSecretPassword123"; // Use a strong, securely managed key

try {
    $db->encryptFile($encryptionKey);
    echo "The file has been encrypted to data.enc.";
    // You might want to delete the original file now, e.g., unlink('my_app_data.json');
} catch (Exception $e) {
    echo "Encryption failed: " . $e->getMessage();
}

Reads the data.enc file, decrypts its contents using the provided $key, and writes the plain-text JSON back to its original filename. The data.enc file is automatically deleted upon successful decryption.

Example:

$decryptionKey = "MySuperSecretPassword123"; // The same key used for encryption

try {
    $db->decryptFile($decryptionKey);
    echo "The file has been decrypted successfully.";
} catch (Exception $e) {
    echo "Decryption failed: " . $e->getMessage();
}

Adds a new item to a specified entity.

Example:

$newProduct = [
    "name" => "Wireless Keyboard",
    "price" => 79.99,
    "stock" => 150
];
$createdProduct = $db->create('products', $newProduct);
echo "Created product with ID: " . $createdProduct['id'];
// Let's save this ID for other examples: $productId = $createdProduct['id'];

Updates an existing item identified by its id.

Example:

// Update the price of the product created earlier
$updateData = ['price' => 75.50];
$updatedProduct = $db->update('products', $productId, $updateData);
if ($updatedProduct) {
    echo "Updated price for " . $updatedProduct['name'] . " to $" . $updatedProduct['price'];
}

Deletes an item from an entity using its id.

Example:

// Delete the product we created and updated
$deletedProduct = $db->delete('products', $productId);
if ($deletedProduct) {
    echo "Successfully deleted product: " . $deletedProduct['name'];
}

Example:

// Find all products with stock greater than 100
$highStockProducts = $db->where('products', 'stock', '>', 100);
print_r($highStockProducts);

// Find all users who are not active
$inactiveUsers = $db->where('users', 'status', '!=', 'active');
print_r($inactiveUsers);

Searches for items with an exact match for a value.

Example:

// Find a user by their exact email address
$user = $db->search('users', 'alice@example.com', ['email']);
if ($user) {
    echo "Found user with matching email: " . $user[0]['name'];
}

Checks if any item contains a substring. Returns true or false.

Example:

if ($db->isLike('products', 'Laptop')) {
    echo "The database contains products with 'Laptop' in their data.";
}

Alias for isLike().

Example:

if ($db->orContain('users', 'Wonderland')) {
    echo "A user with 'Wonderland' in their name exists.";
}

Finds items where a field's value falls within a given range (inclusive).

Example:

// Find products with stock between 10 and 50 units
$lowStockProducts = $db->isBetween('products', 'stock', 10, 50);
print_r($lowStockProducts);

Sorts the items within an entity based on a field. This method returns the sorted data but does not persist the sort order to the JSON file.

Example:

// Get all users, sorted by age from oldest to youngest
$usersByAge = $db->sort('users', 'age', 'desc');
print_r($usersByAge);

A convenient wrapper for calculations. Supported operators: 'sum', 'average' or 'avg', 'max', 'min'.

Example:

// Find the average price of all products
$avgPrice = $db->calculate('products', 'avg', 'price');
echo "The average product price is: $" . number_format($avgPrice, 2);

Counts how many items match a specific value in a given field.

Example:

// Count how many users have an 'active' status
$activeUserCount = $db->countIf('users', 'status', 'active');
echo "Number of active users: " . $activeUserCount;

Explicitly creates a new, separate JSON file. Throws an exception if the file already exists.

Example:

try {
    $initialConfig = ['settings' => ['theme' => 'dark', 'notifications' => true]];
    $db->createFile('config.json', $initialConfig);
    echo "Successfully created config.json";
} catch (Exception $e) {
    echo "Could not create file: " . $e->getMessage();
}

Retrieves the size of the current JSON data file in a human-readable format.

Example:

echo "Current database file size: " . $db->getFileSize();

Exports data from an entity to a CSV file.

Example:

// Export just the name and stock of all products to a CSV file.
try {
    $db->export('products', 'inventory.csv', ['name', 'stock']);
    echo "Inventory list exported.";
} catch (Exception $e) {
    echo "Export failed: " . $e->getMessage();
}

Imports data from a CSV file into an entity. The CSV must have a header row.

Example:

// First, create a CSV file named 'new_users.csv' in the same directory
// with the following content:
/*
name,email,age,status
"David Chen",david@example.com,42,"active"
"Eva Green",eva@example.com,29,"inactive"
*/

try {
    $db->import('users', 'new_users.csv');
    echo "New users imported successfully.";
} catch (Exception $e) {
    echo "Import failed: " . $e->getMessage();
}

Exports data to a table in a SQLite database file. All fields are created as TEXT.

Example:

// Create a SQLite backup of the users table
try {
    $db->exportToSqlite('users', 'backup.sqlite', 'archived_users');
    echo "Users table backed up to SQLite.";
} catch (Exception $e) {
    echo "SQLite export failed: " . $e->getMessage();
}