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.

Automatically be notified of any errors on your server

Sometimes a script on your website/server might be causing some errors that you are unaware of. Some users might be seeing these errors under certain circumstances and others not. If users don’t report these errors, you won’t know unless you’re regularly checking your server logs. But no one likes to check server logs as part of their daily routine 🙂 So, here is a one-liner you can add to your crontab file:

0 2 * * * find / -mtime -1 -name "error_log" | mail -E -s "error_logs recently modified" yourmail@server.com

The above line would automatically check your server daily at 2:00 am for any error_log files modified within the last 1 day (this is what the option -mtime -1 above does). If any ones are found, you’ll get an email listing them — don’t forget to change the email address above to your email.

Displaying count of child records on the tab title

AppGini supports displaying a list of child records in the detail view of the parent record. For example, here is the detail view of an employee record from the online Northwind demo:

(Ignore the weird cartoons for now!) So … It would be nice if the count of subordinates is displayed on the ‘Subordinates’ tab, as well as the count of ‘Initiated orders’ on its tab title. One way of doing this is through JavaScript. Add the following code to the generated hooks/footer-extras.php file:

<script>
    $j(function() {
        setInterval(function() {
            $j('.children-tabs tfoot td').each(function() {
                var txt = $j(this).text().trim();
                // The line below assumes your app is in English language .. modify if it's in a different lang.
                var pattern = /Records [0-9]+ to [0-9]+ of ([0-9]+)/g;
                var match, recs;
                match = pattern.exec(txt);
                recs = (match !== null ? match[1] : 0);
                var id = '#' + $j(this).parents('.tab-pane').attr('id').replace(/^panel/, 'tab');

                if(!$j(id + ' .badge').length) {
                    $j('<span class="badge hspacer-md"></span>').appendTo(id);
                }
                $j(id + ' .badge').html(recs);
            })
        }, 1000);
    })
</script>

Afterwards, all child tabs for all tables would display a count of child records. Here is how this looks for our employee above:

The count would auto-update if you add or remove any child records. We might implement this functionality in future releases of AppGini. For now, I hope this helps 🙂