PHP Mysql query issue?

[pfoster77]

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

 

<?php
include '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';
?>

[cyberRobot]

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

[pfoster77]

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

[cyberRobot]

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?

[pfoster77]

SELECT * FROM events ORDER BY date,time LIMIT 5

[cyberRobot]

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

[pfoster77]

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.

[cyberRobot]

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'))

[pfoster77]

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

[cyberRobot]

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.

[Jacques1]

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().

[Psycho]

^^ 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