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

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?

What's Bad About LM

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

// make friendly names
$lm->rename = array('country_id' => 'Country');

// define input controls on the form
$lm->form_input_control = array('photo' => '--image', 'is_active' => '--checkbox', 'country_id' => 'select country_id as val, country_name as opt from country; --select');

// define editable input controls on the grid
$lm->grid_input_control = array('is_active' => '--checkbox');

// define output control on the grid; make email clickable and the photo a clickable link
$lm->grid_output_control = array('contact_email' => '--email', 'photo' => '--image');

// query for grid(). if the last column selected is the primary key identity, then the [edit] and [delete] links are displayed
$lm->grid_sql = "select m.market_name, m.photo, m.contact_email, c.country_name, m.is_active, m.create_date, m.notes, m.market_id from market m left join country c on m.country_id = c.country_id order by m.market_id desc";

// query for form()
$lm->form_sql = 'select * from market where market_id = :market_id';
$lm->form_sql_param = array(':market_id' => intval($_REQUEST['market_id']));

// optional, display a related table under the edit record form
$lm->child_title = 'Sub Markets';
$lm->child_table = 'sub_market';
$lm->child_identity_name = 'sub_market_id';
$lm->child_parent_identity_name = 'market_id';
$lm->child_input_control = array('photo' => '--image');

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


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, grid_output_control, or child_input_control
    // $called_from: which function called this user function; form, grid, or child_grid

    $val = htmlspecialchars($value, ENT_QUOTES, 'UTF-8');
    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, grid_input_control, and child_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, grid_output_control, and child_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

Customizing the Search Form

Example:

$lm->grid_show_search_box = false; // hide the default search box

// use unique variable names. these are prefixed by '_' to conflicts
$_new_search1 = htmlentities($_REQUEST['_new_search1'], ENT_QUOTES); 
$_new_search2 = htmlentities($_REQUEST['_new_search2'], ENT_QUOTES);

// display our own search form
if($_REQUEST['action'] == ''){
    echo "
    <form action='$uri' method='post'>
    <input type='text' name='_new_search1' value='$_new_search1'>
    <input type='text' name='_new_search2' value='$_new_search2'>
    <input type='hidden' name='_csrf' value='$_SESSION[_csrf]'>
    <input type='Submit' value='Search'>
    </form>
    ";
}

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

Customizing the Search Query

LM alters the sql statement to inject search clause. To control how the search is performed, or if LM generated query is failing, a custom search query may be defined.

Example 1: 

$lm->grid_run_search = false; // disable automatic search

$lm->grid_sql = "select *, market_id from market where coalesce(market_name, '') like :_search"; 

$lm->grid_sql_param[':_search'] = '%' . $_REQUEST['_search'] . '%';

Example 2: 

$lm->grid_run_search = false; // disable automatic search

if(trim($_REQUEST['_search']) != ''){
	// search 
	$lm->grid_sql = "select *, market_id from market where coalesce(market_name, '') like :_search"; 
	$lm->grid_sql_param[':_search'] = '%' . trim($_REQUEST['_search']) . '%';
}
else{
	// no search
	$lm->grid_sql = "select *, market_id from market";
}

Counting Records

For efficiency when working on large datasets, LM runs a sql count() statement to find the number of results.
Depending on the complexity of the grid_sql statement, LM may fail to create working count() query. In this case, you must define the grid_sql_count.

$lm->grid_sql = "select * from market where coalesce(market_name, '') like :market_name";
$lm->grid_sql_param = array(':market_name' => '%' . $_REQUEST['_search'] . '%')

// only requied if LM fails to generate a working count statement
// make this query identical to grid_sql but with a count() 
$lm->grid_sql_count = "select count(1) from market where market_name like :market_name"; 

Server-Side Validation

Server-side validation can be added by defining user function names in:

Example: 

$lm->on_insert_user_function = 'my_validate';
$lm->on_update_user_function = 'my_validate';

function my_validate(){

    if($_POST['email'] == '')
        return 'Email is Required';
}

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.

Non US date formats

Example: 

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

// how date fields are cast when using search
$lm->date_out_mysql = '%d-%m-%Y';      
$lm->datetime_out_mysql = '%d-%m-%Y %h:%i %p'; 

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