Jump to content

PHP Mysql query issue?


pfoster77
Go to solution Solved by cyberRobot,

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by pfoster77
Link to comment
Share on other sites

  • Solution

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'))
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.  :happy-04:

 

Note that the ORDER BY clause defaults to ascending order, so the "ASC" isn't really necessary.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.