A Few Potential Enhancements

4 posts by 2 authors in: Forums > CMS Builder
Last Post: September 25   (RSS)

By tbcshifter - September 23

Here's a few things I've added over the years that have been useful to me in case they make sense to add to the codebase.

mysql_where(): support searching for NULL values

elseif (is_null($value)) {
    $where .= "`$fieldName` IS NULL AND ";
}
_getRecords_getQuery(): support selecting only part of the table for performance with large tables, support forcing an index
if (@$options['selectExpr']) {
    $selectFields = "`{$options['tableName']}`." . $options['selectExpr'];
}

// create query
$query = "SELECT $selectFields\n";
$query .= "FROM `" .DB::$tablePrefix. "{$options['tableName']}` as `{$options['tableName']}`\n";
$query .= (@$options['useIndex']) ? "USE INDEX ({$options['useIndex']})\n" : '';
$query .= $LEFT_JOIN;

...
_getRecords_getCountQuery(): useIndex same as getQuery()

isValidEmail(): prevent leading/trailing spaces from throwing an error since they don't break any mail functions I've found
$input = trim(is_string($input) ? $input : (string) $input);

By Dave - September 25

Hi tbcshifter, 

Thanks for the suggestions and feedback.  I've added these ones: 

- Programmers: mysql_where() - Added support for NULL value comparisons in criteria array
- Programmers: isValidEmail() - Allow leading and trailing spaces in email addresses
- Programmers: getRecords() - Added useIndex option to specify MySQL index hint

For selectExpr, how are you using it?  getRecords() runs a lot of extra code, I'm wondering if calling mysql_select_query() directly would provide even more performance?

Also, note that we're going to migrate the viewer and database code to ZenDB at some point in the future.  It'll have all the same benefits but allow easier access to add/modify underlying SQL.

Thanks!

Dave Edis - Senior Developer
interactivetools.com

By tbcshifter - September 25 - edited: September 25

For selectExpr, how are you using it?  getRecords() runs a lot of extra code, I'm wondering if calling mysql_select_query() directly would provide even more performance?

Not frequently, but mostly when there's a table with large text or blob fields that we want to exclude when fetching a bunch of records for memory sake, but we still want to take advantage of some of the extra getRecords() options.