Solved

DIsplay Archive for events PHP & MySQL

Posted on 2011-09-08
14
380 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
  • 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 109

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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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 500 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
The viewer will learn how to dynamically set the form action using jQuery.
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…

821 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