Prevent specific groups from directly accessing a table in AppGini

In some application scenarios, you might meet the usage case where you want one or more user groups to be able to access a table only if it’s displayed as a child table, but not directly as a master table.

Let’s take an example from the Northwind demo. Let’s say you have a users group named CustomerSupport and you don’t want that group to access the orders table unless they are accessing the orders of a specific customer. To clarify, you don’t want this group to directly access all orders like this:

This is the table view of the Orders table of the Northwind demo app. We want to prevent CustomerSupport users group from accessing this page.

However, you still want the group, when they choose a specific customer, to be able to access his orders like this:

The detail view of a specific customer, showing a list of their orders below. We want CustomerSupport users group to be able to access orders only through this page.

So, here is how to do this through a simple AppGini customization.

STEP 1/3: Remove the table link from the homepage

The first step would be to remove the link to the orders table from the homepage if the current user belongs to the CustomerSupport group. This is how the homepage for the Northwind demo looks like before any customization:

A screenshot of the homepage of Northwind app, highlighting the link to orders table that we want to remove for CustomerSupport group.

We’ll add the code for removing the link in hooks/footer-extras.php:

<?php
    $mi = getMemberInfo();
    // hide the table only if the user belongs to this group
    if($mi['group'] == 'CustomerSupport') {
        ?>
        <script>$j(funtion() {
           $j('#orders-tile').remove();
        })</script>
        <?php
    }
?>

Change CustomerSupport in the above code to the actual name of the concerned group. And change orders to the actual name of the table you want to remove the link for. If there are multiple groups you want to hide the link from, change the 4th line to something like this:

if(in_array($mi['group'], ['CustomerSupport', 'Sales', 'Example3'])) {

If you want to hide links to multiple tables rather than just one, change line 7 to something like this:

$j('#orders-tile, #products-tile, #table3-tile').remove();

STEP 2/3: Hide the table link from the navigation menu

The next step is to hide the link to orders table from the navigation menu for the CustomerSupport group. Here is how the navigation menu looks like by default:

The navigation menu of Northwind app. The link to orders table (highlighted) is what we want to remove for specific groups.

To remove that link, we could use a JavaScript statement like this:

$j('.nav a[href^=orders_view]').remove();

It basically tells the browser to remove the link to the orders_view.php page (which is the page showing the orders table) from the navigation bar. We could place that code line in the same code block we wrote in step 1 to avoid rewriting the if condition that checks for the user group. So, here is how our code in hooks/footer-extras.php should look like now:

<?php
    $mi = getMemberInfo();
    // hide the table only if the user belongs to this group
    if($mi['group'] == 'CustomerSupport') {
        ?>
        <script>$j(funtion() {
           $j('#orders-tile').remove();
           $j('.nav a[href^=orders_view]').remove();
        })</script>
        <?php
    }
?>

STEP 3/3: Prevent accessing the page unless it’s in embedded mode

So far, we’ve been using JavaScript code to hide the link to orders from a specific group. This basically hides the link, but doesn’t prevent the users from that group from accessing the orders table if they know how to type the link in the browser navigation bar.

In this step, we’ll actually add server-side code for effectively preventing the CustomerSupport group from accessing the orderstable, even if they manually type the link in the browser.

For doing this, we’ll use the orders_init hook function in hooks/orders.php. All we need to do is check if the current user belongs to the CustomerSupport group and if so, display an access denied message and return false. Returning false from the init hook prevents rendering any part of the table.

But, wait a minute! Do we really want to prevent CustomerSupport users from accessing the orders table entirely? Maybe we still want them to access the detail view of orders when they are viewing the info of a customer, and seeing a list of his orders in a child tab (see the second screenshot in this post). In order to allow this, we’ll need to check if the orders page is in embedded mode or not. Embedded mode is when a table is accessed from the child tab of a parent table, allowing displaying the detail view, but not the table view.

So, our orders_init code would finally read:

function orders_init(&$options, $memberInfo, &$args){
    // for users from CustomerSupport group in non-embedded mode, prevent access
    if($memberInfo['group'] == 'CustomerSupport' && empty($_REQUEST['Embedded'])) {
        echo '<div style="height: 4em;"></div>';
        echo '<div class="alert alert-danger">Access denied.</div>';
        return false;
    }

    return TRUE;
}

And if a user from CustomerSupport tries to access the orders page directly, this is what they’ll get:

Users from CustomerSupport group trying to directly access the orders table would now get an access denied error.

However, they’d be able to access orders of a specific customer by opening the customer details, scrolling down to his orders and clicking on any order to open in embedded mode.

I hope this was helpful. Please feel free to post questions, enhancements and comments below.

A work-around to implement a multiple choice lookup field in your AppGini apps

AppGini provides a multiple choice option list field, which is a handy way to allow users of your app to select multiple values from a list, for example to add tags/labels for a record. This is how it works:

Tags field above allows users to select multiple values from an auto-complete drop-down.

And this is how to configure it in AppGini:

Creating a multiple-choice option list in AppGini. To accommodate a potentially large number of items, the field data type should be Text or similar.

There is a problem with the above approach however. If you want to modify the list (to add new options, edit, or remove existing ones), you have to do this in AppGini, by opening the project file, editing the list of options, regenerating, and re-uploading your application files to the server.

The above is doable, but it’s a lot of pain. There is an easier method fortunately, which is to include a list of options in a file inside the hooks folder. If you create a file in hooks with the special name of {tablename}.{fieldname}.csv (where {tablename} is the name of the concerned table, and {fieldname} is the name of the multiple-choice field, for example products.tags.csv), your AppGini app would use the contents of that magic file to populate the drop-down list when users are filling in that field. It would simply override the list of options configured in AppGini.

Editing this special file is much easier than having to edit the list of options in AppGini, regenerate and re-upload the entire app. The file contents should be a list of options, separated by double semi-colons ;;, just like the format used for option lists in AppGini. For example:

widget;;new;;summer;;light colors;;dark colors;;girls;;boys

Still, editing that file requires accessing the server through FTP, shell, or some online file editor. It’s much easier than re-uploading the entire app, but not easy enough 😀 … Moreover, it still requires admin access to the server. Non-admin users won’t be able to edit that list on their own.

What if you could store the list in another table that can be easily configured to allow some users/groups to add, edit and delete entries? We’ll see how to do this now!

STEP 1: Add a new table to your project

Let’s create the tags table, which would allow users to add/edit product tags, which would show up in the multiple-choice Tags field in products table. It’s a very simple table with just 2 fields: id, and tag:

The tag field should be set as a varchar or similar data type. It will be used to dynamically populate tags drop-down field of the products table.

Next, generate the application, making sure to overwrite existing files.

STEP 2: Configure the hooks for updating the options list file

Let’s now add code so that whenever a use adds, edits or deletes records from the tags table we created in step 1, the options list file would automatically be updated. First, let’s define the update_tags_list() function that would retrieve tags from the table, and use them to populate the options list file. In the generated hooks/tags.php file, append this code block to the end of the file:

And invoke that function after inserting any records in the tags table by calling it in the tags_after_insert() hook:

Do the same in tags_after_update() to update the list if any tags are edited:

And in tags_after_delete() as well:

STEP 3: Upload the app files to your server

We’re ready now to upload the updated app to the server. Make sure to overwrite existing files. If you visit the app in your browser, the new tags table would automatically be created. You can confirm by visiting the tags table and adding a few records:

The tags table, populated with some tags. These will be automatically used to populate the drop-down options list of the Tags field of the products table.

STEP 4: Begin populating your multi-choice list by adding records to the table

Any records added to the tags table should now instantly update the Tags list in the products table:

The options listed when populating the Tags field are now dynamically populated from the tags table.

Your app users can now control the tags list through the tags table if you give them permission to edit it. And this is done instantly without editing the project in AppGini, or uploading any files, or manually editing any files.

Possible enhancements

The above solution has room for enhancement that I’ll leave to readers 🙂

Consider the scenario where you added the tag dark colors for example to a product, then decided later to edit that tag in the Tags table and renamed it to darks … This will not automatically update the tag for products that already have it. Same if you decide to delete a tag from the Tags table. If that tag is already assigned to a product, it will stay assigned to it despite being deleted from Tags.

To fix this, if needed, you should add code to the tags_before_update() hook to get the old value of the tag and perform a search and replace operation to the products.tags field.

Similarly, to handle deleting tags, you should add code to the tags_before_delete() hook to also search for that tag in products.tags and clear it.

Alright, maybe I’ll handle this in some future post if there is enough demand for it 🙂

I hope that helps 🙂 Feel free to post any feedback or questions below.

How to handle many-to-many relations in AppGini

Many-to-many relations (also known as N:M relations) is a way of describing the relationship between two entities/tables where each one could have, or own, or be related to, many items of the other. To clarify this, let’s use the example from WikiPedia, books and authors. An author can write one or more books, and a book can be written by one author or a team of authors.

Now, if we had a 1:N relation instead, for example, if and author can write many books, but a book can be written only by a single author rather than a team of authors, we’d have a simple relationship between books and authors tables. In AppGini, we’d simply create a lookup field in books table to link it to authors as shown below.

The lookup field ‘author’ field inside ‘books’ table tells who is the author of a book. However, this has a limitation where a book can have one and only one author.

A naive approach to many-to-many relations: author1, author2, …

In the AppGini screenshot above, we created a lookup field in the books table to link it to an author. But that’s a single author. What if the book was authored by 2 or 3 authors? A first thought that might jump to our minds in this case is to add 2 more lookups in the books table for author2 and author 3:

Trying to handle the case where a book has more than one author by adding more lookup fields to the ‘books’ table.

But the above approach has several limitations…

First, it’s not scalable. What if we got a new book that has 5, or 10 authors? Yes, that can happen! We’d then have to go back to the project file, add a few more lookup fields, regenerate the app and re-upload it to our web server. Although AppGini makes this process smooth, it’s still a lot of work to add a new book entry. We’d better get our database structure correct at the beginning and keep schema changes to a minimum when users are actually using our app in production environments.

In addition, this makes it harder to perform searches. What is we want to find books wrote by a specific author? We’d then have to look for books whose author1 is x, or author2 is x, or author3 is x … Have 10 author lookup fields in your books table? Good luck with searching through that!

A smarter approach: intermediate (or junction) tables

To avoid the above issues, a much better approach is to create an intermediate table that has a lookup field to books, and another one to authors. This way, if a book has 5 authors, it would have 5 records in the intermediate table, where the book lookup field points to the same book, while the author lookup field points to a different author in each of the 5 records.

An author can write one or more books, and a book can be written by one author or a team of authors. To describe this relationship correctly, we need to have an intermediate (or junction) table that links books to authors. Diagram source: Wikipedia.

Here is how this would look like in AppGini:

‘books_authors’ is an intermediate table to represent the many-to-many relationship between ‘books’ and ‘authors’ by having 2 lookup fields, one to ‘authors’ and another to ‘books’.

Is this a scalable approach? Certainly. Although it’s unlikely to have a book written by 1000 authors, there are other examples where this might be true. Consider a college course taken by thousands of students. A ‘courses_students’ intermediate table can very easily handle this by adding a record for each student enrolled in the course.

Does this make it easier to search? Yep. If you go to the ‘books_authors’ table and search for a specific author, you are searching only one field, the ‘author’ field. And you’d get all books written by that author, whether she wrote it alone, or with other authors.

Furthermore, we could now use the parent/children feature in AppGini so that when we’re viewing an author, we can see a list of books she wrote below the author details, and when we’re viewing a book, we can see the list of authors of that book below the book details.

I hope that helps. Please feel free to post any questions below.

Using full-text searches with AppGini apps

I recently received a very interesting question from an AppGini user regarding implementing full-text searches in AppGini apps. The user had a table containing millions of records, and normal search operations on a text field took too long (about 1.5 minutes according to the user). When he tried adding a full-text index on that field, and using full-text search, search time dropped dramatically to several milliseconds. Unfortunately, AppGini doesn’t yet support full-text searches. So, in this post, we’ll try to address that using hooks.

Full-text search in MySQL/MariaDB databases lets users run full-text queries against character-based data in a table. You must create a full-text index on the table before you run full-text queries on that table. This can be done in phpMyAdmin, or any other MySQL admin utility, by executing a command similar to this:

ALTER TABLE `customers` ADD FULLTEXT INDEX `Address` (`Address`)

The above would add a full-text index on the ‘Address’ field of the ‘customers’ table. Full-text indexes can be used only with MyISAM tables. (In MySQL 5.6 and up, they can also be used with InnoDB tables.) Full-text indexes can be created only for CHAR, VARCHAR, or TEXT columns. If you’re using MariaDB, check full-text compatibility here.

Having created a full-text index on a field, we’d like now to change normal search filters performed on that field into full-text searches. Normal search using the LIKE operator looks something like this:

`customers`.`Address` LIKE '%place%'

This might get very slow for TEXT fields in huge tables that have millions of records. Applying a normal index on the field won’t fix this since normal indexes work only on the first 765 bytes of the field. If you’re searching beyond those first 765 bytes, you’re out of luck with normal indexes. But full-text indexes come to the rescue in this scenario. The full-text search clause in that case would look something like this:

MATCH(`customers`.`Address`) AGAINST ('place' IN BOOLEAN MODE)

The question now is how to apply the above search clause in your AppGini app? We’ll walk through one method that is easy to apply using hooks. First, we’ll add 2 new functions to the hooks/__global.php file. We can add them anywhere, but, for simplicity’s sake, let’s append them to the end of the file:

function use_full_text_search($field_index, $field_name, &$options) {
	// if we have a LIKE/NOT LIKE filter on full-text field,
	// 1. remove it from REQUEST
	// 2. inject a hint in REQUEST to re-apply the filter info later in _footer hook
	// 3. add a full-text search on field in the WHERE clause
	if(!isset($_REQUEST['FilterField'])) return;

	// avoid this procedure in filter page
	if(!empty($_REQUEST['Filter_x'])) return;

	// also avoid it when emptying filters by clicking 'Show all'
	if(!empty($_REQUEST['NoFilter_x'])) return;

	foreach($_REQUEST['FilterField'] as $i => $field) {
		if($field != $field_index) continue; // not our full-text field
		
		$combine = $_REQUEST['FilterAnd'][$i];
		$op = $_REQUEST['FilterOperator'][$i];
		$search = $_REQUEST['FilterValue'][$i];
		
		if($combine != 'or') $combine = 'and';
		if(!in_array($op, array('like', 'not-like'))) continue;
		if(!$search) continue;

		// remove filter from REQUEST
		$_REQUEST['FilterAnd'][$i] =
		$_REQUEST['FilterField'][$i] =
		$_REQUEST['FilterOperator'][$i] =
		$_REQUEST['FilterValue'][$i] = '';

		// inject hint to REQUEST to re-apply the filter later in _footer hook
		$_REQUEST['full-text'] = json_encode(array(
			'i' => $i,
			'FilterAnd' => $combine,
			'FilterField' => $field_index,
			'FilterOperator' => $op,
			'FilterValue' => $search
		));

		// add a full-text search on field in the WHERE clause
		if(!$options->QueryWhere)
			$options->QueryWhere = ' where ';
		else
			$options->QueryWhere .= ' and ';
		$options->QueryWhere .= ($op == 'not-like' ? 'NOT ' : '') . 
			'MATCH(' . $field_name . ') AGAINST (\'' . makeSafe($search) . '\' IN BOOLEAN MODE) ';
	}
}

function reapply_pre_full_text_filter($footer) {
	// if we have a REQUEST hint added from _init hook,
	// re-apply the removed filter
	if(!empty($_REQUEST['full-text'])) {
		$filter = json_decode($_REQUEST['full-text']);
		if(!$filter) return $footer;
		if(empty($filter->FilterAnd)) return $footer;
		if(empty($filter->FilterOperator)) return $footer;
		if(empty($filter->FilterField)) return $footer;
		if(empty($filter->FilterValue)) return $footer;

		// add hidden inputs to the form for the removed filter
		ob_start(); ?>
		<script>$j(function() {
			var filter = <?php echo $_REQUEST['full-text']; ?>;
			var addHidden = function(i, name, val) {
				$j('<input type="hidden"></input>')
					.attr('name', name + '[' + i + ']')
					.attr('value', val)
					.appendTo('form');
			};

			addHidden(filter.i, 'FilterAnd', filter.FilterAnd);
			addHidden(filter.i, 'FilterField', filter.FilterField);
			addHidden(filter.i, 'FilterOperator', filter.FilterOperator);
			addHidden(filter.i, 'FilterValue', filter.FilterValue);
		})</script><%%FOOTER%%>
		<?php
		$footer .= ob_get_clean();

		return $footer;
	}
}

The above defines 2 functions: use_full_text_search() and reapply_pre_full_text_filter(). The first function should be called inside the init hook for our table, and what it does is prevent our AppGini application from performing normal search with LIKE operator on our full-text field. This is done by removing any defined LIKE searches on that field.

The second function should be called inside the footer hook. Its purpose is to restore the removed LIKE search so that it’s still visible and configurable in the filters page. So, basically, we’re removing the filter before our AppGini app constructs the SQL query, and then adding it back after the query has been executed.

Applying the above 2 functions in our table hooks

After defining the functions above in global hooks, we should now use them in our table. Let’s continue assuming that our table is named ‘customers’, and our full-text field is named ‘Address’. We also need to know the index (order) of that field in the table, as defined in AppGini. For example, if that field is the fifth field, its index is 5, as shown in this screenshot:

The index of the 'Address' field is 5 since it's the fifth field in our table, as displayed in AppGini. This is regardless of the actual order of the field in the database.
This is how to retrieve the index of a field from the AppGini project.

Now that we know all the necessary information, let’s open the hooks/customers.php hook file in our preferred text editor, and let’s add this line to the customers_init() hook before the return statement:

use_full_text_search(5, '`customers`.`Address`', $options);

‘5’ is the index of the field as explained above, and then we pass the field name, including the table name. Next, let’s add this line to the customers_footer() hook before the return statement:

$footer = reapply_pre_full_text_filter($footer);

That’s all we need to do. Now, whenever any user defines a LIKE or NOT LIKE filter on the ‘Address’ field, our code would automatically change that to a full-text search on the field, dramatically reducing the query time.

Limitations/possible enhancements

This is a quick and experimental post that I wrote hastily in response to a user question. There are several limitations for the above code listed below. The purpose of the post is to act as a starting point for anyone who’d like to perform full-text search in AppGini, rather than a fully-working solution. Here are the limitations I’m aware of:

  • The above code won’t work unless the field has a full-text index applied to it.
  • If all the data in the concerned field has a length of less than 756 bytes (which translates roughly to 255 characters if using Unicode encoding), it’s much simpler to apply a normal index and avoid all the above procedure. The above method works best with longer fields.
  • The above code assumes only one single full-text field per table. If you have more than one field, you might need to modify the code to adapt to all fields, but I’ll leave this as an exercise to you 😉
  • The above code probably works fine if you define only one filter. It might not work when defining multiple filters, but I haven’t investigated this. Might revisit this post later and fix that, or if you have suggestions, please feel free to post your comment below.
  • Moreover, the above code won’t work with quick search. I welcome contributions for applying full-text search to quick search 🙂

Fixing issues when trying to import CSV data to your AppGini app

Importing CSV data to AppGini is pretty easy using the CSV import page in the admin area. However, if the CSV file is mal-formatted, it might be partially imported, or not at all. A pretty easy way to check and validate your CSV file if it’s not importing correctly is to upload it to CSVLint. After uploading, click the ‘Validate’ button to see validation results. This should tell you if the CSV file has structure errors that prevent it from being parsed correctly when importing it, and suggests a fix for it as well.

Warning: CSVLint allows you to either specify the URL of the CSV file you want to validate, or to upload it. If you specify a URL, CSVLint assumes that this URL is public and publishes it! If you have sensitive data in your CSV file, consider installing the linter, which is open source, on a private PC/server instead.

Here is an example report showing validation errors for a CSV file that I received from a user — Data omitted from the screenshot for privacy.

CSVLint is a quick and easy way to validate CSV files. If you see errors like the above, try fixing them by clicking Download Standardized CSV button, then retry importing the fixed CSV file to your AppGini application.

How to hide columns in table view based on user group?

I got a question from an AppGini user on how to hide specific columns in the table view based on the user group. This blog post explains how to do this through hooks.

As an example, we’ll modify the orders table of the Northwind demo to hide the ‘Customer’ column for ‘Suppliers’ and ‘anonymous’ groups, while keeping it visible for all others, as demonstrated in the following 2 screenshots:

Orders table, with all columns visible, including the ‘Customer’ column.
Orders table, with the ‘Customer’ column hidden for specific user groups as we’re going to see how in this tutorial.

Now, we could do this in a trivial way through JavaScript, or in a more robust way through server-side PHP code. The JavaScript method would work until some ‘geek’ user figures out how to use the browser console to unhide the column. The more robust server-side method is a more secure way that users can’t circumvent. And that’s what we’re going to explain here.

Step 1: Copy a few lines from the generated {tablename}_view.php file

{tablename} in our example is ‘orders’. So, let’s open the generated orders_view.php file in a text editor and search for a line starting with $x->ColWidth. We should copy that line and the following ones until the line starting with $x->SelectedTemplate. For the Orders table of Northwind, these lines read:

$x->ColWidth   = array(  75, 200, 150, 100, 150, 150);
$x->ColCaption = array("Order ID", "Customer", "Employee", "Order Date", "Ship Via", "Ship Country");
$x->ColFieldName = array('OrderID', 'CustomerID', 'EmployeeID', 'OrderDate', 'ShipVia', 'ShipCountry');
$x->ColNumber  = array(1, 2, 3, 4, 7, 14);

// template paths below are based on the app main directory
$x->Template = 'templates/orders_templateTV.html';
$x->SelectedTemplate = 'templates/orders_templateTVS.html';

Step 2: Paste the copied lines into the {tablename}_init hook

For our example again, that would be the orders_init hook in the generated hooks/orders.php file. After pasting the code we copied in step 1, that hook function would read:

function orders_init(&$options, $memberInfo, &$args){
		$x->ColWidth   = array(  75, 200, 150, 100, 150, 150);
		$x->ColCaption = array("Order ID", "Customer", "Employee", "Order Date", "Ship Via", "Ship Country");
		$x->ColFieldName = array('OrderID', 'CustomerID', 'EmployeeID', 'OrderDate', 'ShipVia', 'ShipCountry');
		$x->ColNumber  = array(1, 2, 3, 4, 7, 14);

		// template paths below are based on the app main directory
		$x->Template = 'templates/orders_templateTV.html';
		$x->SelectedTemplate = 'templates/orders_templateTVS.html';

	return TRUE;
}

Note that we added an extra indentation level (via Tab or spaces, whichever you prefer) while pasting the code. This is for a reason that will be revealed below.

Step 3: Replace ‘$x’ with ‘$options’, and ‘templates’ with ‘hooks’

After pasting the code in step 2, we need to change $x into $options and templates into hooks:

function orders_init(&$options, $memberInfo, &$args){
		$options->ColWidth   = array(  75, 200, 150, 100, 150, 150);
		$options->ColCaption = array("Order ID", "Customer", "Employee", "Order Date", "Ship Via", "Ship Country");
		$options->ColFieldName = array('OrderID', 'CustomerID', 'EmployeeID', 'OrderDate', 'ShipVia', 'ShipCountry');
		$options->ColNumber  = array(1, 2, 3, 4, 7, 14);

		// template paths below are based on the app main directory
		$options->Template = 'hooks/orders_templateTV.html';
		$options->SelectedTemplate = 'hooks/orders_templateTVS.html';

	return TRUE;
}

Step 4: Remove the references to the column we want to hide

In our example, we’d like to hide the ‘Customer’ column. This is the second column in the table view. To remove references to it in the init hook, we should remove the second item in each of the 4 arrays present in lines 2-5 in our code:

function orders_init(&$options, $memberInfo, &$args){
		$options->ColWidth   = array(  75, 150, 100, 150, 150);
		$options->ColCaption = array("Order ID", "Employee", "Order Date", "Ship Via", "Ship Country");
		$options->ColFieldName = array('OrderID', 'EmployeeID', 'OrderDate', 'ShipVia', 'ShipCountry');
		$options->ColNumber  = array(1, 3, 4, 7, 14);

		// template paths below are based on the app main directory
		$options->Template = 'hooks/orders_templateTV.html';
		$options->SelectedTemplate = 'hooks/orders_templateTVS.html';

	return TRUE;
}

If we need to hide more columns, we should do the same for each.

Step 5: Add a conditional check for user group

So far, we’ve hidden the ‘Customer’ column header for all users. We need to wrap our code inside a conditional check to hide the column only for specific user groups:

function orders_init(&$options, $memberInfo, &$args){
	$hide_groups = array('anonymous', 'Suppliers');
	if(in_array($memberInfo['group'], $hide_groups)) {
		$options->ColWidth   = array(  75, 150, 100, 150, 150);
		$options->ColCaption = array("Order ID", "Employee", "Order Date", "Ship Via", "Ship Country");
		$options->ColFieldName = array('OrderID', 'EmployeeID', 'OrderDate', 'ShipVia', 'ShipCountry');
		$options->ColNumber  = array(1, 3, 4, 7, 14);

		// template paths below are based on the app main directory
		$options->Template = 'hooks/orders_templateTV.html';
		$options->SelectedTemplate = 'hooks/orders_templateTVS.html';
	}
	return TRUE;
}

We’ve added a line listing the user groups we want to hide the ‘Customer’ column from: ‘anonymous’ and ‘Suppliers’. Then we wrapped the code we pasted in the previous steps inside an if check. This would cause the code for hiding the column to apply only if the user belongs to any of the groups we specified. Note that because we double-indented the pasted code in step 2, it’s now properly formatted after wrapping it inside the if block.

We’ve now finished all the necessary edits for the init hook. In the following steps, we’ll make a copy of the table view templates to remove the ‘Customer’ table from there as well.

Step 6: Copy the table view templates to the hooks folder

The table view templates are stored in the templates folder, and named {tablename}_templateTV.html and {tablename}_templateTVS.html. For our example, these are orders_templateTV.html and orders_templateTVS.html. We should copy those 2 files to the hooks folder. We’ll then edit the copied templates to remove the ‘Customer’ column.

To clarify this step a little more, we now have 2 sets of templates for the table view of the orders table: the default one in the templates folder would apply to all groups except ‘anonymous’ and ‘Suppliers’, and the copied one inside hooks that would apply to those 2 groups.

Step 7: Edit the copied templates to remove the column

Let’s open the copied hooks/orders_templateTV.html file in our text editor. It should read:

<td id="orders-OrderID-<%%VALUE(OrderID)%%>" class="orders-OrderID text-right"><%%SELECT%%><%%VALUE(OrderID)%%><%%ENDSELECT%%></td>
<td id="orders-CustomerID-<%%VALUE(OrderID)%%>" class="orders-CustomerID"><%%SELECT%%><%%VALUE(CustomerID)%%><%%ENDSELECT%%></td>
<td id="orders-EmployeeID-<%%VALUE(OrderID)%%>" class="orders-EmployeeID"><%%SELECT%%><%%VALUE(EmployeeID)%%><%%ENDSELECT%%></td>
<td id="orders-OrderDate-<%%VALUE(OrderID)%%>" class="orders-OrderDate"><%%SELECT%%><%%VALUE(OrderDate)%%><%%ENDSELECT%%></td>
<td id="orders-ShipVia-<%%VALUE(OrderID)%%>" class="orders-ShipVia"><%%SELECT%%><%%VALUE(ShipVia)%%><%%ENDSELECT%%></td>
<td id="orders-ShipCountry-<%%VALUE(OrderID)%%>" class="orders-ShipCountry"><%%SELECT%%><%%VALUE(ShipCountry)%%><%%ENDSELECT%%></td>

To remove the ‘Customer’ column, we should remove the second line:

<td id="orders-OrderID-<%%VALUE(OrderID)%%>" class="orders-OrderID text-right"><%%SELECT%%><%%VALUE(OrderID)%%><%%ENDSELECT%%></td>
<td id="orders-EmployeeID-<%%VALUE(OrderID)%%>" class="orders-EmployeeID"><%%SELECT%%><%%VALUE(EmployeeID)%%><%%ENDSELECT%%></td>
<td id="orders-OrderDate-<%%VALUE(OrderID)%%>" class="orders-OrderDate"><%%SELECT%%><%%VALUE(OrderDate)%%><%%ENDSELECT%%></td>
<td id="orders-ShipVia-<%%VALUE(OrderID)%%>" class="orders-ShipVia"><%%SELECT%%><%%VALUE(ShipVia)%%><%%ENDSELECT%%></td>
<td id="orders-ShipCountry-<%%VALUE(OrderID)%%>" class="orders-ShipCountry"><%%SELECT%%><%%VALUE(ShipCountry)%%><%%ENDSELECT%%></td>

Finally, we’ll do the same for hooks/orders_templateTVS.html:

<td id="orders-OrderID-<%%VALUE(OrderID)%%>" class="orders-OrderID text-right"><%%VALUE(OrderID)%%></td>
<td id="orders-EmployeeID-<%%VALUE(OrderID)%%>" class="orders-EmployeeID"><%%VALUE(EmployeeID)%%></td>
<td id="orders-OrderDate-<%%VALUE(OrderID)%%>" class="orders-OrderDate"><%%VALUE(OrderDate)%%></td>
<td id="orders-ShipVia-<%%VALUE(OrderID)%%>" class="orders-ShipVia"><%%VALUE(ShipVia)%%></td>
<td id="orders-ShipCountry-<%%VALUE(OrderID)%%>" class="orders-ShipCountry"><%%VALUE(ShipCountry)%%></td>

By now, we’re done! If a guest (anonymous) user, or a user from the ‘Suppliers’ group visits the orders table, they won’t be able to see the ‘Customer’ field in the table view. For all other user groups, the ‘Customer’ field would be visible.

Further improvements

The above steps hide the ‘Customer’ column for ‘anonymous’ and ‘Suppliers’ groups, but only in the table view (this includes the print view as well). However, if users from those 2 groups have access to the detail view, they would be able to see the hidden ‘Customer’ column. So we need to hide it from the detail view as well, but we’ll leave that to another future post.

In addition, if we enable the option Allow saving data to CSV files in AppGini, those users would be able to click the ‘Save CSV’ button, and they’d be able to access the ‘Customer’ field in the exported dump. To avoid this, we could disable saving CSV for specific user groups (see the example for tablename_init hook), or we could modify the CSV query to remove a certain field for specific groups in a method similar to this, but for the $options->QueryFieldsCSV property.

Overwriting AppGini functions, allowing admin group members to access the admin area

You might already know that AppGini applications can be extended through hooks. Hooks allow you to intercept certain events, like when a user submits a new record to be inserted into a table, or when a record has been updated, .. etc. The great benefit of using hooks is that you write them once, and they persist even if you re-generate your AppGini application any number of times later. So, it’s a write once and forget way of extending your AppGini apps.

Psst! Want to learn more about using AppGini hooks, even if you’re not a programmer? Check our online video course.

Sometimes, however, the modification you want to make might have no relevant hook. In that case, you might find the only way to apply that modification is to manually edit the generated code. This is a painful process that I usually advise against unless it’s absolutely necessary. The problem with this method is that your changes would be overwritten if you re-generate your code later, and you’ll have to re-apply them. Moreover, there is a big chance those changes might not work correctly in future versions of AppGini. Using Git or a similar source code management tool might help, but it’s still a pain.

In this post, we’ll discuss a method that makes this process a little easier. You can use it to replace one or more PHP functions defined by AppGini with your own version. It works by looking for the function body and comparing it with our custom version. If they are not the same, our custom version replaces the one generated by AppGini. This is all performed in an automated manner and the only action we need to take is call a script once after we re-generate our AppGini app.

Let’s start by creating a new file inside the hooks folder, hooks/replace-appgini-functions.php, and adding the following code to it:

<?php // Save this file as 'hooks/replace-appgini-functions.php'

	$hooks_dir = dirname(__FILE__);
	include("{$hooks_dir}/../defaultLang.php");
	include("{$hooks_dir}/../language.php");
	include("{$hooks_dir}/../lib.php");
	
	// Step 1: Specify the file containing the function we want to overwrite
	$appgini_file = "{$hooks_dir}/../admin/incFunctions.php";

	// Step 2: Specify the file containing our version of the function
	$mod_file = "{$hooks_dir}/mod.getLoggedAdmin.php";

	// Step 3: Specify the name of the function we want to overwrite
	$func_name = 'getLoggedAdmin';

	echo "<br>{$func_name}: " . replace_function($appgini_file, $func_name, $mod_file);

	#######################################

	function replace_function($appgini_file, $function_name, $mod_file) {
		// read the new code from the mod file
		$new_code = @file($mod_file);
		if(empty($new_code)) return 'No custom code found.';

		// remove the first line containing PHP opening tag and keep the rest as $new_snippet
		array_shift($new_code);
		$new_snippet = implode('', $new_code);

		$pattern1 = '/\s*function\s+' . $function_name . '\s*\(.*\).*(\R.*){200}/';
		$pattern2 = '/\t#+(.*\R)*/';

		$entire_code = file_get_contents($appgini_file);
		if(!$entire_code) return 'Invalid AppGini file.';

		$m = [];
		if(!preg_match_all($pattern1, $entire_code, $m)) return 'Function to replace not found.';
		$snippet = $m[0][0];

		if(!preg_match_all($pattern2, $snippet, $m)) return 'Could not find the end of the function.';
		$snippet = str_replace($m[0][0], '', $snippet);

		$snippet_nocrlf = str_replace("\r\n", "\n", $snippet);
		$new_snippet_nocrlf = str_replace("\r\n", "\n", $new_snippet);
		if(trim($snippet_nocrlf) == trim($new_snippet_nocrlf)) return 'Function already replaced.';

		// back up the file before overwriting
		if(!@copy(
			$appgini_file, 
			preg_replace('/\.php$/', '.backup.' . date('Y.m.d.H.i.s') . '.php', $appgini_file)
		)) return 'Could not make a backup copy of file.';

		$new_code = str_replace(trim($snippet), trim($new_snippet), $entire_code);
		if(!@file_put_contents($appgini_file, $new_code)) return "Couldn't overwrite file.";

		return 'Function overwritten successfully.';
	}

To make the code easier to follow, I’ve added comments above to mark the 3 steps we need to apply to change the above code.

Step 1: Specify the file containing the function we want to overwrite

In this step, we should provide the full path to the file to be modified. The above code can replace AppGini functions defined only in incCommon.php or admin/incFunctions.php. Functions defined in other generated files won’t be replaced using the above code, but you could make some modifications to the search algorithm if you wish in order to make this possible.

Step 2: Specify the file containing our version of the function

In this example, we’re going to modify the getLoggedAdmin() function. This function is responsible for authenticating the super admin user, controlling who can access the admin area of your AppGini application. AppGini allows only a single user to access that area. We’ll modify this function to allow any user in the ‘Admins’ group to access the admin area. Let’s define the customized code in a file that we’ll name hooks/mod.getLoggedAdmin.php:

<?php // save this file as 'hooks/mod.getLoggedAdmin.php'
	function getLoggedAdmin() {
		// checks whether the current user is a member of the Admins group or not
		// if not, it returns false
		// if yes, it sets the session variable for super admin and returns the user id

		$adminConfig = config('adminConfig');
		if(!isset($_SESSION['memberID']) || empty($_SESSION['memberID'])) return false;

		$current_user = $_SESSION['memberID'];
		$current_user_safe = makeSafe($_SESSION['memberID']);
		$is_admin = sqlValue("SELECT COUNT(1) FROM 
			membership_users u LEFT JOIN membership_groups g ON u.groupID = g.groupID
			WHERE LCASE(u.memberID) = LCASE('{$current_user_safe}') AND
			g.name = 'Admins'");
		if(!$is_admin) return false;

		$_SESSION['adminUsername'] = $current_user;
		return $current_user;
	}

The above code is our own version of the getLoggedAdmin() function, allowing any user that belongs to Admins group to access the admin area.

Step 3: Specify the name of the function we want to overwrite

Finally, we’ll specify that we want to change the function named getLoggedAdmin.

How do we apply the modification?

We’ve now created 2 files inside the hooks folder: replace-appgini-functions.php and mod.getLoggedAdmin.php. Whenever you want to regenerate your AppGini application, all you need to do is to call replace-appgini-functions.php once to apply your modified code. As a safety measure, the file to be modified will be backed up first. Also, our code above would check if modifications were already applied, and if so they won’t be re-applied. So, it’s actually safe to call this file multiple times. Only the first time will apply modifications, and others would do nothing and just notify you that changes were already applied.

Assuming our app is hosted on example.com/staff-db, to apply the modified getLoggedAdmin() function, we should visit this URL from a browser: example.com/staff-db/hooks/replace-appgini-functions.php

Changing multiple functions

The same method above could be used to modify multiple functions. Simply repeat steps 1 to 3 for each function. Make sure to store each modified function in a separate mod file. For example, let’s say we’re going to modify getLoggedAdmin() and sendmail(). We should store the new code of getLoggedAdmin() in hooks/mod.getLoggedAdmin.php, and the new code of sendmail() in hooks/mod.sendmail.php.

Next, we should modify the steps in hooks/replace-appgini-functions.php to the following:

	// Step 1: Specify the file containing the function we want to overwrite
	$appgini_file = "{$hooks_dir}/../admin/incFunctions.php";

	// Step 2: Specify the file containing our version of the function
	$mod_file = "{$hooks_dir}/mod.getLoggedAdmin.php";

	// Step 3: Specify the name of the function we want to overwrite
	$func_name = 'getLoggedAdmin';

	echo "<br>{$func_name}: " . replace_function($appgini_file, $func_name, $mod_file);

	// Step 1: Specify the file containing the function we want to overwrite
	$appgini_file = "{$hooks_dir}/../admin/incFunctions.php";

	// Step 2: Specify the file containing our version of the function
	$mod_file = "{$hooks_dir}/mod.sendmail.php";

	// Step 3: Specify the name of the function we want to overwrite
	$func_name = 'sendmail';

	echo "<br>{$func_name}: " . replace_function($appgini_file, $func_name, $mod_file);

After every time you regenerate your AppGini app, visit replace-appgini-functions.php once in your browser to apply all modifications. I hope you find this approach much more convenient than manually modifying generated files. Comments are welcome 🙂

Moving an AppGini application to a new server

So, you have an AppGini-generated application that is up and running on your server. You then decided you want to move it to a new server. May be because you found a better hosting service, or you’re upgrading the server, or for any other reason. This is a simple checklist of the steps you should follow to make sure your app and data are moved successfully without any data losses, and with minimum downtime.

  1. On the old server you’re moving from, sign in to your AppGini app as admin, and go to the admin area.
  2. Open the Utilities menu > Database backups.
  3. Create a new backup.
  4. Go to the admin homepage by clicking the link to the top left of the page.
  5. Switch to maintenance mode. This would prevent users form modifying the database during migration. You might wish to edit the maintenance mode message displayed to users to tell them when the application is expected to be up again. To do so, open the Utilities menu > Admin settings, and scroll down to the “Maintenance mode message”, where you can edit the default message.
  6. Copy the entire application folder to your new server (to a folder under your web document root). Depending on your server configuration, this could be done using FTP, or SSH, or through the file manager under your server hosting control panel.
  7. On the new server, delete the config.php and setup.md5 files from the copied app folder. Also, under the ‘admin’ folder, delete the file ‘.maintenance’ (yes, it begins with a dot!)
  8. Open the URL of the application on your new server in a web browser. You should see the setup page.
  9. Enter your database login info and your admin user info (admin info should be the same as the old app: same admin username, password and email)
  10. After setup is finished, go to the admin area on the newly set-up app and open the Utilities menu > Database backups.
  11. You should see the backup file you made on your old server. Restore it to retrieve your data to the new server.

Following the above steps, your app should be ready on the new server, with all the data and uploaded files. Depending on your setup, you might also need to adjust your DNS settings to point your domain to the new server.

Trying to detect your application URI using DOCUMENT_ROOT? Read this first!

The application URI is the part of the URL after the host name (domain name) and before the home of your application. For example, for this blog, the URI is /blog/. If you are writing a PHP application to be distributed and installed in many environments that you have no idea how they are configured, you need to write your application to be as generic as possible when it comes to handling environment parameters. And one of the things you should not assume, is the path in which the application is going to be installed, and accordingly, the application URI.

I used to do the following in my applications to detect the application URI, and I thought that was generic enough to work on any environment:

1. function app_uri() {
2.     $doc_root = $_SERVER['DOCUMENT_ROOT'];
3.     $helper_path = dirname(__FILE__);
4.
5.     $lib_uri = str_replace($doc_root, '', $helper_path);
6.     // returning the app uri after removing 'lib'
7.     return substr($lib_uri, 0, -3);
8. }

I’d place the above function into a helper file and include it in all application pages. calling app_uri() should then return the application URI and it should, presumably, work on all environments, right? Wrong! Sorry!

First of all, let’s see how the above function works, and then see where it would fail. Line 2 above uses DOCUMENT_ROOT to retrieve the path to the main directory for serving pages from our web server. This would, in most Debian systems, for example, return /var/www/html.

Line 3 would return the path to the file containing the app_uri() function. This might be something like /var/www/html/blog/lib, assuming the helper file is stored inside a sub directory named ‘lib’. Now, retrieving the application URI would be a simple matter of removing the DOCUMENT_ROOT from that path, and removing the ‘lib’ sub directory at the end, which is what line 7 does, returning the precious /blog/ URI.

This used to work beautifully, until it didn’t! Like I mentioned above, we have no idea whatsoever about the environments in which our app is going to be deployed. Making the assumption that the above works everywhere was, as I found painfully, an arrogant claim!

Some environments are configured so that the document root is a symbolic link. Maybe, to make things easier for users of a server, the server administrator decided to define a symbolic link in each user’s home directory named public_html that links to a directory in their name under /var/www … For example, let’s say our user is johndoe, and his home directory on the server is /home/johndoe

When johndoe lists his home directory contents, he sees public_html and so he installs our app into that directory. So, is our app installed into /home/johndoe/public_html/blog? Well, not really. Since public_html is actually a symbolic link, the real path to our app is /var/www/johndoe/blog.

The problem with DOCUMENT_ROOT is that it doesn’t resolve real paths .. so it would return /home/johndoe/public_html … OTOH, __FILE__ does resolve the real path, thus it would return /var/www/johndoe/blog/lib … Suddenly, our beautiful little function above that used to work elegantly no longer works!

I tried fixing this by applying realpath() to DOCUMENT_ROOT .. Unfortunately, some server configurations would fail that as well.

So, after researching the above for hours, I came to the conclusion that the ONLY accurate way of detecting the application URI is not to try! Simply avoid DOCUMENT_ROOT and __FILE__ altogether, and write the URI into a config file:

// db parameters
...
// app uri
define('APPLICATION_URI', '/blog/');

And the above line would be added to the config file by the setup script that runs the first time the app is installed to the server, and can be obtained from dirname($_SERVER['REQUEST_URI']). It seems this is indeed the way popular open source PHP apps/frameworks retrieve the application URI .. they don’t try to detect it from DOCUMENT_ROOT .. they just save it as a fixed predefined config value. I didn’t find this explicitly mentioned anywhere I searched. I guess this is because people usually write about how to do things rather than how not to do them! So, I thought I’d write a post about this.

To sum up, don’t try to detect the application URI from DOCUMENT_ROOT … you’re doing it wrong this way, even if it seems to work! Save yourself the trouble, detect it once during setup using REQUEST_URI, save that to a config file, and read it from there from now on!

Make it mandatory for new users to agree to terms and conditions before signing up

I got a question from an AppGini user on how to make it a requirement for users to check an ‘I agree’ box when they sign up to AppGini — possibly as part of GDPR compliance, or to agree to some other terms and conditions.

We do plan to add support for this as part of future versions of AppGini. However, for now it can be done through a quick JavaScript code customization in hooks/footer-extras.php. The steps in brief are: check if the current page is the signup page, and if so, change custom field #4 to a checkbox, and prevent form submission if the box is unchecked.

The steps above make use of one of the custom user fields and turn it into a checkbox. In AppGini, the user profile supports storing up to 4 customizable fields. If you follow the steps here, one of them will be used for the checkbox, so you’ll have only 3 left. It’s a caveat but can be used as a work-around for now till we implement it as a separate feature in AppGini. So, the code to be placed in hooks/footer-extras.php would look like this:

You could change the wording of the checkbox by editing line 12 above. Also, you could change the error message by changing line 17. You’ll need to add a file containing the terms and conditions in the hooks folder, naming it terms.php. It might look something like this:

Change the wording above as you see fit. Please let me know if you have any questions or comments.