I have an application that grabs rows from external APIs, and sends them to the browser without storing in the database. The database only holds the URLs for the API requests.

The rows received from the APIs needs to be filtered per row, either server(PHP) or client side, and the filters need to be stored in the database. A filter for a row can for example be substring or replace.

I assume you never want to have javascript/PHP code in the database, but how else could this be done? Are there any appropriate javascript solutions?

What I want in my database table is this:

Row ------------------ API URL ------------------- Filter

Row 1 --------------- api/get_data-----------STRING FILTER HERE

Row 2 --------------- api/get_more_data---STRING FILTER HERE

Where the "Filter" contains instructions on how to filter the string in the API result.

I have an application that grabs rows from external APIs, and sends them to the browser without storing in the database. The database only holds the URLs for the API requests.

The rows received from the APIs needs to be filtered per row, either server(PHP) or client side, and the filters need to be stored in the database. A filter for a row can for example be substring or replace.

I assume you never want to have javascript/PHP code in the database, but how else could this be done? Are there any appropriate javascript solutions?

What I want in my database table is this:

Row ------------------ API URL ------------------- Filter

Row 1 --------------- api./get_data-----------STRING FILTER HERE

Row 2 --------------- api./get_more_data---STRING FILTER HERE

Where the "Filter" contains instructions on how to filter the string in the API result.

• javascript
• php
• string

5 Answers 5

Depends on the kind of filter you want to use, storing codes in database might be needed, and I don't think there's a way around it since you don't have any restriction for the filter. However, you could try the following:

1/ Function names: if there's only a handful number of filters, you can write functions for them and store the filter name in the database.

2/ Regular Expression: RegExp is enough for doing basic substring and replace, and filtering. You can even have multiple RegExp per URL for plex filters.

How about storing the class name of the filter, instantiating an instance, and then running data through the filter that way?

So you'd have filters set up like:

abstract class FilterAbs {
    abstract public function filter($to_filter);
}

class TrimFilter extends FilterAbs {
    public function filter($to_filter) {
        return trim($to_filter);
    }
}

Then your filter column would have a list of filters (e.g. "TrimFilter, EmailFilter"). You'd create instances and filter like this:

$to_filter = "blah";

$filter_col = "TrimFilter, EmailFilter";

$filters = explode(",", $filter_col);

foreach($filters as $filter_name) {
    $filter_name = trim($filter_name);
    $filter_obj = new $filter_name();
    if( !is_a($filter_obj, "FilterAbs") ) continue;
    $to_filter = $filter_obj->filter($to_filter);
}

If you needed to have params, you could store them as a json encoded array, and use ReflectionClass to create an instance using params (c.f. instantiate a class from a variable in PHP?).

The APIs you store already return data in a structured format. The most mon data formats here would be JSON or XML.

Using a regular expression as a filter would work, however, if the data is already structured in a certain way, maybe there are specific ways of dealing with it(query languages that have been built for that specific format).

For example, if the API returns XML, you can use XPath and XQuery as filters.A matches() function is available, as well as a replace() function (some examples can be found here). Some other good sources of information about XPath are these MDN articles. Also, PHP es with XPath support via the built-in DOMXPath class or SimpleXMLElement::xpath.

If your API returns JSON, there are some ways of filtering:

• JSON-Path (in the documentation it's mentioned that this library implements RFC 6901.). You may find an implementation for PHP here.
• JsonPath (implementing this spec)
• jq (a very easy to use and handy JSON filtering/processing utility. another good option to base your filters syntax on)
• json:select. A PHP implementation is available here.
• jsonpatch.js - an implementation of both RFC 6901 and RFC 6902. There's also a PHP implementation of this here.

Sidenote: RFC 6902 adds operations add/remove/replace/move/copy/test to the aforementioned RFC 6901.

These can be viable options when viewed as languages describing filters(which you want to store in your database).

I would suggest using the Factory pattern and storing a json object in the database that contains instructions for constructing a filter. The json object would be retrieved from the DB and passed to the Factory which would create the correct kind of Filter object.

Suppose you had two filters one that uses a regular expression and another that filters for a value range. You could then create each filter as follows:

class Filter{
    public function filter($results){
        $filteredResults = array();
        foreach($results as $item){
            if($this->isMatch($item)){
                $filteredResults[]=$item;
            }
        }
        return $filteredResults
    }
    public function isMatch($row){
        return true;
    }
}
class RegexFilter extends Filter{
    public function __construct($regexString){
        $this->regex = $regexString;
    }
    public function isMatch($item){
        return preg_match($this->regex,$item) ==1;
    }
}
class RangeFilter extends Filter{
    public function __construct($min,$max){
        $this->max = $max;
        $this->min = $min;
    }
    public function isMatch($item){
        return $this->min < $item && $item <$this->max;
    }
}
class FilterFactory{
    public function createFilter($json){
        $filterData = json_decode($json);
        if($filterData->filterType == 'regex'){
            return new RegexFilter($filterData->regexStr);
        }
        elseif($filterData->filterType == 'range'){
            return new RangeFilter($filterData->min,$filterData->max);
        }
    }
}
// Example use
// match only results that start with http
$regexJsonStr = '{
        "filterType":"regex",
        "regexStr":"/^http/"
}';
$filter = FilterFactory::createFilter($regexJson);
$filteredResults = $filter->filter($results);

// Match results that values between 0 and 100 exclusive
$regexJsonStr = '{
        "filterType":"range",
        "min":0,
        "max":100
}';
$filter = FilterFactory::createFilter($rangeJson);
$filteredResults = $filter->filter($results);

This approach allows you to use best practices for maintaining your codebase while still storing filtering instructions in your database.

Child classes use Filter's filter method but their own isMatch method, making adding new filters easier. You simply A) inherit from the Filter class and implement a new isMatch method; B) add a new clause to FilterFactory->createFilter to create the filter correctly, and C) add the Json describing the filter to the database.

Everywhere else you can use the exact same logic to filter the results:

$filter=FilterFactory::createFilter($jsonFromDatabase);
$filteredResults = $filter->filter($resultsFromApi);

// At first, You must be clear that you must be instructing code to filter your data as desired. As you are using a number of APIs and their format is not mon (as I saw in previous ments and question), you can create a class (Filter) having filtering functions.

// First create a formatted container of your API data from database in below format
$filterContainer[0]['URL'] = 'http://www.api./get_data';
$filterContainer[0]['Filter'] = 'ALPHA_NUMERIC_ONLY';

$filterContainer[1]['URL'] = 'http://www.api./get_more_data';
$filterContainer[1]['Filter'] = 'STRING';

Row ------------------ API URL ------------------- Filter

Row 1 --------------- api./get_data-------------ALPHA_NUMERIC_ONLY

Row 2 --------------- api./get_more_data--------STRING

/**
 * You will need to use these constant values to store in database in filters.
 *
 * Class APIFilter
 */
class APIFilter
{
    const ALPHA_NUMERIC = 'ALPHA_Numeric';
    const STRING        = 'STRING';

    /**
     * @param string $filterType Filter Constant value
     * @param string $data       Raw data
     *
     * @return string|bool
     */
    static public function GetFilteredData($filterType, $data)
    {
        if (empty($filterType) || empty($data)) {
            return false;
        }

        switch ($filterType) {
            case self::ALPHA_NUMERIC:
                // Call Alpha numeric Filter functions and return filtered data
                break;

            case self::STRING:
                // Call string filter functions and return filtered data
                break;

            default:
                break;
        }

        return false;
    }
    /**
     ******** Write related filter functions here ********
     *
     */
}