Display all years in archive query

I am trying to display a list of events by year (from the database image attached) as a dropdown menu.

Here's the test page..http://www.anjoman.co.uk/previous-events-test2.php

It's pretty much there using this query code...

<?php
$curyear = date('Y');
$dbhandle = new mysqli('localhost', 'anjomanc_bright', 'letitbe', 'london');
$result = "SELECT * FROM events WHERE eventdate like '$curyear%' ORDER BY eventdate DESC";

while (mysqli_num_rows($result) > 0 || $curyear == date('Y')) {
  echo "<b>".$curyear."</b></br>";
  while ($row = $result->fetch_object()) {
    echo "&nbsp;&nbsp;".$row->eventdate."&nbsp;".$row->title."</br>";
  }

  $curyear = $curyear - 1;
  $result = "SELECT * FROM events WHERE eventdate like '$curyear%' ORDER BY eventdate DESC";
}
$dbhandle->close();

?>

The problem is that is displays years when there are no events, is there a way to only show the year if there is an event to show?

 database
BrighteyesDesignAsked:
Who is Participating?
 
hieloCommented:
on my post (AND on YOUR original post cannot possible be working because it too has the same problem on that line) the third line should be:
$result = $dbhandle->query( ... );
<?php
$dbhandle = new mysqli('localhost', 'anjomanc_bright', 'letitbe', 'london');
$result = $dbhandle->query("SELECT e.*, DATE_FORMAT(`eventdate`,'%Y') as y FROM `events` e WHERE (`title` IS NOT NULL) AND Trim(`title`)!='' ORDER BY `eventdate` DESC");

$curyear = null;
while ($row = $result->fetch_object())
{
	if( $curyear!=$row->y )
	{
		$curyear=$row->y;
		echo '<b>', $curyear, '</b><br />';
	}
        echo '&nbsp;&nbsp;', $row->eventdate, '&nbsp;', $row->title, '<br />';
}
$dbhandle->close();
?>

Open in new window

0
 
Ray PaseurCommented:
Yes, I think so.  After the second query that says this:

$result = "SELECT * FROM events WHERE eventdate like '$curyear%' ORDER BY eventdate DESC";

Get the number of rows in $result and if that number is zero, use continue;
0
 
BrighteyesDesignAuthor Commented:
Thanks Ray, I'm not sure how to write that code, could you help?

The code in the question was put together by another expert not me!
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Ray PaseurCommented:
Maybe something like this... I do not have your data base so I cannot test the code, sorry.
<?php
$curyear = date('Y');
$dbhandle = new mysqli('localhost', 'anjomanc_bright', 'letitbe', 'london');
$result = mysqli_query("SELECT * FROM events WHERE eventdate like '$curyear%' ORDER BY eventdate DESC");

while (mysqli_num_rows($result) > 0 || $curyear == date('Y')) {
  echo "<b>".$curyear."</b></br>";
  while ($row = $result->fetch_object()) {
    echo "&nbsp;&nbsp;".$row->eventdate."&nbsp;".$row->title."</br>";
  }

  $curyear = $curyear - 1;
  $result = mysqli_query("SELECT * FROM events WHERE eventdate like '$curyear%' ORDER BY eventdate DESC");
  if (mysqli_num_rows($result) == 0) continue;
}
$dbhandle->close();

Open in new window

0
 
hieloCommented:
Try the attached code:
<?php
$dbhandle = new mysqli('localhost', 'anjomanc_bright', 'letitbe', 'london');
$result = "SELECT e.*, DATE_FORMAT(`eventdate`,'%Y') as y FROM `events` e WHERE (`title` IS NOT NULL) AND Trim(`title`)!='' ORDER BY `eventdate` DESC";

$curyear = null;
while ($row = $result->fetch_object())
{
	if( $curyear!=$row->y )
	{
		$curyear=$row->y;
		echo '<b>', $curyear, '</b><br />';
	}
        echo '&nbsp;&nbsp;', $row->eventdate, '&nbsp;', $row->title, '<br />';
}
$dbhandle->close();
?>

Open in new window

0
 
BrighteyesDesignAuthor Commented:
Thanks for that, both queries return blank pages though.

Hielo's code is live here  http://www.anjoman.co.uk/previous-events-test2.php

The original results are here...http://www.anjoman.co.uk/previous-events-test3.php
0
 
BrighteyesDesignAuthor Commented:
Thanks a lot for that!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.