PHP Class - Database operations (MySQLi)

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!

Scroll to Top
Wordpress I've lately been using:
  • Wordpress
  • PHP
  • CSS
  • jQuery

Summary: Font-end and Back-end developer, also designer with 25+ years of experience. Currently based in Poland. Eligible to work in the US (US Social Security Number holder) for any employer on W2 / 1099 basis. Utilizes both technical skills and designing aptitude. Lived and worked in Europe, Australia and North America.

PHP CMS HTML5 CSS3 RWD OOP MySQL PDO JS jQuery JSON GIT Bitbucket GitHub Gulp