sorting is not logical

[Ian]

When the system displays items, I notice that the sort is not logical.  I use numbers for items in my store, but when it is is displayed on a page, the items sort by the first digit.  Is it possible to change the “text field” of the Product Field to a numeral field, or add a numerical field for this number, So it can sort 1,2,3,4,5,6,7,8,9,10  instead of 1,10,2,3,4,5,6,7,8,9,? or is there another way of doing that?

Ian

[bsmither]

Welcome Ian! Glad to see you made it to the forums.

CubeCart is asking the database engine (MySQL) to make the sort - as well as only returning X-number of records starting at record-#.

The MySQL database engine cannot do what is called a "natural case sort" which would sort as follows: 9, 80, 700, 6000. MySQL can only sort on a character-by-character basis: 6000, 700 ,80 ,9.

However, PHP can do a "natural case sort" but once the group of records have been fetched from the database, it is to late to sort by any other method - unless *all* of the records (perhaps hundreds) have been fetched from the database.

There are "tricks" one can use to fake a natural case sort. They involve having the database engine first sort by string length: all single digit values come first, then all two-digit values come after that, etc, then sub-sort by the actual value.

Another approach is to update all the values to have, perhaps, an absolute number of eight characters with sufficient leading zeroes. Then, a simple PHP "trim" of the values stripping off the leading zeroes before displaying the list.

[Ian]

6 hours ago, bsmither said:

Another approach is to update all the values to have, perhaps, an absolute number of eight characters with sufficient leading zeroes. Then, a simple PHP "trim" of the values stripping off the leading zeroes before displaying the list.

I would think this might be the best way to do it, now how would I proceed?  Is there any way that this could be incorporated into an updated version of Cube Cart?

[bsmither]

Are we specifically dealing with 'product_code' from the CubeCart_inventory table?

If so, we can hard-code a tweak to the recordset returned from a database SELECT command.

Assuming you have changed all rows of CubeCart_inventory 'product_code' column to have the same number of characters using sufficient leading zeroes, try this:

In /classes/db/database.class.php, near line 708:

Find:

        if (is_array($this->_result) && count($this->_result) >= 1) {
            foreach ($this->_result as $row) {
                $output[] = $row;
            }


Change to:

        if (is_array($this->_result) && count($this->_result) >= 1) {
            foreach ($this->_result as $row) {
array_walk(
	$row,
	function(&$val,$key) {if ($key == 'product_code') { $val = ltrim($val,'0'); } }
);
                $output[] = $row;
            }

This is being done here instead of in mysqli.class.php where $this->_result is built, because after $this->_result is built, it gets cached. We might not want to cache modified results.

[Ian]

OK, that seems to work ok, There are some things I had to do to make it work completely, but I am I think happier with the results.  Thank you.  I will let you know if there are any additional questions or concerns with this solution.

 

On 1/26/2021 at 3:06 PM, bsmither said: