?
Solved

DIsplay Archive for events PHP & MySQL

Posted on 2011-09-08
14
Medium Priority
?
384 Views
Last Modified: 2012-05-12
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!
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
  • 7
  • 6
14 Comments
 
LVL 24

Expert Comment

by:slyong
ID: 36508710
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36509860
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
 

Author Comment

by:BrighteyesDesign
ID: 36524914
Sorry for delay, i'll be looking at this tomorrow...
0
WordPress Tutorial 3: Plugins, Themes, and Widgets

The three most common changes you will make to your website involve the look (themes), the functionality (plugins), and modular elements (widgets).

In this article we will briefly define each again, and give you directions on how to install them.

 

Author Comment

by:BrighteyesDesign
ID: 36527668
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
 
LVL 24

Accepted Solution

by:
slyong earned 2000 total points
ID: 36527754
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
 

Author Comment

by:BrighteyesDesign
ID: 36527776
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
 
LVL 24

Expert Comment

by:slyong
ID: 36527799
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
 

Author Comment

by:BrighteyesDesign
ID: 36528389
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
 
LVL 24

Expert Comment

by:slyong
ID: 36528415

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
 

Author Comment

by:BrighteyesDesign
ID: 36528549
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
 
LVL 24

Expert Comment

by:slyong
ID: 36528939
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
 

Author Comment

by:BrighteyesDesign
ID: 36529213
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
 
LVL 24

Expert Comment

by:slyong
ID: 36529288
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
 

Author Comment

by:BrighteyesDesign
ID: 36529335
Ok great, it's pretty much there. I'll ask the other points in separate posts.

Thanks for your help
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
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…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

741 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