Flexible PHP 7- MySQLi database class (+ download)

Flexible PHP 7- MySQLi database class (+ download)

We all know it: Write annoying code over and over again! That’s over now. With this PHP MySQLi database class you can easily connect to your MySQL database.

This class is very simple and flexible for any project. It also ensures that no SQL-Injectionattacks can be carried out. You may use the class below freely for your projects (also commercially) and of course extend it as you like. And so you use it:

1. Include class in your project structure

You copy the complete class and create a new file in your project structure, e.g. Database.php and paste the complete code there.

<?php
/**
* Simple Database class for PHP7+
* The class contains main functions for your database. For a detailed documentation, see: https://webdeasy.com/
* created 08.11.2017
* 
* @author LH
*/
class Database {
  private $host, $database, $username, $password, $connection;
  private $port = 3306;

/** *

  • Sets the connection credentials to connection 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 parameter
  • @param string $string - your parameter to escape
  • @return the escaped string
  • / function escape($string) { return $this->connection->escape_string($query); } } ?>

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’s the different between require and includeThis!

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 construct in the class. A call could look like this:

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

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

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

4. Escape parameters!

That’s probably the most important step. To prevent SQL injections, every parameter you use in the query must be escaped. This can prevent harmful SQL statements – whether intentional or unintentional – from getting into your query and thus into your server system. The following lines must be inserted before each query:

$parameter = $_POST["id"];
$parameter = $db->escape($parameter);

In this example, the id parameter of the POST request is passed to PHP and escaped by the second line.

5. Your queries

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

Example of an Insert Query

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

Example of a select query with output of the result

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

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

At the end of your program you should call the close() function to close open database connections.

$db->close();

Ultimately, 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 have much more functions to offer. But this class is just 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! 

Originally published at webdeasy.de on 26. May 2019

====================================================================

Thanks for reading :heart: If you liked this post, share it with all of your programming buddies! Follow me on Facebook | Twitter


Learn More

☞ Ultimate PHP Basics for Absolute Beginners - [200+ PHP Code]

☞ PHP in Web Development in 2020

☞ PHP for Beginners - Become a PHP Master - CMS Project

☞ Learn Object Oriented PHP By Building a Complete Website

☞ MEVP Stack Vue JS 2 Course: MySQL + Express.js + Vue.js +PHP

☞ Object Oriented PHP & MVC

☞ PHP OOP: Object Oriented Programming for beginners + Project

☞ Learn PHP Fundamentals From Scratch

☞ The Complete PHP MySQL Professional Course with 5 Projects

☞ The Complete JavaScript Course 2019: Build Real Projects!

php mysql database

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

Compare ScaleGrid MySQL vs. DigitalOcean Managed Databases - See which offers the best MySQL throughput, latency, and pricing on DigitalOcean across workloads.

PHP School Management System (PHP Web Application With MySQL Database)

School Management System In PHP (Manage Students, Teachers, Exams and Much More). This Web Application Project is build with PHP and JavaScript programming Language and MySQL Database.

PHP MySQL Tutorial - User Registration System Using PHP & MySQL

In this PHP MySQL Tutorial, you'll learn how to create a user registration and login system using PHP and a MySQL database.

How to Make Mobile Shopee using PHP and MySQL Database

In this complete E-commerce website, you'll learn how to create Mobile Shopee Using PHP and MySQL Database. We gonna start by creating an HTML template then convert it into PHP and then use MySQL database to fetch products and display it in the project.

Shopping Cart System with PHP and MySQL Database

We'll be creating a shopping cart system with PHP and MySQL Database. Using the Bootstrap card we gonna display information about the shopping cart products.