PHP code example of initphp / database

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

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

    

initphp / database example snippets

use \InitPHP\Database\DB;

// Connection
DB::createImmutable([
    'dsn'       => 'mysql:host=localhost;port=3306;dbname=test;charset=utf8mb4',
    'username'  => 'root',
    'password'  => '',
    'charset'   => 'utf8mb4',
    'collation' => 'utf8mb4_general_ci',
]);

use \InitPHP\Database\DB;
$data = [
    'title'     => 'Post Title',
    'content'   => 'Post Content',
];

$isInsert = DB::create('post', $data);

/**
* This executes the following query.
* 
* INSERT INTO post 
* (title, content) 
* VALUES 
* ("Post Title", "Post Content");
*/
if($isInsert){
    // Success
}

use \InitPHP\Database\DB;

$data = [
    [
        'title'     => 'Post Title 1',
        'content'   => 'Post Content 1',
        'author'    => 5
    ],
    [
        'title'     => 'Post Title 2',
        'content'   => 'Post Content 2'
    ],
];

$isInsert = DB::createBatch('post', $data);

/**
* This executes the following query.
* 
* INSERT INTO post 
* (title, content, author) 
* VALUES 
* ("Post Title 1", "Post Content 1", 5),
* ("Post Title 2", "Post Content 2", NULL);
*/

if($isInsert){
    // Success
}

use \InitPHP\Database\DB;


/**
* This executes the following query.
* 
* SELECT user.name AS author_name, post.id, post.title 
* FROM post, user 
* WHERE user.id = post.author AND post.status = 1
* ORDER BY post ASC, post.created_at DESC
* LIMIT 20, 10
*/

/** @var \InitORM\DBAL\DataMapper\Interfaces\DataMapperInterface $res */
$res = DB::select('user.name as author_name', 'post.id', 'post.title')
    ->from('post')
    ->selfJoin('user', 'user.id=post.author')
    ->where('post.status', 1)
    ->orderBy('post.id', 'ASC')
    ->orderBy('post.created_at', 'DESC')
    ->offset(20)->limit(10)
    ->read();
    
if($res->numRows() > 0){
    $results = $res->asAssoc()
                    ->rows();
    foreach ($results as $row) {
        echo $row['title'] . ' by ' . $row['author_name'] . '<br />';
    }
}

use \InitPHP\Database\DB;
$data = [
    'title'     => 'New Title',
    'content'   => 'New Content',
];

$isUpdate = DB::where('id', 13)
                ->update('post', $data);
    
/**
* This executes the following query.
* 
* UPDATE post 
* SET title = "New Title", content = "New Content"
* WHERE id = 13
*/
if ($isUpdate) {
    // Success
}

use \InitPHP\Database\DB;
$data = [
    [
        'id'        => 5,
        'title'     => 'New Title #5',
        'content'   => 'New Content #5',
    ],
    [
        'id'        => 10,
        'title'     => 'New Title #10',
    ]
];

$isUpdate = DB::where('status', '!=', 0)
                ->updateBatch('id', 'post', $data);
    
/**
* This executes the following query.
* 
* UPDATE post SET 
* 	title = CASE 
* 		WHEN id = 5 THEN 'New Title #5' 
* 		WHEN id = 10 THEN 'New Title #10' 
* 		ELSE title END, 
* 	content = CASE 
* 		WHEN id = 5 THEN 'New Content #5'
* 		ELSE content END 
* WHERE status != 0 AND id IN (5, 10)
*/
if ($isUpdate) {
    // Success
}

use \InitPHP\Database\DB;

$isDelete = DB::where('id', 13)
                ->delete('post');
    
/**
* This executes the following query.
* 
* DELETE FROM post WHERE id = 13
*/
if ($isUpdate) {
    // Success
}

use \InitPHP\Database\DB;

/** @var \InitORM\DBAL\DataMapper\Interfaces\DataMapperInterface $res */
$res = DB::query("SELECT id FROM post WHERE user_id = :id", [
    ':id'   => 5
]);

use \InitPHP\Database\DB;

/** @var \InitORM\DBAL\DataMapper\Interfaces\DataMapperInterface $res */
$res = DB::select(DB::raw("CONCAT(name, ' ', surname) AS fullname"))
        ->where(DB::raw("title = '' AND (status = 1 OR status = 0)"))
        ->limit(5)
        ->read('users');
        
/**
 * SELECT CONCAT(name, ' ', surname) AS fullname 
 * FROM users 
 * WHERE title = '' AND (status = 1 OR status = 0)
 * LIMIT 5
 */
$results = $res->asAssoc()
                ->rows();
foreach ($results as $row) {
    echo $row['fullname'];
}

use \InitPHP\Database\DB;

DB::connect([
    'dsn'       => 'mysql:host=localhost;port=3306;dbname=test;charset=utf8mb4',
    'username'  => 'root',
    'password'  => '',
    'charset'   => 'utf8mb4',
    'collation' => 'utf8mb4_general_ci',
]);

namespace App\Model;

class Posts extends \InitPHP\Database\Model
{

    /**
    * If your model will use a connection other than your global connection, provide connection information.
    * @var array|null <p>Default : NULL</p> 
    */
    protected array $credentials = [
        'dsn'               => '',
        'username'          => 'root',
        'password'          => '',
        'charset'           => 'utf8mb4',
        'collation'         => 'utf8mb4_unicode_ci',
    ];

    /**
     * If not specified, \InitPHP\Database\Entity::class is used by default.
     * 
     * @var string<\InitPHP\Database\Entity>
     */
    protected $entity = \App\Entities\PostEntity::class;

    /**
     * If not specified, the name of your model class is used.
     * 
     * @var string
     */
    protected string $schema = 'posts';

    /**
     * The name of the PRIMARY KEY column. If not, define it as NULL.
     * 
     * @var string
     */
    protected string $schemaId = 'id';

    /**
     * Specify FALSE if you want the data to be permanently deleted.
     * 
     * @var bool
     */
    protected bool $useSoftDeletes = true;

    /**
     * Column name to hold the creation time of the data.
     * 
     * @var string|null
     */
    protected ?string $createdField = 'created_at';

    /**
     * The column name to hold the last time the data was updated.
     * 
     * @var string|null
     */
    protected ?string $updatedField = 'updated_at';

    /**
     * Column name to keep deletion time if $useSoftDeletes is active.
     * 
     * @var string|null
     */
    protected ?string $deletedField = 'deleted_at';

    protected bool $readable = true;

    protected bool $writable = true;

    protected bool $deletable = true;

    protected bool $updatable = true;
    
}

namespace App\Entities;

class PostEntity extends \InitPHP\Database\Entity 
{
    /**
     * An example of a getter method for the "post_title" column.
     * 
     * Usage : 
     * echo $entity->post_title;
     */
    public function getPostTitleAttribute($title)
    {
        return strtoupper($title);
    }
    
    /**
     * An example of a setter method for the "post_title" column.
     * 
     * Usage : 
     * $entity->post_title = 'New Post Title';
     */
    public function setPostTitleAttribute($title)
    {
        $this->post_title = strtolower($title);
    }
    
}

use \InitPHP\Database\DB;

DB::createImmutable([
    'dsn'       => 'mysql:host=localhost;dbname=test;port=3306;charset=utf8mb4;',
    'username'  => 'root',
    'password'  => '',
    
    'log'       => __DIR__ '/logs/db.log', // string, callable or object
]);

use \InitPHP\Database\DB;

class Logger {
    
    public function critical(string $msg)
    {
        $path = __DIR__ . '/log.log';
        file_put_contents($path, $msg, FILE_APPEND);
    }

}

$logger = new Logger();

DB::createImmutable([
    'dsn'       => 'mysql:host=localhost;dbname=test;port=3306;charset=utf8mb4;',
    'username'  => 'root',
    'password'  => '',
    
    'log'       => $logger, // or [$logger, 'critical']
]);

use \InitPHP\Database\DB;

DB::createImmutable([
    'dsn'       => 'mysql:host=localhost;dbname=test;port=3306;charset=utf8mb4;',
    'username'  => 'root',
    'password'  => '',
    
    'log'       => function (string $msg) {
        $path = __DIR__ . '/log.log';
        file_put_contents($path, $msg, FILE_APPEND);
    },
]);

use \InitPHP\Database\DB;

DB::createImmutable([
    'dsn'       => 'mysql:host=localhost;dbname=test;port=3306;charset=utf8mb4;',
    'username'  => 'root',
    'password'  => '',
    
    'debug'     => true, // boolean
]);

use InitPHP\Database\DB;

DB::enableQueryLog();

DB::table('users')->where('name', 'John')->read();

var_dump(DB::getQueryLogs());

/**
 * The output of the above example looks like this;
 * [
 *      [
 *          'query' => 'SELECT * FROM users WHERE name = :name',
 *          'time'  => '0.00064',
 *          'args'  => [
 *              ':name'     => 'John',
 *          ]
 *      ]
 * ]
 * 
 */