logo live demo | download | class source | demo source | changelog.txt | email me
current version: 2015-05-06
project home and demos: http://lazymofo.wdschools.com/

What's New

What is Lazy Mofo (LM) PHP Datagrid?

LM is a single PHP5 class for performing CRUD (create, read, update and delete) operations on a MySQL database table.
What can LM do?

Requirements

Example 1 - Basic Usage


// connect to database with pdo
$dbh = new PDO("mysql:host=localhost;dbname=test;", 'user', 'password');

// create LM object, pass in PDO connection
$lm = new lazy_mofo($dbh); 

// 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();

Example 2 - Advanced Usage

// connect with pdo 
$dbh = new PDO("mysql:host=$db_host;dbname=$db_name;", $db_user, $db_pass);


// create LM object, pass in PDO connection
$lm = new lazy_mofo($dbh); 


// 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'] = '--image';
$lm->form_input_control['is_active'] = "select 1, 'Yes' union select 0, 'No' union select 2, 'Maybe'; --radio";
$lm->form_input_control['country_id'] = 'select country_id, country_name from country; --select';


// optional, define editable input controls on the grid
$lm->grid_input_control['is_active'] = '--checkbox';


// optional, define output control on the grid 
$lm->grid_output_control['contact_email'] = '--email'; // make email clickable
$lm->grid_output_control['photo'] = '--image'; // image clickable  


// new in version 2015-02-?? - all searches have to be done manually
$lm->grid_show_search_box = true;


// optional, query for grid(). LAST COLUMN MUST BE THE IDENTITY for [edit] and [delete] links to appear
$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 m.market_name like :_search or m.contact_email like :_search or c.country_name like :_search order by m.market_id desc";
$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';
$lm->form_sql_param[":$lm->identity_name"] = intval(@$_REQUEST[$lm->identity_name]); 


// optional, validation. input:  regular expression (with slashes), error message, tip/placeholder
// first element can also be a user function or 'email'
$lm->on_insert_validate['market_name'] = array('/.+/', 'Missing Market Name', 'required'); 
$lm->on_insert_validate['contact_email'] = array('email', 'Invalid Email', 'optional', true); 


// copy validation rules to update - same rules
$lm->on_update_validate = $lm->on_insert_validate;  


// use the lm controller
$lm->run();

Redirect to Edit Screen After Update and Insert

By default the user is redirected back to the edit form after making updates or inserting a record. The user may be redirected to the opening grid screen with the following settings:


    $lm->return_to_edit_after_insert = false;
    $lm->return_to_edit_after_update = false;

Input and Output Controls - define how a field is rendered

Input and Output Controls are associative arrays used to define how to render input or output for a field. Inputs render form inputs like: text, checkbox, radio, etc. Outputs render: text, links, and images.

Define Inputs on edit form()
$lm->form_input_control["field_name"] = "[sql] --command";

Define Inputs on grid()
$lm->grid_input_control["field_name"] = "[sql] --command";

Define Output on grid()
$lm->grid_output_control["field_name"] = "[sql] --command";


Examples: 

    $lm->form_input_control['client_pic'] = '--image';

    $lm->form_input_control['pdf'] = '--document';

    $lm->form_input_control['weird_data']  = '--my_user_function';

    $lm->form_input_control['will_you_attend'] = "select 1 as key, 'Yes' as val union select 0, 'No' union select 3, 'Maybe'; --radio";

    $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"; // without a sql prefix this control will default to yes/no

Defining Custom Input and Output Controls

User defined functions can be defined to render an input or output control. 

Example: 

$lm->form_input_control['weird_data'] = '--my_user_function';

function my_user_function($column_name, $value, $command, $called_from){

    // $column_name: field name
    // $value: field value  
    // $command: full command as defined in the arrays: form_input_control, grid_input_control, or grid_output_control
    // $called_from: which function called this user function; form, or grid

    global $lm;
    $val = $lm->clean_out($value);
    return "<input type='text' name='$column_name' value='$val' size='100'>";

}

Auto Populated Controls

If auto_populate_controls = true, get_columns() will populate form_input_control and grid_output_control with --date, --datetime, --number and --textarea according to meta data.

Input Controls

For use with form_input_control and grid_input_control arrays.

--my_input_control

define your own function and return any HTML. example: function my_input_control($column_name, $value, $command, $called_from)

--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.

[sql] --select

select dropdown, sql statement is optional.

[sql] --selectmultiple

select dropdown with multiple options. values are stored in a delimited list. sql statement is optional.

[sql] --checkbox

input checkboxes. values are stored in a delimited list. sql statement is optional.

[sql] --radio

radio buttons. sql statement is optional.

Commands for Output Control

For use with form_output_control and grid_output_control arrays.

--my_output_control

define your own function and return any HTML. example: function my_output_control($column_name, $value, $command, $called_from)

--text

outputs plain text (default)

--date

outputs date according to date_out setting

--datetime

outputs datetime according to datetime_out setting

--email

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 html without tags or formatting

Adding Search

Version >= 2015-05-06 requires searching to be done manually.

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 m.market_name like :_search or m.contact_email like :_search or c.country_name like :_search order by m.market_id desc";
$lm->grid_sql_param = array(':_search' => '%' . trim(@$_REQUEST['_search']) . '%');

Customizing the Search Form

Placeholders [script_name], [_csrf], and [_search] (not show here) are populated by the class. 
Example:

$lm->grid_show_search_box = true; // show html defined in grid_search_box

$_new_search1 = $lm->clean_out(@$_REQUEST['_new_search1']);
$_new_search2 = $lm->clean_out(@$_REQUEST['_new_search2']);

// define our own search form with two inputs instead of the default one
$lm->grid_search_box = "
<form action='[script_name]' method='post' class='lm_search_box'>
	<input type='text' name='_new_search1' value='$_new_search1' size='20' class='lm_search_input'>
	<input type='text' name='_new_search2' value='$_new_search2' size='20' class='lm_search_input'>
	<input type='submit' value='Search' class='lm_search_button'>
	<input type='hidden' name='action' value='search'><input type='hidden' name='_csrf' value='[_csrf]'>
</form>
"; 

$lm->query_string_list = "_new_search1,_new_search2"; // add variable names to querystring so search is perserved when paging, sorting, and editing.

Using a Different Add and Edit Forms

Different forms may be defined for adding records versus editing records.

Example:

if(intval(@$_REQUEST[$lm->identity_name]) == 0){
	// 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 = array(':' . $lm->identity_name => intval(@$_REQUEST[$lm->identity_name]));

Validation

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(string $regexp_or_user_function, string $error_message[, string $tip_placeholder , boolean optional_input]);

$lm-gt;on_update_validate['field_name'] = array(string $regexp_or_user_function, string $error_message[, string $tip_placeholder , boolean optional_input]);

Example: 

$lm->on_insert_validate['market_name'] = array('/.+/', 'Missing Market Name', 'This is Required'); 
$lm->on_insert_validate['contact_email'] = array('email', 'Missing or invalid Email', 'Optional Email', true); // built in validator for email, set to optional input
$lm->on_insert_validate['country_id'] = array('my_validate', 'Missing or invalid country', 'Required');        // user defined function

// copy array - same setting for updates
$lm->on_update_validate = $lm->on_insert_validate;

function my_validate(){

	if($_POST['country_id') == '')
		return false;
	else
		return true;

}

On Insert/Update/Delete Events

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

function my_hash(){

	if(isset($_POST['password_reset']))
		$_POST['password'] = md5($_POST['password_reset']);
	
	if(mb_strlen($_POST['password_reset']) > 100)
		return "Password too long";

}

After Insert/Update/Delete Events

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

}

Cross Site Request Forgery - csrf

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')
	if($_SESSION['_csrf'] != $_POST['_csrf'])
		die('Invalid csrf token');

Date Formats

Example: 

// how dates are displayed
$lm->date_out = 'd-m-Y';
$lm->datetime_out = 'd-m-Y h:i A';

Adding JQuery UI Datepicker

Example: 

<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>
$(function() {
	
	// replace lm_field_name with your input's class name
	$('input.lm_field_name').datepicker(); 
	
	// non US date example dd-mm-yy and week starting on monday(1) instead of sunday(0)
	$('input.lm_non_us_date').datepicker({ dateFormat: 'dd-mm-yy', firstDay: 1 });


});
</script>

Export to CSV

Output buffering (ob_start) must be used at the beginning of the script for the export to CSV feature to function properly.

More Features and Settings

View class source code to see all the available settings and features.




Project Sponsored By:
Computer Animation Schools | Graphic Design Degrees | Web Design School | Criminal Justice and Paralegal Schools
Accounting Schools | Massage Therapy Schools | Pharmacy Tech Schools | Auto Mechanic Schools
Dental Hygienist Schools | Medical Tech Training | Phlebotomy Schools | Technician Schools