PHP MySQL Database Class

With this PHP MySQL database class you can establish a connection to your MySQL database and execute secure queries within a few seconds.

Simple, flexible and yet secure: This PHP MySQL database class uses the mysqli extension and prepared statements, which automatically prevents SQL injection attacks. This makes it ideal for beginners.

Simple and fast: PHP MySQL database class

For easier management of the code, I made a GitHub repository. You can use this class with any PHP version currently available (including PHP 8.3).

You are free to use this course for your projects at no cost, even for commercial purposes. You can also extend and change it as you desire. I would appreciate it if you could report any issues or pull requests on the repository. Let’s proceed to enhance this course continuously.

Step 1: Copy the database class into the project

For the first step, copy the entire class and create a new file in your project structure, e.g. Database.php with the following content:

<?php

/**
 * Simple PHP MySQLi Database class for PHP7.* & PHP8.*
 * ----------------------------------------
 * This class provides a simple way to connect to your database and execute your queries.
 * It uses prepared statements to prevent SQL injections.
 * 
 * @author LH
 * @link https://webdeasy.de/en/php-mysql-database-class
 */
class Database {

  private $host, $database, $username, $password, $connection;  // connection credentials
  private $port = 3306; // default port

  private $affected_rows;  // properties to save before executing next query

  /**
   * Sets the connection credentials to connect to your database.
   *
   * @param string $host - the host of your database
   * @param string $username - the username of your database
   * @param string $password - the password of your database
   * @param string $database - your database name
   * @param integer $port - the port of your database
   * @param boolean $autoconnect - to auto connect to the database after settings connection credentials
   */
  function __construct($host, $username, $password, $database, $port = 3306, $autoconnect = true) {
    $this->host = $host;
    $this->database = $database;
    $this->username = $username;
    $this->password = $password;
    $this->port = $port;

    if ($autoconnect) $this->open();
  }

  /**
   * Open the connection to your database.
   */
  function open() {
    $this->connection = new mysqli($this->host, $this->username, $this->password, $this->database, $this->port);
  }

  /**
   * Close the connection to your database.
   */
  function close() {
    $this->connection->close();
  }

  /**
   *
   * Execute your query
   *
   * @param string $query - your sql query
   * @param array $parameters - your parameters to bind to your query
   * @return mysqli_result result of the executed query 
   */
  function query($query, $parameters = array()) {
    // reset data of last query
    $this->affected_rows = 0;

    // prepare the query
    $stmt = $this->connection->prepare($query);

    // check if prepare statement failed
    if ($stmt === false) {
      die("Error in prepare statement: " . $this->connection->error);
    }

    // check if parameters are given
    if (!empty($parameters)) {
      $types = "";
      $bindParams = [];

      // get the types of the parameters
      foreach ($parameters as $param) {
        if (is_int($param)) {
          $types .= "i";
        } elseif (is_double($param)) {
          $types .= "d";
        } else {
          $types .= "s";
        }
        $bindParams[] = $param;
      }

      // bind the parameters to the query
      $stmt->bind_param($types, ...$bindParams);
    }

    // execute the query
    if ($stmt->execute()) {
      $result = $stmt->get_result();
      $this->affected_rows = $stmt->affected_rows;
      $stmt->close();
      return $result;
    } else {
      die("Error executing query: " . $stmt->error);
    }
  }

  /**
   * Get the amount of affected rows of the last executed query.
   * @return integer
   */
  function get_affected_rows() {
    return $this->affected_rows;
  }

  /**
   * Get the last inserted id of the last executed query.
   * @return integer
   */
  function get_last_inserted_id() {
    return $this->connection->insert_id;
  }
}

Step 2: Include PHP MySQL database class

The rest of your program code should be written in another PHP file. There you insert the following code to include the class:

require_once("path/to/your/file/Database.php");

Step 3: Create database instance

To connect to your MySQL database, we need to create an instance of the class. To do this we need the access data to the database. You can find the optional parameters of the structure in the Readme. A call could look like this:

$database = new Database("localhost", "testuser", "verySafePassword", "yourDatabase");

An attempt is made to establish a connection to the database using the access data provided. This happens automatically if the $autoconnect parameter is set to true (see line 27).

If you don’t get any errors, the connection to the database has been established successfully. Now you can formulate your SQL queries.

Step 4: Run your own SQL queries

You can pass the queries to the query($query, $params) function. The query is sent to the database and the function returns the result as a result.

The function expects two parameters: the query string and an array with parameters. You replace all values with a ? in your query. These serve as placeholders and are passed in the $params array. The number of question marks and the number of parameters of the $params array must match. I think this becomes most understandable if we look at code examples.

Example: Insert Query

$query = "INSERT INTO users (id, name) VALUES (?, ?);";
$params = array(1, "Peter Parker");

$database->query($query, $params);

Example: Select Query

$query = "SELECT name FROM users WHERE id = ?";
$params = array(1);

$result = $database->query($query, $params);

while($row = mysqli_fetch_assoc($result)) {
  echo "Name: " . $row["name"];
}

Example: Update Query

$query = "UPDATE users SET name = ?, nickname = ?, updated = now() WHERE id = ?";
$params = array("Tony Stark", "Iron Man", 42);

$result = $database->query($query, $params);

Example: Delete Query

$query = "DELETE FROM users WHERE registered < ?";
$params = array(date("Y-m-d"));  // returns the current date

$result = $database->query($query, $params);

Example: Read out the last inserted ID

$query = "INSERT INTO users (id, name) VALUES (?, ?);";
$params = array(1, "Peter Parker");

$database->query($query, $params);

echo $database->get_last_inserted_id();

Example: Read out changed lines

$query = "DELETE FROM users WHERE id = ?";
$params = array(42);

$result = $database->query($query, $params);

echo "Deleted rows: " . $database->get_affected_rows();

Note: This function returns the value for affected_rows and num_rows.

You should also call the close() function at the end of your program to close open database connections.

$database->close();

Conclusion

This PHP MySQL database class is as small as possible and only provides the most necessary functions. This class should be exactly right for you, especially as a beginner. Nevertheless, all safety-relevant aspects are of course taken into account.

Descriptions of all functions can be found here in the GitHub repository. Security information or suggestions for improvement are welcome as an issue in the GitHub repository.