We have our database connection, our database, and our table. We just need to start adding some data to it. This is a basic functionality that you’ll need to do constantly. Each time a form is submitted, where’s that data going to go? Will it be emailed or will it be stored in a database?

Let’s do this the right way: the object oriented way. Our assignment for this article will be to connect to a database and insert an author into our authors table.

If you need to review how we got to this point, check out the previous two articles.

https://blog.devgenius.io/php-p83-mysql-db-connection-8a34c4056863
https://blog.devgenius.io/php-p84-mysql-tables-83b203d28ff7

Step 1: Creating a Database Connection

To keep consistent with our Object Oriented Principles, we’ll move our script into a class.

<?php
namespace Database;

use mysqli;

class DB
{
    private string $_server   = "localhost";
    private string $_username = "admin";
    private string $_password = "OOwfvsr2soGW";
    private string $_database = "dino_test_db";

    public mysqli $_connection;

    public function __construct()
    {
        $this->connect();
        return $this->_connection;
    }

    public function connect()
    {
        $this->_connection = new mysqli(
            $this->_server,
            $this->_username,
            $this->_password,
            $this->_database
        );

        if ( $this->_connection->connect_error ) {
            die( $this->_connection->connect_error );
        }
    }

    public function insert( string $sql ): string
    {
        if ($this->_connection->query($sql) === TRUE) {
            return "Inserted successfully";
        } else {
            return $this->_connection->error;
        }
    }

    public function close()
    {
        $this->_connection->close();
    }
}

Looks like a lot going on, but it’s really straight forward. The class, DB, contains the following:

• A Database namespace. It’s just a virtual directory that I made up. How? Just as you’re seeing it. Place the word namespace and whatever you want. Done.
• Since we’re now in a virtual directory, we have to tell PHP that we’re going to be using mysqli.
• The class was named DB.
• The required properties were set, which are the server, username, password, and database.
• There is also one additional property, $_connection. Once we instantiate our mysqli object, we’ll store it there.
• The connect method just uses our properties to establish our connection to the database. The intsantiated mysqli object is stored in the $_connection property.
• When the DB constructor is called, it’s going to call the connect method, so that the connection to the database can be established, and will then return the $_connection property that holds the instantiated mysqli object.
• The class holds an insert method. The insert method expects a query to be passed to it. It executes the query and returns Inserted Successfully if everything was good. The sql script that it expects will be covered shortly.
• The close method just closes a connection to the database.

We can test our connection like this:

require_once("DB.php");

use Database\DB;
$db = new DB;
$db->close();

If you don’t get any errors, you did it right.

Step 2: Creating the Insert SQL Script

The syntax is pretty straightforward for insertion.

INSERT INTO table_name (first_column, second_column, ...)
VALUES (value_for_first_column, value_for_second_column, ...);

The syntax can be broken down into:

• table_name is the table name, such as authors, where data will be going into.
• first_column, second_column are the column titles, such as first_name, last_name, and email.
• value_for_first_column, value_for_second_column are the column values that will be inserted into the column titles that you just outlined. For example Dino for your first_name and Cajic for your last_name.

Let’s create a test file and pass the SQL script to our insert method.

require_once("DB.php");

use Database\DB;
$sql = "INSERT INTO authors (first_name, last_name, email) 
        VALUES ('Dino', 'Cajic', 'dino@example.com')";
$db = new DB;
$db->insert( $sql );
$db->close();

You’ve just successfully inserted your first author record. But, let’s go a step further and introduce an Author object.

Step 3: Creating the Author Object

The Author object will contain information about the author. We’ll build on this object as we progress. For now, we’ll just have it insert an author into the authors table.

<?php
namespace Authors;

require_once("DB.php");

use Database\DB;

class Author
{
    public function insert( array $data )
    {
        $sql = "INSERT INTO authors (first_name, last_name, email) 
                VALUES (
                        '" . $data['first_name'] . "', 
                        '" . $data['last_name'] . "', 
                        '" . $data['email'] .  "')";

        $db = new DB;
        $db->insert($sql);
        $db->close();
    }
}

Time to walk through the code:

• The Author will be actually inserting the author’s details into the database, so it will need access to the DB.
• The only method that it needs right now is the insert method. It’s going to accept an array of data, which should hold the first_name, last_name, and email.
• The insert method than constructs the SQL query for the INSERT statement, connects to the DB, and inserts it into the authors table.

We’re not doing any checks at the moment. We just want to pretend like all of the data coming in is perfect. We’ll add some checks later.

Our test file now starts to look like this:

<?php
require_once("Author.php");

use Authors\Author;

$data = [
    "first_name" => "Dino",
    "last_name"  => "Cajic",
    "email"      => "dino@example.com"
];

$author = new Author;
$author->insert($data);

We created an array of data that holds the necessary information. The Author is instantiated and the $data is passed to the insert method. And viola, it works.

Some Final Cleanup

Our DB::insert statement returns an Inserted Successfully string when everything is good or an error when the insertion fails. Let’s provide that feedback to the user.

We set the $db->insert($sql) response to be stored in the $status variable and echo that variable at the end of our Author::insert method. We should now see Inserted Successfully when we reload our page. Check your authors table as well. There you are.

Next

A little excessive for just the INSERT statement, but I wanted to make sure that we have a foundation that we can build on for the next few articles. It’s also a nice recap of the various different functionality that we’ve worked on aimlessly in the previous articles, like namespaces and object oriented programming in general.

As a final note, this is not the right way to do this. This exposes us to many vulnerabilities. We’ll take a look at how to tighten the rains in the next article.