Archive by year

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

aghast
Forum Newbie
Posts: 16
Joined: Wed Jul 28, 2010 3:22 pm

Archive by year

Post by aghast »

Hello, this is my first post on phpdn and I'm also a complete and utter newby when it comes to php.

I recently took on a project for a friend of mine and it requires a paginated archive for displaying his past and present artwork (think wordpress style archiving, accept styled horizontally). I soon realized that I was in way over my head and though there are thousands of tutorials for pagination I could find nothing that touches on pagination with the years as links.

If anyone can point me in the right direction I would greatly appreciate it.

Thank you
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: Archive by year

Post by jraede »

Pass the year as a query variable like <a href="?year=2008">2008</a>, and then use that as a limiter in your database query's WHERE clause.

Code: Select all

$year = $_GET['year'];
if(!$year) $year = date('Y');
$results = mysql_query("SELECT * FROM `artwork` WHERE YEAR(`date_created`)='$year'");
aghast
Forum Newbie
Posts: 16
Joined: Wed Jul 28, 2010 3:22 pm

Re: Archive by year

Post by aghast »

jraede wrote:Pass the year as a query variable like <a href="?year=2008">2008</a>, and then use that as a limiter in your database query's WHERE clause.

Code: Select all

$year = $_GET['year'];
if(!$year) $year = date('Y');
$results = mysql_query("SELECT * FROM `artwork` WHERE YEAR(`date_created`)='$year'");
Hey, thanks! i see how it could work but I'm not completely sure how to implement it. I played with it for a while but don't really understand what you mean by "Pass the year as a query variable like <a href="?year=2008">2008</a>, and then use that as a limiter in your database query's WHERE clause." . :?

thanks jraede.
aghast
Forum Newbie
Posts: 16
Joined: Wed Jul 28, 2010 3:22 pm

Re: Archive by year

Post by aghast »

This is what I'm working with, It's the regular paging for the site. I need to add the date paging which will go in the header. Now that I have the main idea on how to make the Date pagination how would I combine the two to work together?

Code: Select all


<?php

  include ("includes/dblink.php");



$per_page = 1;

$start = $_GET['start'];



$record_count = mysql_num_rows(mysql_query("SELECT * FROM archive"));


$max_pages = $record_count / $per_page;

if (!$start)
   $start = 0;
   

$get = mysql_query("SELECT * FROM archive LIMIT $start, $per_page");
while ($row = mysql_fetch_assoc($get))

{	
 
 $title= $row['title'];
 $location= $row['location'];
 $post= $row['post'];
 
 $date = date( 'F d, Y', $phpdate );
 $phpdate = strtotime( $mysqldate );
 
 
 	
	echo "<div id='title'>$title</div> ";
	echo "<div id='location'>$location </div> ";
	echo "<div id='title-date'>$date</div> ";
	echo "<p id='description'>$post</p>";
	//echo '<div class="post-image"><img src="image_uploads/'.$row['image'].'"></img></div>';
	
	
}

$prev = $start - $per_page;
$next = $start + $per_page;

//show prev button
if (!($start<=0))
       echo "<a href='index.php?start=$prev'>Prev</a> ";

//show page numbers

//set variable for first page
$i=1;

for ($x=0;$x<$record_count;$x=$x+$per_page)
{
 if ($start!=$x)
    echo " <a href='index.php?start=$x'>$i</a> ";
 else
    echo " <a href='index.php?start=$x'><b>$i</b></a> ";
 $i++;
}

//show next button
if (!($start>=$record_count-$per_page))
       echo " <a href='index.php?start=$next'>Next</a>";

?>
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: Archive by year

Post by jraede »

Let's take this out of PMs so everyone can see...

Are you trying to paginate WITHIN years? Or just have the pages be the years? From what I understood about your original post, you're trying to have the years as the pages, which would mean all listings for that year would show up on the year page. Paginating within years is an entirely different process.
aghast
Forum Newbie
Posts: 16
Joined: Wed Jul 28, 2010 3:22 pm

Re: Archive by year

Post by aghast »

jraede wrote:Let's take this out of PMs so everyone can see...

Are you trying to paginate WITHIN years? Or just have the pages be the years? From what I understood about your original post, you're trying to have the years as the pages, which would mean all listings for that year would show up on the year page. Paginating within years is an entirely different process.

Each page would have 1 or 2 posts but each year would have 7 to 10 total posts. It would be too bulky if I put every years posts on one page because each will be very image heavy.There will be 2 navigations. One in the header to skip around within the archive by year like: 2010 2009 2008 2007 2006... and then the footer: 1 2 3 4 >>... I hope I'm making sense.
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: Archive by year

Post by jraede »

Ok, so the pagination capability that you already posted, assuming it works for regular queries, will also work for this. You just have to add " WHERE YEAR(`date`) = '$year'" to the query. To create the links for the years, just get all the years that have rows:

Code: Select all

SELECT YEAR(`date`) FROM `data` GROUP BY YEAR(`date`) ORDER BY YEAR(`date`)
...and like I said before, use them as query variables, so the code for that would look like:

Code: Select all

$years = mysql_query("SELECT YEAR(`date`) FROM `data` GROUP BY YEAR(`date`) ORDER BY YEAR(`date`)");
while($row = mysql_fetch_row($years)) {
     echo  '<a href="?year='.$row[0].'">'.$row[0].'</a>';
}
Then you'd use the year passed in through the URL to limit your query that you send to your pagination script.
aghast
Forum Newbie
Posts: 16
Joined: Wed Jul 28, 2010 3:22 pm

Re: Archive by year

Post by aghast »

jraede wrote:Ok, so the pagination capability that you already posted, assuming it works for regular queries, will also work for this. You just have to add " WHERE YEAR(`date`) = '$year'" to the query. To create the links for the years, just get all the years that have rows:

Code: Select all

SELECT YEAR(`date`) FROM `data` GROUP BY YEAR(`date`) ORDER BY YEAR(`date`)
...and like I said before, use them as query variables, so the code for that would look like:

Code: Select all

$years = mysql_query("SELECT YEAR(`date`) FROM `data` GROUP BY YEAR(`date`) ORDER BY YEAR(`date`)");
while($row = mysql_fetch_row($years)) {
     echo  '<a href="?year='.$row[0].'">'.$row[0].'</a>';
}
Then you'd use the year passed in through the URL to limit your query that you send to your pagination script.
Okay, we're getting there, I'm showing the dates and they change in the address bar when clicked but they stay on the first date even though there is content in the database. Also I need to get the numerical paging to link using dates as well so the URL shows something like this: index.php?year=2010/1 instead of index.php?start=1. Thanks so much for your help so far.
aghast
Forum Newbie
Posts: 16
Joined: Wed Jul 28, 2010 3:22 pm

Re: Archive by year

Post by aghast »

Well, I moved on to other parts of the site to give time for a response here. I would really like to walk away from this with a good grasp on the function I'm implementing. So far I've gotten some very important bits and pieces that work. But being very new to this, I'm having a hard time implement them properly. Any help is appreciated and again thanks to jraede for sharing his knowledge with us.
aghast
Forum Newbie
Posts: 16
Joined: Wed Jul 28, 2010 3:22 pm

Re: Archive by year

Post by aghast »

Code: Select all

$get = mysql_query("SELECT * FROM archive WHERE YEAR(`date`) = '$year' LIMIT $start, $per_page");
while ($row = mysql_fetch_assoc($get))
I got the date links working but in doing so I broke the number links. It has something to do with the LIMIT (above) i believe. When I remove the WHERE clause the numbers work but the dates don't and when the LIMIT is removed, of course the numbers stop working. Any clues?
aghast
Forum Newbie
Posts: 16
Joined: Wed Jul 28, 2010 3:22 pm

Re: Archive by year

Post by aghast »

I've posted in other forums and it's either that I'm stumping everybody or I'm not explaining myself very well. I thought it was a fairly straight forward function to me. I've seen much more complex issues more easily resolved. I don't understand why there is no tutorial or information on something similar. Am i really the first person to ever attempt building an archive ordered by date with pagination? If blogspot can do something similar I imagine someone knows. I'll make my own tutorial if i ever figure this out. Wish me luck!
shawngoldw
Forum Contributor
Posts: 212
Joined: Mon Apr 05, 2010 3:38 pm

Re: Archive by year

Post by shawngoldw »

With that query you are only going to pull $per_page number of rows from the database. So if you're only getting the number of rows which will be able to show on 1 page, you are only going to know that there are enough rows to fill 1 page, not more.

If there is not going to be a huge number of rows in the database with the appropriate year, you need to NOT limit the query, so that you can see how many results there are. Then you need to only display the results from $start to $start + $per_page

If there will be a huge number of rows in the database for the apprioriate year, you need to find an alternative to calculate how many pages you need.


Shawn
aghast
Forum Newbie
Posts: 16
Joined: Wed Jul 28, 2010 3:22 pm

Re: Archive by year

Post by aghast »

shawngoldw wrote:With that query you are only going to pull $per_page number of rows from the database. So if you're only getting the number of rows which will be able to show on 1 page, you are only going to know that there are enough rows to fill 1 page, not more.

If there is not going to be a huge number of rows in the database with the appropriate year, you need to NOT limit the query, so that you can see how many results there are. Then you need to only display the results from $start to $start + $per_page

If there will be a huge number of rows in the database for the apprioriate year, you need to find an alternative to calculate how many pages you need.


Shawn
Thanks for the reply,
Removing the limit only seams to disable the number paging. It will show the first page then show blank pages when I hit next.
shawngoldw
Forum Contributor
Posts: 212
Joined: Mon Apr 05, 2010 3:38 pm

Re: Archive by year

Post by shawngoldw »

What code are you using for the next button?

Is it this?

Code: Select all

if (!($start>=$record_count-$per_page))
       echo " <a href='index.php?start=$next'>Next</a>";
If so, it needs to be:

Code: Select all

if (!($start>=$record_count-$per_page))
       echo " <a href='index.php?year=$year&start=$next'>Next</a>";
aghast
Forum Newbie
Posts: 16
Joined: Wed Jul 28, 2010 3:22 pm

Re: Archive by year

Post by aghast »

shawngoldw wrote:What code are you using for the next button?

Is it this?

Code: Select all

if (!($start>=$record_count-$per_page))
       echo " <a href='index.php?start=$next'>Next</a>";
If so, it needs to be:

Code: Select all

if (!($start>=$record_count-$per_page))
       echo " <a href='index.php?year=$year&start=$next'>Next</a>";
Okay, i see what you meant, but the year stays at 2010, even though I have a 2009 and 2008 entry in the database.
Post Reply