Ask your WordPress questions! Pay money and get answers fast! (more info)

Relative-width Archive Links WordPress

<strong>*** Update ***</strong>

<strong>I just added an extra $10 to prize. My original question <em>may have</em> left out some important details in relation to the monthly archives part of this question</strong>. These extra details being .....

- Both yearly and monthly archive links are called via index.php and are being used as the primary site navigation (to put it into context)
- Yearly archives links display on all pages (already work. Don't need further attention)
- Monthly archive links (not yet working) <em>only</em> display once viewing a yearly archive, so <strong>these month archive links need to be relative to the year being viewed</strong>
- Example 1: if viewing the 2004 archive at website.com/2004 then the monthly links need to point to 2004/01 and 2004/02 and 2004/03 etc.
- Example 2: if viewing the 2009 archive at website.com/2009 then the monthly links need to point to 2009/01 and 2009/02 and 2009/03 etc.

<strong>Notes</strong>
# The monthly links still need to use the relative width % value as outlined in the original question
# At no point on any page will there be links to months belonging to other years. Each month is only display once per page and relative to the year being viewed.
# Both answers from John and Arnav work as far as Yearly archive links go (as they don't need to be "relative" per se), and as far as calculating the width as a percentage of the total post count. But I failed to explain the monthly archive thoroughly enough.



*** Original Question ***

I need some help with some custom archive links that ...

1) <strong>Are custom formatted</strong>, and
2) <strong>Calculate the width element of the link as a % value of the total post count for that archive</strong>

Here's the HMTL example .....

<div class="progress">
<a href="#"><div class="bar bar-1" style="width: 10%;">2001</div></a>
<a href="#"><div class="bar bar-2" style="width: 20%;">2002</div></a>
<a href="#"><div class="bar bar-3" style="width: 5%;">2003</div></a>
<a href="#"><div class="bar bar-4" style="width: 40%;">2004</div></a>
<a href="#"><div class="bar bar-5" style="width: 25%;">2005</div></a>
</div>



And here's the example post count (used to calculate the width)
<strong>Yearly Archives</strong>
Total Posts = 100
2001 = 10 posts, so style="width: 10%"
2002 = 20 posts, so style="width: 20%"
2003 = 5 posts, so style="width: 5%"
2004 = 40 posts, so style="width: 40%"
2005 = 25 posts, so style="width: 25%"

<strong>Monthly Archives 2004</strong>
Total Posts = 40
Jan = 10 posts, so style="width: 25%"
Feb = 4 posts, so style="width: 10%"
Mar = 8 posts, so style="width: 20%"
Apr = 18 posts, so style="width: 45%"

Answers (3)

2012-10-23

Francisco Javier Carazo Gil answers:

First, you have to have a function which calculate the total amount of posts each year:


$numposts = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->posts WHERE post_status = 'publish' AND year(post_date) = $year");
if (0 < $numposts) $numposts = number_format($numposts);


Francisco Javier Carazo Gil comments:

Later, you have to do a loop from the first year to the last, summing the amounts.

At the end, with this sum you will only have to do: width = (number_posts_of_year / number_posts_total) * 100;

And you will have the %.


Francisco Javier Carazo Gil comments:

To improve this, and Cotton solution, you have to be care with %, you cannot add more than 100 so you will have to loop over the array and making a substract (excess shouldn't be big) o addition, to leave it perfect.


TheLoneCuber comments:

Francisco you provide advice but not a coded solution. I need a coded solution.

2012-10-23

John Cotton answers:

I haven't tried this but....

function get_posts_by_year() {
global $wpdb;

return $wpdb->get_results("SELECT * FROM (
SELECT YEAR(post_date) AS year, COUNT(ID) AS total FROM $wpdb->posts WHERE post_status = 'publish' GROUP BY YEAR(post_date)
UNION
SELECT 9999, COUNT(ID) FROM $wpdb->posts WHERE post_status = 'publish'
) T1 ORDER BY year DESC;");
}

function get_posts_by_month( $year ) {
global $wpdb;

return $wpdb->get_results("SELECT * FROM (
SELECT MONTH(post_date) AS month, COUNT(ID) AS total FROM $wpdb->posts WHERE post_status = 'publish' AND YEAR(post_date) = $year GROUP BY MONTH(post_date)
UNION
SELECT 9999, COUNT(ID) FROM $wpdb->posts WHERE post_status = 'publish' AND YEAR(post_date) = $year
) T1 ORDER BY month DESC;");
}

<div class="progress">
<?php
$rows = get_posts_by_year();
$total = array_shift($rows);

foreach($rows as $row) {
echo sprintf( '<a href="#"><div class="bar bar-1" style="width: %s;">%s</div></a>', $row->year / $total->year .'%', $row->year );
}
?>
</div>


The monthly one would be similar, but you'd pass a year parameter obviously.

The advantage of this approach is a single SQL query so performance should be pretty good. You could modify the two SQL queries to filter out post types or whatever if you need to.


...just so it's clear, the SQL has a total row which gets ORDERed to the top. That row gets array_shifted off the stack leaving a clean array to loop through and compare.


TheLoneCuber comments:

It's always a hug bonus that you include explanations John. Thanks for that.

The code doesn't seem to return any results though.


John Cotton comments:

There was a mistake in it, but that should not have affected the output.

Nevertheless try this:


<?php
function get_posts_by_year() {

global $wpdb;



return $wpdb->get_results("SELECT * FROM (

SELECT YEAR(post_date) AS year, COUNT(ID) AS total FROM $wpdb->posts WHERE post_status = 'publish' GROUP BY YEAR(post_date)

UNION

SELECT 9999, COUNT(ID) FROM $wpdb->posts WHERE post_status = 'publish'

) T1 ORDER BY year DESC;");

}



function get_posts_by_month( $year ) {

global $wpdb;



return $wpdb->get_results("SELECT * FROM (

SELECT MONTH(post_date) AS month, COUNT(ID) AS total FROM $wpdb->posts WHERE post_status = 'publish' AND YEAR(post_date) = $year GROUP BY MONTH(post_date)

UNION

SELECT 9999, COUNT(ID) FROM $wpdb->posts WHERE post_status = 'publish' AND YEAR(post_date) = $year

) T1 ORDER BY month DESC;");

}
?>


<div class="progress">
<?php
$rows = get_posts_by_year();
$total = array_shift($rows);

foreach($rows as $row) {
echo sprintf( '<a href="#"><div class="bar bar-1" style="width: %s;">%s</div></a>', $row->total / $total->total .'%', $row->year );
}

?>
</div>


TheLoneCuber comments:

Years is working now John - I think it was just a maths thing. The widths were 2 decimal places off (and therefore making them 0.003% wide) so I added "* 100" and that solved that problem.

was ...
$row->total / $total->total

now ...
$row->total / $total->total * 100


But neither version works for months? Months is returning <em>no data at all</em> - it's an entirely empty divide.

<div class="progress"></div>


John Cotton comments:

And you are definitely passing a year parameter?

If so, change the months function to this and tell me what you see:


function get_posts_by_month( $year ) {



global $wpdb;







$r = $wpdb->get_results("SELECT * FROM (
SELECT MONTH(post_date) AS month, COUNT(ID) AS total FROM $wpdb->posts WHERE post_status = 'publish' AND YEAR(post_date) = $year GROUP BY MONTH(post_date)

UNION

SELECT 9999, COUNT(ID) FROM $wpdb->posts WHERE post_status = 'publish' AND YEAR(post_date) = $year

) T1 ORDER BY month DESC;");

echo $wpdb->last_query;
echo $wpdb->last_error;
return $r;

}


TheLoneCuber comments:

When you ask if I'm "passing a year parameter" ..... I reply "probably not. I don't know how I'm supposed to". I'm no coder John - just a copy/paste/masher of code.

Here is the the output as per latest code version ....
<blockquote>
SELECT * FROM (

SELECT MONTH(post_date) AS month, COUNT(ID) AS total FROM hnh_2_posts WHERE post_status = 'publish' AND YEAR(post_date) = GROUP BY MONTH(post_date)

UNION

SELECT 9999, COUNT(ID) FROM hnh_2_posts WHERE post_status = 'publish' AND YEAR(post_date) =

) T1 ORDER BY month DESC;You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY MONTH(post_date)

UNION

SELECT 9999, COUNT(ID) FROM hnh_2_posts ' at line 3</blockquote>


TheLoneCuber comments:

The Years works fine as is John, though I wanted to change to sort order.

So I swapped ....
T1 ORDER BY month DESC;");

to this .....
T1 ORDER BY month ASC;");

But that outputs the years plus "9999" as a year. And the calculation I added "* 100" needs to be modified to "* 10"

Is that what that line of the code does - select 9999 published posts from year X and then sort them according to the order by value? If so, this would have to be updated if/when posts exceed a count of 10,000?


John Cotton comments:

<blockquote>probably not</blockquote>

! That's why it doesn't work :)

You need to call it like this:


<?php get_posts_by_month( 2012 )?>

I'm not sure where you are putting this months thing - in a loop of the years?

Also, you can't change the order without changing the ordering column. 9999 was used to make it at the top of the list if sorting descending (as 9999 will be more than any year or month value - EVER!).

If you want to order ASC, you still need to have the total column first in the rows so that you can shift it off before looping through the real list. To be at the top of an ASC list, it must be small, so make it -9999 which is smaller than any year or month value - EVER!


TheLoneCuber comments:

Yes if on yearly archive page, then show get_posts_by_month. So I would have to establish what year's archive page was being viewed, and then pass that to the get_posts_by_month function call?


John Cotton comments:

<blockquote>Yes if on yearly archive page, then show get_posts_by_month. So I would have to establish what year's archive page was being viewed, and then pass that to the get_posts_by_month function call?</blockquote>

Yes. So get_posts_by_month( get_query_var('year') ) should work, assuming your page structure is normal.


John Cotton comments:

This should give you two sets of bars - but only on a year page.

If you are able to give a link to where this is, things might be quicker :)

<?php

function get_posts_by_year() {
global $wpdb;


return $wpdb->get_results("SELECT * FROM (
SELECT YEAR(post_date) AS year, COUNT(ID) AS total FROM $wpdb->posts WHERE post_status = 'publish' GROUP BY YEAR(post_date)
UNION
SELECT 9999, COUNT(ID) FROM $wpdb->posts WHERE post_status = 'publish'
) T1 ORDER BY year DESC;");
}



function get_posts_by_month( $year ) {
global $wpdb;

return $wpdb->get_results("SELECT * FROM (
SELECT MONTH(post_date) AS month, COUNT(ID) AS total FROM $wpdb->posts WHERE post_status = 'publish' AND YEAR(post_date) = $year GROUP BY MONTH(post_date)
UNION
SELECT -9999, COUNT(ID) FROM $wpdb->posts WHERE post_status = 'publish' AND YEAR(post_date) = $year
) T1 ORDER BY month ASC;");
}

?>



<div class="progress">
<?php
$rows = get_posts_by_year();
$total = array_shift($rows);

foreach($rows as $row) {
echo sprintf( '<a href="/%d"><div class="bar bar-1" style="width: %s;">%s</div></a>', $row->year, ($row->total / $total->total) * 100 .'%', $row->year );
}
?>
</div>

<?php

if( $year = get_query_var('year') ) {
$rows = get_posts_by_month($year);
$total = array_shift($rows);

?>
<div class="progress">
<?php
foreach($rows as $row) {
echo sprintf( '<a href="\%d/%d"><div class="bar bar-1" style="width: %s;">%s</div></a>', $year, $row->month, ($row->total / $total->total) * 100 .'%', $row->month );
}
?>
</div>




2012-10-23

Arnav Joy answers:

try this

<?php
global $wpdb;


$years = $wpdb->get_results("SELECT DISTINCT YEAR( post_date ) AS year, COUNT( id ) as post_count FROM $wpdb->posts WHERE post_status = 'publish' and post_date <= now( ) and post_type = 'post' GROUP BY year ORDER BY post_date DESC");

foreach($years as $year) :
$totalposts = $totalposts+$year->post_count;
endforeach;

?>
<div class="progress">
<?php foreach($years as $year) :?>
<a href="#"><div class="bar bar-1" style="width: <?php echo round(($year->post_count*100)/$totalposts); ?>%;"><?php echo $year->year; ?></div></a>
<?php endforeach; ?>


</div>


TheLoneCuber comments:

This almost works Arnav. There must be a rounding issue though? One of the years is calculated to 0%, and therefore not displaying (because 0% width is 0% wide).

Maybe that's what Fancisco was referring to?


Arnav Joy comments:

so , what to do if year has no posts with it , any fixed value?


Arnav Joy comments:

I have rounded off the number otherwise it will look something like 70.777 etc.
so let me know what should be width of the divs that are not having any posts for that year.


TheLoneCuber comments:

I thought default archive behaviour for all archives (day, month or year) was if they have no posts (for that archive period) that they doesn't show as an archive link? So a year that has no posts in it doesn't show as a year at all?

So using the original data sample, if 2003 = 0 posts, then the 2003 link would not be outputted?

2003 = 0 posts (this link does not show)

* They number being 70.777 is perfectly fine, as it's not displayed on the frontend
* It's very important that the total widths sum to exactly 100%


Arnav Joy comments:

try this

<?php

global $wpdb;





$years = $wpdb->get_results("SELECT DISTINCT YEAR( post_date ) AS year, COUNT( id ) as post_count FROM $wpdb->posts WHERE post_status = 'publish' and post_date <= now( ) and post_type = 'post' GROUP BY year ORDER BY post_date DESC");



foreach($years as $year) :

$totalposts = $totalposts+$year->post_count;

endforeach;



?>

<div class="progress">

<?php foreach($years as $year) :?>
<?php if( $year->post_count > 0 ) : ?>

<a href="#"><div class="bar bar-1" style="width: <?php echo round(($year->post_count*100)/$totalposts); ?>%;"><?php echo $year->year; ?></div></a>
<?php endif ?>
<?php endforeach; ?>





</div>


TheLoneCuber comments:

This works for the years Arnav, but there is not code for monthly archives?


Arnav Joy comments:

can you tell me how it should look in case of months? let me know the full format for it.


Arnav Joy comments:

can you tell me , what you passed to get yearly archive link ?

means

<a href="??????">2011</a>

what is the value of "?????" i your case