SitemapXML v.2

[lat9]

I have even more...

As $from and $where variables can be not empty in 'includes/modules/pages/sitemapxml/sitemapxml_ezpages.php' file after fixing the bug with using of quotes for TABLE_EZPAGES_TEXT constant (see earlier correspondence in this tread), "p." prefix must be added to all columns belonging to TABLE_EZPAGES table in all sql-queries, for ex:

    $page_query_sql =
        "SELECT p.toc_chapter
           FROM " . TABLE_EZPAGES . " p " . $from . "
          WHERE p.alt_url_external = ''
            AND (
                (p.status_header = 1 AND p.header_sort_order > 0)
                 OR (p.status_sidebox = 1 AND p.sidebox_sort_order > 0)
                 OR (p.status_footer = 1 AND p.footer_sort_order > 0)
                )
           AND p.status_toc != 0" .
           $where . "
         GROUP BY p.toc_chapter";

GitHub issue created: https://github.com/lat9/sitemapxml/issues/54

[lat9]

@yesaul, I've addresses all the issues you've identified in the SitemapXML github: https://github.com/lat9/sitemapxml

Would you mind doing a 3rd-party verification that I've addressed them all to your satisfaction?

[Shop Suey]

I now understand this is not a known issue.
I'm using the PHP 8.3 compatible European branch for German language version (1.5.7h)
I wrote a bug report at github

I likely don't use the most recent version. So my fault likely. I'm going to test the most recent one.

[yesaul]

@yesaul, I've addresses all the issues you've identified in the SitemapXML github: https://github.com/lat9/sitemapxml

Would you mind doing a 3rd-party verification that I've addressed them all to your satisfaction?

I am awfully sorry, I am probably not very familiar with Github mechanic, but I do still see something like

if ($sitemapXML->dbTableExist('TABLE_HIDE_CATEGORIES') === true)

( \includes\modules\pages\sitemapxml\sitemapxml_categories.php flie, line 20)

in archive I have just downlaoded from GitHub

[lat9]

I am awfully sorry, I am probably not very familiar with Github mechanic, but I do still see something like

if ($sitemapXML->dbTableExist('TABLE_HIDE_CATEGORIES') === true)

( \includes\modules\pages\sitemapxml\sitemapxml_categories.php flie, line 20)

in archive I have just downlaoded from GitHub

Yep, that's to be expected. You can download the current GitHub code using this link: https://github.com/lat9/sitemapxml/a...ads/master.zip

[yesaul]

Yep, that's to be expected. You can download the current GitHub code

Hello, Thank you. I do not see any further issues.

Now you are using constant function in dbTableExist method, so constant names in quotes should be Ok
I should have noticed this earlier, sorry, my bad.

Thank you for your efforts supporting this addon

[lat9]

Hello, Thank you. I do not see any further issues.

Now you are using constant function in dbTableExist method, so constant names in quotes should be Ok
I should have noticed this earlier, sorry, my bad.

Thank you for your efforts supporting this addon

And thanks back at you for the update; I'll get an updated version released real-soon-now!

[lat9]

v4.0.4 of SitemapXML is now available for download: https://www.zen-cart.com/downloads.php?do=file&id=367

This release contains changes associated with these GitHub issues:

#50: Use !empty instead of direct comparison to an empty string, possible NULL values.
#51: Don't include manufacturers with no products in the manufacturers' sitemap output.
#52: Correct detection of plugin-supplied additional tables, e.g. TABLE_BOX_NEWS.
#53: TABLE_BOX_NEWS, 'news_published_date' field can be null.
#54: sitemapxml_ezpages.php: Ensure that all fields for TABLE_EZPAGES are prefixed with p.

[showren]

Hi lat9,

I'm using SitemapXML (formerly "Google Sitemap") - Version: v4.0.4 on Zen Cart 2.1.0, and I encountered a MySQL error when attempting to rebuild the sitemap. The issue specifically occurs with the Ezpages Sitemap generation.

Here’s the error from my logs:
[13-Mar-2025 08:03:17 UTC] Request URI: /index.php?main_page=sitemapxml&rebuild=yes, IP address: 220.133.4.108, Language id 4
#0 [internal function]: zen_debug_error_handler()
#1 /var/www/vhosts/hz.com.tw/httpdocs/includes/classes/db/mysql/query_factory.php(733): trigger_error()
#2 /var/www/vhosts/hz.com.tw/httpdocs/includes/classes/db/mysql/query_factory.php(678): queryFactory->show_error()
#3 /var/www/vhosts/hz.com.tw/httpdocs/includes/classes/db/mysql/query_factory.php(307): queryFactory->set_error()
#4 /var/www/vhosts/hz.com.tw/httpdocs/includes/modules/pages/sitemapxml/sitemapxml_ezpages.php(99): queryFactory->Execute()
#5 /var/www/vhosts/hz.com.tw/httpdocs/includes/templates/template_default/sitemapxml/tpl_main_page.php(21): require('...')
#6 /var/www/vhosts/hz.com.tw/httpdocs/index.php(94): require('...')
--> PHP Fatal error: MySQL error 1054: Unknown column 'p.sidebox_sort_orderASC' in 'order clause' :: SELECT *
FROM ezpages p
WHERE p.alt_url_external = ''
AND (
(p.status_header = 1 AND p.header_sort_order > 0)
OR (p.status_sidebox = 1 AND p.sidebox_sort_order > 0)
OR (p.status_footer = 1 AND p.footer_sort_order > 0) OR p.toc_chapter IN (0)
) ORDER BY p.sidebox_sort_orderASC, p.header_sort_orderASC, p.footer_sort_orderASC ==> (as called by) /var/www/vhosts/hz.com.tw/httpdocs/includes/modules/pages/sitemapxml/sitemapxml_ezpages.php on line 99 <== in /var/www/vhosts/hz.com.tw/httpdocs/includes/classes/db/mysql/query_factory.php on line 733.
Issue Summary:
The error seems to originate from ORDER BY in sitemapxml_ezpages.php, where column names are concatenated with "ASC" without a space.
The generated SQL query includes ORDER BY p.sidebox_sort_orderASC, p.header_sort_orderASC, p.footer_sort_orderASC, which is not valid SQL syntax.
This results in MySQL error 1054 (Unknown column 'p.sidebox_sort_orderASC').
Steps to Reproduce:
Install SitemapXML v4.0.4 on Zen Cart 2.1.0.
Run the sitemap rebuild via:
https://hz.com.tw/index.php?main_pag...ml&rebuild=yes
The error occurs when generating the Ezpages Sitemap.
Possible Cause:
In sitemapxml_ezpages.php, the ORDER BY clause is constructed dynamically:
$order_by_elements = explode(',', SITEMAPXML_EZPAGES_ORDERBY);
If SITEMAPXML_EZPAGES_ORDERBY is missing spaces between column names and ASC, the query becomes invalid.
Temporary Fix:
I manually checked sitemapxml_ezpages.php and ensured that ORDER BY is formatted correctly:

ORDER BY p.sidebox_sort_order ASC, p.header_sort_order ASC, p.footer_sort_order ASC
This resolved the issue.

Request:
Could you confirm whether this is a bug in v4.0.4 and whether an official patch will be released?
If needed, I can provide additional debugging details.

Thanks for your help!

Best regards,
[showren]

[lat9]

Hi lat9,

I'm using SitemapXML (formerly "Google Sitemap") - Version: v4.0.4 on Zen Cart 2.1.0, and I encountered a MySQL error when attempting to rebuild the sitemap. The issue specifically occurs with the Ezpages Sitemap generation.

Here’s the error from my logs:
[13-Mar-2025 08:03:17 UTC] Request URI: /index.php?main_page=sitemapxml&rebuild=yes, IP address: 220.133.4.108, Language id 4
#0 [internal function]: zen_debug_error_handler()
#1 /var/www/vhosts/hz.com.tw/httpdocs/includes/classes/db/mysql/query_factory.php(733): trigger_error()
#2 /var/www/vhosts/hz.com.tw/httpdocs/includes/classes/db/mysql/query_factory.php(678): queryFactory->show_error()
#3 /var/www/vhosts/hz.com.tw/httpdocs/includes/classes/db/mysql/query_factory.php(307): queryFactory->set_error()
#4 /var/www/vhosts/hz.com.tw/httpdocs/includes/modules/pages/sitemapxml/sitemapxml_ezpages.php(99): queryFactory->Execute()
#5 /var/www/vhosts/hz.com.tw/httpdocs/includes/templates/template_default/sitemapxml/tpl_main_page.php(21): require('...')
#6 /var/www/vhosts/hz.com.tw/httpdocs/index.php(94): require('...')
--> PHP Fatal error: MySQL error 1054: Unknown column 'p.sidebox_sort_orderASC' in 'order clause' :: SELECT *
FROM ezpages p
WHERE p.alt_url_external = ''
AND (
(p.status_header = 1 AND p.header_sort_order > 0)
OR (p.status_sidebox = 1 AND p.sidebox_sort_order > 0)
OR (p.status_footer = 1 AND p.footer_sort_order > 0) OR p.toc_chapter IN (0)
) ORDER BY p.sidebox_sort_orderASC, p.header_sort_orderASC, p.footer_sort_orderASC ==> (as called by) /var/www/vhosts/hz.com.tw/httpdocs/includes/modules/pages/sitemapxml/sitemapxml_ezpages.php on line 99 <== in /var/www/vhosts/hz.com.tw/httpdocs/includes/classes/db/mysql/query_factory.php on line 733.
Issue Summary:
The error seems to originate from ORDER BY in sitemapxml_ezpages.php, where column names are concatenated with "ASC" without a space.
The generated SQL query includes ORDER BY p.sidebox_sort_orderASC, p.header_sort_orderASC, p.footer_sort_orderASC, which is not valid SQL syntax.
This results in MySQL error 1054 (Unknown column 'p.sidebox_sort_orderASC').
Steps to Reproduce:
Install SitemapXML v4.0.4 on Zen Cart 2.1.0.
Run the sitemap rebuild via:
https://hz.com.tw/index.php?main_pag...ml&rebuild=yes
The error occurs when generating the Ezpages Sitemap.
Possible Cause:
In sitemapxml_ezpages.php, the ORDER BY clause is constructed dynamically:
$order_by_elements = explode(',', SITEMAPXML_EZPAGES_ORDERBY);
If SITEMAPXML_EZPAGES_ORDERBY is missing spaces between column names and ASC, the query becomes invalid.
Temporary Fix:
I manually checked sitemapxml_ezpages.php and ensured that ORDER BY is formatted correctly:

ORDER BY p.sidebox_sort_order ASC, p.header_sort_order ASC, p.footer_sort_order ASC
This resolved the issue.

Request:
Could you confirm whether this is a bug in v4.0.4 and whether an official patch will be released?
If needed, I can provide additional debugging details.

Thanks for your help!

Best regards,
[showren]

I'll get that corrected on the next release. For now, you can just remove the redundant ' ASC' after each of the order-by clauses for the EZ-pages.