Loop Through Nested Results?

jamesjmann · December 29, 2011

Hi, I'm developing a "gallery" script so that I can manage my photos online, and I'm having a problem I can't seem to figure out. Hoping someone can help...

So here's my problem.

I have two main tables for my application (amongst several others but for all intents and purposes they aren't needed). One is "albums" and the other "photos".

Here's the structure for both:

Albums

CREATE TABLE IF NOT EXISTS `kill4silence_photos_albums` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `parent_id` int(255) NOT NULL,
  `user_id` int(255) NOT NULL,
  `cover` int(255) NOT NULL,
  `name` varchar(50) NOT NULL,
  `description` text NOT NULL,
  `location` varchar(255) NOT NULL,
  `time` varchar(50) NOT NULL,
  `date` varchar(50) NOT NULL,
  `timestamp` int(255) NOT NULL,
  `last_updated_date` varchar(50) NOT NULL,
  `last_updated_time` varchar(50) NOT NULL,
  `last_updated_timestamp` int(255) NOT NULL,
  `views` int(255) NOT NULL,
  `tags` longtext NOT NULL,
  `downloads` int(255) NOT NULL,
  `download_disabled` int(1) NOT NULL,
  `comments_disabled` int(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=156 ;

-----------------------------------------------------------------------------------------
Photos

CREATE TABLE IF NOT EXISTS `kill4silence_photos_photos` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `album_id` int(255) NOT NULL,
  `user_id` int(255) NOT NULL,
  `name` varchar(50) NOT NULL,
  `caption` varchar(200) NOT NULL,
  `time` varchar(50) NOT NULL,
  `date` varchar(50) NOT NULL,
  `timestamp` int(255) NOT NULL,
  `file_name` varchar(5000) NOT NULL,
  `file_type` varchar(100) NOT NULL,
  `file_ext` varchar(25) NOT NULL,
  `location` varchar(200) NOT NULL,
  `photo_time` varchar(50) NOT NULL,
  `photo_date` varchar(50) NOT NULL,
  `views` int(255) NOT NULL,
  `tags` longtext NOT NULL,
  `downloads` int(255) NOT NULL,
  `downloads_disabled` int(1) NOT NULL,
  `comments_disabled` int(1) NOT NULL,
  `visible` int(1) NOT NULL,
  `last_updated_date` varchar(50) NOT NULL,
  `last_updated_time` varchar(50) NOT NULL,
  `last_updated_timestamp` int(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=204 ;

Now basically how this works is when a new album is created, it is given a parent_id of '0' where the parent_id is the identifer of it's "parent" album.

Say, I have five albums, each nested within each other like so.

1, 2, 3, 4, 5.

On the page, it would look like,

Album 1 (link that takes you to Album 2)

Album 2 (link that takes you to Album 3)

And so on...

I use $_GET to send you to the next nested album.

Each album's query string looks like this:

photos.php?album={$id}

No matter what album you're in (be it the "father" parent album or a nested album)

Now, what I want to do is construct a method to add to my "photos" class, that creates an array of all parent album id's depending on the current nested album, so I can use that array to display a "navigation" type thingy-ma-jigger at the top of each page. For example:

Current album is "Ghosts"

Navigation would look like: Paranormal Activity -> Experiences -> Ghosts

Where "Ghosts" and "Experiences" are albums nested in "Paranormal Activity"

I figure you'd have to go about this using a loop of some kind, but I can't for the life of me figure it out. The only thing I can think of to do that doesn't entirely solve this problem is get the parent id of the current album. So as of right now, if "Ghosts" is currently being displayed, I can output a link taking you to "Experiences", but I can't do so to get "Paranormal Activity". I mean I could, but I'd have to loop through twice to get both ID's. I need a method that will loop through infinitely until there are no more parent albums to be found.

Hope this all makes sense.

Here's my code for viewing an album, just so you can get a clear picture (I'm using the Smarty extension case the HTML code confuses you)

<div id='view_album'>
    <!--<a href='photos.php?album={$PARENT_ALBUM_ID}'>« Back to {$PARENT_ALBUM_NAME}</a>-->
    <div class='album_name'>
        {$ALBUM_NAME}
    </div>
    <div class='album_info'>
        Created by <a href=''>{$USERNAME}</a> (<a href="photos.php?album={$PARENT_ALBUM_ID}">{$PARENT_ALBUM_NAME}</a>) {$DATE_FANCY} • Updated {$LAST_DATE_FANCY}
        {if strlen($LOCATION) > 0}
         • Taken at {$LOCATION}
        {/if}
        {if $smarty.const.PHOTOS_ADMIN_ACCESS == true}
         • <a href='photos.php?album={$ALBUM_ID}&action=edit'>Edit Album</a> 
         • <a href='photos.php?album={$ALBUM_ID}&action=delete'>Delete Album</a> 
         • <a href='photos.php?album={$ALBUM_ID}&action=upload&max=1&submit=false'>Upload Photos</a>
        {/if}
    </div>

<!--Start albums-->
    {$i= 0}
    {$max_columns = 5}
    {$column_width = 100 / {$max_columns}}
    <table width='100%'>
        {foreach $ALBUMS_ARRAY as $id => $id}
        	{if $i == 0}
            <tr>
            {/if}
                <td width='20%' align='center'>
                    <div class='album_graphic_container'><a href='photos.php?album={$id}'>
                    	{if {albums::info ("id", "=", {$id}, "cover")} == 0}
                            <a href="photos.php?album={$id}"><img src='photos/images/icons/album_default.png'></a>
                        {else}
                        	<img src='photos/covers/{$id}/default.jpg' style="width: 100%;"/>
                        {/if}
                    </div>
                    <div class='album_graphic_info'>
                        <a href='photos.php?album={$id}'>{albums::info ("id", "=", {$id}, "name")}</a>
                        <br>
                        {albums::info ("id", "=", {$id}, "num_albums")} Albums • {albums::info ("id", "=", {$id}, "num_photos")} Photos
                        <br>
                        <a href='' style='font-size: 100%;'>{albums::info ("id", "=", {$id}, "num_comments")} Comments</a>
                    </div>
                </td>
            {$i = $i + 1}
            {if $i == $max_columns}
                </tr>
                {$i = 0}
            {/if}
        {/foreach}
        {if $i < $max_columns}
            {$j = 0}
            {for $k = $i; $k < $max_columns - 1; $k++}
                {if $j == 0}
                    <td width='{$column_width}%' align='center'>
                        <div class='album_graphic_container'>
                            <a href='photos.php?album={$ALBUM_ID}&action=compose'>
                            <img src='photos/images/icons/album_default.png'>
                            </a>
                        </div>
                        <div class='album_graphic_info'>
                            <a href='photos.php?album={$ALBUM_ID}&action=compose'>+ Add New Album</a>
                            <br>
                             
                            <br>
                             
                        </div>
                    </td>
                {/if}
                {$j = 1}
                <td width='{$column_width}%' align='center'> </td>
            {/for}
        {else}
        {/if}
</table>

<!--Start photos-->
    <div class='view_album_lft'>
        {if {albums::info ("id", "=", {$ALBUM_ID}, "num_photos")} == 0}
         
        {else}
            <!--<div class='view_album_photos_header'>";
            Photos In <a href=''>" . albums::info ("id", "=", $_GET["album"], "name") . "</a> (" . albums::info ("id", "=", $_GET["album"], "num_photos") . ")";
            </div>";-->
            {$p = 0}
            {$p_max_columns = 6}
            {$p_column_width = 100 / $p_max_columns}
            <table width='100%'>
                {foreach $PHOTOS_ARRAY as $id => $id}
                	{if $p == 0}
                    	<tr>
                    {/if}
                    <td width='{$p_column_width}%' align='center'>
                        <div class='album_graphic_container'>
                        	<a href='photos.php?album={$ALBUM_ID}&photo={$id}'><img src='{photos::info ("id", "=", $id, "src_thumb_medium")}' style='width: 100%;'></a>
                        </div>
                        <div class='album_graphic_info'>
                            {photos::info ("id", "=", {$id}, "views")} Views
                            <br>
                            {photos::info ("id", "=", {$id}, "num_comments")} Comments
                        </div>
                    </td>
                    {$p = $p + 1}
                    {if $p == $p_max_columns}
                        </tr>
                        {$p = 0}
                    {/if}
                {/foreach}
                {if $p < $p_max_columns}
                    {for $q = $p; $q < $p_max_columns; $q++}
                        <td width='{$p_column_width}%'> </td>
                    {/for}
                {/if}
            </table>
        {/if}
    </div>
    
    <div class='album_info_2_lft'>
        {if {likes::determine ("album", {$ALBUM_ID}, $smarty.const.PHOTOS_USER_LOGGED)} == -1}
            {$NUM_LIKES} Likes • <a href="photos.php?album={$ALBUM_ID}&action=like">Like this album</a> | {$NUM_DISLIKES} Dislikes • <a href="photos.php?album={$ALBUM_ID}&action=dislike">Dislike this album</a>
        {/if}
        {if {likes::determine ("album", {$ALBUM_ID}, $smarty.const.PHOTOS_USER_LOGGED)} == 1}
            {$NUM_LIKES} Likes • <a href="photos.php?album={$ALBUM_ID}&action=unlike">Unlike this album</a> | {$NUM_DISLIKES} Dislikes • <a href="photos.php?album={$ALBUM_ID}&action=dislike">Dislike this album</a>
        {/if}
        {if {likes::determine ("album", {$ALBUM_ID}, $smarty.const.PHOTOS_USER_LOGGED)} == 0}
            {$NUM_LIKES} Likes • <a href="photos.php?album={$ALBUM_ID}&action=like">Like this album</a> | {$NUM_DISLIKES} Dislikes • <a href="photos.php?album={$ALBUM_ID}&action=undislike">Undislike this album</a>
        {/if}
    </div>
    <div class='album_info_2_rt'>
    	{$DOWNLOADS} Downloads • <a href="photos.php?album={$ALBUM_ID}&action=download">Download this album</a>
    </div>
    <br clear="all" />

    <!--<div class='view_album_rt'>
        <div class='view_album_rt_header'>
            Share This Album
        </div>
        <div class='view_album_rt_header'>
            Download This Album
        </div>
        <a href=''>Low Quality ({albums::info ("id", "=", {$ALBUM_ID}, "album_size_low")})</a>
        <br>
        <a href=''>Medium Quality ({albums::info ("id", "=", {$ALBUM_ID}, "album_size_medium")})</a>
        <br>
        <a href=''>High Quality ({albums::info ("id", "=", {$ALBUM_ID}, "album_size_high")})</a>
        <br>
        <a href=''>Full Size Quality ({albums::info ("id", "=", {$ALBUM_ID}, "album_size_full")})</a>
    </div>
    
    <br clear='all'>-->
    
    <div class='view_album_comments_container'>
        <div class='view_album_comments_header'>
            All Comments ({$ALBUM_NUM_COMMENTS})
        </div>
    
        <div class='view_album_comments_add'>
            {if !{$PHOTOS_USER_LOGGED}}
                You must <a href='login.php'>login</a> or <a href='register.php'>sign up</a> in order to post comments
            {else}
                {if $COMMENTS_DISABLED == 0}
                    <div class='view_album_comments_single_wrapper'>
                        <div class='view_album_comments_single_lft'>
                            <img src='members/{users::info ($smarty.const.PHOTOS_MEMBERS_FIELD_ID, "=", $smarty.const.PHOTOS_USER_ID, "avatar_src")}'>
                        </div>
                        <div class='view_album_comments_single_rt'>
                        	<form action='photos.php?album={$ALBUM_ID}&comment=new&action=add' method='post'>
                            <input type='hidden' name='parent_id' value='0' />
                            <textarea name='body' placeholder="Write your response" class='view_album_comments_add_textarea'></textarea>
                            <span style="float: right;"><input type="submit" class="fb_button_blue" value="Post Comment" /></span>
                            </form>
                        </div>
                        <br clear='all'>
                    </div>
                {else}
                    Comments for <a href=''>{$ALBUM_NAME}</a> has been disabled
                {/if}
            {/if}
        </div>

        {foreach $COMMENTS_ARRAY as $id => $id}
            <div class='view_album_comments_single_wrapper'>
                <div class='view_album_comments_single_lft'>
                    <img src='members/{users::info ($smarty.const.PHOTOS_MEMBERS_FIELD_ID, "=", {comments::info ("id", "=", {$id}, "user_id")}, "avatar_src")}'>
                </div>
                <div class='view_album_comments_single_rt'>
                    <div class='view_album_comments_single_rt_username'><a href=''>{comments::info ("id", "=", {$id}, "username")}</a></div>
                    <div class='view_album_comments_single_rt_body'>{comments::info ("id", "=", {$id}, "body")}</div>
                    <div class='view_album_comments_single_rt_info'>
                        {comments::info ("id", "=", {$id}, "date_fancy")}
                        • <a href="">Like</a> | <a href="">Dislike</a>
                        {if $smarty.const.PHOTOS_USER_LOGGED == true}
                            {if $smarty.const.PHOTOS_ADMIN_ACCESS || $smarty.const.PHOTOS_USER_ID == {comments::info ("id", "=", {$id}, "user_id")}}
                                • <a href="">Delete</a> 
                            {/if}
                        • <a href="">Report</a>
                       {/if}
                    </div>
                </div>
                <br clear="all" />
            </div>
        {/foreach}
        
        <div id='view_album_comment_report'>
        	<div id='view_album_comment_report_body'>
            <span class='view_album_comment_report_body_header'>Report Comment</span>
            </div>
        </div>
        
    </div>
    
</div>

Need more info, just ask. Thanks.

Muddy_Funster · December 29, 2011

Your coming at this from the wrong angle. A much better way to do this is to build a trail as you venture deeper into the nest. It's called a "breadcrumb trail" and is used by most things that operate the way you want your galleries to. It's much more efficient as you only need to run a single query and don't need to loop anything at all. All you need is an array or list and to use a session variable (although you could pass it through the URL it's a bit messy)

kicken · December 29, 2011

Given your current db structure, you can do it one of two ways:

1) Issue a query to select the current albums name and parent id. Save the name and then repeat the query using the parent id just received as the new current album id. Continue this process until the parent id is 0

2) Choose most likely maximum nesting level (say, 6) and then issue a single query with a bunch of left joins.

If you feel like changing the db structure, you could implement it as a nesting doll type approach, which makes things like querying the parent categories easy, but slightly complicates adding/removing items.

jamesjmann · December 29, 2011

Given your current db structure, you can do it one of two ways:

1) Issue a query to select the current albums name and parent id. Save the name and then repeat the query using the parent id just received as the new current album id. Continue this process until the parent id is 0

This was my original approach, but the thing I can't figure out is how would you set it up so that the loop or whatever you'd use would stop checking that process once the parent id reaches 0 on it's own?

2) Choose most likely maximum nesting level (say, 6) and then issue a single query with a bunch of left joins.

This solution would only be okay if I had wanted a maximum nesting level, which I don't. I'm developing this application as freesource which I will eventually publish on my website and I want users to have the maximum amount of capabilities/options, meaning I want them to be able to make as many nested albums as they wish. But the farther you go into the "nest", the harder it is to navigate to previous albums.

If you feel like changing the db structure, you could implement it as a nesting doll type approach, which makes things like querying the parent categories easy, but slightly complicates adding/removing items.

I took a look at the link above, but didn't find it very useful.

But now I have yet another question because the user above you stated that I'm "coming at this from the wrong angle". Is my DB structure how you'd go about achieving something like this? I'd imagine it's the same as how PHPBB stores their forums. If I'm correct, PHPBB allows for unlimited nested forums. I mean this is just how I thought something like this should be done, but if there's another better way that doesn't involve more than one table, please let me know!

kicken · December 29, 2011

This was my original approach, but the thing I can't figure out is how would you set it up so that the loop or whatever you'd use would stop checking that process once the parent id reaches 0 on it's own?

Just like you'd do any loop.

function getParentList($currentId){
$list=array();
do {
	$sql = 'SELECT name, parent_id FROM kill4silence_photos_albums WHERE id='.intval($currentId);
	$res = mysql_query($sql);
	if ($res){
		$row = mysql_fetch_assoc($res);
		$currentId = $row['parent_id'];
		$list[] = $row['name'];
	}
	else {
		$currentId = 0;
	}
} while ($currentId != 0);

return $list;
}

I mean this is just how I thought something like this should be done, but if there's another better way that doesn't involve more than one table, please let me know!

It's a valid approach. On the positive side, it's much simpler to implement and maintain. On the down side, it makes things like this bread crumb list less efficient. The better way is described in the link I posted above.

jamesjmann · December 30, 2011

Just like you'd do any loop.

function getParentList($currentId){
$list=array();
do {
	$sql = 'SELECT name, parent_id FROM kill4silence_photos_albums WHERE id='.intval($currentId);
	$res = mysql_query($sql);
	if ($res){
		$row = mysql_fetch_assoc($res);
		$currentId = $row['parent_id'];
		$list[] = $row['name'];
	}
	else {
		$currentId = 0;
	}
} while ($currentId != 0);

return $list;
}

Thanks so much! I've been scratching my head for the past hour trying to tweak a "Breadcrumb" script I found that actually worked to return an array instead of printing the "crumbs". Nothing I did seemed to work, and didn't help that script in question had a method calling itself within itself when executed wherever. I kind of feel like an idiot for not having realized that this could have been achieved with such little code, but then again I had never worked with dowhile loops before...

It's a valid approach. On the positive side, it's much simpler to implement and maintain. On the down side, it makes things like this bread crumb list less efficient. The better way is described in the link I posted above.

Not that I'm too concerned about this, but what do you mean it is less efficient? I've tested the code already and it seems to work exactly how I want it to, but now I'm starting to have doubts about it's overall efficiency.

Muddy_Funster · December 30, 2011

Running Queries within loops is NEVER a good idea. ESPECIALY if you are planning on passing your app out to general population. multiple people on the same hosting bombarding thier provider with several queries a second just to build a breadcrumb list is really not going to make anyone happy. Add to that the potential for infinate loops and your backing a looser.

There are better ways of doing this, as I already said, but you go with what makes you happy.

jamesjmann · December 30, 2011

Running Queries within loops is NEVER a good idea. ESPECIALY if you are planning on passing your app out to general population. multiple people on the same hosting bombarding thier provider with several queries a second just to build a breadcrumb list is really not going to make anyone happy. Add to that the potential for infinate loops and your backing a looser.

There are better ways of doing this, as I already said, but you go with what makes you happy.

That makes sense. But I'd imagine you could use mysql_free_result() after the method goes through the loop each time? Not sure if that would make a difference, but I suppose it's a possibility...

And I have just one more question regarding this...how would I go about getting the breadcrumbs for nested albums?

Like say, I'm currently in an album called "Photography" and it has several albums inside, each with their own subalbums and photos. What I want to be able to do is add up the number of photos in each sub album that's nested in "Photography" so I can display it on the page for "Photography". Getting the number of photos in each album isn't the problem. If I can get this method to construct an array with the ids of every nested album, I can use one of my other methods to do the calculations. S'just the problem is GETTING those nested albums' ids.

I would imagine you'd have to do the above in reverse, but I haven't the slightest clue how.

kicken · December 30, 2011

If you want to get the sub-albums then you just query for all the albums who's parent_id is the current album. You can do that and get your # of photos per album in a single query.

Muddy_Funster · December 30, 2011

mysql_free_result() makes absoloutly no difference on the sheer volume of queries themselves. To get the number of albums within an album you would use a recusive count query running through from the current location and tracing through the parent tree. One level down isn't so bad, but tracing the tree to the verry end of each branch is, I think, virtualy impossible with your table structure.

jamesjmann · December 30, 2011

If you want to get the sub-albums then you just query for all the albums who's parent_id is the current album. You can do that and get your # of photos per album in a single query.

Well, I was able to do that, but I also want to get the number of photos in each nested albums nested albums.

For example, say I'm on "Photography". This the navigation tree for that album:

Photography -> A (4 photos), B (3 photos) -> C (20 photos), D (8 photos) -> E (39 photos) -> F (60 photos), G (29 photos) -> H (1 photo) -> I, J (0 photos), K

Where the labels after the "->" signify a nested album.

----or-----

**********************************Photography**********************************

***********A**********B**********D**********G**********J**********K***********

******** ****** | ****** | ******* | ****** ****** ***********

C E H

| |

F i

As of right now I am able to get all of the photos in A, B, D, G, J, and K, by running a query like this:

$result_9		= mysql_query("SELECT id FROM " . PHOTOS_TABLE_ALBUMS . " WHERE parent_id = '" . $row["id"] . "'");
$info			= self::info ("id", "=", $row["id"], "num_photos");

while ($row_9 = mysql_fetch_array($result_9)) {
$info = $info + self::info ("id", "=", $row_9["id"], "num_photos");
}
$info = $info;

Which is inside of the class/method: albums/info. What it does is gets the number of photos in the current album and adds it to the number of photos in each nested album. What I can't figure out is how to get the number of photos inside of "C", "E", "H", "F", and "I".

I mean, I could get the numbers for those albums, but, again, I need a query that will "loop" through until there are no more nested albums in each nested album to check.

Hope this all makes sense, but case in point, in regards to the hypothetical example above, I want to get all 164 photos from all of the albums in "Photography" and display "164" on the page for "Photography", rather than just get 44 photos from the first set of nested albums.

jamesjmann · December 30, 2011

mysql_free_result() makes absoloutly no difference on the sheer volume of queries themselves. To get the number of albums within an album you would use a recusive count query running through from the current location and tracing through the parent tree. One level down isn't so bad, but tracing the tree to the verry end of each branch is, I think, virtualy impossible with your table structure.

Is there a solution to the "multiple query" thing? I ask this because I have at least 50 methods defined in about 5-6 classes and almost all of them execute a query of some kind. Perhaps running mysql_close at the very end of the script would solve this? Or is it inevitable, because so many queries are being executed anyway?

And I'm thinking it can't be impossible to get to the very end of each branch. There's GOT to be a solution.

Muddy_Funster · December 30, 2011

You should be running mysql_close at the end of every query, not at the end of the script. I can't endorse looping select queries, it's really bad practice. There may be a way arround it using some complex PHP, I'll need to have a think on it.

jamesjmann · December 30, 2011

You should be running mysql_close at the end of every query, not at the end of the script. I can't endorse looping select queries, it's really bad practice. There may be a way arround it using some complex PHP, I'll need to have a think on it.

Okay, but do you think there's a way to do the "reverse" Breadcrumb trail? You said it's impossible with my table structure, but I KNOW there's something I can do about it. Just got to dig deep down into the depths of my brain. Which I'm having a slight difficulty in doing at the moment lol *scratches head*

Random question...I've heard of mysql_close and understand it's uses for implementation, but I always wondered, is it global (for want of a better word)?

By global I mean if I have two methods, A and B, both with mysql queries, will running it in A stop any queries/connections that may transpire in B? I'm looking at it as though Javascript wherein code is, essentially, executed in literal order. I'm guessing it would only close the connections in A, but, like return, it may only cause crazy things to happen in the function and only in that function.

Muddy_Funster · December 30, 2011

I'm trying to see if I can work out a way of manipulating some arrays to fix it so only one query need be run for the folder contents, as for the mysql_close() it's not what you think. It doesnt terminate a query, it closes the current connection to the database. so long as you check for an existing connection and create one if it's not there before each query / function you'll be fine.

jamesjmann · December 30, 2011

I'm trying to see if I can work out a way of manipulating some arrays to fix it so only one query need be run for the folder contents, as for the mysql_close() it's not what you think. It doesnt terminate a query, it closes the current connection to the database. so long as you check for an existing connection and create one if it's not there before each query / function you'll be fine.

Ok, yeah I was thinking it would terminate the query and as I have so many that would be a catastrophe lol. But I'm confused...are you tweaking the original code you gave me or trying to come up with a way to solve the "reverse order breadcrumb trail"? I could care less for a tweaked version of the latter, although I appreciate your efforts on the matter. I'm sure an improved version with no loop would be ideal, but it's crucial I come up with a method that can get all ids of nested albums (rather than the parent albums). It's like, almost completely integral in making my application complete. Like without it, I'd probably have to kill myself in defeat lol.

Muddy_Funster · December 30, 2011

I was trying to work out a way to run the reverse tree lookup with only one query, I got code that havs managed to break out each distinct level of the tree, but can't get my head around how to have them recursivly relate to level above with a direct refference.

It's going to take someoone who is much better at contitional array mannipulation than I am to sort that (fotunately, there are many of them on this forum so hope is not lost).

kicken · December 30, 2011

The only way you'll get your tree structure (reverse breadcrumb) with your current db structure (without a multiple-select loop) is to select everything from your albums table and build the tree in PHP. You can have a second query which pulls the count of photos in each album and merge that into the tree while you build it.

//Get photo count
$sql = '
SELECT
album.id as album_id,
COUNT(photo.id) as cnt
FROM kill4silence_photos_albums album
LEFT JOIN kill4silence_photos_photos photos ON album.id=photos.album_id AND photo.visible=1
GROUP BY
album.id
';

$res = mysql_query($sql);

$photoCounts=array();
while ($row=mysql_fetch_assoc($res)){
$photoCounts[$row['album_id']] = $row['cnt'];
}


$sql = '
SELECT
id,
parent_id,
name,
description
FROM kill4silence_photos_albums
ORDER BY
parent_id
';

$res = mysql_query($sql);

$albumTree = array();
$albumMap = array();
while ($row=mysql_fetch_assoc($res)){
$id = $row['id'];
$parent = $row['parent_id'];

$row['children']=array();
if (isset($photoCounts[$id])){
	$row['photoCount'] = $photoCounts[$id];
}

if ($parent != 0 && isset($albumMap[$parent])){
	$albumMap[$parent]['children'][] =& $row;
}
else if ($parent == 0){
	$albumTree[] =& $row;
}

$albumMap[$id] =& $row;
unset($row);
}

You should be running mysql_close at the end of every query, not at the end of the script.

If your going to run mysql_close at all, only run it at the end of the script. Closing the connection after every query is wasteful and only going to make the script even less efficient.

laffin · December 31, 2011

Breadcrumbs are easy to do, but the systems implemented can be a pain, if you store breadcrumbs in the db, this makes easy retrieval but a pain to update. I had a similar situation, what I opted for was to build dynamically as you did earlier, but used a cache file as well. This proved to be great on a system where I rarely changed the categories, and I could refresh the cache file anytime. So using breadcrumbs in the DB isn't the only solution.

And it's far easier to deal with than a complicated update if you decide to move one category to another.

jamesjmann · December 31, 2011

If you don't mind me asking, can you explain what the above does? I'm not too familiar with LEFT JOINS and the code is confusing the hell out of me lol.From what I've gathered, you constructed a tree-like array of all the albums. And um, yeah thats pretty much all I gathered lol.

jamesjmann · December 31, 2011

I was also thinking maybe I could just add an extra field to the albums table andwhere the field would hold a value of either 0 or 1, where 1 would signify that it has nested albums inside. In which case then itd be easier to loop through but assuming we dont want a loop that probably wouldnt be best.

And in regards to mysql_close, i read on php.net that your supposed to run it at the end of your script not each query. Im thinking that everytime you run it your creating more work for the server so the less its the better.

laffin · December 31, 2011

Here let me help you with some sample code.

<?php

// Following Section builds a simple database, with sqlite

if(!$db=sqlite_open(':memory:'))
  die('DB Open failure');
sqlite_exec('CREATE TABLE category(id INTEGER PRIMARY KEY, Name TEXT, Parent Integer)',$db);

$data =array(
  'Family/Events/Birthdays',
  'Family/Events/Holidays/Halloween',
  'Family/Events/Holidays/Thanksgiving',
  'Family/Events/Holidays/Christmas',
  'Family/Relatives',
  'Friends/Family',
  'Friends/Events'
);
  

foreach($data as $tree)
{
  $cats=explode('/',$tree);
  $Parent=0;
  foreach($cats as $cat)
  {
    $res=sqlite_query($db,'SELECT id FROM category WHERE Name=\''. sqlite_escape_string($cat) .'\' AND Parent='. $Parent);
    if(sqlite_num_rows($res))
    {
       $Parent=sqlite_fetch_single($res);
    } else {
      sqlite_exec($db,$sql='INSERT INTO category (Name,Parent) VALUES (\''. sqlite_escape_string($cat) .'\','. $Parent .')',$err) or die("<br />$err<br />$sql");
      $Parent=sqlite_last_insert_rowid($db);
    }
  }
}

// Following section retrieves all categegories into an array to build our breadcrumb info

$res=sqlite_query($db,'SELECT * FROM category');
$cats=array();
while($row=sqlite_fetch_array($res,SQLITE_NUM))
{
  echo print_r($row,true).'<br />'.PHP_EOL;
  $cats[$row[0]] = array($row[1],$row[2]);
}

// Following Section builds the the links from node to node info (This is main part you want to understand)
$breadcrumbs=array();
foreach($cats as $idx=>$cat)
{
  $bc=array();
  $pcat=$cat;
  $ok=true;
  do
  {
    $bc[]=$pcat;
    if($pcat[1])
      $pcat=$cats[$pcat[1]];
    else
      $ok=FALSE;
  } while($ok);
  $breadcrumbs[$idx]=array_reverse($bc);
}

// Just display results
foreach($breadcrumbs as $bc)
{
  $bcc = count($bc);
  for($i=0;$i<$bcc;$i++)
  {
    echo $bc[$i][0];
    if(($i+1)<$bcc) echo ' -> ';
  }
  echo '<br />'.PHP_EOL;
}


?>

After the tree is built use a simple serialize/unserialize to create/fetch the file cache. saving your queries and having the breadcrumb info on hand. I would put this routine on your update/add/delete functions.

Well good luck on yer project

kicken · December 31, 2011

I'm not too familiar with LEFT JOINS and the code is confusing the hell out of me lol.

JOIN syntax

FROM kill4silence_photos_albums album
LEFT JOIN kill4silence_photos_photos photos ON album.id=photos.album_id AND photo.visible=1

In that part, kill4silence_photos_albums is considered to be the left table, and kill4silence_photos_photos is the right. The condition after the ON is the join condition.

When you do a LEFT join, then the database takes all matching rows from the left table, and attempts to find matching rows in the right table based on the join condition. If it finds no matching rows, then it uses an implicit all-null row to match against.

Essentially, that means you will always get the full result of rows from the left table (all the albums). For each album it will find any photos belonging to that album. if there are none, it just uses a NULL row to fill in the blanks.

Then the GROUP BY clauses tells it to group the results by each unique album id, and the COUNT() function will give a count of all the photo id that are non-null. For an album that has photos, this will be the number of photos matched. For an album that has no photos, it will be 0.

Sign In

Loop Through Nested Results?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information