INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Creating three-tier parent-child display with special fields

Creating three-tier parent-child display with special fields

Creating three-tier parent-child display with special fields

(OP)
Good afternoon,

Firstly thanks to Justin and BillyRay for all your help. I soft-launched my site the other day and have a few people testing it. It's working well, thanks to your input.

One thing people are asking for is a feature I used to have on my old website, and that is a list of every place we have visited, broken down by country, region and location.

Now I've already captured this data via special fields, which work on a three-level hierarchy: Country - Region - Location. For example: UK - South Coast- Brighton.

How could I display an alphabetical list, with each country broken out by region, and with each region broken out by location? The result would look like this, where every option is a clickable link:

Antigua
- East Coast
---Green Island

UK
-East Coast
---Orford
---Aldeborough
---Clacton
-South Coast
---Brighton
---Dover

When clicking on any link it will produce a search result page with all articles tagged with that field name. I think I'd want to avoid it taking you directly to the post as some locations have more than one post assigned to them.

I'm struggling to get my head around parent-child relationships with special fields in Wordpress.

Any help muchos appreciated.

RE: Creating three-tier parent-child display with special fields


Just out of interest, would a geo tagging plugin be of help to you here, as you could tag posts with the location (city, country, etc), and potentially locate them on a map as well.

This would avoid you having to deal with creating any data structures, etc. I found these 4, although I'm not sure if they're what you're after or not...

http://www.wpgeo.com/

http://georss.org/geopress/

http://wordpress.org/extend/plugins/google-maps-advanced/

http://plugins.trac.wordpress.org/wiki/GeoPlugin

Dan

 

Coedit Limited - Delivering standards compliant, accessible web solutions

Dan's Page @ Code Couch: http://www.codecouch.com/dan/

Code Couch Tech Snippets & Info: http://www.codecouch.com/
 

RE: Creating three-tier parent-child display with special fields

(OP)
Hi Dan,

I do use geo-tagging for my images but was unaware that you could do it to posts, so these are interesting and something I might add (more toys to waste hours over!).

I do have issue with these though. The feedback I received was that users want a list that they can scan quickly by recognisable name. For example one user wanted to know about the marina in Marmaris. Using this list idea he could quickly scroll to Turkey and then Marmaris. If he had to do it by map he'd have to know where to click on the map, as well as scrolling to find it in the first place. Also we have multiple entries for Marmaris, which would clutter the map.

That's my initial reaction to these plug-ins. If you know any different then please let me know.

RE: Creating three-tier parent-child display with special fields

Hi jamie

from recollection you are not storing any relationship between each of the special fields, they are each individual data points.  thus there is no inherent parent->child->grandchild relationship that the code can infer, which would make it easy to 'walk the tree'.

i suspect what you will have to do is create a programmatic solution for the tree hierarchy.  As the first stage of this, can you add this code to the class as a new method.  and then add this tag to your template somewhere.  then post back the output. the idea is to make sure that the shape of the array is sensible. then we can work on the output.

CODE --> tag

$extraCommentFields->hierarchicalLists();

CODE --> method

public function hierarchicalList(){
        //first get all the results
        global $wpdb;
        $array = array();
        $data = $wpdb->get_col("Select extraFields from {$this->table} order by extraFields ASC");
        foreach ($data as $d){
            $_d = unserialize($d);
            if (!isset($array[$_d['country']])){
                $array[$_d['country']] = array();
            }
            if (!isset($array[$_d['country']][$_d['region']])){
                $array[$_d['country']][$_d['region']] = array();
            }
            if (!isset($array[$_d['country']][$_d['region']][$_d['location']])){
                $array[$_d['country']][$_d['region']][$_d['location']] = '';    
            }
        }
        echo "<pre>". print_r($array, true) . "</pre>";
        
    }

[code]
"select  

RE: Creating three-tier parent-child display with special fields

(OP)
Hi Justin,

OK, hand-holding time. I'm unsure how or where to place the method. What I've done is create a page template and within the content of the page I've added the following, verbatim:

CODE

<?php $extraCommentFields->hierarchicalLists(); ?>
                            
                            <?php public function hierarchicalList(){
                            //first get all the results
                            global $wpdb;
                            $array = array();
                            $data = $wpdb->get_col("Select extraFields from {$this->table} order by extraFields ASC");
                            foreach ($data as $d){
                                $_d = unserialize($d);
                                if (!isset($array[$_d['country']])){
                                    $array[$_d['country']] = array();
                                }
                                if (!isset($array[$_d['country']][$_d['region']])){
                                    $array[$_d['country']][$_d['region']] = array();
                                }
                                if (!isset($array[$_d['country']][$_d['region']][$_d['location']])){
                                    $array[$_d['country']][$_d['region']][$_d['location']] = '';    
                                }
                            }
                            echo "<pre>". print_r($array, true) . "</pre>";
                            
                        }
                            
                            ?>

What am I doing wrong? It's producing a

Quote:

Parse error: syntax error, unexpected T_PUBLIC in E:\...

RE: Creating three-tier parent-child display with special fields

nope.  in the template you just have the tag.  that's all.

you put the public function (which is called a method) in the class that we wrote for the extra fields in comments. on my installation this would mean editing wp-contents/plugins/extraFields.php and pasting the text before the end of the class.

 

RE: Creating three-tier parent-child display with special fields

(OP)
Good afternoon Justin,

Apologies for the delay. Very busy now that I have 'officially' launched. Wish I'd done this before going public though as it's getting a little harder to test!

What I've tried to do is add the above public function on line 252. I then added the tag in my template. I deactivated the plug-in and reactivated it. It is coming back with

Quote:

Fatal error: Call to undefined method extraComments::hierarchicalLists() in E:\domains\f\followtheboat.com\user\htdocs\wp-content\themes\followtheboat\gallery.php on line 32

Where line 32 is the tag. My code for my template page looks something like this:

CODE

<div class="post-gallery" id="post-<?php the_ID(); ?>">
                    <div class="posttop-gallery">
                        

                        <h2><?php the_title(); ?></h2>
                        
                        <div class="headingline-gallery">
                          <div class="previoustop" title="previous post"><?php previous_post('%', '&laquo;&laquo; ', 'no'); ?></div>                        
                          <div class="nexttop" title="next post"><?php next_post('%', ' &raquo;&raquo;', 'no'); ?></div>
                        </div>
                    </div>
                    <div id="entry-gallery">
                        <?php the_content('<p class="serif">Read the rest of this entry &raquo;</p>'); ?>    
                        <?php link_pages('<p><strong>Pages:</strong> ', '</p>', 'number'); ?>                    
                        <br />

                            <?php $extraCommentFields->hierarchicalLists(); ?>
        
        
                        <?php if (is_page('89')): ?>
                         <div class="whatnow">
                            <div class="previous"></div>
                            <div class="com2"><a class="nav" href="#comments-header">Your Comments On Our Pics</a></div>
                            <div class="next">    </div>
                        </div>
                        <div class="comments"><?php comments_template(); ?></div>

                        <?php endif; ?>
                    

RE: Creating three-tier parent-child display with special fields

sorry. it should be singular.

CODE

<?php $extraCommentFields->hierarchicalLists(); ?>
 

RE: Creating three-tier parent-child display with special fields

(OP)
OK, got it. In fact both had to be singular

CODE

<?php $extraCommentFields->hierarchicalList(); ?>

See the result here:

http://www.followtheboat.com/index.php/delete-me/

It appears to be calling the location from the comments table. I recognise some of my test locations for test comments. Remember this location is different to the special field location. At the moment we are looking at the extra comments field plug-in, but ultimately i want to be interrogating the other three special fields (I keep calling them special fields - I actually mean custom fields).

RE: Creating three-tier parent-child display with special fields

that is indicative that you have not got all the data split out between country->region->location.  but just have them as location.

any thoughts?

one thought from me: my code deals only with extra fields in comments.  is this what you are trying to retrieve, or are you looking for extra fields stored in the posts instead?

 

RE: Creating three-tier parent-child display with special fields

(OP)
The latter, Justin. You know the sidebar in a post that displays the log book? All of those are custom fields and then underneath that are three further custom fields (country/region/location). Do you remember helping me display this data? The call in the sidebar is:

CODE

<?php /*echo "<pre>" . print_r($GLOBALS, true) . "</pre>"; */$GLOBALS['customFieldsFilter']->displayMetaText(); ?>

You created a plug-in called 'custom field search', allowing us to display a search-results page of all articles that fell into any of these custom fields.

RE: Creating three-tier parent-child display with special fields

Hmm. Ok will ponder. I have a horrible feeling that the solution won't be pretty.   

RE: Creating three-tier parent-child display with special fields

(OP)
Justin, I don't mind how pretty the solution is if you don't. If there is a solution though I would extremely grateful. I'd almost be tempted to sail from Turkey to France to buy you a beer or two for your troubles ;)

RE: Creating three-tier parent-child display with special fields

i fear that this is going to be very processor intensive.  it would probably be better to cache the results. but let's worry about that later...
it's also possible that we could do this in php rather than in mysql (ie. one query rather than a cartesian product).  but again, let's see how bad the processor drain is.

can you put this class inside your theme's functions.php file. and then use this tag to display the output.  once we're sure that the shape of the array is correct, we can worry about the output

CODE --> tag

$crlHierarchy->displayResults();

CODE --> class

<?php

class crlHierarchy{
    public function __construct(){
        //establish the order of the hierarchy
        //get the countries
        $this->places = array();
        $countries = $this->getCountries;
        foreach ($countries as $key=>$country){
            $this->place[$key]['country'] = $country;
            $regions = $this->getRegions($country);
            foreach ($regions as $rkey=>$region){
                $this->places[$key]['regions'][$rkey]=$region;
                $locations = getLocations($country, $region);
                foreach ($locations as $lKey=>$location){
                    $this->places[$key]['regions'][$rkey]['locations'][] = $location;
                }
            }
        }
        
    }
    public function displayResults(){
        echo "<pre>".print_r($this->places, true) . "</pre>";
    }
    
    private function getCountries(){
        global $wpdb;
        $sql = $wpdb->prepare(" select lower(meta_value) as meta_value from $wpdb->postmeta where lower(meta_key)=%s group by meta_value order by meta_value asc", strtolower($value));
        return $wpdb->get_col($sql);
    }
    private function getRegions($country){
        global $wpdb;
        $sql = $wpdb->prepare("
        select
            lower(meta_value) as meta_value
        from $wpdb->postmeta t1
        join
            $wpdb->postmeta t2
            on (t1.post_id = t2.post_id)
        where
            lower(t1.meta_key)=%s
            and
            (    lower(t2.meta_key=%s)
                and
                lower(t2.meta_value=%s)
            )
        group by
            t1.meta_value
        order by
            t1.meta_value asc",
        'region',
        'country',
        $country);
        return $wpdb->get_col($sql);
    }
    
    private function getLocations($country, $region){
        global $wpdb;
        $sql = $wpdb->prepare("
        select
            lower(meta_value) as meta_value
        from $wpdb->postmeta t1
        join
            $wpdb->postmeta t2,
            $wpdb->postmeta t3
            on (t1.post_id = t2.post_id = t3.post_id)
        where
            lower(t1.meta_key)=%s
            and
            (    lower(t2.meta_key=%s)
                and
                lower(t2.meta_value=%s)
                and (
                    lower(t3.meta_key=%s)
                    and
                    lower(t3.meta_value=%s)
                    )
            )
        group by
            t1.meta_value
        order by
            t1.meta_value asc",
        'location',
        'region',
        $region,
        'country',
        $country
        );
        return $wpdb->get_col($sql);
    }
}
$crlHierarchy = new crlHierarchy();

?>

RE: Creating three-tier parent-child display with special fields

(OP)
Hi Justin - I don't have a functions.php file for my theme because, well, it's MY theme and not one I downloaded. Should I just put this code into a new file and call it functions.php, saved within my theme or should I just append it to my main wordpress functions.php file?

I didn't want to experiment because the site is live and my connection is slow (ie if anything unexpected happens it will take a few moments to correct it).

RE: Creating three-tier parent-child display with special fields

just plonk it in single.php or something then.  the idea is to get an output to tell the shape of the array. you only need to run it once.

or if you would rather, change the displayresults() method to the following

CODE


   public function displayResults(){
       file_put_contents( 'debug.txt', print_r($this->places, true));
    }
and then post the contents of debug.txt

this will stop the page output from being interfered with (assuming i have made no errors in the file...)

i am concerned particularly about the sql queries (which are quite complex.  so if you get no output in debug.php please post the output of the php error logs instead.
 

RE: Creating three-tier parent-child display with special fields

(OP)

Quote:

Warning: Invalid argument supplied for foreach() in E:\domains\f\followtheboat.com\user\htdocs\wp-content\themes\followtheboat\gallery.php on line 39

That line is

CODE

  foreach ($countries as $key=>$country){

I thought it was because $countries was not capitalised, so I changed that to $Countries which now returns the error:

CODE

Warning: Invalid argument supplied for foreach() in E:\domains\f\followtheboat.com\user\htdocs\wp-content\themes\followtheboat\gallery.php on line 39

Array
(
)

RE: Creating three-tier parent-child display with special fields

it's very difficult for me to debug this without data to test with.  is there anyway you can send me a sql dump of postmeta?

RE: Creating three-tier parent-child display with special fields

some minor debugging.  could you use this

CODE

<?php

class crlHierarchy{
    public function __construct(){
        //establish the order of the hierarchy
        //get the countries
        $this->places = array();
        $countries = $this->getCountries;
        foreach ($countries as $key=>$country){
            $this->place[$key]['country'] = $country;
            $regions = $this->getRegions($country);
            foreach ($regions as $rkey=>$region){
                $this->places[$key]['regions'][$rkey]=$region;
                $locations = getLocations($country, $region);
                foreach ($locations as $lKey=>$location){
                    $this->places[$key]['regions'][$rkey]['locations'][] = $location;
                }
            }
        }
        
    }
    
    public function display(){
        $output ="<ul id=\"countries\">";
        foreach ($this->places as $key=>$country){
            $output .= "<li class=\"country\">$country[country]";
            $output .= "<ul class=\"regions\">";
            foreach ($country['regions'] as $rkey=>$region){
                $output .= "<li class=\"region\">$region[region]";
                $output .= "<ul class=\"locations\">";
                foreach ($region['regions'] as $lkey=>$location){
                    $output .= "<li class=\"location\">$location</li>";
                }
                $output .= "</ul>";
                $output .= "</li>";
            }
            $output .= "</ul>";
            $output .= "</li>";
        }
        $output .= "</ul>";
        echo $output;
    }
    public function displayResults(){
        echo "<pre>".print_r($this->places, true) . "</pre>";
    }
    
    private function getCountries(){
        global $wpdb;
        $sql = $wpdb->prepare(" select lower(meta_value) as meta_value from {$wpdb->postmeta} where lower(meta_key)=%s group by meta_value order by meta_value asc", strtolower($value));
        return $wpdb->get_col($sql);
    }
    private function getRegions($country){
        global $wpdb;
        $sql = $wpdb->prepare("
        select
            lower(t1.meta_value) as meta_value
        from {$wpdb->postmeta} t1
        join
            {$wpdb->postmeta} t2
            on (t1.post_id = t2.post_id)
        where
            lower(t1.meta_key)=%s
            and
            (    lower(t2.meta_key=%s)
                and
                lower(t2.meta_value=%s)
            )
        group by
            t1.meta_value
        order by
            t1.meta_value asc",
        'region',
        'country',
        $country);
        return $wpdb->get_col($sql);
    }
    
    private function getLocations($country, $region){
        global $wpdb;
        $sql = $wpdb->prepare("
        select
            lower(t1.meta_value) as meta_value
        from {$wpdb->postmeta} t1
        join
            {$wpdb->postmeta} t2,
            {$wpdb->postmeta} t3
            on (t1.post_id = t2.post_id = t3.post_id)
        where
            lower(t1.meta_key)=%s
            and
            (    lower(t2.meta_key=%s)
                and
                lower(t2.meta_value=%s)
                and (
                    lower(t3.meta_key=%s)
                    and
                    lower(t3.meta_value=%s)
                    )
            )
        group by
            t1.meta_value
        order by
            t1.meta_value asc",
        'location',
        'region',
        $region,
        'country',
        $country
        );
        return $wpdb->get_col($sql);
    }
}
$crlHierarchy = new crlHierarchy();

?>

of course, use the alternative display method if you need.

RE: Creating three-tier parent-child display with special fields

(OP)
Still coming back with invalid argument. It's displayed the page ok but the pink box with the array is erroring. See here:

http://www.followtheboat.com/index.php/delete-me/

I'm putting this method, followed by the tag, within the content of a page template.

If I use

CODE

<?php public function displayResults(){
       file_put_contents( 'debug.txt', print_r($this->places, true));
    } ?>
then the page is not displaying and it just comes back with

Quote:

Parse error: syntax error, unexpected T_PUBLIC in E:\domains\f\followtheboat.com\user\htdocs\wp-content\themes\followtheboat\gallery.php on line 146
.
How do I send you a sql dump of postmeta?

 

RE: Creating three-tier parent-child display with special fields

no no ! the replacement method (public function ...) you should copy and paste over the equivalent in the class.

if you upload the dump to my site (www.adieandco.com/upload.php) that should work.

RE: Creating three-tier parent-child display with special fields

(OP)
I'm sorry, Justin, I don't understand. I have placed all of the code from post 11.30 within my page template. I tried it followed by

CODE

<?php public function displayResults(){
       file_put_contents( 'debug.txt', print_r($this->places, true));
    } ?>

and I've tried it without anything following it. What am I doing wrong?

RE: Creating three-tier parent-child display with special fields

methods must live inside classes.  have a read up on objects in the php manual.

put simply

this is an example of a function and a class
note that the function is OUTSIDE the class

CODE

class foo{
 
}
function bar(){
}

here is an example of a function in a class. this is called a method

CODE

class foo{
 public function foo(){
 }
}
to use the file debugging method you need to replace the existing displayResults() method with the new displayResults method.

RE: Creating three-tier parent-child display with special fields

(OP)
I'm gonna sleep on this and return to it tomorrow, Justin. We had gale-force winds ripping the Turkish coastline apart last night. I didn't get much sleep, worried that the boat and me would wake up in a different location. I am knackered!

I'm sure it will all be clearer in the morning. Thanks for your help so far. Have a good Friday evening.

RE: Creating three-tier parent-child display with special fields

(OP)
I'm gonna sleep on this and return to it tomorrow, Justin. We had gale-force winds ripping the Turkish coastline apart last night. I didn't get much sleep, worried that the boat and me would wake up in a different location. I am knackered!

I'm sure it will all be clearer in the morning. Thanks for your help so far. Have a good Friday evening.

RE: Creating three-tier parent-child display with special fields

we're having 170km/hr winds here.  i guess they're coming up from your neck of the woods.  knocked over some big pines in our garden, ripped up a swimming pool cover and decimated the tiles on our roof so far!  but in a bizarre departure from the norm, the telephone and electricity lines are holding up.   

RE: Creating three-tier parent-child display with special fields

(OP)
Good morning, Justin. Took a day off yesterday - I was busily posting my first article for 2009 - so I'm returning to this today. How's things in France? Been hearing through the grapevine just how bad it is in your area. It sounds horrendous.

Whilst I look at the above, take a look at these clips:

http://www.followtheboat.com/index.php/2009/01/24/a-tale-of-two-cities/

 

RE: Creating three-tier parent-child display with special fields

(OP)
OK, so I've revisited this and gone through it. I understand the concept of methods/functions/class (I hope). Working through the thread I see that the post at 11.30 displays the necessary function within the class, so I am just pasting this code in its entirety. It is then followed by the display tag as per post 7:14.

It is tripping up

Quote:

Warning: Invalid argument supplied for foreach()

on this line

CODE

foreach ($countries as $key=>$country){
which is the public function construct.

RE: Creating three-tier parent-child display with special fields

that means that for some reason the $countries variable is not an array.

this might be because i failed to give a value for $value ...

try this method instead

CODE

    private function getCountries(){
        global $wpdb;
        $sql = $wpdb->prepare(" select lower(meta_value) as meta_value from {$wpdb->postmeta} where lower(meta_key)=%s group by meta_value order by meta_value asc",'country');
        return $wpdb->get_col($sql);
    }

RE: Creating three-tier parent-child display with special fields

(OP)
I've tried that but that doesn't make sense as the public function construct happens before the getCountries method, so it's still tripping up on the construct.

RE: Creating three-tier parent-child display with special fields

this is getting a bit tortuous!  is there any chance that you can send me a sql dump of your postmeta table and i can work form that?

RE: Creating three-tier parent-child display with special fields

(OP)
Yes, it is and it's my fault - I was being an idiot over something that I shan't bother explaining. Apologies.

I have an issue downloading a dump of just that table through myphpadmin so instead I have used the back-up plug-in in wordpress and have sent a copy of the complete db (1.6mb) to you. Thanks Justin.

RE: Creating three-tier parent-child display with special fields

will take a butchers later.  am currently (virtually) on the roof trying to fix the tiles that have come loose.  i could have done with some sail making skills earlier whilst fixing the pool cover (duct tape ...).  and i am now rather wishing that the municipality would finish lunch and come and fix the water supply (we have had none for 24 hours)...

RE: Creating three-tier parent-child display with special fields

(OP)
Sounds like a 'mare. Do the French council workers actually work on a Sunday??? I've heard of the west coast of Europe getting battered with 9m high waves... it's hitting the whole of the Med too.

Good luck with it.

RE: Creating three-tier parent-child display with special fields

the word 'work' is suspect as to every day of the week.  I have heard mates of mine, French engineers, say that they only have to work 35 hours a week and that this is equivalent to 48 hours elsewhere in europe because they are so much more efficient.  Straight faced too.

RE: Creating three-tier parent-child display with special fields

(OP)
LOL. This does not surprise me.

RE: Creating three-tier parent-child display with special fields

i've got the code up and working on your database. but the performance really really sucks.  upwards of minutes per run.

i'm pondering how to improve it without restructuring your pages.

RE: Creating three-tier parent-child display with special fields

(OP)
Hmmm. What kind of restructure would be required for the pages? It sounds long-winded...

You also mentioned a possibility of doing this with PHP instead. I didn't understand what the difference would be. Surely the database has to be interrogated whatever the solution, no?

RE: Creating three-tier parent-child display with special fields

the problem is that there is no innate relationship between custom fields in a wp database.  but you are wanting not only to infer a relationship but also a hierarchy.  
the method i came up with joins the table to itself three times over to get the location data - but this is really really processor intensive.
the alternate method is to get all the data into php and manipulate it there as a series of single and multi-dimensional arrays.  this is possible but, again, very processor intensive (less so than mysql only method, however).
i'm toying with a third solution which is to create a temporary table and insert data into that, then query the temporary table.  i have a feeling that this is likely to be the best method.

RE: Creating three-tier parent-child display with special fields

(OP)
OK.  Is there a way of adding in that relationship between the fields? After all the three fields always appear next to each other, in the same order. I don't know, you know best. Just bear in mind, however, that some entries only have two of those three fields with a value (Atlantic Ocean log entries, for example, don't have a location or region value).

I'm still battling gusts - the boat has moved about 10ft off the pontoon so it's difficult to get ashore. Should calm down in a bit. Thanks for your time. Keep me posted, Justin.

RE: Creating three-tier parent-child display with special fields

this has been a right pain in the a***.  trying to impose a relationship where there is none is not, in itself, difficult but it is very time consuming for the processor and the easy routes murdered by processes... (one method took over 20 minutes).

i have ended up going with a hybrid solution, combining a temporary table with a file-system cacheing mechanism. i think I have it down to about 1.5 secs on my machine.  the code can be used as is below.

to display the hierarchy in your page/post use this tag

CODE --> tag

$crlHierarchy->displayTree();
note that this will not work in the sidebar/header/footer.  in order to use it there you will have to use this tag instead

CODE

$GLOBALS[crlHierarchy]->displayTree();

the tree uses a number of classes to make it easy for you to style
this is the hierarchy of classes

CODE --> classes

<ul class="countries">
  <li class="country">
    <ul class="regions">
      <li class="region">
        <ul class="locations">
          <li class="location">

within each class the link is an unstyle anchor.  you can style it as you wish by using declarations as usual like this

CODE --> css

ul.countries li.country a {color=red;}

do read up about specificity.  This is as good a write up as I have seen on the subject.

each item in the hierarchy is converted to a link that will be interpreted by the code we wrote the other week for searching custom meta fields.  So nothing more to do to make the results searchable.

BUT ... there is a but...  if a location exists in more than one region, or a region exists in more than one country then clicking on the region or the location will bring up a list of all posts matching that region/location, irrespective of whether the location is actually located within the region of the original hierarchical position.

for example
UK->south coast->bournemouth
Spain->south coast->Malaga

clicking on south coast will bring up a list of all south coast references whether they be in the UK or Spain.

I can see no easy way around this other than rewriting that plugin from the other week to do the searching.  and I don't have time to do that at the moment.  and i'd add that it's really not a straightforward fix.

so ... the code for the plugin is here.  you do not have to add it as a plugin but there is no harm in doing so.  copy it to wp-content/plugins and then enable in the admin panel.

then just add the tag in whichever template you want the hierarchy to appear.  i think it's a bit big to enable as a widget but I can widgetise it if you need (makes it available for the sidebar).  I have your email address so if you want a nicely formatted version of the code then i shall send it through.  For the other readers, this code uses some techniques that are useful both for cacheing computationally expensive mysql queries and for imposing structure on essentially free text data

CODE --> thecode.php

<?php
/*
Plugin Name: 3-tier Hierarchy
Plugin URI: rathercurious.net
Description: Akismet checks your comments against the Akismet web service to see if they look like spam or not. You need a <a href="http://wordpress.com/api-keys/">WordPress.com API key</a> to use it. You can review the spam it catches under "Comments." To show off your Akismet stats just put <code>&lt;?php akismet_counter(); ?&gt;</code> in your template. See also: <a href="http://wordpress.org/extend/plugins/stats/">WP Stats plugin</a>.
Version: 0.0.1
Author: Justin Adie
Author URI: rathercurious.net
*/

class crlHierarchy{
    
    private $flush = false; //set to true to flush the cache
    
    /**
     * constructor method to set variables and start the data gathering
     *
     * @return
     */
    public function __construct(){
        $this->url = $url = get_bloginfo('url');
        $dir = sys_get_temp_dir();
        $this->cache = realpath($dir . "/WP_FTP_CACHE.txt");
    }
    
    /**
     * method to gather the data and display the hierarchy as a multi LI tree
     * @return
     */
    public function displayTree(){
        $this->getOutput();
        echo $this->output;
    }
    
    /**
     * method to check the cache status and grab the output either from the cache or the internal methods
     * @return
     */
    private function getOutput(){
        global $wpdb;
        if (file_exists($this->cache)){
            if ($this->flush){
                unlink ($this->cache);
                $this->getData();
                $this->generateOutput();
                unset($this->rawResults);
                $this->cacheResults();
            } else {
                clearstatcache();
                $lastMod = fileatime($this->cache);
                
                //now get latest post mod time
                $query = "Select max(post_modified_gmt) from $wpdb->posts";
                $postLastMod = $wpdb->get_col($query);
                //convert to unix
                $postLastMod = strtotime($postLastMod . " GMT");
                //compare the two values
                if ($lastMod >= $postLastMod){
                    $this->output = file_get_contents($this->cache);
                } else {
                    $this->getData();
                    $this->generateOutput();
                    unset($this->rawResults);
                    $this->cacheResults();
                }
            }
                
        } else {
            $this->getData();
            $this->generateOutput();
            unset($this->rawResults);
            $this->cacheResults();
        }
    }
    
    /**
     * method to cache the html of the tree in the filesystem
     * @return
     */
    private function cacheResults(){
        file_put_contents($this->cache, $this->output);
    }
    
    
    /**
     * method to transform arguments into a clickable link that can be used with the existing search facility back end
     * @return
     * @param string $text
     * @param string $country
     * @param string $region[optional]
     * @param string $location[optional]
     */
    private function makeLink($text, $country, $region=null, $location=null){
        if (is_null($region)){
            $country = urlencode($country);
            return "<a href=\"{$this->url}?metaFilterKey=country&s=$country\">$text</a>";
        } else if (is_null($location)) {
            $region = urlencode($region);
            return "<a href=\"{$this->url}?metaFilterKey=region&s=$region\">$text</a>";
        } else {
            $location = urlencode($location);
            return "<a href=\"{$this->url}?metaFilterKey=location&s=$location\">$text</a>";
        }
    }
    
    /**
     * method to create a temporary table, write data to it and then retrieve the data hierarchically
     *
     * @return
     */
    private function generateOutput(){
        global $wpdb;
        $query = <<<SQL
CREATE TABLE  `tmpLoc` (
 `country` VARCHAR( 255 ) NOT NULL ,
 `region` VARCHAR( 255 ) NULL ,
 `location` VARCHAR( 255 ) NULL
) CHARACTER SET {$wpdb->charset} COLLATE {$wpdb->charset};
SQL;
        $wpdb->query($query);
        /*
        $query = <<<SQL
create UNIQUE index
 conjunction
 on
 tmpLoc
 (country, region, location)
SQL;
        $wpdb->query($query); */
        $_q = ';
        foreach ($this->rawResults as $data){
            $country = (empty($data['country']) ? 'null' : $wpdb->prepare("%s", $data['country']));
            $region = (empty($data['region']) ? 'null' : $wpdb->prepare("%s", $data['region']));
            $location = (empty($data['location']) ? 'null' : $wpdb->prepare("%s", $data['location']));
            $_q[] = "($country, $region, $location)";
        }
        $query = "insert into tmpLoc (country, region, location) values " . implode (',', $_q);
        $wpdb->query($query);
        
        //manage output now
        //get countries
        $countries = $wpdb->get_col("select country from tmpLoc group by country order by country asc");
        $output = <<<HTML

<ul class="countries">

HTML;
        foreach ($countries as $country){
            $_country = trim($country) ==' ? "No country specified" : trim($country);
            $output .= <<<HTML

    <li class="country">
        {$this->makeLink($_country, $_country)}

HTML;
            //get regions for this country
            $query = $wpdb->prepare("select region from tmpLoc where country=%s group by region order by region asc", $country);
            $regions = $wpdb->get_col($query);
            if (empty($regions)){
                $output .= <<<HTML

    </li><!--end of country li-->

HTML;
            } else {
                //iterate the regions
                /********* REGIONS *********/
                $output .= <<<HTML

        <ul class="regions">

HTML;
                foreach ($regions as $region){
                    $output .= <<<HTML

            <li class="region">
                {$this->makeLink($region, $_country, $region)}

HTML;
                    /********* LOCATIONS *********/
                    $query = $wpdb->prepare("select location from tmpLoc where country=%s and region=%s group by location order by region asc", $country, $region);
                    $locations = $wpdb->get_col($query);
                    if (empty($locations)){
                        $output .= <<<HTML

            </li> <!-- end of region li -->

HTML;
                    } else {
                        $output .= <<<HTML

                <ul class="locations">

HTML;
                        foreach ($locations as $location){
                            $output .= <<<HTML

                    <li class="location">
                        {$this->makeLink($location, $_country, $region, $location)}
                    </li>

HTML;
                        }
                        $output .= <<<HTML

                </ul><!-- close locations ul -->

HTML;
                    }
                    /********* END LOCATIONS *********/
                    $output .= <<<HTML

            </li><!--end region li-->

HTML;
                }
                $output .= <<<HTML

        </ul>

HTML;
                /********* END REGIONS *********/
            }
            $output .= <<<HTML

    </li><!--end country li -->

HTML;
        }
        $output .= <<<HTML

</ul>

HTML;
        $this->output = $output;
    }
    
    /**
     * method to query the postmeta table and return all the necessary data in an multidimensional array
     * @return
     */
    private function getData(){
        global $wpdb;
        $query = <<<SQL
SELECT post_id, meta_key, meta_value
FROM wp_postmeta
WHERE LOWER( meta_key )
IN (
'country',  'region',  'location'
)
ORDER BY LOWER( meta_key ) ASC , LOWER( meta_value ) ASC
SQL;
        $results = $wpdb->get_results($query, 'ARRAY_A');
        foreach ($results as $result){
            $type = strtolower($result['meta_key']);
            $this->rawResults[$result['post_id']][$type] = $result['meta_value'];
        }
    }
}
$crlHierarchy = new crlHierarchy();

?>

RE: Creating three-tier parent-child display with special fields

you asked in an earlier post whether

Quote (jamie):

Is there a way of adding in that relationship between the fields?

if you take a look at the postmeta table whether the custom fields are stored, you will see that each field is stored as a row in the table (not as a set of columns). the row consists of id, post_id, meta_key and meta_value. so each item is a unique entity of its own right and there is no interrelationship other than the post_id (which I have used as a step in the relationship creation).  

the better way to do this would be to plugin to the write panel and force the custom fields in the form, and then capture them expressly as a set of columns in a single row (viz the temporary table that i create in the second step of the process that I use).  but it's not at all straightforward to plugin to the write functionality of WP.  you basically have to write a new interface...

my way is an adequate hack for relatively static things like posts.  even for a high traffic site you're unlikely to get more than, say, 4 new posts a day (and thus 4 new hierarchy generations per day).  The generation is currently done when the hierarchy is actually requested so there is no implicit lag at runtime itself.  honestly, as a path of least resistance, I don't think this is too bad.

RE: Creating three-tier parent-child display with special fields

(OP)
OK, that sounds better. What about caching the page? To be honest I'm posting no more than one post a week, let alone 4 a day so caching wouldn't hurt. I know there is a plug-in for caching wordpress pages but I'm not sure you can just cache one page.  

RE: Creating three-tier parent-child display with special fields

take a look at the code. the hierarchical tree is already cached.  and the cache won't be defeated by cache-busting mechanisms etc.

until you are comfortable that it is working for you, you might want to set $flush to true.  that internally kills the cache each time.

the cacheing is done more efficiently than the well-known wp cacheing mechanism (which I'm thinking of rewriting).  go with this and give it a whirl.

RE: Creating three-tier parent-child display with special fields

oops.  get rid of the akismet stuff in the header of my plugin.  add your own description as you like.  but you do need the header in there otherwise WP will not pick it up as a plugin.

and the tag for use in a sidebar etc should have quotes

CODE

$GLOBALS['crlHierarchy']->displayTree();

RE: Creating three-tier parent-child display with special fields

(OP)
Roger. Let us know when you're done and ready to roll. I've got Liz returning from the UK this evening so I'll be offline in a couple of hours until tomorrow. Cheers.

RE: Creating three-tier parent-child display with special fields

Quote (jamie):


Roger. Let us know when you're done and ready to roll


ummm ... done hours ago. I posted the solution at 05h10 today

RE: Creating three-tier parent-child display with special fields

following a previous post when I said that it might be quite difficult to fix the custom meta stuff, i think that there might be a way to get this to work without too much hassle.

you would need to write a plugin to add some new fields to the post/page form.  you can do this with the add_meta_box() function in wp, via the dbx_post_advanced/dbx_page_advanced action hooks.

you would then capture the values of these fields (via another action hook) during the save process and write them directly to the database in a separate table.  

this might have several benefits:
1. simplifies the user interface for you so that these fields are automatically ready for you to complete each post.
2. simplifies the structure needed to grab your hierarchy
3. allows more granular searching via the custom meta search thing.

RE: Creating three-tier parent-child display with special fields

(OP)
That's strange, Justin. Your last few posts have only just appeared in this thread. Anyway, copying now and adding. I leave to pick liz up in an hour so I'll see what I can get done now.

RE: Creating three-tier parent-child display with special fields

(OP)
OK, I've done a straight copy and paste, no styling, which you can see here:

http://www.followtheboat.com/index.php/delete-me/

It's obviously picking up the tag but you can see in the source code that it only reads the very first ul, countries.

Is this because it lives within the WP Loop and is therefore trying to apply a custom field value that doesn't exist, since I've put this code in a page that doesn't have custom fields? Or is it because I haven't styled the uls?

Also I note that your code jumps from 'region' to 'regions' and 'location' to 'locations'. I haven't looked too closely but maybe you did this to label different classes differently?

RE: Creating three-tier parent-child display with special fields

(OP)
Sorry, missed your explanation of 'country' and 'countries'. Ignore that last comment about styling. I understand that bit now.

RE: Creating three-tier parent-child display with special fields

it should not live within the loop, no.  the tree is a static entity and live on its own.  i implemented it at the top of single.php as an example

CODE

<?php get_header(); ?>
<div id="wrap">
<?php
$crlHierarchy->displayTree();
?>
</div>

obviously you would not wish to do quite the same as this would interrupt your post!  but it gives an example.

using your database and your theme, the code works seamlessly (out of the box).  so there must be something you're doing slightly differently in your implementation. any thoughts?

RE: Creating three-tier parent-child display with special fields

during debugging MAKE SURE YOU SET $flush to TRUE!!!!!!

RE: Creating three-tier parent-child display with special fields

(OP)
Got it. I have to leave now so I'll return to this later (midnight-ish). Thank you Justin.

RE: Creating three-tier parent-child display with special fields

(OP)
Back again.

OK, I've stripped my template file right back and also got rid of the sidebar, and flush=true, so all I have loading up is this:

CODE

<?php
/*
Template Name: Gallery
*/
?>

<?php get_header(); ?>
<div id="wrap">                      
    <div id="content-container">    
        <div id="content">              
            <div class="post-container">
            Tree should appear here <?php $crlHierarchy->displayTree(); ?>
            </div>        
        <div style="float:left">
        <?php include('sidebar_nothing.php') ?>
        </div>
    </div>
    <div class="bgbottom"></div>
</div>
</div>
<?php get_footer(); ?>

The page loads but no tree appears. Source code only shows the first ul (countries). However the page hasn't completed loading - it appears to hang. Maybe it is still writing the list but I've left it for two minutes to load and nothing has appeared.

Same page:

http://www.followtheboat.com/index.php/delete-me/

RE: Creating three-tier parent-child display with special fields

(OP)
Sorry, that hanging load was my bad connection. Page loads as normal, minus tree.

RE: Creating three-tier parent-child display with special fields

can you edit the page as follows:

CODE

<?php
/*
Template Name: Gallery
*/

ini_set('display_errors', true);
error_reporting(E_ALL ^ E_NOTICE);

?>

<?php get_header(); ?>
<div id="wrap">                      
    <div id="content-container">    
        <div id="content">              
            <div class="post-container">
            Tree should appear here <?php $crlHierarchy->displayTree(); ?>
            </div>        
        <div style="float:left">
        <?php include('sidebar_nothing.php') ?>
        </div>
    </div>
    <div class="bgbottom"></div>
</div>
</div>
<?php get_footer(); ?>

since the code works on an exact replica of your site, I remain of the view that there is something that you are doing inadvertently that is breaking the code.  

can i just confirm that you have installed the code as a plugin and have enabled the plugin?

RE: Creating three-tier parent-child display with special fields

and ... assuming that you have enabled the plugin can you also add this line to assist in debugging

CODE

<?php echo "<pre>". print_r($crlHierarchy, true) . "</pre>"; ?>

best to add it just above the "Tree should appear here" line.

RE: Creating three-tier parent-child display with special fields

(OP)
Good morning Justin,

I suspect the same thing but I don't know what. That's why I stripped out all the crap on that template so we're just down to the bare essentials. Yes, it's installed as a plug-in. I tried deactivating and reactivating it. Here's the latest result.

CODE

crlHierarchy Object
(
    [flush:private] => 1
    [url] => http://www.followtheboat.com
    [cache] =>
)

     [] www.followtheboat.com []
tales [not just] from the high seas

RE: Creating three-tier parent-child display with special fields

well that does not look too good.

i'm going to send you the file by email.  there's just a chance that something has gone wrong in the cut and paste.

couple of minutes.

RE: Creating three-tier parent-child display with special fields

(OP)
Hi Justin,

I'm back in the UK sans your email address. Could you know me over an email for me to get back to you? Don't worry, no more coding requests!

Thank you. Hope you are well.

Jamie

     [] www.followtheboat.com []
tales [not just] from the high seas

RE: Creating three-tier parent-child display with special fields

will do.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close