CGridview sorting of currency column help requested

Yii 1.1 General Discussion
1

When sorted by the third column, $11.00 comes between $109.00 and $110.00

1009 Item Z $109.00

1076 Item N $11.00

1079 Item A $11.00

1099 Item X $110.00

I’ve been searching forums, reading docs and google too for quite a while. I apologize if it’s been answered before. Is this obvious to anyone what I’m doing wrong? THANKS IN ADVANCE

my view




               $this->widget('zii.widgets.grid.CGridView',

                        array(

                           'id'=>'exchange-gridview',

                           'enablePagination'=>true,

                           'dataProvider'=>$dataProvider,

                           'summaryText'=> '<b>Please click on the item number from this list of eligible products</b>',

                           'columns'=>array(

                              array(

                                 'name'              => 'sku',

                                 'header'            => 'Item No.',

                                 'value'             => 'makeLinkBack($data->sku,"' . $shortName . '","' . $row . '");',

                                 'htmlOptions'       => array('class'=>'leftAlign', 'style' => 'width:15%'),

                                 'headerHtmlOptions' => array('style'=>'text-align:left; margin-left:5px;'),

                              ),

                              array(

                                 'name'              => 'pname',

                                 'header'            => 'Product Name',

                                 'value'             => 'ucwords($data->pname);',

                                 'htmlOptions'       => array('class'=>'leftAlign', 'style' => 'width:65%'),

                                 'headerHtmlOptions' => array('style'=>'text-align:left; margin-left:5px;'),

                              ),

                              array(

                                 'name'              => 'price',

                                 'header'            => 'Price',

                                 'value'             => 'CustomerController::makeMoney($data->price);' ,

                                 'htmlOptions'       => array('class'=>'leftAlign', 'style' => 'width:20%'),

                                 'headerHtmlOptions' => array('style'=>'text-align:left; margin-left:5px;'),

                              ),

                           ),

                        ));

Dataprovider setup in controller




      $criteria=new CDbCriteria;

      $ltypeCriteria = "%" . $ntype . "%";

      $criteria->condition = "ntype like :ltype";

      $criteria->params = array(":ntype" => $ntypeCriteria);

      $dataProvider = new CActiveDataProvider("Product",

                           array(

                           'criteria' => $criteria,

                           'pagination' => array(

                               'pageSize' => 20

                              ),

                         ));
2 

CustomerController::makeMoney($data->price);

how work?

3

Thanks for looking at it.

It just takes the amount in the price field and formats it so it looks like US currency




   public static function makeMoney($amount)

   {

       $amtToCurrency = sprintf('%.2f', round($amount, 2));

       echo "$" . $amtToCurrency;

   }
4

FYI, someone suggested that it had to do with the type of the data in the mysql database, which is currently varchar ( 8 ), so it’s sorting it as text…

I’ll post the confirmation of this when I have it, but it sounds like it makes sense to me

5

Bummer, even though that’s the correct solution, due to the amount of legacy code that uses that table, I can’t go making changes to a table structure of a live, high volume site without risks.

So, I’m back to the start, trying to find a solution for either passing a pointer to my own sort function or something like that. I’ll keep searching and post what I find for the next guy if no one posts a link or solution

6

Look for a post on this page by Juan Ignacio Gomez about how to sort a mysql text column as if it were a number:

http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html

7

That’s super cool

I added this to my criteria and it works perfect…

  &#036;criteria-&gt;order = &quot;(price + 0)&quot;;

BStep, you are THE man!

Thanks

8

Final solution was needed because my previous statement was applying to all columns




      $sort = new CSort();

      $sort->attributes =

            array(

               'sku'    =>array( 'asc'   =>  't.sku',

                                 'desc'  =>  't.sku desc', ),

               'name'  =>array( 'asc'   =>  't.name',

                                 'desc'  =>  't.name desc', ),

               'price'  =>array( 'asc'   =>  '(t.price + 0)',

                                 'desc'  =>  '(t.price + 0) desc',

                  ),

         );

      $dataProvider = new CActiveDataProvider("Product",

                           array(

                              'criteria'   => $criteria,

                              'sort'       => $sort,

                              'pagination' => array(

                              'pageSize'   => 20

                           ),

                         ));

Hope it helps someone someday as you guys have helped me out in the past.

thanks again

"CGridview Custom Sort" <- adding the keywords that I was using for search