PHP as a Pain Reliever
Tags: php, mysql
Published: 2019-10-18 21:09:47

# 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 🗂️
<pre>
- 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
</pre>

# 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](https://getbootstrap.com/docs/4.0/examples/dashboard/) 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>
```

`footer.php`

```
    </main>
    <script>...</script>
</body></html>
```

## 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;
}
```

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');
```

## lib.php [[src]](https://github.com/tayyebi/tiny-php-pain-reliever/blob/master/lib.php)
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;
    }
}
?>  
```

## SuperLiteSQL [[src]](https://github.com/tayyebi/tiny-php-pain-reliever/blob/master/pages/superlitesql.php)
![SQL Editor](https://raw.githubusercontent.com/tayyebi/tiny-php-pain-reliever/master/meta/superlitesql.gif)
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']);
```

## crud.php [[src]](https://github.com/tayyebi/tiny-php-pain-reliever/blob/master/pages/crud.php)
![Create, Read, Update, and Delete](https://raw.githubusercontent.com/tayyebi/tiny-php-pain-reliever/master/meta/crud.gif)
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']))
{
        // ...
}
```
## Users and Permissions
We can easily manage users access level to any table using MySQL predefined functionality.
![Database Users Permissions](https://raw.githubusercontent.com/tayyebi/tiny-php-pain-reliever/master/meta/permissions.gif)

## 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
```
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](https://raw.githubusercontent.com/tayyebi/tiny-php-pain-reliever/master/meta/reports.gif)
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>