VP-ASP :: Shopping Cart Software

Shopping Cart Software Solutions for anywhere in the World

US/Canada(Toll Free): +1 888 587 2278
Europe/UK: +44 (020) 7193 9408
Australia/New Zealand: +61 3 9016 4497

VP-ASP Shopping Cart Customer Forum

Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 VPCart Forum
 Customization
 Custom Display Order
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rrold1
Starting Member

48 Posts

Posted - January 13 2006 :  11:57:58  Show Profile  Reply with Quote
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
1898 Posts

Posted - January 13 2006 :  14:41:56  Show Profile  Visit devshb's Homepage  Reply with Quote
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
Go to Top of Page

rrold1
Starting Member

48 Posts

Posted - January 17 2006 :  10:39:05  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000