Help needed in using an array to replace nested sql queries

PHP
1

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!

2

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?

3

I’d probably go with a data structure similar to the one detailed below…


<?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…


<html>
  <head>
    <title>Demo</title>
  </head>
  <body>
    <ul>
      <li>
        Breakfast
        <ul>
          <li>(11) Eggs</li>
          <li>(12) Sausages</li>
        </ul>
      </li>
      <li>
        Breakfast
        <ul>
          <li>(21) Bread</li>
          <li>(22) Cheese</li>
        </ul>
      </li>
      <li>
        Breakfast
        <ul>
          <li>(31) Rice</li>
          <li>(32) Chilli</li>
        </ul>
      </li>
    </ul>
  </body>
</html>

Obtaining this structure should be fairly trivial given the tables you’ve described. :slight_smile:

4

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:


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


 'Breakfast' [B], 'Mornings'[/B]=> 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).

5

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.

6

Okay, I think I’m following. :slight_smile:


<?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…


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…


<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.


<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>
7

Thanks again… this is a perfect foundation! I’m going to play with this a bit, really appreciate it!

8

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!

9

Great news Ted, I’m glad it worked out. :slight_smile: