Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2009
    Location
    Stuart, FL
    Posts
    12,401
    Plugin Contributions
    87

    Default split_page_results: Why is it so complex?

    Trying to deal with more strict MySQL installations to remove a GROUP BY and substituting instead a sub-query in the SELECT clause for the Back In Stock Notifications to gather the number of subscribers. Currently running zc156c, but doing some fix-ups prior to the zc157 upgrade.
    Code:
            $products_query_raw = 
                "SELECT DISTINCT bisns.product_id, pd.products_name, p.products_type, p.products_model,
                        p.products_quantity AS current_stock, cd.categories_name,
                        (SELECT COUNT(*) FROM " . TABLE_BACK_IN_STOCK_NOTIFICATION_SUBSCRIPTIONS . " b
                           WHERE b.product_id = bisn.product_id) AS num_subscribers
                   FROM " . TABLE_BACK_IN_STOCK_NOTIFICATION_SUBSCRIPTIONS . " bisns
                        LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd
                            ON (pd.products_id = bisns.product_id
                           AND  pd.language_id = " . $_SESSION['languages_id'] . ")
                        LEFT JOIN " . TABLE_PRODUCTS . " p
                            ON p.products_id = pd.products_id
                        LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd 
                            ON (p.master_categories_id = cd.categories_id
                           AND  cd.language_id = " . $_SESSION['languages_id'] . ")";
    Unfortunately, that sub-query gives the split_page_result class hissy-fits and results in a MySQL error:
    Code:
    [10-Feb-2021 19:10:41 Europe/London] Request URI: /mysite/myadmin/back_in_stock_notifications.php?action=send&option=1, IP address: 127.0.0.1
    #1  trigger_error() called at [C:\xampp\htdocs\gointuk2\includes\classes\db\mysql\query_factory.php:171]
    #2  queryFactory->show_error() called at [C:\xampp\htdocs\gointuk2\includes\classes\db\mysql\query_factory.php:143]
    #3  queryFactory->set_error() called at [C:\xampp\htdocs\gointuk2\includes\classes\db\mysql\query_factory.php:270]
    #4  queryFactory->Execute() called at [C:\xampp\htdocs\mysite\myadmin\includes\classes\split_page_results.php:40]
    #5  splitPageResults->__construct() called at [C:\xampp\htdocs\mysite\myadmin\back_in_stock_notifications.php:127]
    --> PHP Fatal error: 1064:You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') AS num_subscribers                FROM back_in_stock_notification_subscription' at line 1 :: select count(distinct  bisns.product_id) as total  FROM back_in_stock_notification_subscriptions b                        WHERE b.product_id = bisn.product_id) AS num_subscribers                FROM back_in_stock_notification_subscriptions bisns                     LEFT JOIN products_description pd                         ON (pd.products_id = bisns.product_id                        AND  pd.language_id = 1)                     LEFT JOIN products p                         ON p.products_id = pd.products_id                     LEFT JOIN categories_description cd                          ON (p.master_categories_id = cd.categories_id                        AND  cd.language_id = 1) ==> (as called by) C:\xampp\htdocs\mysite\myadmin\includes\classes\split_page_results.php on line 40 <== in C:\xampp\htdocs\gointuk2\includes\classes\db\mysql\query_factory.php on line 171.
    What's the purpose of the class finding various MySQL keywords, e.g. GROUP BY, HAVING, instead of simply wrapping the query with
    Code:
    SELECT COUNT(*) FROM ($sql_query) AS count
    ... noting that the above modification allows those sub-queries to be used without an associated MySQL error.

  2. #2
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

    Default Re: split_page_results: Why is it so complex?

    If there are a million results, then it is more performant to exclude a few parts of the query unrelated to counting. For example, a sort is expensive and unnecessary when just trying to determine how many records exist, which is necessary when determining whether to paginate results.

    If your scenario doesn't have more than maybe 100 results, you could skip the split-page feature altogether if the data being returned is small and not going to slow down the page by drawing all the associated rows.
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  3. #3
    Join Date
    Sep 2009
    Location
    Stuart, FL
    Posts
    12,401
    Plugin Contributions
    87

    Default Re: split_page_results: Why is it so complex?

    Quote Originally Posted by DrByte View Post
    If there are a million results, then it is more performant to exclude a few parts of the query unrelated to counting. For example, a sort is expensive and unnecessary when just trying to determine how many records exist, which is necessary when determining whether to paginate results.

    If your scenario doesn't have more than maybe 100 results, you could skip the split-page feature altogether if the data being returned is small and not going to slow down the page by drawing all the associated rows.
    The problem is that the num_subscribers for the BISN query is also potentially used for a sort. Perhaps simply updating that class to remove any ORDER BY clause from the query?

  4. #4
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

    Default Re: split_page_results: Why is it so complex?

    There's a lot more going on in the split_pages class than just the ORDER BY part that I mentioned. It was one example of why it does what it does.

    You said you were trying to resolve a GROUP BY error. What are the details of that?
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  5. #5
    Join Date
    Jul 2012
    Posts
    16,718
    Plugin Contributions
    17

    Default Re: split_page_results: Why is it so complex?

    Completely unrelated to the core of the discussion, but wanted to help out if the sub-query gets used/reused... there is an error in it:
    Code:
    SELECT COUNT(*) FROM " . TABLE_BACK_IN_STOCK_NOTIFICATION_SUBSCRIPTIONS . " b
                           WHERE b.product_id = bisn.product_id
    Should be:
    Code:
    SELECT COUNT(*) FROM " . TABLE_BACK_IN_STOCK_NOTIFICATION_SUBSCRIPTIONS . " b
                           WHERE b.product_id = bisns.product_id
    Where the extra s is missing in the last table alias.
    ZC Installation/Maintenance Support <- Site
    Contribution for contributions welcome...

  6. #6
    Join Date
    Sep 2009
    Location
    Stuart, FL
    Posts
    12,401
    Plugin Contributions
    87

    Default Re: split_page_results: Why is it so complex?

    Quote Originally Posted by DrByte View Post
    There's a lot more going on in the split_pages class than just the ORDER BY part that I mentioned. It was one example of why it does what it does.

    You said you were trying to resolve a GROUP BY error. What are the details of that?
    Trying to convert
    Code:
            $products_query_raw = 
                "SELECT bisns.product_id, pd.products_name, COUNT(*) AS num_subscribers, p.products_type, p.products_model,
                        p.products_quantity AS current_stock, cd.categories_name
                   FROM " . TABLE_BACK_IN_STOCK_NOTIFICATION_SUBSCRIPTIONS . " bisns
                        LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd
                            ON (pd.products_id = bisns.product_id
                           AND  pd.language_id = " . $_SESSION['languages_id'] . ")
                        LEFT JOIN " . TABLE_PRODUCTS . " p
                            ON p.products_id = pd.products_id
                        LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd 
                            ON (p.master_categories_id = cd.categories_id
                           AND  cd.language_id = " . $_SESSION['languages_id'] . ")
                  GROUP BY bisns.product_id";
    to
    Code:
            $products_query_raw = 
                "SELECT DISTINCT bisns.product_id, pd.products_name, p.products_type, p.products_model,
                        p.products_quantity AS current_stock, cd.categories_name,
                        (SELECT COUNT(*) FROM " . TABLE_BACK_IN_STOCK_NOTIFICATION_SUBSCRIPTIONS . " b
                           WHERE b.product_id = bisns.product_id) AS num_subscribers
                   FROM " . TABLE_BACK_IN_STOCK_NOTIFICATION_SUBSCRIPTIONS . " bisns
                        LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd
                            ON (pd.products_id = bisns.product_id
                           AND  pd.language_id = " . $_SESSION['languages_id'] . ")
                        LEFT JOIN " . TABLE_PRODUCTS . " p
                            ON p.products_id = pd.products_id
                        LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd 
                            ON (p.master_categories_id = cd.categories_id
                           AND  cd.language_id = " . $_SESSION['languages_id'] . ")";
    and receiving that error noted above. FWIW, I've run into this issue before with sub-queries but didn't need the sortability.

  7. #7
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

    Default Re: split_page_results: Why is it so complex?

    In the admin split-page-results class, does changing to "strrpos" instead of "strpos" solve the immediate issue?

    eg:
    Code:
         $pos_from = strrpos($query_lower, ' from', 0);
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  8. #8
    Join Date
    Sep 2009
    Location
    Stuart, FL
    Posts
    12,401
    Plugin Contributions
    87

    Default Re: split_page_results: Why is it so complex?

    Quote Originally Posted by DrByte View Post
    In the admin split-page-results class, does changing to "strrpos" instead of "strpos" solve the immediate issue?

    eg:
    Code:
         $pos_from = strrpos($query_lower, ' from', 0);
    In a word: Yes.

  9. #9
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

    Default Re: split_page_results: Why is it so complex?

    Excellent.
    Contemplating some adjustments for v158
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

 

 

Similar Threads

  1. Replies: 3
    Last Post: 14 Dec 2013, 01:32 PM
  2. Replies: 3
    Last Post: 27 Jan 2007, 05:31 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR