markellison

SCORE! IT WORKS!
I cannot thank you enough!!!!

Hi again, thanks so much for the quick reply. Your suggested code worked wonders! The names are now grouped by the last word. Check it out: http://bit.ly/aK3oRH

Two issues still need to be solved, though.

1) “letter groups” should be printed in alphabetical order. So this list:

Antonin Artaud 1
Benedict Anderson 10
Fergus Allen 13 /27

Should be printed before this list:

Angela Bourke 4 /21
Greg Baxter 15 /27 /29 /30 /32 /34
Harry Browne 6 /8 /10 /21 /25 /33

2) within each “letter group”, the names should be in alphabetical order. So this list:

Antonin Artaud 1
Benedict Anderson 10
Fergus Allen 13 /27

Should actually print like this:

Fergus Allen 13 /27
Benedict Anderson 10
Antonin Artaud 1

…because Allen comes before Anderson which comes before Artaud.

Hi t310s_,

I have a new challenge… 🙂

The client now wants the list to be alphabetized not by first word in the tag names, but rather by the second word of the tag names. The reason is that the tags are actually author names and the list I’m creating is meant to be an index.

You can see what I have so far here: http://bit.ly/aK3oRH

Here is the code I’m using (a slight modification of your code to account for my db table names; and I made the html print out in a table rather than ul/li)

<?php

$tags_and_posts = $wpdb->get_results("
	SELECT tr.object_id,p.post_title,p.guid,t.name
	FROM wp_dub_term_relationships tr
	JOIN wp_dub_term_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
	JOIN wp_dub_posts p ON tr.object_id = p.ID
	JOIN wp_dub_terms t ON tt.term_id = t.term_id
	WHERE tt.taxonomy = 'post_tag'
	AND p.post_status = 'publish'
	AND p.post_type = 'page'
	ORDER by t.name
");

$tag_array = array();
$letters = array();

foreach( $tags_and_posts as $unwanted_key => $result ) {

	$tag_array[$result->name][] = '<a href="'. get_permalink( $result->object_id ) .'">' . $result->post_title . '</a>';
}

foreach( $tag_array as $tag => $post_titles ) {

	$letter = $tag{0};
	$letters[ $letter ][] =  $tag . ' <td class="issue-numbers"> ' . implode( ' <span class="sep">/</span>', $post_titles ) . ' </td> ';
}

foreach( $letters as $tag_letter => $tags ) {

	// Create a list for each letter
	print '<table id="index-table"><tr><td class="author-name">' . implode( '</td></tr><tr><td class="author-name">', $tags ) . '</td></tr></table>';
}
?>

If there is a way to achieve this programatically, it would be absolutely huge! If you can solve this, I honestly want to buy you a beer. 🙂

All best,
Mark

Yeah, got those 🙂 Something is still wrong though…

Ah ha! It’s the fact that I’m tagging WP pages and not posts. When I tag a post, your code works perfectly! Exactly as I would have liked. But the code is not picking up tagged WP pages (made possible by the Simple Tags plugin).

I’m going to play around some more…

@t31os_

I tried your suggestion about displaying post titles (with permalinks) alongside tags, but I can’t really get it to work. You are correct that I am trying to grab any post with a tag. I want to create a list of all my tags and, in a list beneath each tag, place permalinks/titles of all posts that are tagged with that tag. Additionally, the tag list should be listed in alphabetical order and grouped by letter. That’s a mouthful! But essentially it’s a combination of this solution:

<?php
    $query_string = '
		SELECT *,name FROM '.$wpdb->prefix.'term_taxonomy
		JOIN '.$wpdb->prefix.'terms
		ON '.$wpdb->prefix.'term_taxonomy.term_id = '.$wpdb->prefix.'terms.term_id
		WHERE '.$wpdb->prefix.'term_taxonomy.taxonomy = "post_tag"
		ORDER by  '.$wpdb->prefix.'terms.name ASC
    ';
	$post_tags = $wpdb->get_results($query_string);
	?>
	<div id="a" class="abc_tags">
		<ul>
	<?php
	foreach($post_tags as $key => $tag) {
		$newletter = substr($tag->name, 0, 1);
		if($newletter !== $letter && $key != 0) { ?>
		</ul>
	</div>
	<div id="<?php echo strtolower($newletter); ?>" class="abc_tags">
		<ul>
	<?php	} $letter = substr($tag->name, 0, 1); ?>
			<li><a href="<?php echo get_tag_link($tag->term_id); ?>" title="<?php echo sprintf( __( "View all posts in %s" ), $tag->name ); ?>"><?php echo $tag->name.' ('.$tag->count.')';?></a></li>
		<?php
	}
		?>
		</ul>
	</div>

and what you proposed here:

$tags_and_posts = $wpdb->get_results("
SELECT tr.object_id,p.post_title,p.guid,t.name
FROM wp_term_relationships tr
JOIN wp_term_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
JOIN wp_posts p ON tr.object_id = p.ID
JOIN wp_terms t ON tt.term_id = t.term_id
WHERE tt.taxonomy = 'post_tag'
AND p.post_status = 'publish'
AND p.post_type = 'post'
");
$tag_array = array();
foreach( $tags_and_posts as $unwanted_key => $result ) {
	$tag_array[$result->name][] = '<a href="'. $result->guid .'">' . $result->post_title . '</a>';
	//$tag_array[$result->name][] = '<a href="'. get_permalink( $result->object_id ) .'">' . $result->post_title . '</a>';
}
print '<ul>';
foreach( $tag_array as $tag => $post_titles ) {
	print '<li>' . $tag . ' ( ' . implode( ', ', $post_titles ) . ' )</li>';
}
print '</ul>';

Can you help? Major high fives if so!

Also, if it makes a difference, I am actually tagging WP pages, not posts, by using the Simple Tags plugin.

Very useful, thanks guys!

The code suggested by t31os_ works perfectly.

I was wondering if there’s a straightforward way to display post titles in a comma separated list, rather than the tag count? And for each post title to link to its respective post. So it’s a combination of t31os_‘s solution and the solution posted here:
http://ww.wp.xz.cn/support/topic/328118?replies=8

For example, say I’ve published the following three posts:

Post title: Old Yeller is a dog
Tagged: Dog

Post title: Old Yeller likes steak
Tags: Dog, Food

Post title: Garfield will eat anything
Tags: Cat, Food

This is the list I want to create:

• Cat (Garfield will eat anything)
• Dog (Old Yeller is a dog, Old Yeller likes steak)
• Food (Old Yeller likes steak, Garfield will eat anything)

Any ideas are very much appreciated!