Bugs solved in PHP libraries

nlooije

There are some bugs, enhancements I would like to share to fix and add functionality in the PHP libraries.

First there is missing for the function _constructSearchBuilderConditions the !starts case. This should be added to Editor.php on line 1737 after the case of starts:

case '!starts':
                        if($data['logic'] === 'AND' || $first) {
                            $query->where($crit['origData'], $val1.'%', 'NOT LIKE');
                            $first = false;
                        }
                        else {
                            $query->or_where($crit['origData'], $val1.'%', 'NOT LIKE');
                        }
                        break;

Second when using MSSQL as database and you want to search case insensitive for some configurations you need to add an collation to the search term. In my case the search must be: LIKE %search% COLLATE SQL_Latin1_General_CP1_CI_AS. I have solved in the code by adding the following to Query.php on line 1179:

elseif ( $this->_dbHost->type === 'Sqlserver' && ($op === 'like' || $op === 'LIKE') ) {
                    // Sqlserver specific collalation neeeded
                    $this->_where[] = array(
                        'operator' => $type,
                        'group'    => null,
                        'field'    => $this->_protect_identifiers($key),
                        'query'    => $this->_protect_identifiers($key) .' '.$op.' '.$this->_safe_bind(':where_'.$i) . ' COLLATE SQL_Latin1_General_CP1_CI_AS'
                    );
                }

Could this be added to the source, maybe with an special database connection parameter.

Last I'm using the yadcf addon (https://github.com/vedmack/yadcf). This is very helpfull for filter invidual columns but also need some extra's in the Editor.php line 1943

// Column filters
        for ( $i=0, $ien=count($http['columns']) ; $i<$ien ; $i++ ) {
            $column = $http['columns'][$i];
            $search = $column['search']['value'];

            if ( $search !== '' && $column['searchable'] == 'true' ) {

                // Added range filter for yadcf
                if (strpos($search, '-yadcf_delim-') !== false) {
                    $limits = explode("-yadcf_delim-", $search);
                    $limit_low = $limits[0];  // piece1
                    $limit_high = $limits[1]; // piece2

                    if ($limit_low != '' && $limit_high != '') {
                        $query->where($this->_ssp_field($http, $i), $limit_low, '>=');
                        $query->where($this->_ssp_field($http, $i), $limit_high, '<=');
                    } elseif ($limit_low != '') {
                        $query->where($this->_ssp_field($http, $i), $limit_low, '>=');
                    } elseif ($limit_high != '') {
                        $query->where($this->_ssp_field($http, $i), $limit_high, '<=');
                    }
                } // Added multi-select filter
                else if (strpos($search, '|') !== false) {
                    $values = explode('|', $search);
                    $query->where($this->_ssp_field($http, $i), $values[0]);
                    foreach (array_slice($values, 1) as $value) {
                        $query->or_where($this->_ssp_field($http, $i), $value);
                    }
                } else {
                    $query->where($this->_ssp_field($http, $i), '%' . $search . '%' , 'like');
                }

            }
        }

allan

!starts case. This should be added to Editor.php on line 1737 after the case of starts:

!starts is actually a new one - it looks like it hasn't been pushed up to the PHP library repo. Sorry about that. We'll get that sorted out and it will be in the next Editor release.

COLLATE SQL_Latin1_General_CP1_CI_AS.

We've not had that request before, so I'm not entirely sure why it is needed. It probably won't be something we include in the main libraries I'm afraid. What collation is your database, and what is the PHP install configured for?

Thanks,
Allan

nlooije

Hi Alan,

Thank you for your support. Our database is an MSSQL database with collation Latin1_General_CI_AS. I'm running PHP on Ubuntu with FreeTDS with client charset UTF8. When I not add COLLATE SQL_Latin1_General_CP1_CI_AS to the WHERE LIKE it search case sensitive, when I add COLLATE SQL_Latin1_General_CP1_CI_AS to the WHERE LIKE then it search case insenitive. I don't think I can solve this in the freetds.conf

allan

Perhaps what you could do is:

$db->sql("SET NAMES utf8 COLLATE 'SQL_Latin1_General_CP1_CI_AS'");

Do that before you call the Editor constructor / factory method? (Based on the MySQL docs for set names).

I'm not sure that is 100% correct setting the names to utf8 and then using a latin 1 collection, but it might be worth a go.

Allan