Maybe simple performace question (think)

[eluser]PoWah[/eluser]
What is the best way (having in mind performance) to do such thing:

I have in DB a field with information representing pages hierarchy:

Code:

1
1.1
1.2
1.2.1
2
2.1
2.2
...
...
...
11
11.1
11.1.1
11.2
11.2.1
11.2.2
11.3
...
and so on...

.. and I need them to be sorted in exactly the same way as I wrote above. Maybe is there any way to do this with only SQL?
At this moment I know the only way (with PHP sorting):

I get all results from DB and then do my sorting:

Code:

$result_array = array();
foreach ($query->result() as $row)
{
    $how_many_dots = substr_count($row->hierarchy, "."); // find how many dots is in 'hierarchy' field data
    $result_array[$how_many_dots][] = $row;
}

ksort($result_array); // sort by array key

$keys = array_keys($result_array);

foreach ($keys as $key)
{
    $run_query = true;
    foreach ($result_array[$key] as $sorted_results)
    {
        $sorted_result_array[] = sorted_results;
    }
}

[eluser]Michael Wales[/eluser]
Not sure if you can change your database structure - but I prefer to give each article an id and a parent_id.

parent_id of 0 means it's a top-level article
parent_id that corresponds to another article means it falls below that article

You could then include an order field that would display the child articles in the correct order

Code:

$this->db->orderby('order', 'asc');
$query = $this->db->getwhere('articles', array('parent_id'=>1));

[eluser]PoWah[/eluser]
id and parent_id is already in db table, order field two. But its not what I asked for. Thanks anyway. Anyone has any more suggestions?

[eluser]Michael Wales[/eluser]
Couldn't you just dynamically generate the page hierarchy then?

Code:

echo $parent_id;
// Loop until there are no more child elements {
  // Loop through all of the child elements, ordered by ID {
    echo $iterative_integer;
  }
}

[eluser]Sean Murphy[/eluser]
$this->db->orderby('hierarchy', 'asc'); Should do it.

[eluser]PoWah[/eluser]
no, it shoudnt :-) try yourself..

[eluser]Sean Murphy[/eluser]
You're right! My bad, my test data wasn't complete. I was missing double digits (e.g. 11).

So, I went back to the drawing board and came up with this:

Code:

$this->db->orderby('hierarchy + 0, hierarchy ASC');

I tested it, and it worked for me. If you have problems, let me know.

[eluser]PoWah[/eluser]
you are awesome ! thank for your patience and valueble help ! ;-)
But one more question.. could you explain what do this "+ 0" do ?

[eluser]Sean Murphy[/eluser]
The + 0 implicitly converts the string to a decimal.

[eluser]Michael Wales[/eluser]
great tip Sean! I'll have to file that one away.