| Index
» Projects
» phpMyEdit
» Forum
» I want a numeric sort, not a string sort |
 |
ian@lljproperty.com
Joined: 26 Feb 2006
Posts: 1
Posted: 2006-02-26 21:54
Back to top
Reply with quote
|
I have two tables (1) invoice (key is id which is the invoice number) and (2) invoice_details (key is id and invoice_id is a foreign key to invoice).
In the PME for the invoice_details I have the following entries:
$opts['sort_field'] = array('invoice_id', 'id');
$opts['fdd']['id'] = array(
'name' => 'ID',
'select' => 'T',
'options' => 'AVCPDR', // auto increment
'maxlen' => 10,
'default' => '0',
'sort' => true
);
$opts['fdd']['invoice_id'] = array(
'name' => 'Invoice',
'select' => 'T',
'maxlen' => 11,
'required' => true,
'sort' => true
);
$opts['fdd']['invoice_id']['values']['table'] = 'llj_invoice';
$opts['fdd']['invoice_id']['values']['column'] = 'id';
$opts['fdd']['invoice_id']['values']['description']['columns'][0] = 'id';
$opts['fdd']['invoice_id']['values']['description']['columns'][1] = 'inv_dt';
$opts['fdd']['invoice_id']['values']['description']['columns'][2] = 'comments';
$opts['fdd']['invoice_id']['values']['description']['divs'][0] = ' ';
$opts['fdd']['invoice_id']['values']['description']['divs'][1] = ' ';
$opts['fdd']['invoice_id']['values']['filters'] = " delete_flag = 'No' ";
$opts['fdd']['invoice_id']['values']['orderby'] = ' -id ';
My problem is that the List screen for invoice_details is ordered by string rather than by number so invoice 10 is displayed before invoice 2.
Any suggestions for a way around this?
|
michal
Joined: 17 Jun 2003
Posts: 537
Location: Slovakia
Posted: 2006-03-01 10:52
Back to top
Reply with quote
|
maybe
'select' => 'N'
(but i think that is only for filters)
|
techno_blaster
Joined: 19 Jan 2007
Posts: 7
Posted: 2007-01-19 21:35
Back to top
Reply with quote
|
Hello All! Newbie here....
When trying to sort my columns by clicking the column header, the data appears to be string sorted instead of numeric. Since I have numbers in these fields, the sort is incorrect.
I noticed the original thread questioned this issue, but there was no resoution.
How do I make the column sort the numeric data properly? I already have the option set as follows on al my numeric fields.
$opts['fdd']['LoanBalance'] = array(
'name' => 'LoanBalance',
'select' => 'N',
'maxlen' => 10,
'default' => '0.00',
'sort' => true,
'total' => sum,
);
Anyone else encounter this problem who may have a solution??
|
michal
Joined: 17 Jun 2003
Posts: 537
Location: Slovakia
Posted: 2007-01-23 13:30
Back to top
Reply with quote
|
sorting is done on sql level, so it is sorted as your db thinks it should be sorted. check whether the column is varchar or int.
|
techno_blaster
Joined: 19 Jan 2007
Posts: 7
Posted: 2007-02-03 21:31
Back to top
Reply with quote
|
Michal: You are correct! My data was varchar. I changed it to type=dec and it sorts correctly now! Thanks...
| michal wrote: |
| sorting is done on sql level, so it is sorted as your db thinks it should be sorted. check whether the column is varchar or int. |
|
rlugo
Joined: 18 Dec 2007
Posts: 4
Posted: 2007-12-23 20:06
Back to top
Reply with quote
|
I am trying to sort this alphanumeric data:
1000MB
100MB
12MB
Into this:
12MB
100MB
1000MB
I tried hacking the phpmyeditclass with this:
...
if ($parts['orderby'] != '')
$ret .= ' ORDER BY CAST('.$parts['orderby'].' AS UNSIGNED),'.$parts['orderby'];
...
but I get the following error:
MySQL error 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`PMEtable0`.`Id` AS UNSIGNED),`PMEtable0`.`Web Space`,`PMEtable0`.`Id` LIMIT 0, ' at line 1
|
apollobc
Joined: 18 Aug 2009
Posts: 8
Posted: 2009-08-18 07:39
Back to top
Reply with quote
|
Fix (HACK) for numerical sort
Search/Find (~Line # 598)/phpMyEdit.class.php
| Code: |
| $ret .= ' ORDER BY '.$parts['orderby']; |
Change To
| Code: |
| $ret .= ' ORDER BY '.$parts['orderby'].'+0'; |
Does not appear to affect other searches?[/code]
|
|
Post new topic
Reply to topic
|
|
|
|
Login
Registration
Slovak
