edoplaza Posted November 3, 2009 Report Share Posted November 3, 2009 Hello, I know it's possible to display records ordered by date, but what if I want to display only future dates counting from the present day? PHP should first calculate the present date and then display dates from there. How do I do that? With a conditional statement? is there a function for that? Example: I have these records stored in my DB: record1 2009/10/02 record2 2009/10/05 record3 2009/10/08 record4 2009/10/18 record5 2009/11/04 record6 2009/11/10 record7 2009/12/07 If the present day is 2009/10/17 I just want to display: record4 2009/10/18 record5 2009/11/04 record6 2009/11/10 record7 2009/12/07 but If the present day is 2009/11/01 I just want to display: record5 2009/11/04 record6 2009/11/10 record7 2009/12/07 Any help will be appreciated, Regards, Eduardo Quote Link to comment Share on other sites More sharing options...
falkencreative Posted November 4, 2009 Report Share Posted November 4, 2009 Here's a quick sample that's been tested and works for me... Database (very basic, just includes two fields -- a unique id and a "date" field with 00-00-00 00:00:00 format) -- phpMyAdmin SQL Dump -- version 3.2.0.1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Nov 03, 2009 at 10:18 PM -- Server version: 5.1.37 -- PHP Version: 5.2.11 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; -- -- Database: `test` -- -- -------------------------------------------------------- -- -- Table structure for table `test` -- CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `date` date NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; -- -- Dumping data for table `test` -- INSERT INTO `test` VALUES(1, '2009-11-04'); INSERT INTO `test` VALUES(2, '2009-11-28'); INSERT INTO `test` VALUES(3, '2009-11-02'); INSERT INTO `test` VALUES(4, '2009-11-05'); INSERT INTO `test` VALUES(5, '2009-11-25'); The PHP: // Database Variables $server = 'localhost'; $user = 'root'; $pass = 'root'; $db = 'test'; // Connect to Database $connection = mysql_connect($server, $user, $pass) or die ("Could not connect to server ... \n" . mysql_error ()); mysql_select_db($db) or die ("Could not connect to database ... \n" . mysql_error ()); // show dates larger than current date $currentdate = date("Y-m-d H:i:s", time()); $result = mysql_query("SELECT * FROM test WHERE date > '$currentdate' ORDER BY date") or die ("error!"); while($row = mysql_fetch_array( $result )) { // Print out the contents of each row echo $row['date'] . " "; } ?> Quote Link to comment Share on other sites More sharing options...
edoplaza Posted November 5, 2009 Author Report Share Posted November 5, 2009 Hello, Thanks for your answer. I'll give it a try. Meanwhile I came up with this: <?php do { ?> <?php $exp_date = $row_Recordset1['date']; $todays_date = date("Y-m-d"); $today = strtotime($todays_date); $expiration_date = strtotime($exp_date); if ($expiration_date > $today) { echo $row_Recordset1['date']; } ?> It works perfectly. The only thing to do now is to show only the first 3 records of that selection: let's say I have 20 records in total. Events from 1 to 7 are "past" events. Events from 8 to 20 are "upcoming" events. With my code, php will display only records 8 to 20. Now I just need to display only the first 3 records of those upcoming events: 8 9 10 Fot that, I was thinking about adding and extra condition with a counter, something like: $counter=0; if (($expiration_date > $today) ) && ($counter <4)) { echo $row_Recordset1['date']; $counter=$counter+1; } What do you think? I'll try that later on my page. Thanks Eduardo Quote Link to comment Share on other sites More sharing options...
edoplaza Posted November 5, 2009 Author Report Share Posted November 5, 2009 (edited) Well, after many tests, I found it! You can see it here: http://ww'>http://ww w.germanmarcano.com/indexEnglish.php compare it to http://ww w.germanmarcano.com/eventos/indexEnglish.php On the second link you can see the list of full events. On the other hand, on the first link, the "upcoming events" section is only displaying the first 3 events, counting from the present date... Here is the code (just the important part): <?php $counter=0; do { $exp_date = $row_RsEventos['fecha']; $todays_date = date("Y-m-d"); $today = strtotime($todays_date); $expiration_date = strtotime($exp_date); if (($expiration_date > $today) && ($counter<3) ){ ?> <?php echo $row_RsEventos['titulo']; ?> Date:<?php echo $row_RsEventos['fecha']; ?>Place:<?php echo $row_RsEventos['lugar']; ?>Time:<?php echo $row_RsEventos['hora']; $counter++; ?> Details... <?php } } while ($row_RsEventos = mysql_fetch_assoc($RsEventos)); ?> Now I just need to convert the yyyy-mm-dd format into a more friendly mm-dd-yyyy one. Cheers, Eduardo Edited November 5, 2009 by edoplaza Quote Link to comment Share on other sites More sharing options...
falkencreative Posted November 5, 2009 Report Share Posted November 5, 2009 Your sample works, but I wouldn't say it is very efficient. The less lines of code you are using, the more straight-forward the logic (in general, at least) and hopefully the easier to update. You've done with a lot of lines what I can do with (basically) two lines: $currentdate = date("Y-m-d H:i:s", time()); $result = mysql_query("SELECT * FROM test WHERE date > '$currentdate' ORDER BY date LIMIT 3") Obviously, that doesn't actually display the dates... you'll need to loop through using "while($row = mysql_fetch_array( $result ))" but with one SQL statement, I only get three results from the database that are in the future. Use the SQL statement to do the heavy lifting for you -- there's really no need to mess with counters and if statements if you can do it using SQL. Quote Link to comment Share on other sites More sharing options...
edoplaza Posted November 5, 2009 Author Report Share Posted November 5, 2009 Hi again. Yes I know. But in my case, efficience will come after learning. I'm just a beginner in PHP . I'll try to to adapt what you suggest into my code. Thanks! Eduardo Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.