Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DIsplay Archive for events PHP & MySQL

Posted on 2011-09-08
14
Medium Priority
?
386 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to create an extensible mechanism for linked drop downs.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

604 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