| Author |
 Topic  |
|
|
rrold1
Starting Member
48 Posts |
 Posted - January 13 2006 : 11:57:58
|
I am trying to make my products list by order of a new field "productrefnum" so customers can easily find items numbered on a parts breakdown image shown at the top of the page. This is desired since a part number like 173723232c is a lot harder to find on a page than a reference number like 66c.
I can't seem to get the new field to order properly in that the first digit takes precedence.
Example:
What I want is:
2
3
14a
18
What I get is:
14a
18
2
3
If I search using the VPASP 6.0 "Advanced" Query (in the admin products section)and set the order by to "ccode", it returns the correct alphanumeric ccode order. I want to mirror this behavior with my new field.
Does anyone know where I would find the related VPASP code that would give me the correct return order or tell me what I need to add to make this work like an "order by ccode" manual query?
I am using SQL 2005
Everything else works and this is my last hurdle. It is proving to be quite a road block.
More info is further down if you think that you may be able to help.
Thanks,
Steve
Manual query that works in the admin section:
SELECT * from products order by ccode ASC
SQL query statement that won't work in my page(listing order wise)
select DISTINCT p.catalogid,productrefnum from products p, prodcategories cc, categories c where cc.intcatalogid=p.catalogid and cc.intcategoryid=c.categoryid and cc.intcategoryid = 5 and hide=0 and cstock> 0 AND (highercatalogid is null) order by productrefnum asc
The new field is varchar(50) just like ccode |
|
|
devshb
Senior Member
United Kingdom
1904 Posts |
Posted - January 13 2006 : 14:41:56
|
It's using the correct ordering, the problem is happening because it's ordering by a text column rather than a number column.
To get it ordering like you're after, you'd need to use a fixed character length for that ordering column. (you can't just TO_NUMBER it because you've got letters in there too).
so, you'd put something like this in your new field to get it ordering in the right way.....
002
003
014A
018
(ie just left-pad your values with zeroes)
In addition to that, just to make sure that it does get ordered like a text column (which is what you need), I'd personally also make sure there's a letter/alpha in every value too, eg:
002-A
003-A
014-A-A
018-A
We use that logic a lot, especially for category ordering. eg in categories we often adopt a format for the ordering column of sets of 3 characters (one set for each level) interspaced with hyphens, as you can then show the whole tree all in the right order etc, eg:
001-000-000
001-001-000
001-001-001
001-001-002
001-002-000
001-003-000
002-000-000
002-001-000
002-002-000
003-000-000
003-001-000
Simon Barnaby
Developer
[email protected]
www.BigYellowZone.com
Web Design, Online Marketing and VPASP addons |
Edited by - devshb on January 13 2006 14:49:01 |
 |
|
|
rrold1
Starting Member
48 Posts |
Posted - January 17 2006 : 10:39:05
|
Simon,
Thanks for the input.
I decided to just make it an integer and refer to variations in the description (a,b and so on). It is effective enough when measured against the time involved in going the other way.
Steve |
|
|
| |
Topic |
|
All Forums
Custom Display Order
New Topic
Printer Friendly
TrustGuard - PCI Security Scanner
