Codegrrl.com Forums > [resolved] Built A Blog : Archived by month

Full Version: [resolved] Built A Blog : Archived by month

Codegrrl.com Forums > Script Help > Tutorial Help

purple_ivy

Nov 3 2006, 09:10 AM

I'm using the Part 5 thing where I can display my archives on my side bar. Mine are ordered by month not year.
Everything works fine EXCEPT that the months in my side bar are alphabetically ordered instead of how they should be (January, February, March, etc...)

Does anyone know how I can fix this?

Code that's for the part in my sidebar:

CODE

<?php
 mysql_connect ('localhost', '********', '**********');
 mysql_select_db ('************');

$result = mysql_query("SELECT FROM_UNIXTIME( timestamp, '%M' ) AS get_month, COUNT(*) AS

entries FROM php_blog GROUP BY get_month");

while ($row = mysql_fetch_array($result)) {
 $get_month = $row['get_month'];
 $entries = $row['entries'];

 echo "<a href=\"http://prettybloomers.com/archives.php?month=" . $get_month . "\">" .

$get_month . "</a> (" . $entries . ") ";
}

?>

And the code for my archives.php page:

CODE

<?php include("header.php"); ?>
<h1><img src="/bullet.gif"> Blog Archives</h1>
<?php
 mysql_connect ('localhost', '***********', '*******');
 mysql_select_db ('*****');

if (!isset($_GET['month'])) {
 die("Invalid month specified.");
}
else {
 $year = (int)$_GET['month'];
}

$result = mysql_query("SELECT timestamp, id, title FROM php_blog WHERE FROM_UNIXTIME( timestamp, '%M' ) = '$month' ORDER BY id DESC");

while ($row = mysql_fetch_array($result)) {
 $date = date("j/m/Y", $row['timestamp']);
 $id = $row['id'];
 $title = $row['title'];

 ?>

 <?php echo $date; ?> <a href="sep.php?id=<?php echo $id; ?>"><?php echo $title; ?></a>
 <?php
}

?>

<a href="java script:history.go(-1);" onMouseOver="window.status=' ';return true" OnMouseOut="window.status='';return true" class="img"><img src="/back.gif"></a>

<?php include("footer.php"); ?>

Thanks so much!

P.S, I'm not terribly knowledgable about PHP and MySQL, so explanations for dummies are always very much appreciated

Amelie

Nov 3 2006, 09:53 AM

Try changing this:

CODE

$result = mysql_query("SELECT FROM_UNIXTIME( timestamp, '%M' ) AS get_month, COUNT(*) AS entries FROM php_blog GROUP BY get_month");

...to this:

CODE

$result = mysql_query("SELECT FROM_UNIXTIME( timestamp, '%M' ) AS get_month, COUNT(*) AS entries FROM php_blog ORDER BY get_month ASC GROUP BY get_month");

purple_ivy

Nov 3 2006, 09:30 PM

I put that in my side bar and got this error:

CODE

[b]Warning[/b]: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in [b]/home/prettybl/public_html/header.php[/b] on line [b]81[/b]

And when I put that same code in my archives.php page I got the same error as above.

Amelie

Nov 3 2006, 10:11 PM

Oops, it needs to go the other way round... Try this instead:

CODE

$result = mysql_query("SELECT FROM_UNIXTIME( timestamp, '%M' ) AS get_month, COUNT(*) AS entries FROM php_blog GROUP BY get_month ORDER BY get_month ASC");

purple_ivy

Nov 4 2006, 02:29 AM

Nope, no change.

Amelie

Nov 4 2006, 10:08 AM

Do you mean it's still showing that error or that it's still listing the months alphabetically?

purple_ivy

Nov 4 2006, 08:24 PM

Still listing them alphabetically.

In the meantime, until I find a way to get the code to do it itself, I've linked to each month in my sidebar, instead of using the code that does it automatically.

Amelie

Nov 4 2006, 09:53 PM

I'll look into it and see what I can do.

purple_ivy

Nov 4 2006, 11:23 PM

Thank you so much!

Amelie

Mar 23 2008, 12:13 PM

Update to this thread - I forgot to say I found out how to fix this.

Use ORDER BY timestamp, not ORDER BY get_month. If you want to show the year, use %M %Y instead of %M in your query, otherwise it will show the months out of sync since May 2007 comes before February 2008, and without the year there, you will have your months listed with February coming after May

I would suggest using a query like this:

SQL

$result = mysql_query("SELECT FROM_UNIXTIME( timestamp, '%M %Y' ) AS get_month, COUNT(*) AS entries FROM php_blog GROUP BY get_month ORDER BY timestamp ASC");

If you left the original get_year part from the tutorial, change get_month to get_year

jae

Oct 25 2008, 01:45 AM

I'm sorry if I'm bumping, but I'm still having a problem; even after reading this. My show_archive.php is working fine; it's the archive.php that just won't show anything.

CODE

<?php

 mysql_connect ('', '', '');
 mysql_select_db ('');

 if (!isset($_GET['month'])) {
 die("Invalid month specified.");
 } else {
 $month = (int)$_GET['month'];
 }

 $result = mysql_query("SELECT timestamp, id, title FROM blog WHERE FROM_UNIXTIME(timestamp, '%M %Y') = '$month' ORDER BY id DESC");
 while ($row = mysql_fetch_array($result)) {
 $date = date("l F d Y", $row['timestamp']);
 $id = $row['id'];
 $title = stripslashes($row['title']);
 echo "$date \n<a href=\"display.php?id=$id\">$title</a> \n";
 }

?>

The url is something like http://domain.com/archive?month=Month Year and displays a blank page instead of the entries.
This is my show_archive.php; I believe it's working fine because it lists the months correctly.

CODE

<?php

 include('header.php');
 echo("Archive \n");
 mysql_connect ('', '', '');
 mysql_select_db ('');

 $result = mysql_query("SELECT FROM_UNIXTIME( timestamp, '%M %Y' ) AS month, COUNT(*) AS entries FROM blog GROUP BY month ORDER BY timestamp ASC");
 while ($row = mysql_fetch_array($result)) {
 $month = $row['month'];
 $entries = $row['entries'];
 echo "<a href=\"archive.php?month=" . $month . "\">" . $month . "</a> (" . $entries . ") \n";
 }

 include('footer.php');

?>

Vixx

Oct 25 2008, 09:33 AM

Bumping is positively encouraged if it's relative to the original topic.

Amelie

Oct 25 2008, 09:40 AM

You need to show it as monthyear not month year. Otherwise the URL will cut off. So the %M %Y needs to be %M%Y

jae

Oct 25 2008, 06:35 PM

It's still not working. I changed %M %Y to %M%Y in archive.php so if I were to go to http://domain.com/archive?month=MonthYear, the posts and such should be displayed but it doesn't. An how do I fix the %M %Y for show_archive.php so it lists the month by Month Year and the link would be MonthYear?

Amelie

Oct 25 2008, 07:05 PM

Try changing this line:

CODE

$month = (int) $_GET['month'];

To:

CODE

$month = mysql_real_escape_string(stripslashes($_GET['month']));

(Extra security added there as well

)

Also I was mistaken in the '%M%Y' thing - it should have the space in it, like '%M %Y' as you had it before. Sorry about that!

jae

Oct 26 2008, 04:12 AM

It works! Thanks for all your help

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.