PHP code example of webfiori / database

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

webfiori / database example snippets

use WebFiori\Database\ConnectionInfo;
use WebFiori\Database\Database;

$connection = new ConnectionInfo('mysql', 'root', '123456', 'testing_db');
$database = new Database($connection);

// Insert
$database->table('posts')->insert([
    'title' => 'Super New Post',
    'author' => 'Me'
])->execute();

// Select
$resultSet = $database->table('posts')
    ->select()
    ->where('author', 'Ibrahim')
    ->execute();

foreach ($resultSet as $record) {
    echo $record['title'];
}

// Update
$database->table('posts')->update([
    'title' => 'Updated Title',
])->where('id', 1)->execute();

// Delete
$database->table('posts')->delete()->where('id', 1)->execute();

use WebFiori\Database\ColOption;
use WebFiori\Database\DataType;

$database->createBlueprint('users')->addColumns([
    'id' => [
        ColOption::TYPE => DataType::INT,
        ColOption::PRIMARY => true,
        ColOption::AUTO_INCREMENT => true
    ],
    'name' => [
        ColOption::TYPE => DataType::VARCHAR,
        ColOption::SIZE => 100
    ],
    'email' => [
        ColOption::TYPE => DataType::VARCHAR,
        ColOption::SIZE => 150
    ]
]);

// Create the table
$database->table('users')->createTable()->execute();

// Single class
$database->addTableFromClass(Users::class);

// Multiple classes at once
$database->addTablesFromClasses([Users::class, Posts::class, Comments::class]);

class Product {
    public ?int $id = null;
    public string $name;
    public float $price;
}

use WebFiori\Database\Repository\AbstractRepository;

class ProductRepository extends AbstractRepository {
    protected function getTableName(): string {
        return 'products';
    }

    protected function getIdField(): string {
        return 'id';
    }

    protected function toEntity(array $row): object {
        $product = new Product();
        $product->id = (int) $row['id'];
        $product->name = $row['name'];
        $product->price = (float) $row['price'];
        return $product;
    }

    protected function toArray(object $entity): array {
        return [
            'id' => $entity->id,
            'name' => $entity->name,
            'price' => $entity->price
        ];
    }
}

$repo = new ProductRepository($database);

// Create
$product = new Product();
$product->name = 'Widget';
$product->price = 29.99;
$repo->save($product);

// Read
$product = $repo->findById(1);
$allProducts = $repo->findAll();

// Update
$product->price = 24.99;
$repo->save($product);

// Delete
$repo->deleteById(1);

// Pagination
$page = $repo->paginate(page: 1, perPage: 20);

use WebFiori\Database\Attributes\Column;
use WebFiori\Database\Attributes\Table;
use WebFiori\Database\DataType;
use WebFiori\Database\Repository\AbstractRepository;

#[Table(name: 'articles')]
class Article extends AbstractRepository {
    #[Column(type: DataType::INT, primary: true, autoIncrement: true)]
    public ?int $id = null;

    #[Column(type: DataType::VARCHAR, size: 200)]
    public string $title = '';

    #[Column(type: DataType::TEXT)]
    public string $content = '';

    protected function getTableName(): string { return 'articles'; }
    protected function getIdField(): string { return 'id'; }
    
    protected function toEntity(array $row): object {
        $article = new self($this->db);
        $article->id = (int) $row['id'];
        $article->title = $row['title'];
        $article->content = $row['content'];
        return $article;
    }

    protected function toArray(object $entity): array {
        return [
            'id' => $entity->id,
            'title' => $entity->title,
            'content' => $entity->content
        ];
    }
}

// Create and save
$article = new Article($database);
$article->title = 'Hello World';
$article->content = 'My first article';
$article->save();

// Query
$all = $article->findAll();
$one = $article->findById(1);

// Update
$article->title = 'Updated Title';
$article->save();

// Delete
$article->deleteById();

// Reload from database
$fresh = $article->reload();

$blueprint = $database->getTable('users');

$generator = $blueprint->getEntityGenerator('User', __DIR__, 'App\\Entity');
$generator->generate();

use WebFiori\Database\Schema\AbstractMigration;

class CreateUsersTable extends AbstractMigration {
    public function up(Database $db): void {
        $db->createBlueprint('users')->addColumns([
            'id' => [ColOption::TYPE => DataType::INT, ColOption::PRIMARY => true, ColOption::AUTO_INCREMENT => true],
            'name' => [ColOption::TYPE => DataType::VARCHAR, ColOption::SIZE => 100]
        ]);
        $db->table('users')->createTable()->execute();
    }
    
    public function down(Database $db): void {
        $db->raw("DROP TABLE users")->execute();
    }
}

use WebFiori\Database\Schema\SchemaRunner;

$runner = new SchemaRunner($connectionInfo);
$runner->discoverFromPath(__DIR__ . '/migrations', 'App\\Migrations');
$runner->createSchemaTable();
$runner->apply();

class CreateReportsTable extends AbstractMigration {
    public function getTargetConnections(): array {
        return ['reporting-db']; // Only runs against the 'reporting-db' connection
    }

    public function up(Database $db): void {
        // ...
    }

    public function down(Database $db): void {
        // ...
    }
}

use WebFiori\Database\Schema\AbstractSeeder;

class UsersSeeder extends AbstractSeeder {
    public function run(Database $db): void {
        $db->table('users')->insert([
            'name' => 'Administrator',
            'email' => '[email protected]'
        ])->execute();
    }
}

use WebFiori\Database\Performance\PerformanceOption;

$database->setPerformanceConfig([
    PerformanceOption::ENABLED => true,
    PerformanceOption::SLOW_QUERY_THRESHOLD => 50
]);

// Execute queries...

$analyzer = $database->getPerformanceMonitor()->getAnalyzer();
echo "Total queries: " . $analyzer->getQueryCount();
echo "Slow queries: " . $analyzer->getSlowQueryCount();

$database->transaction(function (Database $db) {
    $db->table('users')->insert(['name' => 'John'])->execute();
    $db->table('profiles')->insert([
        'user_id' => $db->getLastInsertId(),
        'bio' => 'Developer'
    ])->execute();
});