Solved

Show blog archive with PHP & MySQL

Posted on 2012-03-24
29
258 Views
Last Modified: 2012-03-31
I want to display an archive of blog posts by month and year. So if there's no posts in March that month won't show. A count would also be good.

I am using this code at the moment with nol luck...

mysql_select_db($database_acsinfo, $acsinfo);
$query_archive = "SELECT YEAR(`eventdate`) AS 'year', MONTH(`eventdate`) AS 'month', COUNT(`id`) AS 'count'  FROM `latestnews` GROUP BY YEAR(`eventdate`), MONTH(`eventdate`) DESC
";
$archive = mysql_query($query_archive, $acsinfo) or die(mysql_error());
$row_archive = mysql_fetch_assoc($archive);
$totalRows_archive = mysql_num_rows($archive);

$data = array();
while($row = $result->fetch_assoc()) {
    $data[$row['year']][$row['month']] = $row['count'];
}
$result->free();

Open in new window


and to display...

foreach ($data as $year => $months) {
    echo $year.'<br>';
    foreach ($months as $month => $count) {
            echo $month.'('.$count.')<br>';
    }

Open in new window

}

The database column with the DATE is 'eventdate'

Any ideas what's wrong here?
0
Comment
Question by:BrighteyesDesign
  • 16
  • 13
29 Comments
 
LVL 7

Expert Comment

by:designatedinitializer
Comment Utility
Change line 10 in the 1st listing to:
if( intval($row['count']) >0) $data[$row['year']][$row['month']] = $row['count'];

Open in new window

0
 

Author Comment

by:BrighteyesDesign
Comment Utility
Thanks for that still no joy though. The screen just goes blank

When I remove...

$data = array();
while($row = $result->fetch_assoc()) {
if( intval($row['count']) >0) $data[$row['year']][$row['month']] = $row['count'];

}

Open in new window


The page displays (without the query showing) so this may have something to do with it. In fact just looking at the code how is it linked to...

mysql_select_db($database_acsinfo, $acsinfo);
$query_archive = "SELECT YEAR(`eventdate`) AS 'year', MONTH(`eventdate`) AS 'month', COUNT(`id`) AS 'count'  FROM `latestnews` GROUP BY YEAR(`eventdate`), MONTH(`eventdate`) DESC
";
$archive = mysql_query($query_archive, $acsinfo) or die(mysql_error());
$row_archive = mysql_fetch_assoc($archive);
$totalRows_archive = mysql_num_rows($archive);

Open in new window


Should $date be $archive or something?
0
 
LVL 7

Expert Comment

by:designatedinitializer
Comment Utility
:)

There's something wrong in the 1st listing.
You're not getting the query result object right.
Try this instead:

mysql_select_db($database_acsinfo, $acsinfo);
$query_archive = "SELECT YEAR(`eventdate`) AS 'year', MONTH(`eventdate`) AS 'month', COUNT(`id`) AS 'count'  FROM `latestnews` GROUP BY YEAR(`eventdate`), MONTH(`eventdate`) DESC
";
$result = mysql_query($query_archive, $acsinfo) or die(mysql_error());

$data = array();
while($row = $result->fetch_assoc()) {
    $data[$row['year']][$row['month']] = $row['count'];
}
$result->free();

Open in new window


If this doesn't work, in the first place you should test your SQL. Go into PHPMyAdmin and just paste it in. Then test if the results come correctly into PHP. You can do that by inserting print_r($row) inside the while loop.
0
 

Author Comment

by:BrighteyesDesign
Comment Utility
I have pasted this in PhpmyAdmin and it shows results ok

SELECT YEAR(`eventdate`) AS 'year', MONTH(`eventdate`) AS 'month', COUNT(`id`) AS 'count'  FROM `latestnews` GROUP BY YEAR(`eventdate`), MONTH(`eventdate`) DESC

Open in new window

Screen-shot-2012-03-24-at-15.56..png
0
 
LVL 7

Expert Comment

by:designatedinitializer
Comment Utility
Please read carefully my previous reply.
The problem is that you're mixing your mysql resource variables. The variable in the assignment at line number 4 in your 1st listing should be the same as the variable in the while statement (line number 9).

Furthermore, you must NOT fetch_assoc (line number 5 in your listing) before the while loop, because this will move the result set pointer, and your while loop will start on the 2nd row of the result set!

That's why I rewrote your entire code.
0
 

Author Comment

by:BrighteyesDesign
Comment Utility
Ah ok,

Here's the results using...

mysql_select_db($database_acsinfo, $acsinfo);
$query_archive = "SELECT YEAR(`eventdate`) AS 'year', MONTH(`eventdate`) AS 'month', COUNT(`id`) AS 'count'  FROM `latestnews` GROUP BY YEAR(`eventdate`), MONTH(`eventdate`) DESC
";
$result = mysql_query($query_archive, $acsinfo) or die(mysql_error());

$data = array();
while($row = $result->fetch_assoc()) {
    $data[$row['year']][$row['month']] = $row['count'];
}
$result->free();

Open in new window

Screen-shot-2012-03-26-at-09.31..png
0
 
LVL 7

Expert Comment

by:designatedinitializer
Comment Utility
...you're pasting a PHP statement inside the PHPMyAdmin SQL input box...
Paste only the contents of the string assignment.
0
 

Author Comment

by:BrighteyesDesign
Comment Utility
I did that originally a few posts back and the query was ok.

Just not sure how to check the rest of it?
0
 
LVL 7

Expert Comment

by:designatedinitializer
Comment Utility
well, you can insert before line 8, inside the loop, the following:

print_r($row);

this will print out the structure and contents of each row returned by the while loop.
Check the source of the page, for better text formatting of the output.
0
 

Author Comment

by:BrighteyesDesign
Comment Utility
Thanks for that,

With this...

mysql_select_db($database_acsinfo, $acsinfo);
$query_archive = "SELECT YEAR(`eventdate`) AS 'year', MONTH(`eventdate`) AS 'month', COUNT(`id`) AS 'count'  FROM `latestnews` GROUP BY YEAR(`eventdate`), MONTH(`eventdate`) DESC
";
$result = mysql_query($query_archive, $acsinfo) or die(mysql_error());

$data = array();
while($row = $result->fetch_assoc()) {
print_r($row);    $data[$row['year']][$row['month']] = $row['count'];
}
$result->free();

$data = array();
while($row = $result->fetch_assoc()) {
if( intval($row['count']) >0) $data[$row['year']][$row['month']] = $row['count'];

}

Open in new window


I just get a blank screen and no source code?

http://www.additionalcurates.co.uk/latestnews-archivetest.php
0
 
LVL 7

Expert Comment

by:designatedinitializer
Comment Utility
that means you have an error somewhere before that print_r statement, furthermore you have error reporting disabled.
try adding this on top of your file:

error_reporting(E_ALL);

if that doesn't make the error message show up, then you have to find your php.ini file and turn on error reporting there.

However, any errors will have been output to the error log file.
You have to find that file also. That will be revealed on the php.ini file also.
0
 
LVL 7

Expert Comment

by:designatedinitializer
Comment Utility
you can also try putting a die() statement in there.
That's how I pinpoint hard-to-get errors.
For instance, place this on line 2:

die("so far so good...");

Open in new window

it should display the message. If it doesn't, that means the error is located somewhere BEFORE the die() statement.
0
 

Author Comment

by:BrighteyesDesign
Comment Utility
Ah right, that's a good way of testing. It's shows the message right up until the beginning of line 7 so...

die("so far so good..."); while($row = $result->fetch_assoc()) {

Open in new window

is fine, whereas..

while($row = $result->fetch_assoc()) { die("so far so good..."); 

Open in new window


Shows an error.
0
 
LVL 7

Expert Comment

by:designatedinitializer
Comment Utility
haha! it's mysql_fetch_assoc() not fetch_assoc()
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 7

Expert Comment

by:designatedinitializer
Comment Utility
change that to

while($row = mysql_fetch_assoc($result)) ...

Open in new window

0
 

Author Comment

by:BrighteyesDesign
Comment Utility
Great, that's sorted that part but in turn has discovered another culprit!

die("so far so good..."); $result->free(); 

Open in new window

is fine but...

$result->free();  die("so far so good..."); 

Open in new window

shows an error
0
 
LVL 7

Expert Comment

by:designatedinitializer
Comment Utility
mysql_free_result($result);
0
 

Author Comment

by:BrighteyesDesign
Comment Utility
Right, we're nearly there (or should I say you're nearly there!)

The page now loads ok so I guess the query is ok. There's just nothing showing on the page. There should be a list of years and month with the amount of posts.

Here's the current code...
mysql_select_db($database_acsinfo, $acsinfo);
 $query_archive = "SELECT YEAR(`eventdate`) AS 'year', MONTH(`eventdate`) AS 'month', COUNT(`id`) AS 'count'  FROM `latestnews` GROUP BY YEAR(`eventdate`), MONTH(`eventdate`) DESC
";
$result = mysql_query($query_archive, $acsinfo) or die(mysql_error());

$data = array();
while($row = mysql_fetch_assoc($result)) { 
   $data[$row['year']][$row['month']] = $row['count'];
}
mysql_free_result($result);

$data = array();
while($row = mysql_fetch_assoc($result)) {
if( intval($row['count']) >0) $data[$row['year']][$row['month']] = $row['count'];

Open in new window


And the part that displays the results...

<?php foreach ($data as $year => $months) {
    echo $year.'<br>';
    foreach ($months as $month => $count) {
            echo $month.'('.$count.')<br>';
    }
} ?>

Open in new window

0
 
LVL 7

Expert Comment

by:designatedinitializer
Comment Utility
let's get back to putting

print_r($row);

inside the loop.
What does it print out?
0
 

Author Comment

by:BrighteyesDesign
Comment Utility
I get

Array ( [year] => 2009 [month] => 4 [count] => 1 ) Array ( [year] => 2011 [month] => 6 [count] => 1 ) Array ( [year] => 2012 [month] => 1 [count] => 1 )

Open in new window

0
 
LVL 7

Expert Comment

by:designatedinitializer
Comment Utility
That's right.
Everything's fine now.
If it doesn't work, there's a glitch somewhere else.
Check this out (it's basically the same thing you have, using your data, not the database), it works:
<?php
$rows = Array( Array ( 'year' => 2009, 'month' => 4, 'count' => 1 ), Array ( 'year' => 2011, 'month' => 6, 'count' => 1 ), Array ( 'year' => 2012, 'month' => 1, 'count' => 1 ));
$data = Array();
foreach($rows as $row){
	if( intval($row['count']) >0) $data[$row['year']][$row['month']] = $row['count'];
}
foreach ($data as $year => $months) {
    echo $year.'<br>';
    foreach ($months as $month => $count) {
            echo $month.'('.$count.')<br>';
    }
}
?>

Open in new window

0
 

Author Comment

by:BrighteyesDesign
Comment Utility
Any ideas what else it could be?
0
 
LVL 7

Expert Comment

by:designatedinitializer
Comment Utility
show me your code.
there's got be some tiny typo or something.
0
 

Author Comment

by:BrighteyesDesign
Comment Utility
Here's the whole code!

<?php require_once('Connections/acsinfo.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
	  $theValue = ($theValue != "") ? "'" . date("Y-m-d",strtotime($theValue)) . "'" : "NULL";
      break;
	case "time":
	  $theValue = ($theValue != "") ? "'" . date("H:i:s",strtotime($theValue)) . "'" : "NULL";
      break;
    case "datetime":
	  $theValue = ($theValue != "") ? "'" . date("Y-m-d H:i:s",strtotime($theValue)) . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}





$maxRows_latestnews = 4;
$pageNum_latestnews = 0;
if (isset($_GET['pageNum_latestnews'])) {
  $pageNum_latestnews = $_GET['pageNum_latestnews'];
}
$startRow_latestnews = $pageNum_latestnews * $maxRows_latestnews;

mysql_select_db($database_acsinfo, $acsinfo);
$query_latestnews = "SELECT id, name, subtitle, date_format(`eventdate`,'%D %M %Y') as `formattedDate`, image, `description` FROM latestnews ORDER BY eventdate DESC";
$query_limit_latestnews = sprintf("%s LIMIT %d, %d", $query_latestnews, $startRow_latestnews, $maxRows_latestnews);
$latestnews = mysql_query($query_limit_latestnews, $acsinfo) or die(mysql_error());
$row_latestnews = mysql_fetch_assoc($latestnews);

if (isset($_GET['totalRows_latestnews'])) {
  $totalRows_latestnews = $_GET['totalRows_latestnews'];
} else {
  $all_latestnews = mysql_query($query_latestnews);
  $totalRows_latestnews = mysql_num_rows($all_latestnews);
}
$totalPages_latestnews = ceil($totalRows_latestnews/$maxRows_latestnews)-1;

$maxRows_recent = 5;
$pageNum_recent = 0;
if (isset($_GET['pageNum_recent'])) {
  $pageNum_recent = $_GET['pageNum_recent'];
}
$startRow_recent = $pageNum_recent * $maxRows_recent;

mysql_select_db($database_acsinfo, $acsinfo);
$query_recent = "SELECT * FROM latestnews ORDER BY id ASC";
$query_limit_recent = sprintf("%s LIMIT %d, %d", $query_recent, $startRow_recent, $maxRows_recent);
$recent = mysql_query($query_limit_recent, $acsinfo) or die(mysql_error());
$row_recent = mysql_fetch_assoc($recent);

if (isset($_GET['totalRows_recent'])) {
  $totalRows_recent = $_GET['totalRows_recent'];
} else {
  $all_recent = mysql_query($query_recent);
  $totalRows_recent = mysql_num_rows($all_recent);
}
$totalPages_recent = ceil($totalRows_recent/$maxRows_recent)-1;


mysql_select_db($database_acsinfo, $acsinfo);
 $query_archive = "SELECT YEAR(`eventdate`) AS 'year', MONTH(`eventdate`) AS 'month', COUNT(`id`) AS 'count'  FROM `latestnews` GROUP BY YEAR(`eventdate`), MONTH(`eventdate`) DESC
";
$result = mysql_query($query_archive, $acsinfo) or die(mysql_error());

$data = array();
while($row = mysql_fetch_assoc($result)) { 
 print_r($row);  $data[$row['year']][$row['month']] = $row['count'];
}
mysql_free_result($result);

$data = array();
while($row = mysql_fetch_assoc($result)) {
if( intval($row['count']) >0) $data[$row['year']][$row['month']] = $row['count'];

}




?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title><?php echo $row_latestnews['name']; ?></title>
<meta name="description" content="Please find a list of the latest religious latestnews.">



<link href="css/acsinfo.css" rel="stylesheet" type="text/css" />
<link href="css/dropdown/dropdown.css" media="screen" rel="stylesheet" type="text/css" />
<link href="css/dropdown/themes/default/default.css" media="screen" rel="stylesheet" type="text/css" />



<link rel="stylesheet" media="all" href="css/reset.css" />

	
	<script type="text/javascript" src="js/islider/jquery-1.6.1.min.js"></script>

	<script type="text/javascript" src="js/script.js"></script>
</head>
<body>
<table width="216" border="0" align="center" cellpadding="0" cellspacing="0">
  <tr>
    <td><?php include("header.php"); ?></td>
  </tr>
  <tr>
    <td><table width="100%" border="0" cellpadding="0" cellspacing="0" class="middlewhite">
      <tr>
        <td align="center"><table width="971" border="0" cellpadding="0" cellspacing="0">
          <tr>
            <td width="678"><table width="100%" border="0" cellspacing="0" cellpadding="0">
              <tr>
                <td width="85%" valign="top"><table width="100%" border="0" cellpadding="20" cellspacing="0">
                  <tbody>
                    <tr>
                      <td width="819" valign="top" scope="row"></td>
                      </tr>
                    </tbody>
                </table>
                  <table width="100%" border="0" cellspacing="0" cellpadding="0">
                    <tr>
                      <td width="74%" align="center" valign="top"><?php do { ?>
                          <table width="95%" border="0" cellpadding="10" cellspacing="0" class="titleblue">
                            <tr>
                              <td width="71%" valign="top"><p><img src="admin/uploads/<?php echo $row_latestnews['image']; ?>" alt="Fr Darren" class="floatright"><span class="newstitle"><?php echo $row_latestnews['name']; ?></span></p>
                                <span class="sub"><?php echo $row_latestnews['subtitle']; ?></span>
                                <p><span class="greydate"><?php echo $row_latestnews['formattedDate']; ?></span><br>
                              </p>
                                <p><?php echo $row_latestnews['description']; ?></p>
                                </p></td>
                              </tr>
                          </table>
                          <?php } while ($row_latestnews = mysql_fetch_assoc($latestnews)); ?></td>
                      <td width="26%" valign="top"><table width="100" border="0" cellpadding="0" cellspacing="0" class="newsboxes">
                        <tr>
                          <td><table width="250" border="0" cellpadding="0" cellspacing="0" class="bottom10">
                            <tr>
                              <td><img src="images/blog-search.jpg" width="250" height="60" alt="recentposts" /></td>
                            </tr>
                            <tr>
                              <td align="center" bgcolor="#E6E6E6"><table width="88%" border="0" cellspacing="0" cellpadding="0">
                                <tr>
                                  <td><form id="form1" name="form1" method="get" action="newssearch.php">
                                    <label for="search"></label>
                                    <input name="search" type="text" class="bottom5" id="search" />
                                    <br />
                                    <input type="submit" name="button" id="button" value="Go" />
                                  </form></td>
                                </tr>
                              </table></td>
                            </tr>
                            <tr>
                              <td bgcolor="#E6E6E6">&nbsp;</td>
                            </tr>
                          </table></td>
                        </tr>
                        <tr>
                          <td><table width="251" border="0" cellpadding="0" cellspacing="0" class="bottom10">
                            <tr>
                              <td><img src="images/blog-recent.jpg" width="250" height="60" alt="recentposts" /></td>
                            </tr>
                            <tr>
                              <td align="center" bgcolor="#E6E6E6">
                                <table width="88%" border="0" cellpadding="0" cellspacing="0" class="recentline">
                                  <tr>
                                    <td class="recent"><?php do { ?>
                                        <a href="searchrecent.php?id=<?php echo $row_recent['id']; ?>" class="sidemenu"><?php echo $row_recent['name']; ?><br>
                                        </a>
                                        <?php } while ($row_recent = mysql_fetch_assoc($recent)); ?></td>
                                  </tr>
                                </table>
                               </td>
                            </tr>
                            <tr>
                              <td align="center" bgcolor="#E6E6E6">&nbsp;</td>
                            </tr>
                          </table></td>
                        </tr>
                        <tr>
                          <td><table width="251" border="0" cellpadding="0" cellspacing="0" class="bottom3">
                            <tr>
                              <td><img src="images/blog-archive.jpg" width="250" height="60" alt="recentposts" /></td>
                            </tr>
                            <tr>
                              <td align="center" bgcolor="#E6E6E6">
                                <table width="88%" border="0" cellpadding="0" cellspacing="0" class="recentline">
                                  <tr>
                                    <td><?php foreach ($data as $year => $months) {
    echo $year.'<br>';
    foreach ($months as $month => $count) {
            echo $month.'('.$count.')<br>';
    }
} ?></a></td>
                                  </tr>
                                </table></tr>
                            <tr>
                              <td align="center" bgcolor="#E6E6E6">&nbsp;</td>
                            </tr>
                          </table></td>
                        </tr>
                      </table></td>
                    </tr>
                  </table></td>
                </tr>
            </table></td>
            </tr>
          </table></td>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td><img src="images/bottomcurve.png" width="1029" height="25" /></td>
  </tr>
  <tr>
    <td><?php include("footer.php"); ?></td>
  </tr>
</table>
</body>
</html>
<?php
mysql_free_result($latestnews);

mysql_free_result($recent);

mysql_free_result($archive2);








?>

Open in new window

0
 
LVL 7

Expert Comment

by:designatedinitializer
Comment Utility
:)

take a look at lines 86-96...
you have double the while loop, but the second time you loop, you only end up reseting $data, so it becomes empty.

delete lines 86 to 90, and you're good to go.
0
 

Author Comment

by:BrighteyesDesign
Comment Utility
Ah yes! Schoolboy error!

One last thing, How do I show the actual month rather than the number?
Screen-shot-2012-03-31-at-09.28..png
0
 
LVL 7

Expert Comment

by:designatedinitializer
Comment Utility
echo "Month: ", date("F", mktime(0,0,0,$row['month'],1,2000));
echo "Abbbreviated Month: ", date("M", mktime(0,0,0,$row['month'],1,2000));
0
 

Author Comment

by:BrighteyesDesign
Comment Utility
Thanks for that,

How would be added to this?

<?php foreach ($data as $year => $months) {
    echo $year.'<br>';
    foreach ($months as $month => $count) {
            echo $month.'('.$count.')<br>';
    }
} ?>

Open in new window


Thanks for your patience!
0
 
LVL 7

Accepted Solution

by:
designatedinitializer earned 500 total points
Comment Utility
<?php foreach ($data as $year => $months) {
    echo $year.'<br>';
    foreach ($months as $month => $count) {
            echo date("F", mktime(0,0,0,$month,1,2000)).'('.$count.')<br>';
    }
} ?>

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
This article discusses four methods for overlaying images in a container on a web page
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now