Two Grav users have asked me on the sqlite repo about using multiple forms and tables to select/edit items. (Quick suggestion - use the Grav discuss forum for questions like this, because I have set up email digests from here, but not from the github repos).
To answer these questions, here is a short description of a page on a website I set up using Grav. You can see the page on mgc.1ptd.com Below is the page. It is located at user/page/mgc/default.md This is important because the forms generate responses that need to be processed by server side code that are associated with the route mgc.
In addition to the mgc plugin, which is private (but the php for the plugin is given below), I have used the following plugins, which I have published: sqlite, map-marker-leaflet, datatables, persistent-data. They are all independently documented, and a lot of the documented examples were simplified from the code I developed for this page.
Some notes:
- sqlite can be used to output in json form as well as html table form. This was developed and used for the map-marker-leaflet input.
- datatables is a fantastic jQuery plugin. I have only used a very small proportion of its functionality. The inclusion of the scripting shortcode in the plugin I published was intended to expose as much of its functionality as possible. You can see from the code below how I have used the row selection functions to populate elements of a Grav form.
- I will admit that the population of the Grav form is somewhat of a hack. It took quite a bit of time looking at the form Grav generated in order to identify the variables that needed to be returned.
- The persistent data plugin is used to separate two identities, one is the user (also by definition a hiker in the sqlite db) who has logged in, and a hiker chosen from this list of hikers. This allows
- any one to use this page of the site,
- when the selected hiker and the logged in user are the same, another form is made visible, (this is done with on-page twig that conditionally includes the other form), which can then be used to provide data that is added to the sqlite database,
- in another page - only visible to users who have logged in - it is possible to compare two hikers to see which mountain tops they have both done, which neither have done, etc. The idea is to help hikers plan hikes with others.
- The options in the form
change_map_stylerelated to a map provider calledthunderforest. In order to replicate the image on the url above, you would need to obtain a (free for hobbyists) account with thundercloud, and include it in the passkey in the map-leaflet-marker plugin customisation. - The mgc challenge is ending in March 2019, and only a few hikers have used the page. So you may not get the variety of colours to lable the different trek types. But the principle should be clear: use the sql select statement to create different categories and name them appropriately, use the marker lable colors to assign different colours to each category.
- A tip: when data provided by the user (eg. the name of an image file) can be arbitrary, use the php function
escapeString()as seen in the mgc.php codeescapeString($img['name']). It took me a lonnnnnnng time to find this function as several variations exist. - The
actionfield of a Grav form should point back to the page from which the form was generated. This allows Grav to associate the form response with the form processing code. The documentation is not crystal clear about this. - This page also shows how to allow a user to upload an image. Given the short-term nature of this page, I have not included any code to allow a user to delete an image.
Hope this example and the notes help. I have found the combination of the Datatables and Sqlite plugins to be very productive in generating different interfaces.
Here is the code.
---
title: Challenge
body_classes: title-center header-transparent sticky-footer
cache_enable: false
process:
twig: true
forms:
select-hiker:
action: /mgc
fields:
- name: hiker
type: hidden
- name: hiked
type: hidden
- name: hiker_id
type: hidden
- name: hidden
type: hidden
default: "no"
buttons:
- type: submit
value: Select Hiker
- type: reset
value: Deselect Hiker
- type: submit
value: Hide Table
task: show
process:
- mgc: true
show-hikers:
action: /mgc
fields:
- name: hidden
type: hidden
default: "no"
buttons:
- type: submit
task: show
value: Show Hiker Table
process:
- mgc: true
change-map-style:
action: /mgc
fields:
- name: style
type: select
label: 'Map Style'
options:
outdoors: Outdoors
transport: Show Transport
transport-dark: Transport Dark
landscape: Landscape
mobile-atlas: Mobile Optimised
default: outdoors
process:
- mgc: true
update-hikes-form:
action: /mgc
fields:
- name: peak
type: hidden
- name: peak_name
type: display
label: Peak reached
- name: hiker
type: hidden
- name: hiked
type: hidden
- name: hiker_name
type: hidden
- name: done
type: hidden
- name: group
type: select
label: "Which type of hike was this?"
help: "If not a meetup group, then self-guided. Please provide selfie at top."
options:
'Meetup': 'Meetup Group'
'Self-guided': 'Self Guided'
- name: meetuphike
type: text
validation:
required: true
- name: images
type: file
label: 'Images of hike (jpeg/jpg/png)'
multiple: true
destination: user/images
help: "Image(s) of your hike. Obligatory for self-guided. Only jpeg & png formats accepted"
accept: [ 'image/jpeg', 'image/jpg', 'image/png' ]
buttons:
- type: submit
value: Update peak
- type: reset
value: Reset
process:
- mgc-cleanup: true
- userinfo:
update: true
include:
- hiked
- reset: true
---
{% if userinfo or (mgc and mgc.hiker) %}
## Hiker: {{ (mgc and mgc.hiker)?mgc.hiker:userinfo.hiker }}, Peaks climbed: {{ (mgc and mgc.hiked) ? mgc.hiked : userinfo.hiked }}
{% else %}
## Please login or select hiker
{% endif %}
## Peak Map
[map-leaflet lat=22.387015 lng=114.160555 zoom=11 mapname=mgcpeaks height="600px" style="{{ ( mgc and mgc.style )? mgc.style : '' }}" scale]
[a-markers icon='' iconColor=black ]
[sql-table json]
SELECT latitude as lat, longitude as lng,
printf("%s | %s | %dm",eng_name,cn_name,altitude) as title,
peak_id as text,
CASE
WHEN t1.hiked > 0 AND t1.inf=1 THEN 'salmon'
WHEN t1.hiked > 0 AND t1.inf=2 THEN 'pink'
WHEN t1.hiked > 0 AND t1.inf=3 THEN 'lightblue'
ELSE 'lightgreen'
END as markerColor
FROM peaks
LEFT JOIN (
SELECT trek_id as hiked, peak,
CASE
WHEN meetuphike="No info" THEN 1
WHEN meetuphike="Self-guided" THEN 2
ELSE 3
END AS inf
FROM treks WHERE hiker="{{ mgc.hiker_id?mgc.hiker_id:(userinfo.hiker_id?userinfo.hiker_id:'') }}" ) as t1
ON t1.peak=peaks.peak_id
ORDER BY peak_id ASC
[/sql-table]
[/a-markers]
[/map-leaflet]
<div class="mgc-mk">
<div style="background: #eb7d7f;">No information</div>
<div style="background: #ff91ea;">Self-guided</div>
<div style="background: #88daff;">Registered meetup hike</div>
<div style="background: #bbf970;">Not hiked yet</div>
</div>
{% set options = {"outdoors": "Outdoors",
"transport": "Show Transport",
"transport-dark": "Transport Dark",
"landscape": "Landscape",
"mobile-atlas": "Mobile Optimised" }
%}
{% include "forms/form.html.twig" with { form: forms('change-map-style') } %}
<script>
$('select[name="data[style]"]').on('change', function() {
$('#change-map-style').submit();
});
$(document).ready(function() {
$('option[value="{{ mgc.style }}"]').prop('selected', true);
});
</script>
## Hiker Selection
{% if (mgc and mgc.hidden=="yes") %}
{% include "forms/form.html.twig" with { form: forms('show-hikers') } %}
{% else %}
[datatables]
[sql-table hidden=hiker_id]
SELECT fullname as Hiker, count(t2.peak) as "Peaks Completed", hiker_id FROM hikers as t1
LEFT JOIN treks as t2 ON t1.hiker_id=t2.hiker
GROUP BY t1.hiker_id
[/sql-table]
[dt-script]
var table = $(selector).DataTable();
$(selector + ' tbody').on( 'click', 'tr', function () {
if ( $(this).hasClass('selected') ) {
$(this).removeClass('selected');
$('#select-hiker input[name="data[hiker_id]"]').val('');
$('#select-hiker input[name="data[hiker]"]').val('');
$('#select-hiker input[name="data[hiked]"]').val('');
}
else {
table.$('tr.selected').removeClass('selected');
$(this).addClass('selected');
var rd = table.row('.selected').data();
$('#select-hiker input[name="data[hiker_id]"]').val(rd[2]);
$('#select-hiker input[name="data[hiker]"]').val(rd[0]);
$('#select-hiker input[name="data[hiked]"]').val(rd[1]);
}
} );
$("#select-hiker button").click(function(ev){
ev.preventDefault();
if($(this).attr("name")=="task") {
$('#select-hiker input[name="data[hidden]"]').val("yes");
} else if ($(this).attr('type') == 'reset') {
table.$('tr.selected').removeClass('selected');
$('#select-hiker input[name="data[hiker_id]"]').val(' ');
$('#select-hiker input[name="data[hiker]"]').val(' ');
$('#select-hiker input[name="data[hiked]"]').val(' ');
} else {
$('#select-hiker input[name="data[hidden]"]').val("no");
}
$("#select-hiker").submit();
});
[/dt-script]
[/datatables]
{% include "forms/form.html.twig" with { form: forms('select-hiker') } %}
{% endif %}
## Hike Information
[datatables]
[sql-table hidden="done peaks"]
SELECT peak_id as "Order", eng_name as "English Name", cn_name as "Chinese Name", altitude as "Altitude",
case when t2.hiked > 0 then 1 else 0 end as done,
t3.peaks as peaks,
CASE t2.meetuphike
WHEN 'No info' THEN t2.meetuphike
WHEN 'Self-guided' THEN t2.meetuphike
ELSE '<a href="' || t2.meetuphike || '" target="_blank">_Meetup Group_</a>'
END as "Hiked with",
t2.images as "Images"
FROM peaks as t1
LEFT JOIN (SELECT trek_id as hiked, peak, meetuphike, images FROM treks
WHERE hiker="{{mgc.hiker_id?:(userinfo.hiker_id?:'')}}") as t2
on t1.peak_id = t2.peak,
(SELECT count(trek_id) as peaks FROM treks
WHERE hiker="{{mgc.hiker_id?:(userinfo.hiker_id?:'')}}") as t3
[/sql-table]
[dt-script]
var table = $(selector).DataTable();
table.rows().every( function () {
var peak = this.data();
if ( peak[4] == 1 ) {
$(this.node()).addClass('mgc-hiked');
}
});
$(selector + ' tbody').on( 'click', 'tr', function () {
if ( $(this).hasClass('selected') ) {
$(this).removeClass('selected');
$('#update-hikes-form input[name="data[peak]"]').val('');
$('#update-hikes-form input[name="data[hiker]"]').val('');
$('#update-hikes-form input[name="data[done]"]').val('');
$('#update-hikes-form input[name="data[hiker_name]"]').val('');
$('#update-hikes-form input[name="data[hiked]"]').val('');
$('#update-hikes-form div:first-of-type div:nth-of-type(2) div').html('undefined');
}
else {
table.$('tr.selected').removeClass('selected');
$(this).addClass('selected');
var rd = table.row('.selected').data();
$('#update-hikes-form input[name="data[peak]"]').val(rd[0]);
$('#update-hikes-form input[name="data[hiker]"]').val('{{userinfo.hiker_id}}');
$('#update-hikes-form input[name="data[done]"]').val(rd[4]);
$('#update-hikes-form input[name="data[hiker_name]"]').val('{{userinfo.hiker}}');
var pkdone=rd[5];
if(rd[4] == 0) { pkdone = +pkdone +1; }
$('#update-hikes-form input[name="data[hiked]"]').val( pkdone ); // incremented by operation if new
$('#update-hikes-form div:first-of-type div:nth-of-type(2) div').html(rd[0] + ' - ' +rd[1]+' '+rd[2]+' (' + rd[3] + ')');
}
} );
$('#update-hikes-form').on('reset', function(e) {
setTimeout( function() {
table.$('tr.selected').removeClass('selected');
$('#update-hikes-form input[name="data[peak]"]').val('');
$('#update-hikes-form input[name="data[done]"]').val('');
$('#update-hikes-form input[name="data[hiker]"]').val('');
$('#update-hikes-form input[name="data[hiker_name]"]').val('');
$('#update-hikes-form input[name="data[hiked]"]').val('');
$('#update-hikes-form div:first-of-type div:nth-of-type(2) div').html('undefined');
});
});
$('#update-hikes-form select').on('change', function() {
if ( this.value == "Self-guided") {
$('#update-hikes-form div.form-group:has(input[name="data[meetuphike]"])').css('display','none');
$('#update-hikes-form input[name="data[meetuphike]"]').val("Self-guided");
} else {
$('#update-hikes-form div.form-group:has(input[name="data[meetuphike]"])').css('display','');
$('#update-hikes-form input[name="data[meetuphike]"]').val('');
}
});
[/dt-script]
[/datatables]
{% if userinfo and (userinfo.hiker == grav.user.fullname)
and ((mgc and mgc.hiker == userinfo.hiker) or not mgc) %}
{% include "forms/form.html.twig" with { form: forms('update-hikes-form') } %}
{% endif %}
<img id="photo-area" class="mgc-photo"></img>
<script>
$('.mgc-th img').click(function(){
$('#photo-area').attr('src',$(this).attr('src'));
});
$('.mgc-th span').click(function() {
$('#photo-area').attr('src',$(this).attr('data-src'));
});
$('#photo-area').click(function() { $(this).attr('src',''); });
</script>
If you inspect the code, you will see that I have referenced a form processing action mgc. These are contained in a custom plugin because the code is specific to this particular page. I will not describe them, but for completeness and to help someone who might like to duplicate the effects, here is the code of the mgc.php for the plugin.
<?php
namespace Grav\Plugin;
use Grav\Common\Plugin;
use RocketTheme\Toolbox\Event\Event;
use RocketTheme\Toolbox\File\File;
use Symfony\Component\Yaml\Yaml;
class MgcPlugin extends Plugin
{
public static function getSubscribedEvents()
{
return [
'onPluginsInitialized' => ['onPluginsInitialized', 0]
];
}
public function onPluginsInitialized()
{
// Don't proceed if we are in the admin plugin
if ($this->isAdmin()) {
return;
}
// Enable the main event we are interested in
$this->enable([
'onUserLoginRegisterData' => ['onUserLoginRegisterData',0],
'onFormProcessed' => ['onFormProcessed',0],
'onTwigVariables' => ['onTwigVariables', 0]
]);
}
public function onTwigVariables() {
$this->grav['twig']->twig_vars['mgc'] =
$this->grav['session']->getFlashObject('mgc' );
}
public function onUserLoginRegisterData(Event $event) {
$data = $event['data'];
$un = $data['username'];
$id = $data['hiker_id'];
// adjust the database
$sql =<<<SQL
UPDATE hikers SET username="$un"
WHERE hiker_id="$id"
SQL;
$db = $this->grav['sqlite']['db'];
try {
$db->exec($sql) ;
} catch ( \Exception $e ) {
}
// adjust persistent data
$path = DATA_DIR . 'persistent' . DS . $un . '.yaml';
$datafh = File::instance($path);
$userinfo['hiker'] = $data['fullname'];
$userinfo['hiked'] = $data['hiked'];
$userinfo['hiker_id'] = $id;
$datafh->save(Yaml::dump($userinfo));
chmod($path, 0664);
}
public function onFormProcessed(Event $event) {
$action = $event['action'];
$form = $event['form'];
$data = $form->getData()->toArray();
switch ($action) {
case 'mgc':
$stored = $this->grav['session']->getFlashObject('mgc');
foreach( $data as $key => $value) {
$stored[$key] = ($value == ' ')? null : $value;
}
$this->grav['session']->setFlashObject('mgc', $stored );
$this->grav['twig']->twig_vars['mgc'] = $stored;
break;
case 'mgc-cleanup':
$hkname=str_replace(' ','_', $data['hiker_name'] );
$images = '';
if (isset($data['images']) && is_array($data['images'])) {
foreach( $data['images'] as $img ) {
if ( file_exists($img['path']) ) {
$im = 'user/images/' . "$hkname-" . $this->grav['sqlite']['db']->escapeString($img['name']);
$images .= "<div class=\"mgc-th\"><img src=\"$im\"><span data-src=\"$im\">Image</span></div>";
rename($img['path'], $im );
}
}
}
// for UPDATE if necessary
$set = isset($data['meetuphike']) ? "meetuphike=\"{$data['meetuphike']}\"":'';
if (isset($data['images']) ) {
$set .= ( $set?',':'' ) . "images='$images' ";
}
// check on meetuphike
if (! isset($data['meetuphike'])
|| ! preg_match('/Self\-guided|No info|https\:\/\/www\.meetup.com\/hongkonghikingmeetup\/events\/\d+/',$data['meetuphike']))
{
$data['meetuphike'] = 'invalid';
}
if ( isset($data['done']) && $data['done'] ) {
$sql = <<<SQL
UPDATE treks SET $set WHERE peak="{$data['peak']}" and hiker="{$data['hiker']}"
SQL;
} else {
$sql = <<<SQL
INSERT INTO treks (hiker, peak, meetuphike, images)
VALUES ( "{$data['hiker']}", "{$data['peak']}", "{$data['meetuphike']}", '{$images}')
SQL;
}
$this->grav['sqlite']['db']->exec($sql);
}
}
}
, so I can test Grav with SQLite, I will study your code on this page, but I am not a programmer although I am curious and I like it, and played with PHP/MySQL for some stuff, several times.
