Category Archives: PHP

New Mobile Theme for Xataface

During my two week “vacation” from Codename One, I’ve been madly working on a new project using Xataface. For this project, I really needed the mobile interface to be smooth, so I decided to finally make Xataface’s core theme responsive. Along the way, I also made numerous improvements to the flow of the UI especially in relation to sorting and filtering results. Before I go into detail about the new features, here are some screenshots of my app, which uses this new mobile theme.

The new login screen is much cleaner and mobile friendly.
The new login screen is much cleaner and mobile friendly.
The mobile registration form - fields generated based on the fields in the users table.
The mobile registration form – fields generated based on the fields in the users table.
The list view for the "News Feed" table.
The list view for the “News Feed” table.

Screen Shot 2020-08-30 at 7.21.34 AM

Let me unpack that above screenshot of the list view to highlight the various aspects you can see here.

  1. The “tables” menu is rendered along the bottom of the screen as tabs.Table tabs
  2. Sorting and filtering buttons are rendered at the top of the list. Sort and filter buttons When you scroll down the page, these buttons are converted into floating buttons: Floating sort and filter buttons
  3. Notice the Floating Action Button in the lower right for adding new records. Floating Action Button. By default this shows the “New” and “Delete” actions, but you can add your own actions to this menu using the “table_actions_menu” category.
  4. Notice that action icons below each row. These are rendered from the “list_row_actions” category. List row actions

Sorting

Clicking on the “Sort” button displays a sheet with the various options available for sorting.The sort dialog. You can select which columns should be sortable in the fields.ini file using the new sortable directive.

Filtering

Clicking on the “Filter” button displays a sheet with the various options available for filtering.

Screen Shot 2020-08-30 at 7.40.27 AM

This filter dialog is “live”. The button at the bottom that says “Show 977 Results” will dynamically update as you enter your query so that you can see how many results there will be.

Optional Search Header

On some tables you may want the header to be a “search” field. This can be achieved using the the new fieilds.inii “search_field_header” directive, as demonstrated in this table:

Search Header

New/Edit Record Forms

Forms are Xataface’s bread and butter, so they need to be very mobile friendly. I’ve completely revamped the stylesheet to be responsive so that forms are a pleasure to use on the smaller displays.

New Record Form

I’ve also added a new feature to help reduce clutter on forms. You can now make field groups “hidden” by default. Hidden field groups are collapsed into buttons that are rendered at the bottom of the form:

Fieldgroups menu

The user can display a hidden field group by clicking on the corresponding icon. E.g. In the example form shown above, the user might want to edit the “narration”. They can do so by clicking on the “Narration” icon at the bottom of the form, which will reveal the narration-related fields.

Narration field group

More to Come

This is just a quick post to share some of the work. There are tons of new features that I didn’t cover here. I’ll be blogging more about them soon.

I’ve been slowly assembling a “definitive” guide for Xataface. You can see the current version (in progress) at https://shannah.github.io/xataface-manual/

After I’ve ported all of the existing documentation into this manual, I’ll be using it as the basis for a new website. There is lots of new stuff in the pipe for Xataface, so stay tuned.

Using SOUNDEX and MySQL Full-Text Search for Fuzzy Matching

I recently received a question from a Xataface user about how to support searches for misspelled names. Out of the box, Xataface provides some fairly advanced search support that allows users to search on particular columns, across all columns in a single table, or across all tables (from a specified subset of the tables in the database). For the single-table searches, Xataface uses combinations of LIKE and = comparisons depending on the type of query and field type. E.g. If a user enters a search in the last_name column of the people table for “Williams”, it will perform an SQL query like the following, under the hood:

SELECT * FROM `people` where `last_name` LIKE 'Williams'

Note: This isn’t exactly how Xataface generates a query like this. This is simplified for readability.

This works great, but what if we wanted to be able to find results of misspellings of “Williams”. E.g. If the user entered a search for ‘Wlliams’, we still want the same result set to be found.

Soundex is a phonetic algorithm for indexing names by sound, as pronounced in English. It happens to provide a very simple way to search for misspellings. Both PHP and MySQL include a SOUNDEX hashing function that will take string input and produce the SOUNDEX code for that input. The SOUNDEX code for strings that are misspelled are often the same. E.g.

SOUNDEX('Williams') === 'W452'

and

SOUNDEX('Wlliams') === 'W452'

This means that we can allow users to search for misspellings on the last_name field by modifying the SQL query as follows:

SELECT * FROM `people` where SOUNDEX(`last_name`) = SOUNDEX('Wlliams')

This should match a superset of the records that were matched in the original query. You might verbalize this query as “Find all people with last names that sound like Williams.

Matching Records that Contain A Word

The above example works great if we are searching on a column that contains a single word and we are matching against the whole word, but it falls short in cases where we need to find values that contain a search term. E.g. Suppose the people table also has a bio field that contains a short profile of the person. Now suppose we want to find all people whose bio contains the word “cardiac”. Xataface might generate an SQL query like:

SELECT * FROM `people` where `bio` like '%cardiac%'

Now if we want to find misspellings, we can’t simply do:

SELECT * FROM `people` where SOUNDEX(`bio`) like SOUNDEX('cardiac')

MySQL doesn’t provide a simple way to perform string splitting and mapping inside a WHERE clause so we would need to do some work prior to the search to allow us to search for misspellings in the bio field. One possible solution create an index on the bio field that includes soundex codes for each word. E.g. we could add a field named bio_soundex to the people table. Then every time the bio is changed we could update the bio_soundex. In Xataface we could perform this in the beforeSave trigger:

function beforeSave(Dataface_Record $record){
   if ( $record->valueChanged('bio') ){
      $words = preg_split(
          '/((^\p{P}+)|(\p{P}*\s+\p{P}*)|(\p{P}+$))/', 
          $record->val('bio'), 
          -1, 
          PREG_SPLIT_NO_EMPTY
      );
      $soundexWords = array();
      foreach ( $words as $word){
         $soundexWords[] = soundex($word);
      }
      $record->setValue('bio_soundex', implode(' ', $soundexWords));
   }
}

This example makes use of Xataface specific hooks and classes, but the concept of splitting the field content into individual words, producing the soundex code for each word, then saving the concatenation of these codes (separated by spaces) into another field can easily be done in any system.

Now, we are able to perform a query like this:

SELECT * FROM `people` WHERE `bio_soundex` LIKE CONCAT('%',SOUNDEX('crdiac'),'%')

And this will return all records where the bio contains the word “cardiac” (and many misspellings thereof).

Currently Xataface doesn’t automatically perform this type of soundex conversion for single-table searches. This example shows how you can do it yourself. However, as I describe next, Xataface does perform automatic soundex indexing for multi-table searches.

Fuzzy Matching in Multi-Table Search

Xataface has supported a multi-table search option for several years now. It works by maintaining a central index table with an entry for each record that can be searched. This index stores record details such as its table, id, URL, title, description, and searchable text which has a MySQL full-text search index that is used for the actual searching. Using MySQL’s full-text indexing feature allows Xataface to perform relevance sorting. This index is automatically updated each time a record is saved, and Xataface provides administrators with tools to manually clear and regenerate the index.

If a user enters a search for “Cardiac” in the multi-table search box, Xataface will generate an SQL query similar to:

SELECT 
  `record_id`, 
  `table`, 
  `record_url`, 
  `record_title`, 
  `record_description`, 
  `searchable_text`, 
  `lang`, 
  match(searchable_text) AGAINST ('Cardiac') as `relevance`
FROM dataface__index
WHERE `lang`='en' AND 
match(searchable_text) against ('Cardiac')
ORDER BY `relevance` DESC

In order to modify the index to support misspelled searches, I used a strategy similar to the one described above (for the bio field). At the time a record is indexed, I parse the content of the searchable_text into words, then produce a string of concatenated soundex codes corresponding to those words, and append them to searchable text field.

The actual code is as follows (From here:

$searchable_text = preg_replace('/<[^>]*?>/', ' ', $searchable_text);
$words = preg_split('/((^\p{P}+)|(\p{P}*\s+\p{P}*)|(\p{P}+$))/', $searchable_text, -1, PREG_SPLIT_NO_EMPTY);
$soundexAddons = array();
foreach ( $words as $word ){
    $soundexAddons[] = soundex(trim($word));
}

$searchable_text .= '[/////]: '.implode(' ', $soundexAddons);
$searchable_text = strip_tags($searchable_text);

$sql = "
    replace into dataface__index 
        (`record_id`,`table`,`record_url`,`record_title`,`record_description`,`lang`,`searchable_text`)
    values
    (
    '".addslashes($record->getId())."',
    '".addslashes($record->_table->tablename)."',
    '".addslashes($record->getPublicLink())."',
    '".addslashes($record->getTitle())."',
    '".addslashes(strip_tags($record->getDescription()))."',
    '".addslashes($lang)."',
    '".addslashes($searchable_text)."'
    )";
if ( !@xf_db_query($sql, df_db()) ){
    $this->createIndexTable();
    if ( !xf_db_query($sql, df_db()) ){
        trigger_error(xf_db_error(df_db()), E_USER_ERROR);
    }
}

At the time of the search, I perform a similar transformation to the search phrase to append the soundex codes of all words in the query to the end of the query. E.g. If the user searches for “Crdiac”, xataface will actually search for “Crdiac C632” (C632 is the soundex code for Cardiac).

This snippet shows how this is accomplished (From here:

$words = explode(' ', $query['-search']);
$soundexAddons = array();
foreach ( $words as $word){
    $soundexAddons[] = soundex($word);
}
$orig_search = $query['-search'];
$query['-search'] .= ' '.implode(' ', $soundexAddons);

So the resulting SQL query will be something like:

SELECT 
  `record_id`, 
  `table`, 
  `record_url`, 
  `record_title`, 
  `record_description`, 
  `searchable_text`, 
  `lang`, 
  match(searchable_text) AGAINST ('Cardiac C632') as `relevance`
FROM dataface__index
WHERE `lang`='en' AND 
match(searchable_text) against ('Cardiac C632')
ORDER BY `relevance` DESC

More Information