DIsplay Archive for events PHP & MySQL

I have a page on this website that shows past events...http://www.anjoman.co.uk/previous-monthly-meeting.php

As you can see the menu on the left is way too long now so I need to display this menu as an archive..Something like this... How the archive needs to work
All the data is in a MySQL database... database
Currently, the list that you see in the link above is created by the query..

mysql_select_db($database_anjoman, $anjoman);
$query_events = "SELECT id, title, `date`, invitation FROM events ORDER BY `date` DESC";
$events = mysql_query($query_events, $anjoman) or die(mysql_error());
$row_events = mysql_fetch_assoc($events);
$totalRows_events = mysql_num_rows($events);

and...

<?php do { ?>
                          <a href="previous-events-archive.php?date=<?php echo $row_events['date']; ?>" class="eventsdrop">» <?php echo $row_events['title']; ?>

So in a nutshell, I need to display past events as a dropdown archived by year.

What query would I use to get the info from the database and how would I display the results?

Thanks in Advance!
BrighteyesDesignAsked:
Who is Participating?
 
slyongCommented:
Hi BrighteyesDesign,

This is my database:

CREATE DATABASE `test`;
USE `test`;

CREATE TABLE `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) DEFAULT NULL,
  `eventdate` date DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `events` VALUES (1,'A','2011-02-01'),(2,'B','2010-12-07'),(3,'C','2009-01-15'),(4,'D','2010-11-02'),(5,'E','2010-05-06'),(6,'F','2010-01-12'),(7,'G','2009-11-05'),(8,'H','2009-10-01'),(9,'I','2009-09-03');

Open in new window


and to pull the records, I just use the code:

<?php
$curyear = date('Y');
$dbhandle = new mysqli('localhost', 'root', 'jSNAMW?3', 'test');
$result = $dbhandle->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 = $dbhandle->query("SELECT * FROM events WHERE eventdate like '$curyear%' ORDER BY eventdate DESC");
}
$dbhandle->close();

?>

Open in new window


You can check the result here: http://passport.e-solex.com/test.php
0
 
slyongCommented:
Would this work for you?

<?php

$curyear = date('Y');
$dbhandle = new mysqli('localhost', 'dbuser', 'dbpassword', 'db');
$result = $dbhandle->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 = $dbhandle->query("SELECT * FROM events WHERE eventdate like '$curyear%' ORDER BY eventdate DESC")
;
}
$dbhandle->close();

?>

Open in new window

0
 
Ray PaseurCommented:
archived by year tells me you would want some kind of GROUP and ORDER clause in the queries.

But I am curious about this block of code:

$events = mysql_query($query_events, $anjoman) or die(mysql_error());
$row_events = mysql_fetch_assoc($events);
$totalRows_events = mysql_num_rows($events);

That will run a query, then fetch only the first row of the results set, but will also get the total number of rows in the results set, which may be zero, one or more.  Why do you do that?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
BrighteyesDesignAuthor Commented:
Sorry for delay, i'll be looking at this tomorrow...
0
 
BrighteyesDesignAuthor Commented:
Hi Ray, that was just me trying something out, I wouldn't take much notice of that code. As I could not figure out the archive function I had to just show the results as a long list.

Thanks slyong that code shows a blank page with '2011' on it..http://www.anjoman.co.uk/previous-events-test2.php

I am using...



$curyear = date('Y');
$dbhandle = new mysqli('localhost', 'dbuser', 'dbpassword', 'db');
$result = $dbhandle->query("SELECT * FROM events WHERE date 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 = $dbhandle->query("SELECT * FROM events WHERE date like '$curyear%' ORDER BY eventdate DESC")
;
}
$dbhandle->close();


and the code below to display the results...

<?php do { ?>
                          <a href="previous-events-test2.php?date=<?php echo $row_curyear['date']; ?>" class="eventsdrop">» <?php echo $row_curyear['date']; ?></a><br />
                          <?php } while ($row_events = mysql_fetch_assoc($events)); ?>




0
 
BrighteyesDesignAuthor Commented:
Thanks for that, let me look again...

One thing though, currently I use this code to display the results, i'm pretty sure it's not correct, could you let me know what you are using?

<?php do { ?>
                          <a href="previous-events-test2.php?date=<?php echo $row_curyear['eventdate']; ?>" class="eventsdrop">» <?php echo $row_curyear['eventdate']; ?></a><br />
                          <?php } while ($row_events = mysql_fetch_assoc($events)); ?>
0
 
slyongCommented:
Hi,

The PHP code that I put does the display itself.  The "echo" in the while loop will print out the HTML codes.  You actually don't need to use a separate PHP program to do the display.

Is that a requirement to use a separate PHP program to do the display?
0
 
BrighteyesDesignAuthor Commented:
HI Slyong, it's not a requirement, I understand what you're saying now....

I have created that test database  database
and am using the query...

<?php
$curyear = date('Y');
$dbhandle = new mysqli('localhost', 'root', 'jSNAMW?3', 'test');
$result = $dbhandle->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 = $dbhandle->query("SELECT * FROM events WHERE eventdate like '$curyear%' ORDER BY eventdate DESC");
}
$dbhandle->close();

?>


but I get these results...http://www.anjoman.co.uk/previous-events-test2.php

Any ideas why I can't see the results like in your link? Do I need to change ' 'jSNAMW?3','? Is that a password or username to the database?
0
 
slyongCommented:

yeah, you are not connecting to your database properly


$dbhandle = new mysqli('localhost', 'root', 'jSNAMW?3', 'test');


root is the user jSNAMW?3 is the password
0
 
BrighteyesDesignAuthor Commented:
Thought so, I still can't connect, i'm trying my username and password to the database with no joy.

I do already have the connections file loading though so would I still need to connect to the database with this code? Can it be reworked without connecting the the database?
0
 
slyongCommented:
I am using mysqli instead of mysql to connect.  This programs includes everything, so you don't need the connection file anymore.

Just fill in the appropriate information in:

$dbhandle = new mysqli('dbhost', 'dbuser', 'dbpassword', 'db');
0
 
BrighteyesDesignAuthor Commented:
Great, I have the displays showing now...http://www.anjoman.co.uk/previous-events-test2.php

Just a couple of things.

Is it possible to initially collapse the menu so you only see the year then once you click on the year the titles show underneath?

Can years without events not show? (like 2011)

And the older dates from 2004 & 2005 do not show?

 database
0
 
slyongCommented:
You can probably control how many years you want to show.  Let's say you want to show 2011-2004, then just use the code:

while (mysqli_num_rows($result) > 0 || $curyear > 2004) {
0
 
BrighteyesDesignAuthor Commented:
Ok great, it's pretty much there. I'll ask the other points in separate posts.

Thanks for your help
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.