Sort a Numbering System with Dashes in It

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
kinggabbo
Forum Newbie
Posts: 5
Joined: Tue Jul 22, 2008 4:06 pm

Sort a Numbering System with Dashes in It

Post by kinggabbo »

My company has documents with a numbering system as follows: 1-1, 1-2, 2-3, 5-3, 10-2, 2-101, etc... Numbers and dashes. I am storing info about these documents, such as these numbers in a mysql database and then outputting the info into an HTML table on the web page. I was wanting to be able to sort the table ascending and descending based on this numbering system. Does anyone have any ideas how to do this? I tried converting the dashes to decimals but that fails whenever you have more than two numbers after the dash. Any help would be appreciated.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Sort a Numbering System with Dashes in It

Post by alex.barylski »

MySQL should sort those documents without problem...

Maybe your query is incorrect. POST the SQL query and your table schema.
WebbieDave
Forum Contributor
Posts: 213
Joined: Sun Jul 15, 2007 7:07 am

Re: Sort a Numbering System with Dashes in It

Post by WebbieDave »

Hockey wrote:MySQL should sort those documents without problem
Right. If, for some reason unbeknownst to us, you need to sort these elements without benefit of SQL (ORDER BY), you can use usort.
http://us.php.net/manual/en/function.usort.php
manixrock
Forum Commoner
Posts: 45
Joined: Sun Jul 20, 2008 6:38 pm

Re: Sort a Numbering System with Dashes in It

Post by manixrock »

try the following (not tested):

Code: Select all

ORDER BY CONVERT(INT, SUBSTRING_INDEX(nr_colomn, '-', 1)), CONVERT(INT, SUBSTRING_INDEX(nr_colomn, '-', -1))
kinggabbo
Forum Newbie
Posts: 5
Joined: Tue Jul 22, 2008 4:06 pm

Re: Sort a Numbering System with Dashes in It

Post by kinggabbo »

I would like it work using SQL if possible. The name of my field that needs to be sorted(with the dashes in it) is named Exhibit Number. It is currently a varchar type. I tried this sql command based on the last post. I'm not sure I did the command correctly.

$results=$exhibits->query("select * from exhibits ORDER BY CONVERT(INT, SUBSTRING_INDEX('`Exhibit Number`', '-', 1)), CONVERT(INT, SUBSTRING_INDEX('`Exhibit Number`', '-', -1))");
User avatar
ghurtado
Forum Contributor
Posts: 334
Joined: Wed Jul 23, 2008 12:19 pm

Re: Sort a Numbering System with Dashes in It

Post by ghurtado »

Hockey wrote:MySQL should sort those documents without problem...
Wouldn't alphabetical sorting on that column do something like this?

1-1
1-10
1-2
1-3

.... etc

In that case, that's probably not what you would want, right?
kinggabbo
Forum Newbie
Posts: 5
Joined: Tue Jul 22, 2008 4:06 pm

Re: Sort a Numbering System with Dashes in It

Post by kinggabbo »

I think I just tried a regular ORDER BY and that's what it did. That is not what I'm looking for.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Sort a Numbering System with Dashes in It

Post by Christopher »

Easiest would be to either separate the two numbers into two columns in the table, or zero pad them internally "01-07". Or do something like:

ORDER BY CAST(LEFT(mycode, LOCATE('-', mycode)-1) AS UNSIGNED), CAST(SUBSTRING(mycode, LOCATE('-', mycode)+1) AS UNSIGNED)
(#10850)
User avatar
ghurtado
Forum Contributor
Posts: 334
Joined: Wed Jul 23, 2008 12:19 pm

Re: Sort a Numbering System with Dashes in It

Post by ghurtado »

I think this problem may be close to impossible to resolve in SQL alone. Your biggest hurdle is the fact that you have to split the document ID by the dashes and then add zero padding to the document number, otherwise documents 1-12 and 11-2 would both wind up as "112".

In PHP, your best bet may be to retrieve all the records from the database, split the document ID string and add zero padding, then do the actual sort.

My advice would be to standardize in a document ID format that is "sortable" for the future; that is, require zero padding when entering document IDs.
kinggabbo
Forum Newbie
Posts: 5
Joined: Tue Jul 22, 2008 4:06 pm

Re: Sort a Numbering System with Dashes in It

Post by kinggabbo »

Would this work with documents that might have 3 digits after the dash, such as 1-202? Also, if I was somehow able split the document IDs and and add the zeros, how would I sort the whole table based on the document ID field? Would I use a php sort method? Or use SQL ORDER BY on the split document ID fields?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Sort a Numbering System with Dashes in It

Post by Christopher »

You really need to start trying things. Both manixrock and I have given you SQL. They do essentially the same thing.
(#10850)
manixrock
Forum Commoner
Posts: 45
Joined: Sun Jul 20, 2008 6:38 pm

Re: Sort a Numbering System with Dashes in It

Post by manixrock »

kinggabbo wrote:I would like it work using SQL if possible. The name of my field that needs to be sorted(with the dashes in it) is named Exhibit Number. It is currently a varchar type. I tried this sql command based on the last post. I'm not sure I did the command correctly.

$results=$exhibits->query("select * from exhibits ORDER BY CONVERT(INT, SUBSTRING_INDEX('`Exhibit Number`', '-', 1)), CONVERT(INT, SUBSTRING_INDEX('`Exhibit Number`', '-', -1))");

You need to pass as the first parameter to SUBSTRING_INDEX the name of the column (without any dashes or quotes) holding the dash-separated numbers. For example, if the column name is "product_ids", then your query should be:

Code: Select all

ORDER BY CONVERT(INT, SUBSTRING_INDEX(product_ids, '-', 1)), CONVERT(INT, SUBSTRING_INDEX(product_ids, '-', -1))
kinggabbo
Forum Newbie
Posts: 5
Joined: Tue Jul 22, 2008 4:06 pm

Re: Sort a Numbering System with Dashes in It

Post by kinggabbo »

I found help on another forum that was similar to what some had posted here. I still need to test it some, but I think it works. Here is the SQL:

SELECT * FROM table ORDER BY
CAST(LEFT(`Exhibit Number`, LOCATE('-', `Exhibit Number`)-1) AS SIGNED INTEGER),
CAST(RIGHT(`Exhibit Number`, LENGTH(`Exhibit Number`) - LOCATE('-', `Exhibit Number`)) AS SIGNED INTEGER)


Thanks for all the help!
User avatar
ghurtado
Forum Contributor
Posts: 334
Joined: Wed Jul 23, 2008 12:19 pm

Re: Sort a Numbering System with Dashes in It

Post by ghurtado »

Works like a charm indeed

When I said "I think this problem may be close to impossible to resolve in SQL alone." I did not realize that you only ever have one dash in your document ID, I misunderstood and thought your problem was that you could have an arbitrarily long doc ID like 1-23-1234-123-45...

Glad you found the answer, and thanks for sharing it!
Post Reply