· 6 years ago · Nov 06, 2019, 05:48 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 <link rel='stylesheet' type='text/css' href='style.css'>
48 <meta name='robots' content='noindex,nofollow'>
49</head>
50<body>
51";
52
53
54// enter your database host, name, username, and password
55$db_host = 'localhost';
56$db_name = 'test';
57$db_user = 'root';
58$db_pass = '';
59
60
61// connect with pdo
62try {
63 $dbh = new PDO("mysql:host=$db_host;dbname=$db_name;", $db_user, $db_pass);
64}
65catch(PDOException $e) {
66 die('pdo connection error: ' . $e->getMessage());
67}
68
69// create LM object, pass in PDO connection, see i18n folder for country + language options
70$lm = new lazy_mofo($dbh, 'en-us');
71
72
73// table name for updates, inserts and deletes
74$lm->table = 'market';
75
76
77// identity / primary key for table
78$lm->identity_name = 'market_id';
79
80
81// optional, make friendly names for fields
82$lm->rename['country_id'] = 'Country';
83
84
85// optional, define input controls on the form
86$lm->form_input_control['photo'] = array('type' => 'image');
87$lm->form_input_control['is_active'] = array('type' => 'radio', 'sql' => "select 1, 'Yes' union select 0, 'No' union select 2, 'Maybe'");
88$lm->form_input_control['country_id'] = array('type' => 'select', 'sql' => 'select country_id, country_name from country');
89$lm->form_input_control['create_date'] = array('type' => 'readonly_date');
90
91
92// optional, define editable input controls on the grid
93$lm->grid_input_control['is_active'] = array('type' => 'checkbox');
94
95
96// optional, define output control on the grid
97$lm->grid_output_control['contact_email'] = array('type' => 'email'); // make email clickable
98$lm->grid_output_control['photo'] = array('type' => 'image'); // make image clickable
99
100
101// new in version >= 2015-02-27 all searches have to be done manually, added in where clause of grid_sql
102$lm->grid_show_search_box = true;
103
104
105// optional, query for grid().
106// ** IMPORTANT - last column must be the identity/key for [edit] and [delete] links to appear **
107$lm->grid_sql = "
108select
109 m.market_id
110, m.market_name
111, m.photo
112, m.contact_email
113, c.country_name
114, m.is_active
115, m.create_date
116, m.market_id
117from market m
118left
119join country c
120on m.country_id = c.country_id
121where coalesce(m.market_name, '') like :_search
122or coalesce(m.contact_email, '') like :_search
123or coalesce(c.country_name, '') like :_search
124order by m.market_id desc
125";
126$lm->grid_sql_param[':_search'] = '%' . trim(@$_REQUEST['_search']) . '%';
127
128
129// optional, define what is displayed on edit form. identity id must be passed in also.
130$lm->form_sql = "
131select
132 market_id
133, market_name
134, country_id
135, photo
136, contact_email
137, is_active
138, notes
139, create_date
140from market
141where market_id = :market_id
142";
143$lm->form_sql_param[":$lm->identity_name"] = @$_REQUEST[$lm->identity_name];
144
145
146// optional, validation - regexp may be 'email' or a user defined function, all other parameters optional
147$lm->on_insert_validate['market_name'] = array('regexp' => '/.+/', 'error_msg' => 'Missing Market Name', 'placeholder' => 'this is required', 'optional' => false);
148$lm->on_insert_validate['contact_email'] = array('regexp' => 'email', 'error_msg' => 'Invalid Email', 'placeholder' => 'this is optional', 'optional' => true);
149
150
151// copy validation rules, same rules when updating
152$lm->on_update_validate = $lm->on_insert_validate;
153
154$lm->after_insert_user_function = 'set_create_date';
155$lm->after_update_user_function = 'set_create_date';
156
157// run the controller
158$lm->run();
159
160
161echo "</body></html>";
162
163function set_create_date($market_id = 0){
164
165 // noticed an issue here, after_update_user_function does not pass id like insert version does
166 // this may be changed soon if i can make the fix backward compatible, regardless this sample will work
167 if($market_id == 0)
168 $market_id = $_POST['market_id'];
169
170 global $lm;
171 $sql = "update market set create_date = now() where market_id = :market_id";
172 $sql_param = array(':market_id' => $market_id);
173 $lm->query($sql, $sql_param);
174
175}