Active Records Design Pattern Database Object

This is where you share YOUR scripts with others

Moderators: egami, macek, gesf

Post Reply
User avatar
Strider64
php-forum GURU
php-forum GURU
Posts: 421
Joined: Sat Mar 23, 2013 8:24 am
Location: Livonia, MI
Contact:

Sat Jan 30, 2021 6:49 am

This is just a simple Database Object Class that can save you save time developing CRUD classes.

Code: Select all

<?php


namespace Miniature;

use PDO;

class DatabaseObject // Extended by the children class:
{
    static protected string $table = ""; // Overridden by the calling class:
    static protected array $db_columns = []; // Overridden by the calling class:
    static protected array $objects = [];
    static protected array $params = [];
    static protected $searchItem;
    static protected $searchValue;

    /*
     * There is NO read() method this fetch_all method
     *  basically does the same thing. The query ($sql)
     *  is done in the class the calls this method.
     */
    public static function fetch_by_column_name($sql)
    {
        $stmt = Database::pdo()->prepare($sql);

        $stmt->execute([ static::$searchItem => static::$searchValue ]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    /*
     * Total rows in Database Table
     */
    public static function countAll() {
        return Database::pdo()->query("SELECT count(*) FROM " . static::$table)->fetchColumn();
    }

    /*
     * Pagination static function/method to limit
     * the number of records per page. This is
     * useful for tables that contain a lot of
     * records (data).
     */
    public static function page($perPage, $offset): array
    {
        $sql = 'SELECT * FROM ' . static::$table . ' ORDER BY date_updated DESC LIMIT :perPage OFFSET :blogOffset';
        $stmt = Database::pdo()->prepare($sql); // Prepare the query:
        $stmt->execute(['perPage' => $perPage, 'blogOffset' => $offset]); // Execute the query with the supplied data:
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /*
     * Grab Record will be used for editing:
     */
    public static function fetch_by_id($id)
    {
        $sql = "SELECT * FROM " . static::$table . " WHERE id=:id LIMIT 1";
        $stmt = Database::pdo()->prepare($sql);
        $stmt->execute(['id' => $id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    /*
     * Create/Insert new record in the database table
     * that can be used for more than one table.
     */
    public function create():bool
    {

        /* Initialize an array */
        $attribute_pairs = [];

        /*
         * Setup the query using prepared states with static:$params being
         * the columns and the array keys being the prepared named placeholders.
         */
        $sql = 'INSERT INTO ' . static::$table . '(' . implode(", ", array_keys(static::$params)) . ', date_updated, date_added)';
        $sql .= ' VALUES ( :' . implode(', :', array_keys(static::$params)) . ', NOW(), NOW() )'; // Notice the 2 NOW() calls for dates:

        /*
         * Prepare the Database Table:
         */
        $stmt = Database::pdo()->prepare($sql);

        /*
         * Grab the corresponding values in order to
         * insert them into the table when the script
         * is executed.
         */
        foreach (static::$params as $key => $value)
        {
            if($key === 'id') { continue; } // Don't include the id:
            $attribute_pairs[] = $value; // Assign it to an array:
        }

        return $stmt->execute($attribute_pairs); // Execute and send boolean true:

    }

    /*
     * This is the update that method that I came up with and
     * it does use named place holders. I have always found
     * updating was easier that creating/adding a record for
     * some strange reason?
     */
    public function update(): void
    {
        /* Initialize an array */
        $attribute_pairs = [];

        /* Create the prepared statement string */
        foreach (static::$params as $key => $value)
        {
            if($key === 'id') { continue; } // Don't include the id:
            $attribute_pairs[] = "{$key}=:{$key}"; // Assign it to an array:
        }

        /*
         * The query/sql implodes the prepared statement array in the proper format
         * and I also hard code the date_updated column as I practically use that for
         * all my database table. Though I think you could override that in the child
         * class if you needed too.
         */
        $sql  = 'UPDATE ' . static::$table . ' SET ';
        $sql .= implode(", ", $attribute_pairs) . ', date_updated=NOW() WHERE id =:id';

        /* Normally in two lines, but you can daisy chain pdo method calls */
        Database::pdo()->prepare($sql)->execute(static::$params);

    }

    /*
     * Delete is probably the most easiest of CRUD (Create Read Update Delete),
     * but is the most dangerous method of the four as the erasure of the data is permanent of
     * PlEASE USE WITH CAUTION! (I use a small javascript code to warn users of deletion)
     */
    public function delete($id): bool
    {
            $sql = 'DELETE FROM ' . static::$table . ' WHERE id=:id';
            return Database::pdo()->prepare($sql)->execute([':id' => $id]);
    }

}
It's commented pretty good in my opinion and should help you out with Object-Oriented Programming

Here's an example of how to implement it with another class

Code: Select all

<?php


namespace Miniature;

use JetBrains\PhpStorm\Pure;
use DateTime;
use DateTimeZone;

class CMS extends DatabaseObject
{
    protected static string $table = "cms"; // Table Name:
    static protected array $db_columns = ['id', 'user_id', 'thumb_path', 'image_path', 'Model', 'ExposureTime', 'Aperture', 'ISO', 'FocalLength', 'author', 'heading', 'content', 'data_updated', 'date_added'];
    public $id;
    public $user_id;
    public $thumb_path;
    public $image_path;
    public $Model;
    public $ExposureTime;
    public $Aperture;
    public $ISO;
    public $FocalLength;
    public $author;
    public $heading;
    public $content;
    public $date_updated;
    public $date_added;


    #[Pure] public static function intro($content = "", $count = 100, $id = 0, $page = 'display_page'): string
    {
        return substr($content, 0, $count) . '<a class="moreBtn" href="' . $page . '?id=' . urldecode($id) . '"> ...more</a>';
    }

    public static function styleDate($prettyDate): string
    {

        $dateStylized = new DateTime($prettyDate, new DateTimeZone("America/Detroit"));

        return $dateStylized->format("F j, Y");
    }

    public function __construct($args = [])
    {
//        $this->user_id = $args['user_id'] ?? null;
//        $this->author = $args['author'] ?? null;
//        $this->heading = $args['heading'] ?? null;
//        $this->content = $args['content'] ?? null;
//        $this->date_updated = $args['date_updated'] ?? null;
//        $this->date_added = $args['date_added'] ?? null;


        // Caution: allows private/protected properties to be set
        foreach($args as $k => $v) {
           if(property_exists($this, $k)) {
            $this->$k = $v;
            static::$params[$k] = $v;
            static::$objects[] = $v;
          }
        }
    } // End of construct method:

} // End of class:
Ass you can see your classes that use the DatabaseObject classes are pretty simple and the nice thing about it is that once you written the classes that it can be used in other projects with little or no modifications to them. To show that it works -> https://www.miniaturephotographer.com/
Life is a fig newton of your imagination! https://www.phototechguru.com/
Post Reply