Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

First of all thanks to stackoverflow to provide this plattform and you, to give a newbie a help ;-)

Now .. I have 2 tables: pages and sections Each has an own id. A page has one or more sections. A section belongs to exactly 1 page. The list sequence of both id's is handled in a different field.

I read both tables and create an (unsorted) array. What I finally need is a sorted list as shown below, having the page_id's and section_id's in the correct sequence..

Here an example of my tarray after retrieve data:

myArr[] = array( page_id=>2,  section_id=>2,  parent_id=>0, level=>0, page_seq=>1, section_seq=>2, page_title=>p1 );
myArr[] = array( page_id=>2,  section_id=>9,  parent_id=>0, level=>0, page_seq=>1, section_seq=>1, page_title=>p1 );
myArr[] = array( page_id=>3,  section_id=>3,  parent_id=>0, level=>0, page_seq=>2, section_seq=>1, page_title=>p2 );
myArr[] = array( page_id=>4,  section_id=>4,  parent_id=>0, level=>0, page_seq=>3, section_seq=>1, page_title=>p3 );
myArr[] = array( page_id=>5,  section_id=>5,  parent_id=>3, level=>1, page_seq=>3, section_seq=>1, page_title=>p2-3 );
myArr[] = array( page_id=>6,  section_id=>6,  parent_id=>3, level=>1, page_seq=>2, section_seq=>1, page_title=>p2-2 );
myArr[] = array( page_id=>7,  section_id=>7,  parent_id=>4, level=>1, page_seq=>1, section_seq=>1, page_title=>p3-1 );
myArr[] = array( page_id=>8,  section_id=>8,  parent_id=>7, level=>2, page_seq=>1, section_seq=>1, page_title=>p3-1-1 );
myArr[] = array( page_id=>9,  section_id=>10, parent_id=>5, level=>2, page_seq=>1, section_seq=>1, page_title=>p2-1-1 );
myArr[] = array( page_id=>9,  section_id=>11, parent_id=>5, level=>2, page_seq=>1, section_seq=>2, page_title=>p2-1-1 );
myArr[] = array( page_id=>10, section_id=>12, parent_id=>3, level=>1, page_seq=>1, section_seq=>1, page_title=>p2-1 );

My problem is the sorting.

  • The section_seq is a sequence of the section within the page.
  • The page_seq is a sequence of the page within the level for the same parent.

I find some rekursive loop examples already here, but - to be honest - I'm not able to adapt them to my needs. And, do I need a rekursive loop ?

What should be the key of my array: the section_id, because it is unique over all pages ? How to do the correct sort ?

Just to be noted: the page title could unfortunately not to be used - as in the example above - for sorting purposes ;-) as it is free text...

So what I need is:

  • read first page (1) with level 0 and page_seq = 1
  • read first page (2) having level 1 -if exists- with page (1) as parent and page_seq = 1
  • read first page (3) having level 2 -if exists- with page (2) as parent and page_seq = 1
  • ... continue as long as no more deeper level exists
  • read second page (4) having level 2 -if exists- with page (2) as parent and page_seq = 1
  • ... continue as long as no more deeper level exists and also no more pages on this level having page (2) as parent
  • read second page (5) having level 1 -if exists- with page (1) as parent and page_seq = 1
  • ... continue as long as no more deeper level exists and also no more pages on this level having page (5) as parent
  • read second page (6) with level 0 and page_seq = 2
  • and so on.

Any powerfull help & ideas ?

Thanks in advance Wolfgang

share|improve this question
I am having a difficult time understanding the question. Do you want to sort by page_title? If so, just use an ascending alphabetical sort. – erisco Feb 3 at 20:10
I'm having a hard time seeing what you're actually sorting by in your example.. – fiskfisk Feb 3 at 22:33
If I check the origin array after filling it up, then it is sorted by page_id. As I wrote above, I can NOT use the page title, as it is free text and contain anything. It is just used here to show what I wanted. – user2037828 Feb 4 at 7:29
Btw, I've updated the array above to show array sequence after data read. – user2037828 Feb 4 at 7:34

2 Answers

up vote 0 down vote

My idea would be to use the database to do the sorting. With MySQL you could use a left join on both tables to get the sections and pages data in one results table. In the query you could use the ORDER BY keyword to specify the column(s) according to which the results must be sorted.

share|improve this answer
Yes, but ... let's do an order by level, page_seq, section_seq. And I get: page id/section_id = 2/2, 2/9, 3/3, 4/4, 5/5, 6/6, 7/7, 8/8, 9/10, 9/11, 10/12 But what I need would be: page_id/section_id = 2/9, 2/2, 3/3, 10/12, 9/10, 9/11, 6/6, 5/5, 4/4, 7/7, 8/8 – user2037828 Feb 4 at 7:40
I've updated origin question with step by step explained reading requirement. – user2037828 Feb 4 at 7:48
I have read the list of bullets you added. I understand the first bullet represents page 1 an the second bullet represents page 2. I see how the page represented by the second bullet can have the page represented by the first bullet as a parent page. But how can the page represented by the third bullet have page 1 as a parent? In a tree structure you would expect it to have the page represented by the second bullet as a parent page. – Henk Kroon Feb 4 at 17:16
Hello Henk. You are right. I've updated bullets accordingly. – user2037828 Feb 5 at 15:11
Please keep in mind that unfortunately the id's () mentioned in the bullet are not in perfect order in real live .... – user2037828 Feb 5 at 15:13
up vote 0 down vote

So finally I found a solution

1) create a list of all pages to be process. The $key generated looks on first level like "1", on 2nd level like "1.1", on 3rd level like "1.1.1" etc. 

read all pages
while ( $fetch_obj_pages = $obj_pages->fetchRow() ):
    // do some validations and keep only those in mind which should be processed
    ....

    // get and save key for sorting
    if ( $isValid == TRUE ):
        $key = '';
        if ( array_key_exists ( $fetch_obj_pages[ 'parent_id' ],  $pageSortList ) == TRUE ):
            $key = $pageSortList[ $fetch_obj_pages[ 'parent_id' ]] . '.';
        endif;
        $key .= $fetch_obj_pages[ 'page_seq' ];
        $pageSortList[ $fetch_obj_pages[ 'page_id' ]] = $key;
     endif;
endwhile;

2) create a list of all sections to process. It is taken the key generated in pages list above and enhance it with section sorting sequence, then save it per section_id as key.

read all sections
while ( $fetch_obj_sections = $obj_sections->fetchRow() ):
    // do some validations and keep only those in mind which should be processed
    ....

    // get and save key for sorting
    if ( $isValid == TRUE ):
        // get key from page and save key for section
        $key = '';
        if ( array_key_exists ( $fetch_obj_sections[ 'page_id' ],  $pageSortList ) == TRUE ):
            $key = $pageSortList[ $fetch_obj_sections[ 'page_id' ]] . '-';
        endif;
        $key .= $fetch_obj_sections[ section_seq ];
        $sectionSortList[ $fetch_obj_sections[ 'section_id' ]] = $key;
    endif;
endwhile;

3) sort the section list natsort ( $sectionSortList );

4) building the result by looping over $sectionSortList and getting the neccessary data due to section_id used as key in this list.

Before someone ask: Of course, all possible filters are done in the SQL. But some are not possible and due to need to be done in a loop-

Most probably, step 1) and 2) could be done in one step using a JOIN and a proper ORDER BY.

Last-Bit-Saving-Junkies will find lot of places for code optimizations and reduction. However, I like the alternate functionality with : and endif; ;-)

In case someone sees a possibility to make things really faster, feel welcome to provide your ideas.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.