modification to splitPageResults?

[damiantaylor]

Hi Guys,
I'm working on a mod for my store that requires me to add a GROUP BY and a HAVING clause to the query that lists category products.

The only problem with this is the splitPageResults class strips these 2 functions (and ORDER BY) from the query so it can do it's counting.

I've made a couple of minor changes to this clever class but am worried they might break something else, so am wondering if anyone can have a look at my changes and see if it's likely to mess something up.

Essentially, I've left the 2 SQL clauses in and I'm using RecordCount() to do the counting.

My changes are in red below

    function splitPageResults($query, $max_rows, $count_key = '*', $page_holder = 'page', $debug = false) {
        global $db;

        $this->sql_query = $query;
        $this->page_name = $page_holder;

        if ($debug) {
            echo 'original_query=' . $query . '<br /><br />';
        }

        // perPage contains the number of items to list per page. 0 is used to indicate ALL

        if (isset($_GET['perPage'])) {

            $max_rows = (int) $_GET['perPage'];
            if ($max_rows < 0 or $max_rows >= 5000) {
                $max_rows=MAX_DISPLAY_PRODUCTS_LISTING;
                //This can be found in admin - config - maximum values.
            } elseif ($max_rows==0) {
                $max_rows=5000;
                //0 indicates ALL selected. So we use 5000 as a maximum
            }

            // uncomment the 3 lines below if you wish to have the perPage setting stored from one category to the other.
            //      $_SESSION['perPage']=$max_rows;
            //   } elseif (isset($_SESSION['perPage']))  {
            //        $max_rows = $_SESSION['perPage'];

        } else {
            //$max_rows = MAX_DISPLAY_PRODUCTS_LISTING;
        }

        if (isset($_GET[$page_holder])) {
            $page = $_GET[$page_holder];
        } elseif (isset($_POST[$page_holder])) {
            $page = $_POST[$page_holder];
        } else {
            $page = '';
        }

        if (empty($page) || !is_numeric($page)) $page = 1;
        $this->current_page_number = $page;

        $this->number_of_rows_per_page = $max_rows;

        $pos_to = strlen($this->sql_query);

        $query_lower = strtolower($this->sql_query);
        $pos_from = strpos($query_lower, ' from', 0);

//        $pos_group_by = strpos($query_lower, ' group by', $pos_from);
//        if (($pos_group_by < $pos_to) && ($pos_group_by != false)) $pos_to = $pos_group_by;

//        $pos_having = strpos($query_lower, ' having', $pos_from);
//        if (($pos_having < $pos_to) && ($pos_having != false)) $pos_to = $pos_having;

        $pos_order_by = strpos($query_lower, ' order by', $pos_from);
        if (($pos_order_by < $pos_to) && ($pos_order_by != false)) $pos_to = $pos_order_by;

        if (strpos($query_lower, 'distinct') || strpos($query_lower, 'group by')) {
            $count_string = 'distinct ' . zen_db_input($count_key);
        } else {
            $count_string = zen_db_input($count_key);
        }
        //$count_query = "select count(" . $count_string . ") as total " .
        $count_query = "select " . $count_string .

        substr($this->sql_query, $pos_from, ($pos_to - $pos_from));
        if ($debug) {
            echo 'count_query=' . $count_query . '<br /><br />';
        }
        $count = $db->Execute($count_query);

        //$this->number_of_rows = $count->fields['total'];
        $this->number_of_rows = $count->RecordCount();

        $this->number_of_pages = ceil($this->number_of_rows / $this->number_of_rows_per_page);

        if ($this->current_page_number > $this->number_of_pages) {
            $this->current_page_number = $this->number_of_pages;
        }

        $offset = ($this->number_of_rows_per_page * ($this->current_page_number - 1));

        // fix offset error on some versions
        if ($offset < 0) { $offset = 0; }

        $this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;

    }

I've had a wander around my store and everything seems to still work as it should but I'd like a second opinion if possible

Thanks for looking!

[brittainmark]

I was having similar issues. I modified the split page to use the RecordCount() function from the query factory. this seems to work fine. does any one know if this will be an issue going forward?

my mod

    function splitPageResults(&$current_page_number, $max_rows_per_page, &$sql_query, &$query_num_rows) {
      global $db;

      $sql_query = preg_replace("/\n\r|\r\n|\n|\r/", " ", $sql_query);

      if (empty($current_page_number)) $current_page_number = 1;
      $current_page_number = (int)$current_page_number;
/*
 * start MJFB
      $pos_to = strlen($sql_query);

     $query_lower = strtolower($sql_query);
     $pos_from = strpos($query_lower, ' from', 0);

     $pos_distinct_start = strpos($query_lower, ' distinct', 0);
     $pos_distinct_end = strpos(substr($query_lower, $pos_distinct_start), ',', 0);

     $pos_group_by = strpos($query_lower, ' group by', $pos_from);
     if (($pos_group_by < $pos_to) && ($pos_group_by != false)) $pos_to = $pos_group_by;

     $pos_having = strpos($query_lower, ' having', $pos_from);
     if (($pos_having < $pos_to) && ($pos_having != false)) $pos_to = $pos_having;

     $pos_order_by = strpos($query_lower, ' order by', $pos_from);
     if (($pos_order_by < $pos_to) && ($pos_order_by != false)) $pos_to = $pos_order_by;

      $sql = ($pos_distinct_start == 0) ? "select count(*) as total " : "select count(distinct " . substr($sql_query, $pos_distinct_start+9, $pos_distinct_end-9) . ") as total ";
      $sql .= substr($sql_query, $pos_from, ($pos_to - $pos_from));
     
      $reviews_count = $db->Execute($sql);

      $query_num_rows = $reviews_count->fields['total'];
 */
      $sql =$sql_query;
      $reviews_count = $db->Execute($sql);
      $query_num_rows = $reviews_count->RecordCount();
/*
 * end mjfb
 */      
      $num_pages = ceil($query_num_rows / $max_rows_per_page);
      if ($current_page_number > $num_pages) {
        $current_page_number = $num_pages;
      }
      $offset = ($max_rows_per_page * ($current_page_number - 1));

// fix offset error on some versions
      if ($offset < 0) { $offset = 0; }

      $sql_query .= " limit " . $offset . ", " . $max_rows_per_page;
    }