Infusing Tables with Fresh Data Energy
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 ourmysqli
object, we’ll store it there. - The
connect
method just uses our properties to establish our connection to the database. The intsantiatedmysqli
object is stored in the$_connection
property. - When the
DB
constructor is called, it’s going to call theconnect
method, so that the connection to the database can be established, and will then return the$_connection
property that holds the instantiatedmysqli
object. - The class holds an
insert
method. Theinsert
method expects a query to be passed to it. It executes the query and returnsInserted Successfully
if everything was good. Thesql
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 asauthors
, where data will be going into.first_column
,second_column
are the column titles, such asfirst_name
,last_name
, andemail
.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 exampleDino
for yourfirst_name
andCajic
for yourlast_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 theDB
. - The only method that it needs right now is the
insert
method. It’s going to accept an array of data, which should hold thefirst_name
,last_name
, andemail
. - The
insert
method than constructs theSQL
query for theINSERT
statement, connects to theDB
, and inserts it into theauthors
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.
MYSQL TABLES NEATLY STORE DATA IN ROWS AND COLUMNS
It’s time to create some tables. If you remember the previous analogy, the database can be thought of as an Excel Workbook and the tables can be thought of as sheets within the Workbook.
Infusing Tables with Fresh Data Energy
When you have a database connection setup, the next logical step is to insert data into the tables. You handle that with the MySQL insert command.
MYSQL’S SHIELDED DATA TRANSFORMATION
PHP – P86: MYSQL PREPARED STATEMENTS
There’s one big flaw in our previous MySQL insert statement: it’s open to SQL Injection Attacks. Prepared statements virtually eliminate that concern