LM is a single PHP class for performing CRUD (create, read, update and delete) operations on a MySQL database table.
No new features will be added, this project is in maintenance mode.
include('lazy_mofo.php');
// required for csv export
ob_start();
// connect to database with pdo
$dbh = new PDO("mysql:host=localhost;dbname=test;", 'user', 'password');
// create LM object, pass in PDO connection, see i18n folder for language + country options
$lm = new lazy_mofo($dbh, 'en-us');
// table name for updates, inserts and deletes
$lm->table = 'market';
// identity / primary key column name
$lm->identity_name = 'market_id';
// use the lm controller
$lm->run();
// enter your database host, name, username, and password
$db_host = 'localhost';
$db_name = 'test';
$db_user = 'root';
$db_pass = '';
// connect with pdo
try {
$dbh = new PDO("mysql:host=$db_host;dbname=$db_name;", $db_user, $db_pass);
}
catch(PDOException $e) {
die('pdo connection error: ' . $e->getMessage());
}
// create LM object, pass in PDO connection, see i18n folder for country + language options
$lm = new lazy_mofo($dbh, 'en-us');
// table name for updates, inserts and deletes
$lm->table = 'market';
// identity / primary key for table
$lm->identity_name = 'market_id';
// optional, make friendly names for fields
$lm->rename['country_id'] = 'Country';
// optional, define input controls on the form
$lm->form_input_control['photo'] = array('type' => 'image');
$lm->form_input_control['is_active'] = array('type' => 'radio', 'sql' => "select 1, 'Yes' union select 0, 'No'");
$lm->form_input_control['country_id'] = array('type' => 'select', 'sql' => 'select country_id, country_name from country');
// optional, define editable input controls on the grid
$lm->grid_input_control['is_active'] = array('type' => 'checkbox');
// optional, define output control on the grid
$lm->grid_output_control['contact_email'] = array('type' => 'email'); // make email clickable
$lm->grid_output_control['photo'] = array('type' => 'image'); // make image clickable
// show search box, but _search parameter still needs to be passed to query below
$lm->grid_show_search_box = true;
// query to define grid view
// IMPORTANT - last column must be the identity/key for [edit] and [delete] links to appear
// include an 'order by' to prevent potential parsing issues
$lm->grid_sql = "
select
m.market_id
, m.market_name
, m.photo
, m.contact_email
, c.country_name
, m.is_active
, m.create_date
, m.market_id
from market m
left
join country c
on m.country_id = c.country_id
where coalesce(m.market_name, '') like :_search
or coalesce(m.contact_email, '') like :_search
or coalesce(c.country_name, '') like :_search
order by m.market_id desc
";
// bind parameter for grid query
$lm->grid_sql_param[':_search'] = '%' . trim($_REQUEST['_search'] ?? '') . '%';
// optional, define what is displayed on edit form. identity id must be passed in also.
$lm->form_sql = "
select
market_id
, market_name
, country_id
, photo
, contact_email
, is_active
, create_date
, notes
from market
where market_id = :market_id
";
// bind parameter for form query
$lm->form_sql_param[':market_id'] = intval($_REQUEST['market_id'] ?? 0);
// optional, validation - regexp, 'email' or a user defined function, all other parameters optional
$lm->on_insert_validate['market_name'] = array('regexp' => '/.+/', 'error_msg' => 'Missing Market Name', 'placeholder' => 'this is required', 'optional' => false);
$lm->on_insert_validate['contact_email'] = array('regexp' => 'email', 'error_msg' => 'Invalid Email', 'placeholder' => 'this is optional', 'optional' => true);
// copy validation rules, same rules when updating
$lm->on_update_validate = $lm->on_insert_validate;
// run the controller
$lm->run();
Example:
// change back button to read "Cancel"
$lm->form_back_button = "<input type='button' value='Cancel' class='lm_button dull' onclick='_back();'>";
// alter link text
$lm->grid_add_link = str_replace('Add a Record', 'Add New', $lm->grid_add_link);
$lm->grid_edit_link = str_replace('[edit]', 'Edit', $lm->grid_edit_link);
// hide delete and export links
$lm->grid_delete_link = "";
$lm->grid_export_link = "";
// change success message
$lm->form_text_record_added = "New Record Added";
$lm->grid_text_record_added = "New Record Added";
By default the user is redirected back to the edit form after making updates or inserting a record. Redirect users to the opening grid screen by setting the following variables to false:
$lm->return_to_edit_after_insert = false;
$lm->return_to_edit_after_update = false;
Input and Output Controls are associative arrays used to define how to render input or output for a field.
Inputs render form inputs such as textarea, select, or checkbox.
Outputs render text, links, and images. Output controls only apply to the grid view and are defined in grid_output_control.
For inputs where sql statements are used, the first column in the sql statement corresponds to the select/checkbox/radio value, the second column is the displayed text.Define Inputs on edit form()
$lm->form_input_control["field_name"] = array("type" => string [, "sql" => string [, "sql_param" => array]]);
Define Inputs on grid()
$lm->grid_input_control["field_name"] = array("type" => string [, "sql" => string [, "sql_param" => array]]);
Define Output on grid()
$lm->grid_output_control["field_name"] = array("type" => string);
// legacy syntax still supported (changed in version 2019-01-10) // optional sql string depending on control, type is prefixed with -- $lm->form_input_control["field_name"] = "[sql]--type"; // legacy examples $lm->form_input_control["country_id"] = "select country_id as val, country_name as opt from country;--select"; $lm->form_input_control["is_active"] = "--checkbox";
Examples:
$lm->form_input_control['client_pic'] = array("type" => "image");
$lm->form_input_control['pdf'] = array("type" => "document");
$lm->form_input_control['is_active'] = array("type" => "checkbox");
$lm->form_input_control['will_you_attend'] = array("type" => "radio", "sql" => "select 1 as key, 'Yes' as val union select 0, 'No'");
$lm->form_input_control['country_id'] = array("type" => "select", sql => "select country_id as val, country_name as opt from country");
For use with form_input_control and grid_input_control arrays.
| type | description |
|---|---|
| text | text input (default) |
| password | password input |
| number | text input for number, when cast numbers are filtered through restricted_numeric_input pattern. |
| date | text input, date is formatted according to public $date_format variable |
| datetime | text input, date is formatted according to public $date_format variable |
| textarea | textarea input |
| readonly | plain text (not an input, just displays data) |
| readonly_date | plain text formatted with date settings (not an input, just displays data) |
| readonly_datetime | plain text formatted with datetime settings (not an input, just displays data) |
| image | file input for uploading, if image exists then image is displayed with 'delete' checkbox. |
| document | file input for uploading, if document exists then display link with 'delete' checkbox. |
| select | select dropdown, sql statement is optional. |
| selectmultiple | select dropdown with multiple options. values are stored in a delimited list. sql is optional. sql_param is optional. |
| checkbox | input checkboxes. values are stored in a delimited list. sql is optional. sql_param is optional. |
| radio | radio buttons. sql is optional. sql_param is optional. |
For use with form_output_control and grid_output_control arrays.
| type | description |
|---|---|
| text | outputs plain text (default) |
| date | outputs date according to date_out setting |
| datetime | outputs datetime according to datetime_out setting |
| outputs a clickable email link | |
| image | outputs a clickable link to the image, or display image if grid_show_images = true |
| document | outputs a clickable link to the document |
| html | outputs text without escaping |
User defined functions can be defined to render an input or output control.
Example:
$lm->form_input_control['weird_data'] = array("type" => "my_user_function");
function my_user_function($column_name, $value, $command, $called_from){
// $column_name: field name
// $value: field value
// $command: array defined in the control, or string with legacy syntax; rarely needed
// $called_from: origin of call; 'form', or 'grid'; rarely needed
global $lm;
$val = $lm->clean_out($value);
return "<input type='text' name='$column_name' value='$val' size='100'>";
}
By default form_input_control and grid_output_control will populate with date, datetime, number and textarea according to meta data. To disable this behavior set auto_populate_controls = false.
Example: $lm->grid_show_search_box = true; $lm->grid_sql = "select m.market_id, m.market_name, m.photo, m.contact_email, c.country_name, m.is_active, m.create_date, market_id from market m left join country c on m.country_id = c.country_id where coalesce(m.market_name, '') like :_search or coalesce(m.contact_email, '') like :_search or coalesce(c.country_name, '') like :_search order by m.market_id desc"; $lm->grid_sql_param[':_search'] = '%' . trim($_REQUEST['_search'] ?? '') . '%';
Example:
$lm->grid_show_search_box = true; // show html defined in grid_search_box
$new_search_1 = $lm->clean_out($_REQUEST['new_search_1'] ?? '');
$new_search_2 = $lm->clean_out($_REQUEST['new_search_2'] ?? '');
// redefine our own search form with two inputs instead of the default one
$lm->grid_search_box = "
<form class='lm_search_box'>
<input type='text' name='new_search_1' value='$new_search_1' size='20' class='lm_search_input' placeholder='market'>
<input type='text' name='new_search_2' value='$new_search_2' size='20' class='lm_search_input' placeholder='email'>
<input type='submit' value='Search' class='lm_button'>
<input type='hidden' name='action' value='search'>
</form>
";
$lm->query_string_list = "new_search_1,new_search_2"; // add variable names to querystring so search is saved while paging, sorting, and editing
// set name parameters
$lm->grid_sql_param[':new_search_1'] = '%' . trim($_REQUEST['new_search_1'] ?? '') . '%';
$lm->grid_sql_param[':new_search_2'] = '%' . trim($_REQUEST['new_search_2'] ?? '') . '%';
// define sql
$lm->grid_sql = "
select
m.market_id
, m.market_name
, m.photo
, m.contact_email
, c.country_name
, m.is_active
, m.create_date
, m.market_id
from market m
left
join country c
on m.country_id = c.country_id
where coalesce(m.market_name, '') like :new_search_1
and coalesce(m.contact_email, '') like :new_search_2
order by m.market_id desc
";
Different forms may be defined for adding records versus editing records.
Example:
if(!isset($_REQUEST['market_id'])){
// form for adding records
$lm->form_sql = 'select * from market where market_id = :market_id';
}
else{
// form for editing records
$lm->form_sql = 'select market_id, market_name, country_id, photo, is_active, create_date, notes from market where market_id = :market_id';
}
$lm->form_sql_param[':market_id'] = $_REQUEST['market_id'] ?? 0;
Server-side validation displays an error message next to the form input.
A general error message is displayed at the top and can be defined with the $lm->validate_text_general string setting.
Separate arrays are used for inserts and updates. If the validate needs are the same for both inserts and updates then just copy the existing array to duplicate the rules.
Alternatively, validation can be handled in On Insert/Update/Delete events (see below).
$lm-gt;on_insert_validate["field_name"] = array("regexp" => string, "error_msg" => string [, "placeholder" => string [, "optional" => boolean]]);
$lm-gt;on_update_validate["field_name"] = array("regexp" => string, "error_msg" => string [, "placeholder" => string [, "optional" => boolean]]);
// old style syntax still supported (changed in version 2019-01-10) // same values, without keys $lm-gt;on_insert_validate["field_name"] = array(string regexp, string error_msg, string placeholder, boolean optional);
Example:
// validation using regular expression, slashes required
$lm->on_insert_validate["market_name"] = array("regexp" => "/.+/", "error_msg" => "Missing Market Name", "placeholder" => "This is Required");
// built-in "email" validation
$lm->on_insert_validate["contact_email"] = array("regexp" => "email", "error_msg" => "Missing or invalid Email", "placeholder" => "Optional Email", "optional" => true);
// user defined validation
$lm->on_insert_validate["country_id"] = array("regexp" => "my_validate", "error_msg" => "Missing or invalid country", "placeholder" => "Required");
// copy array - same setting for updates
$lm->on_update_validate = $lm->on_insert_validate;
// user defined validation example
function my_validate(){
if(empty($_POST['country_id'])
return false; // fail
else
return true; // success
}
On Insert/Update/Delete functions are useful for validation and data manipulation.
These functions can be also be used for validation. Strings returned by the user defined functions are displayed at the top as error messages and the insert/update/delete action is halted.
Example:
$lm->on_update_user_function = 'my_hash_update';
function my_hash_update(){
// form_sql had an additional blank string column, example: "select '' as password_reset ..."
$password = $_POST['password_reset'] ?? '';
// no password sent, nothing to do
if(mb_strlen($password) == 0)
return "";
// returned string will be displayed as an error message
if(mb_strlen($password) > 100)
return "Password too long";
if(mb_strlen($password) < 5)
return "Password too short";
// set optional password
$_POST['password'] = password_hash($password, PASSWORD_DEFAULT);
}
$lm->on_insert_user_function = 'my_hash_insert';
function my_hash_insert(){
$password = $_POST['password'] ?? '';
// returned string will be displayed as an error message
if(mb_strlen($password) > 100)
return "Password too long";
if(mb_strlen($password) < 5)
return "Password too short";
// overwrite password field
$_POST['password'] = password_hash($password, PASSWORD_DEFAULT);
}
User define functions can be defined in the properties listed below.
after_ events are useful for running trigger-like actions. The after_insert_user_function event uniquely receives the identity id of the newly added record.
Example:
$lm->after_insert_user_function = 'my_after_insert';
function my_after_insert($id){
// after_insert_user_function is the only action to get the identity id
// now that the record is added we can do anything we need to
global $lm;
$sql_param = array(':market_id' => $id);
$sql = "insert into related_table(field1, market_id) values (now(), :market_id)";
$lm->query($sql, $sql_param);
}
The cast_user_function array is used for storing column names and their corresponding cast function.
Example:
// when using the checkbox input maybe we'd want unchecked to be 0 instead of null
$lm->cast_user_function['is_active'] = 'my_cast';
function my_cast($val){
return intval($val);
}
This script does not validate csrf itself but has a placeholder csrf variable from loaded from $_SESSION['_csrf']. To protect from csrf, place your nonce token in $_SESSION['_csrf'] and validate the csrf on POST commands.
Example:
// in your login script give the user a random string token
$_SESSION['_csrf'] = base64_encode(openssl_random_pseudo_bytes(15));
// somewhere else, before the page is processed, run some code like this
if($_SERVER['REQUEST_METHOD'] === 'POST' && $_SESSION['_csrf'] != $_POST['_csrf'])
die('Invalid csrf token');
By default LM datagrid is us-en (United States - English). At this time very few country-language files are available. Check the i18n folder to see what is available. If your coutry-language is not available consider making an open source contribution using i18n/template.php
Example: // create LM object, pass in PDO connection, see i18n folder for language + country options $lm = new lazy_mofo($dbh, 'en-us');
Lazy Mofo will automatically identify date and datetime fields. All output of dates and datetimes are output in the format defined by member variables date_out and datetime_out.
Example: // default US format $lm->date_out = 'm/d/Y'; $lm->datetime_out = 'm/d/Y h:i A'; // or set non US date format $lm->date_out = 'd/m/Y'; $lm->datetime_out = 'd/m/Y H:i'; // or use a ISO-ish date format for html5 date inputs $lm->date_out = 'Y-m-d'; $lm->datetime_out = 'Y-m-d H:i';
Comma can be used as a decimal separator, this affects input only.
Output must be localized using mysql's format() function.
Example: // using comma for separator, this affects input only, not displayed format $lm->decimal_separator = ','; // using mysql's format() for decimal output localization $lm->form_sql = " select market_id , format(price, 2, 'es_ES') as price , market_name from market where market_id = :market_id "; https://dev.mysql.com/doc/refman/8.0/en/locale-support.html
<link rel='stylesheet' href='//ajax.googleapis.com/ajax/libs/jqueryui/1.10.4/themes/smoothness/jquery-ui.css'>
<script src='//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js'></script>
<script src='//ajax.googleapis.com/ajax/libs/jqueryui/1.10.4/jquery-ui.min.js'></script>
<script src='//ajax.googleapis.com/ajax/libs/jqueryui/1.10.4/i18n/jquery-ui-i18n.min.js'></script>
<script>
$(function() {
$('input[name=create_date]').datepicker();
// non US date example dd/mm/yy and week starting on monday(1) instead of sunday(0)
// also set date_out and datetime_out properties with the desired date format $lm->date_out = 'd/m/Y'; $lm->datetime_out = 'd/m/Y H:i';
/*
var options = $.extend({},
$.datepicker.regional['it'], {
dateFormat: 'mm/dd/yyyy',
changeMonth: true,
changeYear: true,
highlightWeek: true,
firstDay: 1,
}
);
$('input.lm_create_date').datepicker(options);
*/
});
</script>
Clicking the Export link will convert the grid output to a CSV file for downloading. Output buffering (ob_start) must be used at the beginning of the script for the export to CSV feature to function properly.
View class source code to see all the property settings.