Categories:
PDO php

Abstract PDO Class with CRUD operation

21 August 2023
Author: victor-dev
<?php
class Database_PDO
{
    private $server = Config::MYSQL_SERVER;
    private $user = Config::MYSQL_USERNAME;
    private $pass = Config::MYSQL_PASSWORD;
    public $pdo;

    public function __construct($dbName = null)
    {
        if ($dbName === null) {
            $dbName = Config::MYSQL_DB; // Default database name
        }

        $dsn = "mysql:host=$this->server;dbname=$dbName;charset=utf8mb4";

        try {
            $this->pdo = new PDO($dsn, $this->user, $this->pass);
        } catch (PDOException $e) {
            die("Connection failed: " . $e->getMessage());
        }

        $this->pdo->query("SET NAMES utf8");

    }

    ### SELECT FROM DATABASE (STANDARD QUERY)
    public function select($query)
    {
        $stmt = $this->pdo->prepare($query);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    ### INSERT IN DATABASE (SPECIFY TABLE AND ARRAY WITH : KEY FOR COLUMN and VALUE FOR VALUE)
    public function insert($table, $data)
    {
        $keys = implode(', ', array_keys($data));
        $values = implode(', ', array_fill(0, count($data), '?'));

        $query = "INSERT INTO $table ($keys) VALUES ($values)";

        $stmt = $this->pdo->prepare($query);
        $stmt->execute(array_values($data));

        return $stmt->rowCount();
    }

    ### UPDATE DATABASE (SPECIFY TABLE, ARRAY AND CONDITION: KEY FOR COLUMN and VALUE FOR VALUE)
    public function update($table, $data, $where)
    {
        $set = [];
        foreach ($data as $column => $value) {
            $set[] = "$column = ?";
        }
        $setClause = implode(', ', $set);

        $whereClause = '';
        if (!empty($where)) {
            $whereColumns = [];
            foreach ($where as $column => $value) {
                $whereColumns[] = "$column = ?";
            }
            $whereClause = ' WHERE ' . implode(' AND ', $whereColumns);
        }

        $query = "UPDATE $table SET $setClause $whereClause";
        $values = array_merge(array_values($data), array_values($where));

        $stmt = $this->pdo->prepare($query);
        $stmt->execute($values);

        return $stmt->rowCount();
    }

    ### DELETE FROM DATABASE (SPECIFY TABLE, CONDITION: KEY FOR COLUMN and VALUE FOR VALUE)
    public function delete($table, $where)
    {
        $whereClause = '';
        if (!empty($where)) {
            $whereColumns = [];
            foreach ($where as $column => $value) {
                $whereColumns[] = "$column = ?";
            }
            $whereClause = ' WHERE ' . implode(' AND ', $whereColumns);
        }

        $query = "DELETE FROM $table $whereClause";
        $values = array_values($where);

        $stmt = $this->pdo->prepare($query);
        $stmt->execute($values);

        return $stmt->rowCount();
    }

    ### GET CONNECTION
    public function getConnection()
    {
        return $this->pdo;
    }
}