Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Show blog archive with PHP & MySQL

Posted on 2012-03-24
29
Medium Priority
?
292 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 13
29 Comments
 
LVL 7

Expert Comment

by:designatedinitializer
ID: 37760841
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
ID: 37760861
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
ID: 37760878
:)

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:BrighteyesDesign
ID: 37761068
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
ID: 37761404
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
ID: 37764796
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
ID: 37765196
...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
ID: 37771310
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
ID: 37771414
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
ID: 37771439
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
ID: 37771561
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
ID: 37771578
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
ID: 37771715
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
ID: 37771807
haha! it's mysql_fetch_assoc() not fetch_assoc()
0
 
LVL 7

Expert Comment

by:designatedinitializer
ID: 37771838
change that to

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

Open in new window

0
 

Author Comment

by:BrighteyesDesign
ID: 37771896
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
ID: 37771914
mysql_free_result($result);
0
 

Author Comment

by:BrighteyesDesign
ID: 37771978
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
ID: 37772059
let's get back to putting

print_r($row);

inside the loop.
What does it print out?
0
 

Author Comment

by:BrighteyesDesign
ID: 37772111
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
ID: 37772240
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
ID: 37788255
Any ideas what else it could be?
0
 
LVL 7

Expert Comment

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

Author Comment

by:BrighteyesDesign
ID: 37788304
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
ID: 37788828
:)

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
ID: 37790181
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
ID: 37790979
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
ID: 37791010
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 2000 total points
ID: 37791026
<?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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

618 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