Numbers.. Anyone help?

discostudio

I have started selling CDs and CDG karaoke discs..

I have created a table called product_tracklist with the columns

Artist
Trackname
Trackno

And I am importing the following information, when the list is complete, that is.

What type of column should I use for track number? I need 2x leading zeros, so the track numbers will be like follows:

001
002
003
004

I need it to do this or other wise the track numbers display like this:

1
10
11
12
13
2
3
4
5
6

So as you see I need them to be in the DB correctly formatted.

Any ideas are appreciated.

sarahk

You can either store them as a varchar(3) or store them as a tinyint and then reformat them prior to showing on the page. Your choice really.

Sarah

discostudio

Yes but if I format them on pulling them out of the db then when I ORDER BY trackno they will come out in an obscure fashion as shown above won't they?

sarahk

try it but I would have thought it would be perfect

this works on a PHP sort

<?PHP
$abc = array('001', '002', '003', '010', '011', '012');
asort($abc);
foreach($abc as $v) echo $v . "<br>\n";
?>

and I would have thought mysql would be the same.

Sarah

Weedpacket

They would come out in the right order (1,2,3,4,...) if they were stored in the database in a numeric-type field, but as (1,10,11,12,2,3) if they're stored in a character-type field (character type fields are sorted lexicographically, and lexicographically "10"<"2").

Either fix the type of the database field (preferred, and much easier) or in PHP use [man]natsort[/man].