PHP code example of dealnews / db

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

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

    

dealnews / db example snippets

$mydb = \DealNews\DB\Factory::init("mydb");

$crud = \DealNews\DB\CRUD::factory('mydb');

// Create
$result = $crud->create(
    // table name
    "test",
    // data to add
    [
        "name"        => $name,
        "description" => $description,
    ]
);

// Read
$rows = $crud->read(
    // table name
    "test",
    // where clause data
    ["id" => $id]
);

// Update
$result = $crud->update(
    // table name
    "test",
    // data to update
    ["name" => "Test"],
    // where clause data
    ["id" => $id]
);

// Delete
$result = $crud->delete(
    // table name
    "test",
    // where clause data
    ["id" => $row["id"]]
);

// Run a select with no parameters
$stmt = $crud->run("select * from table limit 10");

// Run a select query with paramters
$stmt = $crud->run(
    "select * from table where foo = :foo"
    [
        ":foo" => $foo
    ]
);

// value object
class Book {
    public string $title = '';
    public string $author = '';
    public string $isbn = '';
}

// mapper
class BookMapper extends \DealNews\DB\AbstractMapper {
    
    public const DATABASE_NAME = 'example';
    
    public const TABLE = 'books';
    
    public const PRIMARY_KEY = 'isbn';
    
    public const MAPPED_CLASS = Book::class;
    
    public const MAPPING = [
        'title'  => [],
        'author' => [],
        'isbn'   => []    
    ];
}

$book = new Book();
$book->title = 'Professional PHP Programming';
$book->author = 'Jesus Castagnetto';
$book->isbn = '1-861002-96-3';

$mapper = new BookMapper();
$book = $mapper->save($book); // the entity is returned from save, reloaded from the database

// load a book
$book = $mapper->load('1-861002-96-3');

// delete a book
$mapper->delete('1-861002-96-3');

// find books based on author
$books = $mapper->find(['author' => 'Rasmus Lerdorf'], limit: 10, start: 0, order: 'title');

$crud = \DealNews\DB\CRUD::factory('mydb');
$query = new \DealNews\DB\Util\Query($crud);

$query->select(['id', 'name', 'email'])
    ->from('users')
    ->where('status', '=', 'active')
    ->orderBy('created_at', 'DESC')
    ->limit(10);

$rows = $crud->runFetch($query->getSql(), $query->getParams());

$query = new \DealNews\DB\Util\Query($crud);

$query->select([
        'u.id',
        'u.name',
        \DealNews\DB\Util\Query::raw('COUNT(p.id) AS post_count'),
        \DealNews\DB\Util\Query::raw('AVG(p.views) AS avg_views'),
    ])
    ->from('users', 'u')
    ->leftJoin('posts', 'p', 'p.user_id', '=', 'u.id')
    ->leftJoin('profiles', 'pr', 'pr.user_id', '=', 'u.id')
    ->where('u.status', '=', 'active')
    ->where('pr.verified', '=', true)
    ->groupBy(['u.id', 'u.name'])
    ->having('post_count', '>', 5)
    ->orderBy('post_count', 'DESC')
    ->limit(20);

$rows = $crud->runFetch($query->getSql(), $query->getParams());

$query->select(['id'])
    ->from('users')
    ->where('status', '=', 'active')
    ->where(function ($q) {
        $q->where('role', '=', 'admin')
          ->orWhere('role', '=', 'moderator');
    });

// Generates: WHERE status = :p0 AND (role = :p1 OR role = :p2)