[SOLVED] Sort results by date/time, limit 5 database entries of today &/or upcoming days

bigilworth · September 16, 2007

Dear phpfreak community,

I'm designing a website for my fraternity and in the database I have entries which contain past events, todays events, and upcoming events. My problem is that I can not figure out how to sort the query results to display:

1) only past events based on the CURRENT DATE

2) only the events which fall on the CURRENT DATE or after, limiting the results to 5 entries

3) all events that fall on CURRENT DATE or afterwards

Example:

Today is September 15, 2007

Problem 1) Displaying all events that happened before September 15, 2007

Problem 2) Displaying only 5 results that fall on September 15, 2007 or after

Problem 3) Displaying all events that are happening on or after September 15, 2007

And obviously I would like the CURRENT DATE to change depending on what date it is, currently.

Ok so now I've been very redundant in my explanation of my problem here is the code and a link to what that particular page looks like.

<?php 

include_once 'common.php';
include_once 'db.php';

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Gamma Kappa Chapter of Kappa Kappa Psi</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1">
<link href="res.css" rel="stylesheet" type="text/css">
</head>
<body>

<div id="main_content">
<?php
include_once ("header.php");
?>

<div id="side_panel">
<?php
include_once ("navigation2.php");
?>

<?php
include_once ("right_panel_non_login.php");
?>
</div>

<div id="middle"> 
<p><?php echo "<a href='create_event.php'>Create an Event</a>"; ?> | Upcoming Events | Past Events</p>
<hr class="hr_event" />

<h3>Events</h3><br />

<?php

  $result = @mysql_query("SELECT id, event_name, tagline, description, timestamp, month, date, year, hour, minute, meridiem, location, street, city, phone, email FROM event ORDER BY timestamp DESC LIMIT 5");

if (!$result)
  { exit('<p>Error performing query: ' . mysql_error() . '</p>'); }

// Display the information for each brother
while ($events = mysql_fetch_array($result)) {
  $id = $events['id'];
  $event_name = ($events['event_name']);
  $tagline = ($events['tagline']);
  $description = ($events['description']);
  $timestamp = ($events['timestamp']);
  $month = ($events['month']);
  $date = ($events['date']);
  $year = ($events['year']);
  $hour = ($events['hour']);
  $minute = ($events['minute']);
  $meridiem = ($events['meridiem']);
  $location = ($events['location']);
  $street = ($events['street']);
  $city = ($events['city']);
  $phone = ($events['phone']);
  $email = ($events['email']);

if ($minute<10)
$min = "0$minute"; // add the zero
else
$min = "$minute";


?>


<div id="event">
<div id="event_header">
<p>
<?
$d1=mktime($hour,$minute,0,$month,$date,$year);
echo date("F j, Y",$d1);
?>
</p>
</div>

<div id="event_body">
<p><font size="4" weight="bold" color="#FFFFFF"><?=$event_name?></font><br />
<span class="tiny">"<?=$tagline?>"</span><br /><br /></p>

<table>
<tr><td valign="top"><h5>Description:</h5></td><td><p><?=$description?></p></td></tr>
<tr><td id="td_event"><h5>Start Time:</h5></td><td><p><?=date("g:i a",$d1);?></p></td></tr>
<tr><td id="td_event"><h5>Location:</h5></td><td><p><?=$location?></p></td></tr>
<tr><td id="td_event"><h5>Street:</h5></td><td><p><?=$street?></p></td></tr>
<tr><td id="td_event"><h5>City:</h5></td><td><p><?=$city?></p></td></tr>
<tr><td id="td_event"><h5>Phone:</h5></td><td><p><?=$phone?></p></td></tr>
<tr><td id="td_event"><h5>Email:</h5></td><td><p><?=$email?></p></td></tr>
</table>
</div>
</div>

<?
}
?>

</div>
</div>
   </body>
   </html>

AND the link to the page: http://www.clarinetresource.com/kky/test.php

On this page you'll find what the code above displays. It displays the 5 entries whose dates have the largest timestamp integer, those being after TODAY, September 15, 2007.

One bit of clarification about how I set up my database. In the EVENT table I have a field named TIMESTAMP. The timestamp field's "type" is set to INT not TIMESTAMP OR VARCHAR. I am comfortable with changing my database fields/types around to make this work so don't shy from those types of responses.

Thank you,

Sincerely,

Rich

Barand · September 16, 2007

SELECT id, event_name, tagline, description, timestamp, 
    location, street, city, phone, email 
FROM event 
WHERE FROM_UNIXTIME(timestamp) >= CURDATE()
ORDER BY timestamp DESC LIMIT 5

If you have the date and time in the timestamp, why are you also storing day, month, year, hour, mins?

echo date ('F j, Y', $events['timestamp']):

bigilworth · September 16, 2007

Thanks for the help! =)

I changed some things in my database because someone else told me that I shouldn't name a field 'timestamp' as that is confusing. So I renamed my field 'tstamp' and changed all my code to reflect that.

Your code helped me get an idea. It didn't work on my server exactly as you wrote it. I forgot to mention I'm running php4 not php5. I don't know if that would have made a difference, but alas here is how I got it to work.

Problem 1) Displaying all events that happened before September 15, 2007

WHERE `tstamp` < UNIX_TIMESTAMP(CURRENT_DATE) ORDER BY tstamp DESC"

Problem 2) Displaying only 5 results that fall on September 15, 2007 or after

WHERE `tstamp` >= UNIX_TIMESTAMP(CURRENT_DATE) ORDER BY tstamp ASC LIMIT 5"

Problem 3) Displaying all events that are happening on or after September 15, 2007

WHERE `tstamp` >= UNIX_TIMESTAMP(CURRENT_DATE) ORDER BY tstamp ASC"

Thank you so much for your help. I feel much better now

Sign In

[SOLVED] Sort results by date/time, limit 5 database entries of today &/or upcoming days

Recommended Posts

bigilworth

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

bigilworth

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information