Simple PHP MySQL Database Class (+ Download)

With this PHP MySQL (MySQLi) database class you don’t have to write annoying code over and over again, but can easily use this class in the future.

This class is designed to be simple and flexible for each of your projects. True to the motto: Keep it small, keep it simple.

I have also implemented a method to prevent SQL injection attacks. You should definitely use this method to protect yourself from potential attacks.

As of PHP 7.0 the obsolete MySQL module (mysql_) has been removed, so you have to fall back to MySQLi (mysqli_). Alternatively you can use the MySQL PDO interface. Both have advantages and disadvantages. For example, the use of prepared statements is supported by PDO.

This class is usable for all current PHP versions (including PHP 8.0) – April 2021.

You may use the class below free of charge for your projects (also commercial) and of course you may extend and change it as you like. And this is how you use it:

1. Copy database class into the project

For the first step you copy the complete class and create a new file in your project structure, e.g. Database.php. Then copy the complete code below into this file.

<?php
/**
* Simple MySQLi Database class for PHP5.*, PHP7.* & PHP8.*
* The class contains main functions for your database. For a detailed documentation, see: https://webdeasy.de/en/php-database-class
* created 08.11.2017
* updated: 17.04.2021
* 
* @author LH
*/
class Database {

	private $host, $database, $username, $password, $connection;
	private $port = 3306;
	
	/**
	* 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
	* @return the result of the executed query 
	*/
	function query($query) {
		return $this->connection->query($query);
	}

	/**
	* Escape your parameters to prevent SQL Injections! Usage: See documentation (link at the top of the file)
	*
	* @param string $string - your parameter to escape
	* @return the escaped string 
	*/
	function escape($string) {
		return $this->connection->escape_string($string);
	}
}
?>

2. Include database class

Your program code runs in a different PHP file. There you add the following code to include the class:

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

What is the difference between require and include? That!

3. Create instance

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

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

With the transferred access data an attempt is made to establish a connection to the database. This happens automatically if the parameter $autoconnect is set to true (see line 25).

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

4. Escape parameters!

This is probably the most important step. To prevent SQL injections, every parameter that you use in the query must be masked (escaped). This prevents harmful SQL statements – whether intentional or unintentional – from getting into your query and thus into your server system. The following lines should always be placed before each query:

$parameter = $_REQUEST["id"];
$parameter = $database->escape($parameter);

In this example the parameter id is passed to PHP by request and masked by the second line.

5. Execute own SQL queries

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

Example of an insert query:

$query = "INSERT INTO users (id, name) VALUES (1, 'Peter Parker');";
$database->query($query);

Example of a select query with output of the result:

$query = "SELECT name FROM users WHERE id = 1";
$result = $database->query($query);

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

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

$database->close();

In the end, these are the functions of the database class. I kept them as simple as possible, but still tried to include all important and security relevant functions. And yes: I am aware that PHP and the MySQLi class has a lot more functions to offer. But this class is especially designed for simple, fast and flexible applications that only require a simple database connection.

Safety instructions or suggestions for improvement are welcome in the comments, so that I can add them! 🙂