Sorting Tapes by Index Number

It’s not often that I post a question here - I can usually bash an answer out, but I’m come to something where I’m stuck.

Some background - I have been given an old reel to reel tape deck, and my grandfather’s collection of tapes. There’s a lot of them, and because he was clearly OCD in much the same way I am, everything is indexed and catalogued. Every tape has an index number, and this related to (a) and index card with the tape and (b) a catalogue in one of many ring binders.

My task is to digitise the lost - and I’m making headway. In order to help me, and my mother, convert those index cards into something legible (he was a doctor so the handwriting is challenging to decipher at times), I’m setting up a small site with a MySQL database behind it. The idea is that I can plug in the initial data, but let other family members correct that data.

The site currently sorts the tapes by index number. However, the sorting doesn’t make sense. For example, I’ll get a list like this:

783 A

783 B

ST 25

ST 26

5008 A

5008 B

7003 A

7003 B

ST 29 CD

5031 A - Red

5931 B - Green

743 A - Green

743 A - Red

and so on. The A and B refer to the tape side (these tapes are reversible, and the A and B are used in the index system).

To further muddy the waters, some of the tapes are recorded in mono, with each channel carrying a recording - these tapes are helpfully colour coded so I know what’s where. Those colours form part of the tape index number.

The above list example is being created by a MySQL statement:

SELECT * FROM Track_Recordings WHERE 1
ORDER BY lpad(tapeID,10,0) ASC
LIMIT $pagePos,$pageCount

which I gleefully copied from Stackoverflow
(where $pagePos and $pageCount are for the next/previous links in the list, tapeID is a text field in the database table).

What I am after is a more sensible listing based numerically, so I’m after this:

743 A - Green

743 A - Red

783 A

783 B

5008 A

5008 B

5031 A - Red

5931 B - Green

7003 A

7003 B

ST 25

ST 26

ST 29 CD

Any suggestions? Thanks.


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

How often do you need to do this sort? Most of the SQL solutions I have seen are really heavy, and will make this really time-consuming on every index view. You may want to create a separate order_by column with a numerical key, and perform your logic in a higher level language like PHP or Ruby. Do that once, when you insert the data, and never compute it again.

Walter

On Jul 1, 2014, at 4:59 AM, Paul wrote:

It’s not often that I post a question here - I can usually bash an answer out, but I’m come to something where I’m stuck.

Some background - I have been given an old reel to reel tape deck, and my grandfather’s collection of tapes. There’s a lot of them, and because he was clearly OCD in much the same way I am, everything is indexed and catalogued. Every tape has an index number, and this related to (a) and index card with the tape and (b) a catalogue in one of many ring binders.

My task is to digitise the lost - and I’m making headway. In order to help me, and my mother, convert those index cards into something legible (he was a doctor so the handwriting is challenging to decipher at times), I’m setting up a small site with a MySQL database behind it. The idea is that I can plug in the initial data, but let other family members correct that data.

The site currently sorts the tapes by index number. However, the sorting doesn’t make sense. For example, I’ll get a list like this:

783 A

783 B

ST 25

ST 26

5008 A

5008 B

7003 A

7003 B

ST 29 CD

5031 A - Red

5931 B - Green

743 A - Green

743 A - Red

and so on. The A and B refer to the tape side (these tapes are reversible, and the A and B are used in the index system).

To further muddy the waters, some of the tapes are recorded in mono, with each channel carrying a recording - these tapes are helpfully colour coded so I know what’s where. Those colours form part of the tape index number.

The above list example is being created by a MySQL statement:

SELECT * FROM Track_Recordings WHERE 1
ORDER BY lpad(tapeID,10,0) ASC
LIMIT $pagePos,$pageCount

which I gleefully copied from Stackoverflow
(where $pagePos and $pageCount are for the next/previous links in the list, tapeID is a text field in the database table).

What I am after is a more sensible listing based numerically, so I’m after this:

743 A - Green

743 A - Red

783 A

783 B

5008 A

5008 B

5031 A - Red

5931 B - Green

7003 A

7003 B

ST 25

ST 26

ST 29 CD

Any suggestions? Thanks.


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

To be honest, it’s not a mission critical project, I was just wondering if there was a quick way to achieve what I want. This is more of a small project on the side that I’d just like to get working to my needs.

I’ll take your advice and think about an order_by column.


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options