Natural Sort 3

[dagger2002]

Ok guys and gals question.

I want to natural a list from a datbase.

One way I thought would work would be to make the 1-4 digit number into a 6 digit number with leading 0's so it would go like this.

1 = 000001
10 = 000010
100 = 000100
1000 = 001000

But the problem I have is that i don't know how to count how many digits there are in the number. so that it will equal 6 digits

 

[sleipnir214]

Sorting is best done at the database server, not in PHP code. It looks like you want a numerical sort on string data. Does your database server support typecasting a numeric string to an integer and then sorting on that integer?



Want the best answers? Ask the best questions! TANSTAAFL!

 

[vacunita]

if($number<10)
{ 
Add 5 zeroes
}

if(($number>10)&&($number<100)){
add 4 zeroes
}

...

Or you could [blue]strlen()[/blue] to get the number of digits in the number.

$number="154";
$digits=strlen($number);

echo $digits;[green]//3[/green]

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

But the problem I have is that i don't know how to count how many digits there are in the number. so that it will equal 6 digits

this bit can be done with printf

$vals = array (1, 10, 100, 1000, 10000);
foreach ($vals as $val):
 printf("%06s <br/>", $val);
endforeach;

but i think the sorting, as sleipnir214 says, is better done in the sql call

 

[dagger2002]

I am not sure if it supports typecasting.

How would I do this on the mySql Server?

 

[dagger2002]

Thank you all the strien worked. My Plan is to put it into the database with the 6 digit number and natsort on that.

 

[sleipnir214]

How would I do this on the mySql Server?

Suppose I have a table with a column named "thestring". That column contains:

[tt]+-----------+
| thestring |
+-----------+
| 1 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 2 |
| 20 |
| 21 |
| 22 |
| 23 |
| 24 |
| 25 |
| 3 |
| 4 |
| 5 |
+-----------+[/tt]

(I fetched the above data with the query, "SELECT * FROM foo ORDER BY thestring".)

If I issue the query "SELECT * FROM foo ORDER BY CAST(thestring AS UNSIGNED)", I would get:

[tt]+-----------+
| thestring |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 20 |
| 21 |
| 22 |
| 23 |
| 24 |
| 25 |
+-----------+[/tt]

Which sounds to me like what you want. And as I said before, let the database engine do the sorting: it's very good at it.




Want the best answers? Ask the best questions! TANSTAAFL!

 

[dagger2002]

Ok guys now I can make the new id. Any ideas on how to isert it into my db, which has like 4000 records.

 

[dagger2002]

Thank you all it works

 

[guelphdad]

First you should always mention which database you are using in your first post of a thread, that way people can help you right away.

the solution you need is to change the type of your column from char/varchar to an int type. then it will sort correctly for you.

 

[dagger2002]

no it didn't that was the problem.

I want to natural a list from a datbase.

And i didn't want to do it in the database i wanted to do it with php. It came out in the second post that i can do it with my db and that it was better to do it that way.