DEV Community: Ahmed AbdElLatif

I made a package to manage database connection, it was awesome experience!

Ahmed AbdElLatif — Fri, 06 Aug 2021 17:00:24 +0000

I was working on side project to explore APIs in native PHP, when I realized that I can make reusable piece of code to manage database connection in process of practicing my php OOP skills.

So I started to think about writing a package that handle database connection and queries for native php projects, so I created Envelophp, hereunder how I approached this side-project.

I tried while creating Envelophp to apply DRY concept, so basically the idea is to have database credentials added in .env file (which is added to .gitignore to avoid credentials leakage).

#Database#
HOST = "localhost"
DATABASE_DSN = "mysql"
DATABASE_NAME = "sample_db"
DATABASE_USERNAME = "sample_username"
DATABASE_PASSWORD = "sample_password"

Then I made within Environment Variables Class a method to parse .env file and store credentials provided in it. NB: I plan to extend to more variables other than database credentials.

public function getDatabaseVariables()
    {
        try {
            //open .env file in read mode
            $file = fopen(dirname(__DIR__, 4) . '/.env', "r");

            while (($line = fgets($file)) !== false) {
                $validate_line_from_env_file = preg_match('~^[a-zA-Z]~', $line);
                if ($validate_line_from_env_file) {
                    $env_file_text = explode('= "', $line);
                    $env_variable_key_length = strpos($env_file_text[0], " ");
                    $env_variable_key = substr($env_file_text[0], 0, $env_variable_key_length);
                    $env_variable_value_length = strpos($env_file_text[1], "\"");
                    $env_variable_value = substr($env_file_text[1], 0, $env_variable_value_length);
                    if ($env_variable_value == true) {
                        $this->variables += [$env_variable_key => $env_variable_value];
                    } elseif ($env_variable_value === "") {
                        $this->variables += [$env_variable_key => ""];
                    }

                }
            }
            fclose($file);
            return $this->variables;
        } catch (Error $error) {
            return "Error: $error->getMessage()";
        }
    }

After that I inherited Environment Variables Class in a MysqlDatabase Class to make use of the variables to make all required functions related to mysql database as following:

1- Create Connection

Simply require autoload.php in your file where you want to connect to your database.
Then use the MysqlDatabase namespace
Now you are ready to create a new instance of MysqlDatabase.
to make a database connection call getConnection().

<?php
require_once dirname(__FILE__).'/vendor/autoload.php';
use Envelope\Database\MysqlDatabase;

$database = new MysqlDatabase();
$database->getConnection();

2- CRUD statements using binders:

Simply write native mysql statement passing any arguments using binders array.
Take this example of creating a new user.

$binders = [":userName" => $userName, ":userPassword" => $userPassword, ":userEmail" => $userEmail];

$database->insert("INSERT INTO users (userName, userPassword, userEmail)
                                    VALUES (:userName, :userPassword, :userEmail)",$binders);

3- Close database connection by calling `closeConnection()` method:

$database->closeConnection();

By writing those few lines you make a database connection, apply the required query, then close the database connection.

Also I learnt how to publish this script on Packagist to use it through composer.

composer require talmira/envelophp

You can review full explanation for my first and only package so far :) :) here.

Finally...

it was a very educative experience to work on this application, I'd appreciate any feedback to improve my skills.
I revamped an old project of mine to shorten URLs and I used this package, I'll write an article soon to explain it more!

My first API using native PHP

Ahmed AbdElLatif — Fri, 09 Apr 2021 01:49:07 +0000

I tried to create my first API while exercising OOP using php, and here what I achieved, take live look here!

First things First!

I created env.php which includes class Environment to parse my own .env file through public function getDatabaseVariables() in order to capture database variables

#Database#
HOST = "localhost"
DATABASE_SERVICE = "mysql"
DATABASE_NAME = "example_db"
DATABASE_USERNAME = "example_user"
DATABASE_PASSWORD = "example_password"

I followed the below approach to parse .env file using preg_match to detect the first line.

$validate_line_from_env_file = preg_match('~^[a-zA-Z]~',$line);

then make use of explode and strpos to catch variable in an associative array as following:

$env_file_text = explode('= "',$line);
$env_variable_key_length = strpos($env_file_text[0]," ");
$env_variable_key = substr($env_file_text[0],0,$env_variable_key_length);
$env_variable_value_length = strpos($env_file_text[1],"\"");
$env_variable_value = substr($env_file_text[1],0,$env_variable_value_length);
if ($env_variable_value == true)
{
    $this->variables += [$env_variable_key => $env_variable_value];
}
else if ($env_variable_value === "")
{
    $this->variables += [$env_variable_key => ""];
}

Then those variables are passed to class Database that extends class Environment where the __construct() function store the required variables, after that a database connection will be created using PDO.

Query the items

The database connection function is being called through __construct() function of class Museum, where the query statement exists

public function getMuseums()
{
    $sqlQuery = "SELECT id, mname, arabic_name, city, type, est_year, website, coordinates, wikipedia_url FROM " . $this->db_table;
    $statement = $this->conn->prepare($sqlQuery);
    $statement->execute();
    return $statement;

}

Finally!

query is being executed in read.php which present api result in json format.

$statement = $items->getMuseums();
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
$itemCount = $statement->rowCount();

//prepare array for json format
if ($itemCount > 0){
    $museumArray = array();
    $museumArray["itemCount"] = $itemCount;
    $museumArray["body"] = $result;

    echo json_encode($museumArray);
}
else
{
http_response_code(404);
echo json_encode(
    array("message" => "No record found.")
);

last piece: I added the following .htaccess code to prevent accessing other routes except .api/read.php

Order deny,allow
Deny from all

<Files "read.php">
    Allow from all
</Files>

Output sample

You can check this api

on github
or visit the live version

Thanks for your time reading my article!

Role/Status Based CRUD Application Using Laravel 7

Ahmed AbdElLatif — Tue, 26 Jan 2021 21:36:45 +0000

This Application is demonstration for managing visa center applicants, in terms of receiving passports and managing visa status (received, pending, rejected or approved).

code sample for form that handle status with consideration to current status of a ticket:

Also in this application I included a function assign a ticket to specific user to work on it.

This all come along side with basic dashboard to display aggregation of each status for last 30 days and for same day

So Let's go to the presentation

Solving Magento Re-indexing Error on Replica Database

Ahmed AbdElLatif — Thu, 17 Dec 2020 01:11:13 +0000

A common issue that face many developers who are working on magento 2.x, is indexing error that arise when importing and existing website database to a replica website that acts as back up for the original one, which impact cron jobs.

A quick solution provided by magento CLI is to use the following reindexing command in magento project root:

bin/magento indexer:reindex indexer_name

If everything was working fine so you should see this:

indexer_name has been rebuilt successfully in x time

The other scenario is having error like this:

SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user 'dummy_user'@'localhost' (using password: YES), query was: "What ever SQL statement"

sample error:

This error indicates an issue with view "inventory_stock_1".

The issue is that the DEFINER has wrong user.

To solve this

Through phpmyadmin interface:

Navigate to the required view, in our case "inventory_stock_1".
Go to Structure Tab.
Select Edit View

Add the right db user name in definer field

Note: you can get the right username navigating from magento project root directory to app/etc/env.php.

Now hopefully you can navigate back to your magento project root directory and use rendinxing command.

A Treat

In some cases after reindxing you may face invalidated cache disclaimer like this one.

All you need is to click on Cache Management and select the invalidated cache(s).

Then choose refresh, click submit and all is fine!