Jump to content

sorting is not logical


Ian
 Share

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Edited by bsmither
Link to comment
Share on other sites

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:

 

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...