MySQLi Reusable class for PHP

Creating a reusable class for common CRUD (Create, Read, Update, Delete) operations using MySQLi is a great idea for maintainability and scalability. Here’s how you can design such a class:

---

1. Database Class Design

The class will:

• Establish a database connection.
• Provide methods for CRUD operations.
• Use prepared statements for security.

2. Implementation

Here’s an example of a reusable Database class:

<?php
class Database {
    private $host = "localhost";
    private $user = "root";
    private $password = "";
    private $database = "your_database_name";
    private $conn;

    // Constructor: Establish database connection
    public function __construct() {
        $this->conn = new mysqli($this->host, $this->user, $this->password, $this->database);

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

    // Create or Insert
    public function insert($query, $params, $types) {
        $stmt = $this->conn->prepare($query);
        if ($stmt === false) {
            die("Prepare failed: " . $this->conn->error);
        }

        $stmt->bind_param($types, ...$params);
        $stmt->execute();

        if ($stmt->affected_rows > 0) {
            $result = $stmt->insert_id; // Return the last inserted ID
        } else {
            $result = false;
        }

        $stmt->close();
        return $result;
    }

    // Read or Select
    public function select($query, $params = [], $types = "") {
        $stmt = $this->conn->prepare($query);
        if ($stmt === false) {
            die("Prepare failed: " . $this->conn->error);
        }

        if (!empty($params)) {
            $stmt->bind_param($types, ...$params);
        }

        $stmt->execute();
        $result = $stmt->get_result();

        $data = [];
        while ($row = $result->fetch_assoc()) {
            $data[] = $row;
        }

        $stmt->close();
        return $data;
    }

    // Update
    public function update($query, $params, $types) {
        $stmt = $this->conn->prepare($query);
        if ($stmt === false) {
            die("Prepare failed: " . $this->conn->error);
        }

        $stmt->bind_param($types, ...$params);
        $stmt->execute();

        $result = $stmt->affected_rows > 0;

        $stmt->close();
        return $result;
    }

    // Delete
    public function delete($query, $params, $types) {
        $stmt = $this->conn->prepare($query);
        if ($stmt === false) {
            die("Prepare failed: " . $this->conn->error);
        }

        $stmt->bind_param($types, ...$params);
        $stmt->execute();

        $result = $stmt->affected_rows > 0;

        $stmt->close();
        return $result;
    }

    // Destructor: Close the connection
    public function __destruct() {
        $this->conn->close();
    }
}

---

3. Using the Class

Here’s how you can use the class in your project:

Insert Example

$db = new Database();
$query = "INSERT INTO users (name, email) VALUES (?, ?)";
$params = ["John Doe", "john.doe@example.com"];
$types = "ss"; // Two strings

$insertId = $db->insert($query, $params, $types);
if ($insertId) {
    echo "New record inserted with ID: $insertId";
} else {
    echo "Insert failed.";
}

Select Example

$query = "SELECT * FROM users WHERE email = ?";
$params = ["john.doe@example.com"];
$types = "s";

$result = $db->select($query, $params, $types);
if ($result) {
    print_r($result);
} else {
    echo "No records found.";
}

Update Example

$query = "UPDATE users SET name = ? WHERE email = ?";
$params = ["Jane Doe", "john.doe@example.com"];
$types = "ss";

$isUpdated = $db->update($query, $params, $types);
if ($isUpdated) {
    echo "Record updated successfully.";
} else {
    echo "Update failed.";
}

Delete Example

$query = "DELETE FROM users WHERE email = ?";
$params = ["john.doe@example.com"];
$types = "s";

$isDeleted = $db->delete($query, $params, $types);
if ($isDeleted) {
    echo "Record deleted successfully.";
} else {
    echo "Delete failed.";
}

---

4. Advantages of This Class

1. Reusability: You can use the same class across your project for all database operations.
2. Security: Prepared statements prevent SQL injection.
3. Efficiency: The class is lightweight and handles common scenarios efficiently.
4. Scalability: You can add more methods for complex queries or transactions.

Let me know if you want additional features or customizations!