SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard
    Join Date
    Jan 2005
    Location
    blahblahblah
    Posts
    1,447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how do you handle nested loops?

    Hi,

    I would like to know how you handle nested loops, especially in view-rendering context.

    In my scenario, there are three tables. Let's name them: countries, cities, users.

    As you can guess, there's a list of coutries, in which there are cities, in which you can find users.

    "cities" contains a foreign key (country_id). "users" contains a foreign key (city_id).


    So I guess my question can be separated into three parts:

    1) How would you store such data?
    2) How would you retrieve such data?
    3) How would you display such data (how would you code the view) if you want to display all the countries, all the cities for each country and all the users for each city (like a tree representation)?

    Regards,

    -jj.

  2. #2
    SitePoint Evangelist
    Join Date
    Jun 2006
    Location
    Wigan, Lancashire. UK
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'd use individual tables (normal normalisation rules) and I'd start by formulating a query that joined all three tables, so that all the data could be retrieved with just that one single query
    ---
    Development Projects:
    PHPExcel
    PHPPowerPoint

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,494
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    1) In MySQL, three tables, just like you described
    2) With one query, joining the three tables, sorting by country, city, user
    3) Looping through the result set, and checking for a change in country and/or city to display the correct information

  4. #4
    SitePoint Wizard
    Join Date
    Jan 2005
    Location
    blahblahblah
    Posts
    1,447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your replies
    Quote Originally Posted by guido2004 View Post
    and checking for a change in country and/or city to display the correct information
    Not sure i get that part...

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,494
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Suppose you have a country (Italy), with three cities (Rome, Florence, Napels) and each city has 3 users (R1, R2, R3, F1, F2, F3, N1, N2, N3).
    If you join the three tables, then you'll get the following result:
    Code:
    Italy  Florence  F1
    Italy  Florence  F2
    Italy  Florence  F3
    Italy  Napels    N1
    Italy  Napels    N2
    Italy  Napels    N3
    Italy  Rome      R1
    Italy  Rome      R2
    Italy  Rome      R3
    If you just loop through it and display all info, then the country and the city will be displayed multiple times.
    If you want to display each country and city name only once, like this:
    Code:
    Italy
      Florence
        F1
        F2
        F3
    etc.
    Then you'll have to write some code that checks if the country and/or city in the current row has already been displayed.

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Could you post a result set and tables?

    Please also identify the primary keys and foreign keys in each table.

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Don't have the time to explain this, but this code was designed to do exactly what you want in a generic fashion. Granted there is a lot going here it goes row by row and transforms each into a hierarchical object representation. It was specifically designed to work alongside result sets with n number of joins. Resulting in the possibility to create a infinite depth tree result from a row based result set.

    PHP Code:
    $counties[9]->cities[10]->users[7]->name
    This would be the code that processes each row of the result set:

    PHP Code:
    ...

    $collectionAgent = new ActiveRecordCollectionAgent($select);

     while(
    $row $stmt->fetch(PDO::FETCH_ASSOC)) {
    $collectionAgent->process($row,$node);
    }

    // get top level collection
    $records $collectionAgent->getRecords(); 
    The $select variable is the select statement represented as a broken down object. Specifically for the purpose of translating each column to its appropriate node (table). The $node variable represents the root node (table). Every join that occurs results in a node (table) that becomes a child of the table to which it is joined. This results in tree representation of any query that can then be transposed to a result to make hierarchical data(objects) from a single row – SQL based result set. The $node itself is a tree representation of the from clause to make all this possible.

    Its part of my own little library and works alongside it, but the concepts are the same either way.

    CollectionAgent Code

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Not to plug my own work or anything but the result your after would be one single line of code given the model files existed.

    PHP Code:
    $countries
    Country::find(
        array(
            
    'include'=>'cities'
        
    )
        ,array(
            
    'include'=>'users'
        
    )
    );

    // example of displaying data
    $countries[2]->cities[4]->users[9]->name// if name where a column of user table/model 


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •