PHP CRUD functions to extract data from mysql

If you're looking to build a web application, one of the fundamental tasks you'll need to tackle is creating a file that can handle basic CRUD operations. CRUD stands for Create, Read, Update, and Delete, and refers to the four primary tasks that any database application must perform.

In this tutorial, we'll walk you through the process of building a basic CRUD file from scratch. Our file will allow users to add, view, edit, and delete records in a database, using PHP and SQL. We'll cover each step of the process in detail, from setting up the database to creating the user interface.

By the end of this tutorial, you'll have a fully functioning CRUD file that you can use as a starting point for your own web applications. Whether you're new to programming or an experienced developer, this tutorial is a great way to learn the basics of CRUD operations and get hands-on experience building a web application. So let's get started!

Create:

$result = $init->db_create('classfellows',array('roll'=>'123','cnic'=>'3840138476667')));

Update:

$result = $init->db_update('classfellows',array('class'=>'16'),array('roll'=>'1222222222'));

Delete:

$result = $init->db_delete('classfellows',array('roll'=>'1222222222'));

Select:

$result = $init->db_get('classfellows',array('roll'=>'123'),50);

CODE FILE

JUST IMPORT THE BELOW CODE AND USE ITS FUNCTIONS

<?php
$servername = "localhost";
$username = "root";
$password = "";
$db = "top4u";
$conn = new mysqli($servername, $username, $password, $db); // Create connection

if ($conn->connect_error) { // Check connection
    die("Connection failed: " . $conn->connect_error);
}

// Function to add quotes around the values for the SQL query
function addQuote($v) {
    return "'" . $v . "'";
}

class Index {
    function __construct($conn) {
        $this->conn = $conn;
    }

    // Function to retrieve data from the database
    public function db_get($table = null, $where = null, $limit = 50) {
        if ($table == null) {
            $error = json_encode(array("result" => false, "message" => "table can't be empty"));
            return $error;
        }

        $sql = "SELECT * FROM $table ";
        if ($where) {
            $sql .= "WHERE ";
            foreach ($where as $key => $value) {
                $sql .= " $key = '$value' AND";
            }
        }
        $sql = rtrim($sql, "AND");
        $sql .= " LIMIT $limit";

        $records = array();
        $result = $this->conn->query($sql);
        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $records[] = $row;
            }
            return $records;
        } else {
            $error = json_encode(array("result" => false, "message" => "No record found with command $sql"));
            return $error;
        }
    }

    // Function to insert data into the database
    public function db_create($table = null, $array = null) {
        if ($table == null) {
            $error = json_encode(array("result" => false, "message" => "table can't be empty"));
            return $error;
        } elseif (!is_array($array)) {
            $error = json_encode(array("result" => false, "message" => "Array is invalid "));
            return $error;
        }
        $columns = implode(",", array_keys($array));
        $escaped_values = array_map("addQuote", array_values($array));
        $values = implode(',', $escaped_values);
        $sql = "INSERT INTO $table ($columns) VALUES ($values)";

        if ($this->conn->query($sql)) {
            return true;
        } else {
            return false;
        }
    }

    // Function to update data in the database
    public function db_update($table = null, $array = null, $where = null) {
        if ($table == null) {
            $error = json_encode(array("result" => false, "message" => "table can't be empty"));
            return $error;
        } elseif (!is_array($array)) {
            $error = json_encode(array("result" => false, "message" => "Array is invalid "));
            return $error;
        } elseif (!is_array($where) || empty($where)) {
            $error = json_encode(array("result" => false, "message" => "Where Array is invalid "));
            return $error;
        }

        $sql = "UPDATE $table SET ";
        foreach ($array as $key => $value) {
            $sql.=" $key = '$value' ,";
        }
        $sql = rtrim($sql,",");
        $sql .= " WHERE ";
        foreach ($where as $key => $value) {
            $sql .= " $key = '$value' AND";
        }
        $sql = rtrim($sql, "AND");
        if ($this->conn->query($sql)) {
            return true;
        } else {
            return false;
        }
    }

    // Function to delete data from the database
    public function db_delete($table = null, $where = null) {
        if ($table == null) {
            $error = json_encode(array("result" => false, "message" => "table can't be empty"));
            return $error;
        } elseif (!is_array($where) || empty($where)) {
            $error = json_encode(array("result" => false, "message" => "Where Array is invalid "));
            return $error;
        }

        $sql = "DELETE FROM $table ";
        $sql .= "WHERE ";
        foreach ($where as $key => $value) {
            $sql .= " $key = '$value' AND";
        }
        $sql = rtrim($sql, "AND");
        if ($this->conn->query($sql)) {
            return true;
        } else {
            return false;
        }
    }

    // Function to run custom SQL queries
    public function wp_db_query($sql) {
        $results = $this->conn->query($sql);
        if ($results->num_rows > 0) {
            return $results;
        } else {
            return false;
        }
    }
}

$init = new Index($conn);

//$result = $init->db_get('classfellows',array('roll'=>'123'),50);

//$result = $init->db_create('classfellows',array('roll'=>'123','cnic'=>'3840138476667')));

//$result = $init->db_update('classfellows',array('class'=>'16'),array('roll'=>'1222222222'));

//$result = $init->db_delete('classfellows',array('roll'=>'1222222222'));