· 6 years ago · Jan 19, 2020, 05:22 PM
1<?php
2/*
3
4-- sample sql script to populate database for demo
5-- for older mysql < 5.5 replace utf8mb4 with utf8
6
7create table if not exists country
8( country_id int unsigned not null auto_increment primary key
9, country_name varchar(255)
10) character set utf8mb4 collate utf8mb4_unicode_ci;
11
12insert into country(country_name) values ('Canada'), ('United States'), ('Mexico');
13
14create table if not exists market
15( market_id int unsigned not null auto_increment primary key
16, market_name varchar(255)
17, photo varchar(255)
18, contact_email varchar(255)
19, country_id int unsigned
20, is_active tinyint(1)
21, create_date date
22, notes text
23) character set utf8mb4 collate utf8mb4_unicode_ci;
24
25insert into market(market_name, contact_email, country_id, is_active, create_date, notes) values
26('Great North', 'jane@superco.com', 1, 1, curdate(), 'nothing new'),
27('The Middle', 'sue@superco.com', 2, null, '2001-01-01', 'these are notes'),
28('Latin Market', 'john@superco.com', 1, 1, '1999-10-31', 'expanding soon');
29
30*/
31
32error_reporting(E_ALL);
33
34// speed things up with gzip plus ob_start() is required for csv export
35if(!ob_start('ob_gzhandler'))
36 ob_start();
37
38header('Content-Type: text/html; charset=utf-8');
39
40include('lazy_mofo.php');
41
42echo "
43<!DOCTYPE html>
44<html>
45<head>
46 <meta charset='UTF-8'>
47 <meta name='robots' content='noindex,nofollow'>
48 <link rel='stylesheet' type='text/css' href='style.css'>
49 <link rel='stylesheet' type='text/css' href='https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.12/css/select2.min.css'>
50 <script src='https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js'></script>
51 <script src='https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.12/js/select2.min.js'></script>
52 <script src='https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.12/js/i18n/en.js'></script>
53 <style>
54 /* weird, select2 is rendering too small */
55 .lm_grid .select2-container { min-width: 200px; }
56 .lm_grid .select2-search__field { min-width: 188px; }
57 .lm_form .select2-container { min-width: 400px; }
58 .lm_form .select2-search__field { min-width: 388px; }
59 </style>
60 <script>
61 $(document).ready(function() {
62
63 // commented out - disabled for now
64 // $('select.lm_market_name').select2();
65
66 });
67 </script>
68</head>
69<body>
70";
71
72
73// enter your database host, name, username, and password
74$db_host = 'localhost';
75$db_name = 'test';
76$db_user = 'root';
77$db_pass = '';
78
79
80// connect with pdo
81try {
82 $dbh = new PDO("mysql:host=$db_host;dbname=$db_name;", $db_user, $db_pass);
83}
84catch(PDOException $e) {
85 die('pdo connection error: ' . $e->getMessage());
86}
87
88// create LM object, pass in PDO connection, see i18n folder for country + language options
89$lm = new lazy_mofo($dbh, 'en-us');
90
91
92// table name for updates, inserts and deletes
93$lm->table = 'market';
94
95
96// identity / primary key for table
97$lm->identity_name = 'market_id';
98
99
100// optional, make friendly names for fields
101$lm->rename['country_id'] = 'Country';
102
103
104// optional, define input controls on the form
105$lm->form_input_control['photo'] = array('type' => 'image');
106$lm->form_input_control['is_active'] = array('type' => 'radio', 'sql' => "select 1, 'Yes' union select 0, 'No' union select 2, 'Maybe'");
107$lm->form_input_control['country_id'] = array('type' => 'select', 'sql' => 'select country_id, country_name from country');
108
109// for demo using selectmultiple or select2
110$lm->form_input_control['market_name'] = array('type' => 'selectmultiple', 'sql' => "select 1, 'Option 1' union select 2, 'Option 2' union select 3, 'Option 3'");
111$lm->grid_input_control['market_name'] = $lm->form_input_control['market_name']; // same input setup as form on the form
112
113
114// optional, define editable input controls on the grid
115$lm->grid_input_control['is_active'] = array('type' => 'checkbox');
116
117
118// optional, define output control on the grid
119$lm->grid_output_control['contact_email'] = array('type' => 'email'); // make email clickable
120$lm->grid_output_control['photo'] = array('type' => 'image'); // make image clickable
121
122
123// new in version >= 2015-02-27 all searches have to be done manually, added in where clause of grid_sql
124$lm->grid_show_search_box = true;
125
126
127// optional, query for grid().
128// ** IMPORTANT - last column must be the identity/key for [edit] and [delete] links to appear **
129$lm->grid_sql = "
130select
131 m.market_id
132, m.market_name
133, m.photo
134, m.contact_email
135, c.country_name
136, m.is_active
137, m.create_date
138, m.market_id
139from market m
140left
141join country c
142on m.country_id = c.country_id
143where coalesce(m.market_name, '') like :_search
144or coalesce(m.contact_email, '') like :_search
145or coalesce(c.country_name, '') like :_search
146order by m.market_id desc
147";
148$lm->grid_sql_param[':_search'] = '%' . trim(@$_REQUEST['_search']) . '%';
149
150
151// optional, define what is displayed on edit form. identity id must be passed in also.
152$lm->form_sql = "
153select
154 market_id
155, market_name
156, country_id
157, photo
158, contact_email
159, is_active
160, create_date
161, notes
162from market
163where market_id = :market_id
164";
165$lm->form_sql_param[":$lm->identity_name"] = @$_REQUEST[$lm->identity_name];
166
167
168// optional, validation - regexp may be 'email' or a user defined function, all other parameters optional
169//$lm->on_insert_validate['market_name'] = array('regexp' => '/.+/', 'error_msg' => 'Missing Market Name', 'placeholder' => 'this is required', 'optional' => false);
170$lm->on_insert_validate['contact_email'] = array('regexp' => 'email', 'error_msg' => 'Invalid Email', 'placeholder' => 'this is optional', 'optional' => true);
171
172
173// copy validation rules, same rules when updating
174$lm->on_update_validate = $lm->on_insert_validate;
175
176
177// run the controller
178$lm->run();
179
180
181echo "</body></html>";