Sorting 1.1, 1.2, 1.2.1

[smeagal]

Hi all..

Need some help here. I am having problem sorting numbers like this:

1.1
1.2
1.2.1
2.0
2.1.1
2.2.1
....

How can i achieve this? FYI, the data are all stored in a character field (can i do it in INT?).

Dunno if there already posts like this, if there are please show me where it is.

Thanks in advance 😃

[barand]

If your items are in an array, use [man]natsort[/man]

eg

$a = array('1.2.1','1.2','1.1','2.2.2','2','2.1');
natsort($a); 
echo "<br><br>";
print_r ($a);

gives

Array ( [2] => 1.1 [1] => 1.2 [0] => 1.2.1 [4] => 2 [5] => 2.1 [3] => 2.2.2 )

[smeagal]

i'l try it out..

thanks!

[smeagal]

it seems to be working. but then if i stored those data in a MySql database, how can i sort it? can MySql sort it in such way or I have to do it in PHP?

[barand]

mysql_query("insert into tablec (id,descrip) values ('1.2', 'AAA')");
mysql_query("insert into tablec (id,descrip) values ('1.1.1', 'BBB')");
mysql_query("insert into tablec (id,descrip) values ('1.3.1', 'CCC')");
mysql_query("insert into tablec (id,descrip) values ('2.1.1', 'DDD')");
mysql_query("insert into tablec (id,descrip) values ('2.1', 'EEE')");
mysql_query("insert into tablec (id,descrip) values ('1.2.1', 'FFF')");
mysql_query("insert into tablec (id,descrip) values ('1.1', 'GGG')");
mysql_query("insert into tablec (id,descrip) values ('2', 'HHH')");
mysql_query("insert into tablec (id,descrip) values ('1', 'JJJ')");

$res = mysql_query("select * from tablec order by id");
while (list($i, $d) = mysql_fetch_row($res)) {
	echo "$i<br>";
}

gives

1
1.1
1.1.1
1.2
1.2.1
1.3.1
2
2.1
2.1.1

[barand]

My code posted above fails miserably though once you get to section 10 :o

I can't find a natural sort command in MySql. Perhaps if there are always 3 levels you could add 3 fields to your table L1, L2 and L3 which contain the bits of the number index, so

if id = 2.1.3
then L1 = 2, L2 = 1 and L3 = 3.

[smeagal]

i don't think that i can separate the bits of the number index because, there might be

1.1
1.11.1
1.11.1.111
2.1.3.2.1.2.3.4
... and so on

the bits are dynamic and that's why i put it in one column.

but can i do something like this?

i. populate data in array
ii. sort array
iii. loop array and extract from table like this:

$arr = $array();
...... //populate array

for( $i = 0; $i < sizeof($arr); $i++ )
{
$run_sql = mysql_query( "SELECT indent FROM tbl_indent WHERE indent = '$arr[$i]'", $db_conn );

$ind = mysql_result(  $run_sql, 0, "indent" );

echo $ind."<BR>";
}

not so smart huh? this is the only way that i can think of. i hope there's other more efficient way of doing this..
😕

[barand]

OK but do it using steps 1 and 2 only otherwise it'll take forever.

1. Read data into an array

2. sort the array with custom sort function using [man]strnatcmp[/man]

3. process the array
   [/list=1]

   I'll use my prev example, modified.

   function natsort ($a, $b) {
        return strnatcmp($a['id'], $b['id']);
   }
   
   /* put data in array */
   $res = mysql_query("select * from tablec order by id");
   $data = array();
   $rec = 0;
   while (list($i, $d) = mysql_fetch_row($res)) {
       $data[$rec]['id'] = $id;
       $data[$rec]['descrip'] = $d;
       $rec++;
   }
   
   /* sort array with custom function natsort() defined above */
   usort ($data, 'natsort');
   
   /* process the array */
   foreach ($data as $record) {
       echo $record['id'] . " " . $record['descrip'] . "<br>";
   }
   

   Check out [man]usort[/man] here.

   hth

[smeagal]

yup, it worked nicely.. thx!