That

str_replace(
        'rank',
        '`rank`',
        $cat['image_order']
        );

is not a good idea, because it replaces any occurrence of the substring rank with `rank` so also cranky would become c`rank`y, and even worse, `rank` would become ``rank``.

The patch in detail:

The query recovering the ORDER BY parameters from piwigo_categories.image_order is in include/functions_category.inc.php function get_cat_info($id) lines 156-160. 

function get_cat_info is called by include/functions_url.inc.php function parse_section_url( $tokens, &$next_token) at line 529. 

function get_cat_info is called by include/section_init.inc.php line 114.  The result is merged into array $page.  Then, at line 259, $conf[ 'order_by' ] = ' ORDER BY '.$page['category']['image_order'];.  Element $conf[ 'order_by' ] is used in the query constructed at lines 306-314.  When that query is used without backticks around rank, MySQL 8 throws a reserved word error. 

The new patch, shown below, is inserted include/functions_category.inc.php at line 202.  It works with value NULL, 'rank ASC', and '`rank` ASC'. 

The column piwigo_categories.image_order can have up to three image order choices, based on what is selected on Piwigo page Photos Sort Order.  The patch fixes rank and does not modify other choices. 

The new patch:

  // 20220110 with MySQL 8 'rank' is a reserved word so must be surrounded by backticks for use in a query
  if ( is_string( $cat['image_order'] ) &&
    FALSE === mb_strpos( $cat['image_order'] , '`rank`') )
    $cat['image_order'] = str_replace(
        'rank',
        '`rank`',
        $cat['image_order']
        );

The previous patch in file piwigo/include/section-init.inc.php noted previously is redundant so is unneeded.  If already used, it can be removed after the patch is inserted at include/functions_category.inc.php line 202.   

The previous two patches posted by erAck fix the problem by causing Piwigo to write '`rank` ASC' to piwigo_categories.image_order for the selected category / album.  If a manually sorted category / album is throwing the MySQL reserved word error, then going to the Edit Album / Photos Sort Page for that category / album and pressing [Submit] will replace 'rank ASC' with '`rank` ASC' in piwigo_categories.image_order and the error will not recur.

Last edited by hodgman (2022-02-12 22:46:15)