Solved

DIsplay Archive for events PHP & MySQL

Posted on 2011-09-08
14
377 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 108

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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article discusses four methods for overlaying images in a container on a web page
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
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 create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now