DEV Community

Cover image for PHP as a Pain Reliever
Tayyebi
Tayyebi

Posted on

PHP as a Pain Reliever

Intro 🎬

If you are a dead eye and you don't wan't to fall on boss's sword, and go nuclear ☒️ (frameworks like Laravel I mean) to create a simple CRUD app, this post is written for you!
I will talk about a solution that helps us to rapidly create a PHP-based mini-framework that handles authentication, authorization, tables CRUD, Server status monitor, and etc in some simple magic πŸ§™ steps like shooting fish in a barrel πŸ›’οΈ.

Demo πŸŽ₯

Some GIFs are provided in side each subtitle.

Overview πŸ”­

MySQL functions are used to handle auth, we write HTML in SQL(!), and lots of other spaghetti 🍝 stuff to build the solution fast.

File Structure πŸ—‚οΈ

- index.php
- lib.php
- master/
---- header.php
---- footer.php
- pages/
---- crud.php
---- superlitesql.php
---- users.php
- reports/
---- a_report.sql
---- filters/
-------- a_report.sql.csv

Step by step πŸ”¬

master/header.php and master/footer.php

You any theme on you own. Maybe bootstrap is a good choice. I just copied all the stuff in this example inside header and footer files. Note that you have to keep a free space for variable contents. header and footer files are here to give us a taste of master page just like what we had in ASP.NET web forms (RIP ⚰️).

header.php

<!doctype html>
<header>...<header>
<html><body>
    <main>
Enter fullscreen mode Exit fullscreen mode

footer.php

    </main>
    <script>...</script>
</body></html>
Enter fullscreen mode Exit fullscreen mode

index.php

This file handles authentication, calls header.php and footer.php, and totally he is a good boy, we can call him loader or anything else.

How to authenticate with MySQL users?

$conn = @new mysqli($servername
    , $_SERVER['PHP_AUTH_USER'] // Username
    , $_SERVER['PHP_AUTH_PW'] // Password
    , $dbname);

// Set database charset to support persian.
mysqli_set_charset($conn,"utf8");

// Check Auth
if (!isset($_SERVER['PHP_AUTH_USER'])
    || $conn->connect_error
) {
    // Send login failed error to browser and expire temp login.
    header('WWW-Authenticate: Basic realm="Tayyebi Realm"');
    header('HTTP/1.0 401 Unauthorized');
    // die("Connection failed: " . $conn->connect_error);
    echo 'Access Denied';
    exit;
}
Enter fullscreen mode Exit fullscreen mode

This will use browser basic authentication to ask for SQL connection info. If connection to database was successful, then code will pass to next lines.

Code below, allows us to access to any page content we decide, with passing its id to index.php as a query string:

// Header
include ('master/header.php');

// Container
$_GET['id'] = isset($_GET['id']) ? $_GET['id'] : 'welcome';
// includes the page content
include ('pages/' . $_GET['id'] . '.php');

// Footer
include ('master/footer.php');
Enter fullscreen mode Exit fullscreen mode

lib.php [src]

There are two important functions in this static class. First to create a form from SQL table describe and second to create a HTML table from SQL query.

<?php
abstract class SuperLiteSql {
    // Function to create tables from queries
    static function createTable_from_sql_select_query($sql_link, $query) {
        // ...
        echo $table;
    }

    // This function creates an HTML form from SQL table
    static function GenerateFormFromTable($table_name, $conn, $values) {

        // ...

        return $form;
    }
}
?>  
Enter fullscreen mode Exit fullscreen mode

SuperLiteSQL [src]

SQL Editor
Here we have a textbox and a button that allows you to submit the query and it will print out the results. Also we can pass .sql file names to the page and it will automatically post it to those functions; so we will be able to simply create SQL reports.

if (isset($_GET['entry']))
    $_POST['the_query'] = file_get_contents("reports/" . $_GET['entry']);
// ...
if (isset($_POST['the_query']))
    SuperLiteSql::createTable_from_sql_select_query($conn, $_POST['the_query']);
Enter fullscreen mode Exit fullscreen mode

crud.php [src]

Create, Read, Update, and Delete
CRUD is CRUD, everywhere. There is Create, Select, Update, and Delete on a table row which is known by a key.
So the CRUD.php will use functions defined in lib.php to create a table from items table and a form for each individual row. Finally it will listen for posted data to decide what to edit or delete.

if (isset($_POST['insert'])
{
        // ...
}
else if (isset($_POST['update'])
{
        // declare variables
        $update_query_key_values = '';

        // Get table structure
        // In this case: todo table
        $result = $conn->query("DESCRIBE " . $table_name);

        // output data of each row
        while ($row = $result->fetch_assoc()) {
            if ($row["Field"] != $table_id) { // because of auto increment
                // dont insert comma for the last record
                if ($update_query_key_values != '')
                    $update_query_key_values .= ', ';

                // apend keys to keys array
                $update_query_key_values .= '`' . $row["Field"] . '` = ';

                // Get field type
                // if it was a stirng, add quote in first and last of value
                $type = substr($row["Type"], 0, strpos($row["Type"], '('));
                $symbol = '';
                if ($type == "varchar"
                or $type == "char" ) {
                    $symbol = '\'';
                }


                // If value is posted and its not null
                if (! isset( $_POST[ $row["Field"] ] ) ) {
                    $update_query_key_values .= "NULL" ;
                }
                else if ($_POST[ $row["Field"] ] == ''
                    and ( $type == "int" or $type == "bit" or $type == "decimal" )
                ) {
                    $update_query_key_values .= "NULL" ;
                }
                else {
                    // append value to values array
                    $update_query_key_values .= $symbol . $_POST[ $row["Field"] ]  . $symbol;
                }
            }
        }

        // assemble final query
        $update_query  = 'UPDATE ' . $table_name . ' SET ' . $update_query_key_values . ' WHERE ' . $table_id . ' = '. $_POST[$table_id] ;

        // run the query !
        mysqli_query($conn, $update_query);
}
else if (isset($_POST['delete']))
{
        // ...
}
Enter fullscreen mode Exit fullscreen mode

Users and Permissions

We can easily manage users access level to any table using MySQL predefined functionality.
Database Users Permissions

Bash script to log database transactions

while true
do
        mysql -u root -p<yourpasshere> -e "insert into test.mysql_general_log select * from mysql.general_log where argument like '%tss%' or argument like '%todo%' or argument like '%Transactions%'"
        mysql -u root -p<yourpasshere> -e "truncate mysql.general_log"
done
Enter fullscreen mode Exit fullscreen mode

In the code provided above, which you can call it log.sh and run it in background, MySQL cli will make a copy of all logs that have todo or Transaction tables name into the local mysql_general_log which is made using the result of SHOW CREATE TABLE mysql.general_log query.

Dynamic reports

Reports filtering
We can create dynamic filters by merging our scripts and comma separated value info about tables. We can define variables using a @ symbol and then generate forms that will accept user inputs.

Downloads πŸ“©

Github Release page: https://github.com/tayyebi/tiny-php-pain-reliever/releases

Ref βš“

https://7esl.com/weapons-idioms

Top comments (0)