SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard silver trophy
    Join Date
    Aug 2003
    Location
    Southern California
    Posts
    4,686
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Help needed in using an array to replace nested sql queries

    Running into some trouble with the MVC approach and I think it's a result of "old world" thinking. Basically I'm trying to understand how to implement the right array structure to aovid nested querying [potentially requiring me to put the query into the view].

    For the sake of the question I've simplified the structure.

    I've got two tables:

    list
    list_id,
    list_name,
    list_createddate

    list_items
    item_id,
    list_id,
    item_name,
    item_photo

    What I want to display out is the list name and the items under it. Those items are essentially infinite as is the number of lists so it's not a simple join issue.

    How would I go about building an array structure to collect both the top level list details as well as the corresponding, and potentially infinite, list item records in my model then loop them back out to a view and thus avoid stacking up queries?

    Thanks!
    Last edited by Ted S; May 2, 2011 at 09:31.
    - Ted S

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,269
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Ted S View Post
    Those items are essentially infinite as is the number of lists so it's not a simple join issue.
    maybe it's me, but i don't understand that statement

    are you saying the join has to be a complex join, or are you saying that you're doing a join but displaying the results isn't simple?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I'd probably go with a data structure similar to the one detailed below...
    PHP Code:
    <?php
    function getListItems(){
      return array(
        
    'Breakfast' => array(
          array(
    11'Eggs'),
          array(
    12'Sausages'),
        ),
        
    'Lunch'     => array(
          array(
    21'Bread'),
          array(
    22'Cheese'),
        ),
        
    'Dinner'    => array(
          array(
    31'Rice'),
          array(
    32'Chilli'),
        ),
      );
    }
    ?>
    <html>
      <head>
        <title>Demo</title>
      </head>
      <body>
        <ul>
          <?php foreach(getListItems() as $name => $items): ?>
            <li>
              <?php echo $name?>
              <ul>
                <?php foreach($items as $item): ?>
                  <li><?php printf('(%d) %s'$item[0], $item[1]) ?></li>
                <?php endforeach; ?>
              </ul>
            </li>
          <?php endforeach; ?>
        </ul>
      </body>
    </html>
    This will output...

    PHP Code:
    <html>
      <
    head>
        <
    title>Demo</title>
      </
    head>
      <
    body>
        <
    ul>
          <
    li>
            
    Breakfast
            
    <ul>
              <
    li>(11Eggs</li>
              <
    li>(12Sausages</li>
            </
    ul>
          </
    li>
          <
    li>
            
    Breakfast
            
    <ul>
              <
    li>(21Bread</li>
              <
    li>(22Cheese</li>
            </
    ul>
          </
    li>
          <
    li>
            
    Breakfast
            
    <ul>
              <
    li>(31Rice</li>
              <
    li>(32Chilli</li>
            </
    ul>
          </
    li>
        </
    ul>
      </
    body>
    </
    html
    Obtaining this structure should be fairly trivial given the tables you've described.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  4. #4
    SitePoint Wizard silver trophy
    Join Date
    Aug 2003
    Location
    Southern California
    Posts
    4,686
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by AnthonySterling View Post
    I'd probably go with a data structure similar to the one detailed below...
    Thank you Anthony!

    I'm a bit uncertain on the code to build the array as I know I to have multiple data points for the parent table (i.e. the list_name, list_date) and then records for each child item with a few cells (item_name, item).

    The query I've put together is straight forward enough:

    Code:
    select list_items.item_id,list_items.item_name,list.list_name,list.list_createddate,
    from list_items 
    left join list on list.item_id = list_items.list_id
    I'm aware of how to build a basic structure if the parent has just one point (like what you mapped out) but not how to do something like

    Code:
     'Breakfast' , 'Mornings'=> array(      
    array(11, 'Eggs'),      
    array(12, 'Sausages'),   
     ),
    Any suggestions on how to turn it all into the right array? I've found a few tutorials but most only handle a single value for the parent item (list).
    - Ted S

  5. #5
    SitePoint Wizard silver trophy
    Join Date
    Aug 2003
    Location
    Southern California
    Posts
    4,686
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    maybe it's me, but i don't understand that statement

    are you saying the join has to be a complex join, or are you saying that you're doing a join but displaying the results isn't simple?
    The join is simple enough but once I get the data out I need to build it back into an array that stores both the "list" attributes (name & date) along with the child "item" attributes (name, photo, etc).

    My challenge was two fold in getting the data into the array and then looping through it. I've solved the second part [I believe] but am still struggling to pick up php multi-dimensional arrays enough to get everything inserted properly.
    - Ted S

  6. #6
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Okay, I think I'm following.

    PHP Code:
    <?php
    error_reporting
    (-1);
    ini_set('display_errors'true);

    $resultset = array(
      array(
    1'Anthony''favourites''2011-12-25 00:00:00'),
      array(
    1'Anthony''friends''2011-12-25 00:00:00'),
      array(
    1'Anthony''followers''2011-12-25 00:00:00'),
      array(
    1'Anthony''following''2011-12-25 00:00:00'),
      array(
    2'TedS''sitepoint friends''2011-12-25 00:00:00'),
      array(
    2'TedS''blogs''2011-12-25 00:00:00'),
      array(
    2'TedS''to-do''2011-12-25 00:00:00'),
    );

    $lists = array();

    foreach(
    $resultset as $row){

      list(
    $id$name$list$date) = $row;

      if(
    false === array_key_exists($id$lists)){
        
    $lists$id ] = array(
          
    'name'  => $name,
          
    'lists' => array()
        );
      }

      
    $lists$id ]['lists'][] = array(
        
    'name'    => $list,
        
    'created' => $date
      
    );

    }

    print_r(
      
    $lists
    );
    That code should create this structure from your resultset...

    Code:
    Array
    (
        [1] => Array
            (
                [name] => Anthony
                [lists] => Array
                    (
                        [0] => Array
                            (
                                [name] => favourites
                                [created] => 2011-12-25 00:00:00
                            )
    
                        [1] => Array
                            (
                                [name] => friends
                                [created] => 2011-12-25 00:00:00
                            )
    
                        [2] => Array
                            (
                                [name] => followers
                                [created] => 2011-12-25 00:00:00
                            )
    
                        [3] => Array
                            (
                                [name] => following
                                [created] => 2011-12-25 00:00:00
                            )
    
                    )
    
            )
    
        [2] => Array
            (
                [name] => TedS
                [lists] => Array
                    (
                        [0] => Array
                            (
                                [name] => sitepoint friends
                                [created] => 2011-12-25 00:00:00
                            )
    
                        [1] => Array
                            (
                                [name] => blogs
                                [created] => 2011-12-25 00:00:00
                            )
    
                        [2] => Array
                            (
                                [name] => to-do
                                [created] => 2011-12-25 00:00:00
                            )
    
                    )
    
            )
    
    )
    You would then display like this...

    PHP Code:
    <html>
      <head>
        <title>Demo</title>
      </head>
      <body>
        <ul>
          <?php foreach(getListItems() as $id => $user): ?>
            <li>
              <?php echo $user['name']; ?>
              <ul>
                <?php foreach($user['lists'] as $list): ?>
                  <li><?php printf('%s created on %s'$list['name'], $list['created']) ?></li>
                <?php endforeach; ?>
              </ul>
            </li>
          <?php endforeach; ?>
        </ul>
      </body>
    </html>
    ... and finally.

    PHP Code:
    <html>
      <
    head>
        <
    title>Demo</title>
      </
    head>
      <
    body>
        <
    ul>
          <
    li>
            
    Anthony
            
    <ul>
              <
    li>favourites created on 2011-12-25 00:00:00</li>
              <
    li>friends created on 2011-12-25 00:00:00</li>
              <
    li>followers created on 2011-12-25 00:00:00</li>
              <
    li>following created on 2011-12-25 00:00:00</li>
            </
    ul>
          </
    li>
          <
    li>
            
    TedS
            
    <ul>
              <
    li>sitepoint friends created on 2011-12-25 00:00:00</li>
              <
    li>blogs created on 2011-12-25 00:00:00</li>
              <
    li>to-do created on 2011-12-25 00:00:00</li>
            </
    ul>
          </
    li>
        </
    ul>
      </
    body>
    </
    html
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  7. #7
    SitePoint Wizard silver trophy
    Join Date
    Aug 2003
    Location
    Southern California
    Posts
    4,686
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)
    Thanks again... this is a perfect foundation! I'm going to play with this a bit, really appreciate it!
    - Ted S

  8. #8
    SitePoint Wizard silver trophy
    Join Date
    Aug 2003
    Location
    Southern California
    Posts
    4,686
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)
    Anthony,

    Just thought I'd let you know that this worked great. I had to redo the list bit as that threw some errors (likely because I extended it to a lot more items) but all the logic was dead on, not even a typo. Thanks!
    - Ted S

  9. #9
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Great news Ted, I'm glad it worked out.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.


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
  •