PHPackages                             brybry/sql-practice - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. brybry/sql-practice

ActiveLibrary[Database & ORM](/categories/database)

brybry/sql-practice
===================

A secure PHP package for SQL learning and practice with Laravel and SQLite integration.

012PHP

Since Sep 15Pushed 8mo agoCompare

[ Source](https://github.com/rondinabrybry/brybry-sql-practice)[ Packagist](https://packagist.org/packages/brybry/sql-practice)[ RSS](/packages/brybry-sql-practice/feed)WikiDiscussions main Synced 1mo ago

READMEChangelogDependenciesVersions (1)Used By (0)

SqlCommands Laravel Package
===========================

[](#sqlcommands-laravel-package)

A **secure** PHP package for SQL learning and practice with Laravel and SQLite integration. Perfect for educational environments where students need to practice SQL queries safely.

� **NEW: Zero-Configuration Installation**
------------------------------------------

[](#-new-zero-configuration-installation)

**Routes and web interface are now included automatically!** No manual setup required.

```
composer require brybry/sql-practice
```

Then visit: `http://yourapp.com/sql-practice` - That's it!

�🔒 Security Features
--------------------

[](#-security-features)

- **SQL Injection Prevention**: All queries use parameterized statements
- **Input Sanitization**: Table and column names are properly escaped
- **Operation Restrictions**: Configurable allowed operations
- **Safe Environment**: Isolated practice database separate from production

📦 Installation
--------------

[](#-installation)

Install via Composer:

```
composer require brybry/sql-practice
```

For Laravel projects, the service provider will be automatically registered.

🚀 Laravel Integration
---------------------

[](#-laravel-integration)

### Quick Setup for SQL Practice

[](#quick-setup-for-sql-practice)

1. **Publish the configuration:**

    ```
    php artisan vendor:publish --provider="SqlCommands\Providers\SqlCommandsServiceProvider" --tag="config"
    ```
2. **Create a practice database:**

    ```
    touch database/practice.sqlite
    ```
3. **Configure database connection** in `config/database.php`:

    ```
    'practice' => [
        'driver' => 'sqlite',
        'database' => database_path('practice.sqlite'),
        'prefix' => '',
        'foreign_key_constraints' => true,
    ],
    ```
4. **Initialize sample data:**

    ```
    use SqlCommands\SqlSimulator;

    $simulator = new SqlSimulator(database_path('practice.sqlite'));
    $simulator->createSampleTables();
    $simulator->insertSampleData();
    ```

💻 Usage Examples
----------------

[](#-usage-examples)

### Basic Query Building (Secure)

[](#basic-query-building-secure)

```
use SqlCommands\SqlCommands;

// All methods return ['sql' => $query, 'params' => $parameters]

// SELECT with WHERE conditions
$query = SqlCommands::select('users', ['id', 'name'], [
    'where' => ['status' => 'active', 'age' => [18, 25, 30]], // IN clause
    'orderBy' => 'name',
    'limit' => 10
]);
// Result: ['sql' => 'SELECT `id`, `name` FROM `users` WHERE `status` = ? AND `age` IN (?, ?, ?) ORDER BY `name` LIMIT 10', 'params' => ['active', 18, 25, 30]]

// Secure INSERT
$query = SqlCommands::insert('users', [
    'name' => 'John Doe',
    'email' => 'john@example.com',
    'age' => 28
]);

// Secure UPDATE with WHERE conditions
$query = SqlCommands::update('users',
    ['status' => 'inactive'],
    ['id' => 123]
);

// Safe DELETE with conditions
$query = SqlCommands::delete('users', ['status' => 'spam']);
```

### Advanced Queries

[](#advanced-queries)

```
// JOINs
$query = SqlCommands::leftJoin('users', 'orders', 'users.id = orders.user_id',
    ['users.name', 'orders.total']
);

// Aggregates
$countQuery = "SELECT " . SqlCommands::count('id') . " FROM users";
$sumQuery = "SELECT " . SqlCommands::sum('total') . " FROM orders";

// BETWEEN with parameters
$between = SqlCommands::between('created_at', '2024-01-01', '2024-12-31');
$query = SqlCommands::select('orders', ['*'], [
    'where' => [$between['expression'] => $between['params']]
]);
```

### SQLite Functions (200+ Available)

[](#sqlite-functions-200-available)

The package includes comprehensive support for **all major SQLite functions** plus **advanced educational features**:

```
// 🔤 String Functions
SqlCommands::upper('name');               // UPPER(`name`)
SqlCommands::lower('email');              // LOWER(`email`)
SqlCommands::length('description');       // LENGTH(`description`)
SqlCommands::trim('text', ' .');          // TRIM(`text`, ' .')
SqlCommands::substr('phone', 1, 3);       // SUBSTR(`phone`, 1, 3)
SqlCommands::replace('text', 'old', 'new'); // REPLACE(`text`, 'old', 'new')
SqlCommands::printf('User: %s (ID: %d)', 'John', 123);

// 🎓 NEW: Educational Functions for Teaching Advanced SQL
SqlCommands::caseWhen([
    'age < 18' => 'Minor',
    'age < 65' => 'Adult'
], 'Senior');                             // CASE/WHEN conditional logic

SqlCommands::coalesce('mobile', 'home', 'email', 'N/A'); // NULL handling
SqlCommands::like('name', 'John%');       // Pattern matching with params
SqlCommands::exists($subquery);           // Subquery existence checks

// CTE (Common Table Expressions)
$withQuery = SqlCommands::with(['active_users' => $cte], $mainQuery);

// PIVOT simulation for data analysis
$pivot = SqlCommands::pivot('sales', 'quarter', ['Q1', 'Q2', 'Q3', 'Q4']);

// Database constraints for teaching design
SqlCommands::foreignKey('user_id', 'users', 'id', 'CASCADE');
SqlCommands::check('age >= 0 AND age = 18', 'adult', 'minor'); // IIF(age >= 18, 'adult', 'minor')
SqlCommands::changes();                   // CHANGES()
SqlCommands::lastInsertRowid();          // LAST_INSERT_ROWID()

// Real-world educational example
$query = SqlCommands::select('employees', [
    'name',
    SqlCommands::upper('department') . ' as dept_upper',
    SqlCommands::caseWhen([
        'salary < 30000' => 'Entry',
        'salary < 60000' => 'Mid',
        'salary >= 60000' => 'Senior'
    ], 'Executive') . ' as level',
    SqlCommands::coalesce('bonus', '0') . ' as bonus_amount',
    SqlCommands::round('salary * 1.1', 2) . ' as salary_with_raise'
], [
    'where' => ['active' => 1],
    'orderBy' => 'salary'
]);
```

🎓 **Perfect for Teaching:**

- **Conditional Logic** - CASE/WHEN statements for business rules
- **NULL Handling** - COALESCE, NULLIF for data quality
- **Pattern Matching** - LIKE, GLOB, REGEXP for search functionality
- **Advanced Queries** - Subqueries, CTEs, window functions
- **Data Analysis** - PIVOT simulation, aggregations
- **Database Design** - Constraints, indexes, relationships

📚 **[Complete SQLite Functions Reference →](SQLITE-FUNCTIONS.md)** - Detailed documentation with 200+ functions, examples, and usage patterns.

```

### Using the Simulator

```php
use SqlCommands\SqlSimulator;

$simulator = new SqlSimulator(database_path('practice.sqlite'));

// Execute queries safely
$selectQuery = SqlCommands::select('users', ['name', 'email']);
$result = $simulator->executeQuery($selectQuery);

if ($result['success']) {
    foreach ($result['data'] as $row) {
        echo $row['name'] . ': ' . $row['email'] . "\n";
    }
} else {
    echo "Error: " . $result['error'];
}

// Get database schema for learning
$schema = $simulator->getSchema();
print_r($schema);

```

### Laravel Controller Example

[](#laravel-controller-example)

```