Php Class - Database Operations (mysqli) | Grzegorz Białowąs. Full Stack Developer i Grafik

Very small class I have written to speed up basic Database operations. This works just fine with pretty simple db manipulations like SELECT, INSERT, UPDATE and DELETE, also known as CRUD operations.

CRUD stands for Create Read Update Delete.

The Function

db.class.php


<?php
$conn = mysqli_connect(HOST, USERNAME, PASSWORD, DATABASENAME);
/**
 * Check connection
 */
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

/**
 * Class postActions
 * Contains methods for common database operations (INSERT, SELECT, UPDATE, DELETE)
 */
class postActions
{
    /**
     * INSERT
     * Inserts records into the specified table in the database.
     *
     * @param string $f_table    The target table name.
     * @param array  $f_records  An associative array of field-value pairs to be inserted.
     */
    public function insert2db($f_table, $f_records)
    {
        global $conn;
        $count = 0;
        foreach ($f_records as $key => $val) {
            if ($count == 0) {
                $f_fields = $key;
                if ($val == '') {
                    $f_values = "''";
                } else {
                    $f_values = "'" . $val . "'";
                }
            } else {
                $f_fields .= ", " . $key;
                if ($val == '') {
                    $f_values .= ", ''";
                } else {
                    $f_values .= ", '" . $val . "'";
                }
            }
            $count++;
        }
        $query = mysqli_query($conn, "INSERT INTO " . $f_table . " (" . $f_fields . ") VALUES (" . $f_values . ")") or die(mysqli_connect_error());
    }

    /**
     * SELECT
     * Retrieves specific data from the database based on the provided conditions.
     *
     * @param string $f_what       The columns to retrieve.
     * @param string $f_table      The target table name.
     * @param string $f_condition  The condition for selecting specific records.
     * @param string $f_ekstra     Additional options for the SELECT query.
     *
     * @return mixed               The result of the SELECT query.
     */
    public function selectfromdb($f_what, $f_table, $f_condition = '', $f_ekstra = '')
    {$f_condition
        global $conn;

        if ($f_condition != '') {
            $f_condition = ' WHERE ' . $f_condition;
        }
        if ($f_ekstra != '') {
            $f_ekstra = ' ' . $f_ekstra;
        }
        $query = mysqli_query($conn, "SELECT " . $f_what . " FROM " . $f_table . $f_condition . $f_ekstra . "") or die(mysqli_connect_error());
        return $query;
    }

    /**
     * UPDATE
     * Updates records in the specified table based on the provided conditions.
     *
     * @param string $f_table      The target table name.
     * @param array  $f_records    An associative array of field-value pairs to be updated.
     * @param string $f_condition  The condition for updating specific records.
     */
    public function updatedb($f_table, $f_records, $f_condition)
    {
        global $conn;
        $count = 0;
        foreach ($f_records as $key => $val) {
            if ($count == 0) {
                $f_fields = $key . " = '" . $val . "'";
            } else {
                $f_fields .= ", " . $key . " = '" . $val . "'";
            }
            $count++;
        }
        $query = mysqli_query($conn, "UPDATE " . $f_table . " SET " . $f_fields . " WHERE " . $f_condition . "") or die(mysqli_connect_error());
    }

    /**
     * DELETE
     * Deletes records from the specified table based on the provided conditions.
     *
     * @param string $f_table      The target table name.
     * @param string $f_condition  The condition for deleting specific records.
     */
    public function deletefromdb($f_table, $f_condition)
    {
        global $conn;
        $query = mysqli_query($conn, "DELETE from " . $f_table . " WHERE " . $f_condition . "") or die(mysqli_connect_error());
    }
}

Usage

First we need to initialize the class:


<?php
include_once('db.class.php');
$action = new postActions();
/*  ***   $action can be replaced with any name of your liking   ***  */
?>

For the demonstration purposes let's assume there's a DB with a table Profile. Profile has only three columns: ID, FirstName and LastName.

INSERT demo


<?php
    $insert_array = array(
     'FirstName' => $_POST['FirstName'],
     'LastName' => $_POST['LastName'],
    );
    $action->insert2db('Profile', $insert_array);
?>

SELECT demo

Select all:


<?php
$q = $action->selectfromdb('*', 'Profile');
?>

Select all and list them in descending order by last name:


<?php
$q = $action->selectfromdb('*', 'Profile', '', 'ORDER BY LastName DESC');
?>

Select only a particular ID:


<?php
$q = $action->selectfromdb('*', 'Profile', 'id = '4'');
?>

Once we've selected what we wanted, the rest proceeds as a regular PHP / MySQL syntax:


<?php
while($row = mysqli_fetch_array($q)) {
..
..
..
}
?>

UPDATE demo


<?php
    $update_array = array(
     'FirstName' => $_POST['FirstName'],
     'LastName' => $_POST['LastName'],
    );
    $action->updatedb('Profile', $update_array, 'id = '' . $_GET['inputFieldsValue'] . ''');
?>

DELETE demo


<?php
$action->deletefromdb('Profile', 'id = '' . $_GET['inputFieldsValue'] . ''');
?>

And that's all!

Grzegorz Białowąs

Full Stack Developer