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>
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]
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]
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]
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.
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
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
Top comments (0)