rTyNPLia

1<?php
2/*
3-- sample sql script to populate database for demo
4
5create table if not exists country
6( country_id int unsigned not null auto_increment primary key
7, country_name varchar(255)
8) character set utf8 collate utf8_general_ci;
9
10insert into country(country_name) values ('Canada'), ('United States'), ('Mexico');
11
12create table if not exists province
13( province_id int unsigned not null auto_increment primary key
14, country_id int unsigned 
15, province_name varchar(255)
16) character set utf8 collate utf8_general_ci;
17
18insert into province(country_id, province_name) select country_id, 'Alberta' from country where country_name = 'Canada';
19insert into province(country_id, province_name) select country_id, 'British Columbia' from country where country_name = 'Canada';
20insert into province(country_id, province_name) select country_id, 'Alabama' from country where country_name = 'United States';
21insert into province(country_id, province_name) select country_id, 'Alaska' from country where country_name = 'United States';
22insert into province(country_id, province_name) select country_id, 'Aguascalientes' from country where country_name = 'Mexico';
23insert into province(country_id, province_name) select country_id, 'Baja California' from country where country_name = 'Mexico';
24
25create table if not exists market
26( market_id int unsigned not null auto_increment primary key
27, market_name varchar(255)
28, photo varchar(255)
29, contact_email varchar(255)
30, country_id int unsigned
31, province_id int unsigned
32, is_active tinyint(1)
33, create_date date
34, notes text
35) character set utf8 collate utf8_general_ci;
36
37insert into market(market_name, contact_email, country_id, is_active, create_date, notes) values 
38('Great North', 'jane@superco.com', 1, 1, curdate(), 'nothing new'),
39('The Middle', 'sue@superco.com', 2, null, '2001-01-01', 'these are notes'),
40('Latin Market', 'john@superco.com', 1, 1, '1999-10-31', 'expanding soon');
41*/
42
43error_reporting(E_ALL);
44
45// speed things up with gzip plus ob_start() is required for csv export
46if(!ob_start('ob_gzhandler'))
47	ob_start();
48
49header('Content-Type: text/html; charset=utf-8');
50
51include('lazy_mofo.php');
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
70// create LM object, pass in PDO connection
71$lm = new lazy_mofo($dbh); 
72
73
74// table name for updates, inserts and deletes
75$lm->table = 'market';
76
77
78// identity / primary key for table
79$lm->identity_name = 'market_id';
80
81
82// optional, make friendly names for fields
83$lm->rename['country_id'] = 'Country';
84$lm->rename['province_id'] = 'Province';
85
86// optional, define input controls on the form
87$lm->form_input_control['photo'] = '--image';
88$lm->form_input_control['is_active'] = "select 1, 'Yes' union select 0, 'No' union select 2, 'Maybe'; --radio";
89$lm->form_input_control['country_id'] = 'select country_id, country_name from country; --select';
90
91
92// optional, define editable input controls on the grid
93$lm->grid_input_control['is_active'] = '--checkbox';
94
95
96// optional, define output control on the grid 
97$lm->grid_output_control['contact_email'] = '--email'; // make email clickable
98$lm->grid_output_control['photo'] = '--image'; // image clickable  
99
100
101// new in version >= 2015-02-27 all searches have to be done manually
102$lm->grid_show_search_box = true;
103
104
105// optional, query for grid(). LAST COLUMN MUST BE THE IDENTITY for [edit] and [delete] links to appear
106$lm->grid_sql = "
107select 
108  m.market_id
109, m.market_name
110, m.photo
111, m.contact_email
112, c.country_name
113, p.province_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 
121left  
122join  province p 
123on    m.province_id = p.province_id 
124where coalesce(m.market_name, '') like :_search 
125or    coalesce(m.contact_email, '') like :_search 
126or    coalesce(c.country_name, '') like :_search 
127or    coalesce(p.province_name, '') like :_search 
128order by m.market_id desc
129";
130$lm->grid_sql_param[':_search'] = '%' . trim(@$_REQUEST['_search']) . '%';
131
132
133// optional, define what is displayed on edit form. identity id must be passed in also.  
134$lm->form_sql = "
135select 
136  market_id
137, market_name
138, country_id
139, province_id
140, photo
141, contact_email
142, is_active
143, create_date
144, notes 
145from  market 
146where market_id = :market_id
147";
148$lm->form_sql_param[":$lm->identity_name"] = @$_REQUEST[$lm->identity_name]; 
149
150
151// optional, validation. input:  regular expression (with slashes), error message, tip/placeholder
152// first element can also be a user function or 'email'
153$lm->on_insert_validate['market_name'] = array('/.+/', 'Missing Market Name', 'this is required'); 
154$lm->on_insert_validate['contact_email'] = array('email', 'Invalid Email', 'this is optional', true); 
155
156
157// copy validation rules to update - same rules
158$lm->on_update_validate = $lm->on_insert_validate;  
159
160
161echo "
162<!DOCTYPE html>
163<html>
164<head>
165	<meta charset='UTF-8'>
166	<link rel='stylesheet' type='text/css' href='style.css'>
167</head>
168<body>
169"; 
170
171// use the lm controller
172$lm->run();
173
174echo "</body></html>";
175?>
176
177<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
178<script>
179$(function(){
180
181    // call when page is loaded
182    _sync(); 
183
184    // call when related select box is altered
185    $('.lm_country_id').on('change', _sync);
186
187});
188
189function _sync(){
190    
191    var country_id = $('.lm_country_id').val(); 
192    var province_id = $('.lm_province_id').val(); 
193
194    $.get('ajax_select_external.php', { country_id: country_id, province_id: province_id }, function( data ) {
195        $('.lm_province_id').replaceWith(data);
196    });
197}
198</script>