pfoster77 Posted July 2, 2014 Share Posted July 2, 2014 Hi all I want to display 5 records found in query and display by date order to most recent to todays date. Any help most appreciated. The php script below <?phpinclude 'c/config.php';include 'c/library/opendb.php';include 'c/library/timeFunctions.php';include 'c/library/displayFunctions2.php';$query = "SELECT * FROM events ORDER BY date,time";$result = mysql_query($query);while($row = mysql_fetch_assoc($result)){if ($row['date'] >= date('Y-m-d')){displayEventsCurrent($row);}}include 'c/library/closedb.php';?> Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted July 2, 2014 Share Posted July 2, 2014 You could modify the query so it's sorted in descending order using DESC. More information can be found here: http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html You could then use the LIMIT clause so the query only returns 5 results: http://www.mysqltutorial.org/mysql-limit.aspx Side note: in case you're not aware, the mysql_* functions have been deprecated. At some point in the near future, you'll need to switch to MySQLi or PDO. More information can be found here: http://www.php.net/manual/en/mysqlinfo.api.choosing.php Quote Link to comment Share on other sites More sharing options...
pfoster77 Posted July 2, 2014 Author Share Posted July 2, 2014 (edited) As above, it lists about 15 records after todays date but when I put in LIMIT 5 it displays no records. What could be wrong? Am working off the field `date` which is setup as a data type date, is this the problem? Edited July 2, 2014 by pfoster77 Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted July 2, 2014 Share Posted July 2, 2014 As above, it lists about 15 records after todays date but when I put in LIMIT 5 it displays no records. What does the query look like when you add the LIMIT clause? Have you tried using mysql_error() to see if the query is returning errors? Quote Link to comment Share on other sites More sharing options...
pfoster77 Posted July 2, 2014 Author Share Posted July 2, 2014 SELECT * FROM events ORDER BY date,time LIMIT 5 Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted July 2, 2014 Share Posted July 2, 2014 To get the information you want, you'll need to sort the results. Have you tried something like this: SELECT * FROM events ORDER BY date DESC, time DESC LIMIT 5 Quote Link to comment Share on other sites More sharing options...
pfoster77 Posted July 2, 2014 Author Share Posted July 2, 2014 the above works but displays the last available record which is 1st Nov 2014 but there is a record for 2nd July 2014 but when I change DESC to ASC, i get nothing returned. Quote Link to comment Share on other sites More sharing options...
Solution cyberRobot Posted July 2, 2014 Solution Share Posted July 2, 2014 Also, you could minimize the number of results being processed by the query with the WHERE clause. For example: $query = "SELECT * FROM events WHERE date >= '" . date('Y-m-d') . "' ORDER BY date DESC, time DESC LIMIT 5"; With the above query, you won't need the following if statement: if ($row['date'] >= date('Y-m-d')) Quote Link to comment Share on other sites More sharing options...
pfoster77 Posted July 2, 2014 Author Share Posted July 2, 2014 I put in the above and removed the if statement and changed DESC on both to ASC and it works beautifully. Thank you very much, I have been trying to solve this for days. Thanks again and you have a go at something so long, you miss the obvious thing! lol Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted July 2, 2014 Share Posted July 2, 2014 I put in the above and removed the if statement and changed DESC on both to ASC and it works beautifully. Sorry, I misread the question a bit. But I'm glad you figured it out though. Note that the ORDER BY clause defaults to ascending order, so the "ASC" isn't really necessary. Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted July 2, 2014 Share Posted July 2, 2014 It makes no sense to fetch the current date in PHP and insert it into the query. MySQL is very well able to do that itself: Date and Time functions in MySQL What you want is CURRENT_DATE(). Quote Link to comment Share on other sites More sharing options...
Psycho Posted July 2, 2014 Share Posted July 2, 2014 ^^ What he said Also, you could minimize the number of results being processed by the query with the WHERE clause. For example: $query = "SELECT * FROM events WHERE date >= '" . date('Y-m-d') . "' ORDER BY date DESC, time DESC LIMIT 5"; Use this SELECT * FROM events WHERE date >= CURDATE() ORDER BY date ASC, time ASC LIMIT 5 Also, using * in your select is typically a bad idea unless you really need all the fields. List out only the fields you need 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.