JsonSQL PHP Class

A comprehensive guide to using the `JsonSQL` PHP class. This documentation covers instantiation, CRUD operations, advanced querying with a `where()` clause, aggregation, and data import/export functionalities.

Download JsonSQL.php

JSON File Structure

The `JsonSQL` class expects your JSON data to be structured as a top-level object, where each key represents an "entity" (or "table"). The value associated with each entity key must be an array of objects. Each object within these arrays represents a "row" or "item" and is expected to have a unique 'id' field.

Example JSON Structure (data.json):
{
  "users": [
    {"id": "60c72b2f7d2f9", "name": "Alice Wonderland", "email": "alice@example.com", "age": 30, "status": "active"},
    {"id": "60c72b2f7d30a", "name": "Bob The Builder", "email": "bob@example.com", "age": 25, "status": "inactive"}
  ],
  "products": [
    {"id": "60c72b2f7d31b", "name": "Laptop Pro", "price": 1200.00, "stock": 50},
    {"id": "60c72b2f7d32c", "name": "Wireless Mouse", "price": 25.50, "stock": 200}
  ]
}

Installation

  1. Download the File: Use the download button at the top of this page to get the JsonSQL.php file.
  2. Include in Your Project: Place the file in your project directory and include it in your PHP script using require_once.
require_once 'JsonSQL.php';

Basic Usage: Instantiation

To begin, create an instance of the `JsonSQL` class. You must provide a filename for your JSON data file. It is highly recommended to also specify a secure base directory for your data files.

new JsonSQL(string $filename, string $baseDataPath = __DIR__ . '/data')

  • $filename (string, required): The name of the JSON file (e.g., 'my_data.json'). Do not include directory paths.

  • $baseDataPath (string, optional): The absolute path to the directory where JSON files will be stored. Defaults to a data folder in the current script's directory.

Security Best Practice: Set $baseDataPath to a path outside your web server's document root to prevent direct public access.
Example:
require_once 'JsonSQL.php';

// Define a secure base path (e.g., one level above web root)
$secureDataPath = __DIR__ . '/../data_storage'; 

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

Core Operations (CRUD)

1. Create Data (create())

Adds a new item to a specified entity. A unique id is automatically generated. By default, new items are added to the beginning of the array.

create(string $entity, array $item, bool $descending = true): array
Example:
$newUser = [
    "name" => "Carol Danvers",
    "email" => "carol@example.com",
    "age" => 34,
    "status" => "active"
];
$createdUser = $db->create('users', $newUser);
echo "Created user with ID: " . $createdUser['id'];

2. Read Data

Multiple methods are available to read data from your entities.

getAll(string $entity): array

Retrieves all items from a specified entity.

Example:
$allUsers = $db->getAll('users');
print_r($allUsers);
getById(string $entity, string $id): ?array

Retrieves a single item by its unique id. Returns null if not found.

Example:
// Assuming $createdUser['id'] holds the ID from the create example
$userId = $createdUser['id'];
$user = $db->getById('users', $userId);
if ($user) {
    echo "Found user: " . $user['name'];
}
getLimited(string $entity, int $limit = 25, int $offset = 0, ?string $field = null, string $order = 'asc'): array

Retrieves a limited number of items, ideal for pagination.

Example:
// Get the first 5 products, sorted by price descending
$topProducts = $db->getLimited('products', 5, 0, 'price', 'desc');
print_r($topProducts);
countRows(string $entity): int

Counts the total number of items in an entity.

Example:
$productCount = $db->countRows('products');
echo "There are " . $productCount . " products in the database.";

3. Update Data (update())

Updates an existing item identified by its id. The new data is merged with the existing data.

update(string $entity, string $id, array $updatedItem): ?array
Example:
// Update the status of the user created earlier
$userId = $createdUser['id'];
$updateData = ['status' => 'inactive', 'age' => 35];
$updatedUser = $db->update('users', $userId, $updateData);
if ($updatedUser) {
    echo "User " . $updatedUser['name'] . " is now " . $updatedUser['status'];
}

4. Delete Data (delete())

Deletes an item from an entity using its id.

delete(string $entity, string $id): ?array
Example:
// Delete the user we created and updated
$userId = $createdUser['id'];
$deletedUser = $db->delete('users', $userId);
if ($deletedUser) {
    echo "Successfully deleted user: " . $deletedUser['name'];
}

Advanced Querying (where())

The where() method provides a powerful way to filter data based on various comparison operators, mimicking SQL's WHERE clause.

where(string $entity, string $field, string $operator, mixed $value = null): array
OperatorDescriptionExample Usage
=, ==Equals (loose comparison, numeric if possible).age = 30
<, >, <=, >=Less than, greater than, etc. (numeric or lexical).price > 99.99
<>, !=Not equals (loose comparison).status != 'inactive'
is_nullField value is null or the field does not exist.notes is_null
is_not_nullField exists and its value is not null.notes is_not_null
is_emptyValue is empty ("", 0, null, etc.) or field does not exist.description is_empty
is_not_emptyField exists and its value is not empty.description is_not_empty
Example:
// Get all users younger than 30 who are active
$results1 = $db->where('users', 'age', '<', 30);
$results2 = $db->where('users', 'status', '=', 'active');
// Note: Chaining requires manual intersection in PHP
$activeYoungUsers = array_uintersect($results1, $results2, function($a, $b) {
    return strcmp($a['id'], $b['id']);
});
print_r($activeYoungUsers);

// A simpler, single query:
$inactiveUsers = $db->where('users', 'status', '!=', 'active');
print_r($inactiveUsers);

Searching

The class provides flexible methods for searching data.

search(string $entity, mixed $value, array|bool $fields = false): ?array

Searches for an exact value. Can search all fields or be restricted to specific ones.

Example:
// Find a user by their exact email address
$user = $db->search('users', 'alice@example.com', ['email']);
print_r($user);
isLike(string $entity, string $pattern): bool

Checks if any item contains a substring. Returns true or false. An alias, orContain(), is also available.

Example:
if ($db->isLike('products', 'Laptop')) {
    echo "The database contains products with 'Laptop' in their data.";
}
isBetween(string $entity, string $field, mixed $lowerBound, mixed $upperBound): ?array

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

Example:
// Find products with a price between $100 and $1500
$midRangeProducts = $db->isBetween('products', 'price', 100.00, 1500.00);
print_r($midRangeProducts);

Sorting (sort())

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.

sort(string $entity, string $field, string $order = 'asc'): array
Example:
// Get all users, sorted by age from oldest to youngest
$usersByAge = $db->sort('users', 'age', 'desc');
print_r($usersByAge);

Aggregation

Perform aggregate calculations on numeric fields.

calculate(string $entity, string $operator, string $field): float|int|null

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

Example:
// Find the price of the most expensive product
$maxPrice = $db->calculate('products', 'max', 'price');
echo "The most expensive product costs: $" . $maxPrice;
countIf(string $entity, string $field, mixed $value): int

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

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

File Management

getFileSize(): string

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

Example:
echo "Current database file size: " . $db->getFileSize();
createNewJsonFile(string $newFilename, array $initialData = []): bool

Explicitly creates a new, separate JSON file in the same base data path.

Example:
try {
    $initialLogs = ['events' => []];
    $db->createNewJsonFile('activity_logs.json', $initialLogs);
    echo "Successfully created activity_logs.json";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}

Data Import/Export

Supports exporting data to and importing from CSV files, as well as exporting to SQLite databases.

export(string $entity, string $csvFilename, array|bool $selectedFields = false): void

Exports data from an entity to a CSV file.

Example:
// Export just the name and email of all users to a CSV file.
// The file 'user-contact-list.csv' will be created in the baseDataPath.
try {
    $db->export('users', 'user-contact-list.csv', ['name', 'email']);
    echo "User contact list exported.";
} catch (Exception $e) {
    echo "Export failed: " . $e->getMessage();
}
import(string $entity, string $csvFilename, array|bool $selectedFields = false): void

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

Example:
// First, create a CSV file named 'new_products.csv' in your baseDataPath
// with the following content:
/*
name,price,stock
"USB-C Hub",35.99,75
"Monitor Stand",45.50,120
*/

try {
    $db->import('products', 'new_products.csv');
    echo "New products imported successfully.";
} catch (Exception $e) {
    echo "Import failed: " . $e->getMessage();
}
exportToSqlite(string $entity, string $dbFilename, string $tableName): void

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

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

Security Considerations

  • File Path Security: The class uses a $baseDataPath to restrict file operations to a single directory, and filenames are validated to prevent directory traversal. Always set this path outside your web root.
  • SQL Injection: Table names and data values are sanitized and handled with prepared statements during SQLite export to prevent SQL injection.
  • Cross-Site Scripting (XSS): All string data passed to create() and update() is automatically sanitized with htmlspecialchars() to prevent XSS attacks when rendering data in HTML.

Error Handling

The class uses PHP's Exception mechanism for error handling. It's crucial to wrap your calls in try-catch blocks to gracefully handle potential issues.

try {
    // Attempt to delete a product that might not exist
    $db->delete('products', 'non-existent-id');
} catch (InvalidArgumentException $e) {
    // Handle specific validation errors
    error_log("Validation Error: " . $e->getMessage());
} catch (Exception $e) {
    // Handle all other general errors
    error_log("Application Error: " . $e->getMessage());
    // Display a user-friendly error message
    die("A database error occurred. Please try again later.");
}

Limitations

  • Performance: Not suitable for very large datasets or high-traffic sites as operations involve reading/writing the entire JSON file.
  • Concurrency: Lacks built-in file locking, which can lead to data corruption with simultaneous write requests.
  • Complex Queries: Does not support JOINs, subqueries, or complex transactions.