Php Class - Database Operations (pdo) | Greg Bialowas. Full Stack Developer and Designer

CRUD stands for Create Read Update Delete.

The PDO version of the CRUD operations

First, you need to initiate the DB connection:


<?php
 try
 {
     $DBcon = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USER, DB_PASS);
     $DBcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 }
 catch(PDOException $e)
 {
     echo 'ERROR : '.$e->getMessage();
     die('Something went wrong.');
 }

And then use it! View MySQL CRUD operation for details.


<?php
/**
 * Class PDO_Actions
 * A class for performing common PDO database actions.
 */
class PDO_Actions {

    /**
     * @var PDO $DBcon The PDO instance used for database operations.
     */
    private $DBcon;

    /**
     * Constructor method that initializes the PDO_Actions object with a PDO instance.
     *
     * @param PDO $DBcon The PDO instance to be used for database operations.
     */
    public function __construct(PDO $DBcon) {
       $this->DBcon = $DBcon;
    }

    /**
     * Inserts records into the specified database table.
     *
     * @param string $t_table Name of the target database table.
     * @param array $t_records Associative array of column-value pairs to be inserted.
     */
    function insert_2_db($t_table, $t_records)
    {
        $count = 0;
        $yet_another_array = array();

        foreach ($t_records as $key => $val)
        {
            if ($count === 0) {
              $t_db_fields = $key;
              $t_db_fields_with_colon = ':'.$key;
              if ($val === '') $t_values = "''"; else $t_values = "'" . $val . "'";
            }
            else {
              $t_db_fields .= ", " . $key;
              $t_db_fields_with_colon .= ', ' . ':'.$key;
              if ($val === '') $t_values .= ", ''"; else $t_values .= ", '" . $val . "'";
            }
        $count++;
        $yet_another_array[':'.$key] = $val;
        }

        $sql = "INSERT INTO " . $t_table . " (" . $t_db_fields . ") VALUES (" . $t_db_fields_with_colon . ")";
        $pdo_statement = $this->DBcon->prepare( $sql );
        $pdo_statement->execute( $yet_another_array );
    }

    /**
     * Retrieves and returns a single value from the database based on the specified conditions.
     *
     * @param string $co Column name from which to retrieve the value.
     * @param string $t_table Name of the target database table.
     * @param string $t_condition (Optional) Additional conditions for the SQL WHERE clause.
     * @param string $ekstra (Optional) Additional query options.
     * @return mixed The retrieved value.
     */
    function print_from_db($co, $t_table, $t_condition='', $ekstra='')
    {
        $q = $this->select_from_db($co, $t_table, $t_condition, $ekstra);
        foreach($q as $row) {
            return $row[$co];
        }
    }

    /**
     * Retrieves and returns selected data from the database based on the specified conditions.
     *
     * @param string $co Column name(s) to be selected.
     * @param string $t_table Name of the target database table.
     * @param string $t_condition (Optional) Additional conditions for the SQL WHERE clause.
     * @param string $ekstra (Optional) Additional query options.
     * @return array An array of associative arrays representing the selected rows.
     */
    function select_from_db($co, $t_table, $t_condition='', $ekstra='')
    {
        $t_condition = ($t_condition !== '') ? ' WHERE ' . $t_condition : '';
        $ekstra = ($ekstra !== '') ? ' ' . $ekstra : '';

        $pdo_statement = $this->DBcon->prepare("SELECT " . $co . " FROM " . $t_table . $t_condition . $ekstra);
        $pdo_statement->execute();
        return $pdo_statement->fetchAll();
    }


    /**
     * Updates records in the specified database table based on the given conditions.
     *
     * @param string $t_table Name of the target database table.
     * @param array $t_records Associative array of column-value pairs to be updated.
     * @param string $t_condition Conditions for the SQL WHERE clause.
     */
    function update_db($t_table, $t_records, $t_condition)
    {
        $count = 0;
        foreach ($t_records as $key => $val)
        {
            if ($count === 0) {
                $t_db_fields = $key . " = '" . $val . "'";
            }
            else {
                $t_db_fields .= ", " . $key . " = '" . $val . "'";
            }

            $count++;
        }
        $pdo_statement = $this->DBcon->prepare("UPDATE " . $t_table . " SET " . $t_db_fields . " WHERE " . $t_condition . "");
        $pdo_statement->execute();
    }

    /**
     * Deletes records from the specified database table based on the given conditions.
     *
     * @param string $t_table Name of the target database table.
     * @param string $t_condition Conditions for the SQL WHERE clause.
     */
    function delete_from_db($t_table, $t_condition)
    {
      $pdo_statement = $this->DBcon->prepare("DELETE from " . $t_table . " WHERE " . $t_condition . "");
      $pdo_statement->execute();
    }
}

Somewhere in your code you need to activate the class:


<?php
$action = new PDO_Actions($DBcon);

Example usage:


<?php
$q = $action->select_from_db('*', 'table', 'column = ' . $variable );

And that's all!

PHP Class - Database operations (PDO)