0

I am using the following MySQL statement to echo out my recent changes from my table 'recent_activity'.

$query1 = "SELECT * FROM $table WHERE user_id = '{$_SESSION['id']}' AND date > NOW() - INTERVAL 30 DAY GROUP BY activity_type ORDER BY date DESC";
$result1 = mysql_query($query1);
while ($row1 = mysql_fetch_array($result1)) {

This is designed to echo out the recent changes a user has actioned.

In my table 'recent_activity' I have the following structure:

Id   |   User_Id     |    Date         |   Activity_type   |    status
1           1      20/02/2014  12:00:01    Password
2           1      21/02/2014  12:20:01    Bank 
3           1      22/02/2014  12:01:01    Username
4           1      20/02/2014  12:05:01    Password

my date field is a DATETIME format.

My query groups each activity type so will only ever display one of each action. This is fine, so i end up with the following:

Bank        Actioned on 'date'
Password    Actioned on 'date'
Username    Actioned on 'date'

The problem is I am am ordering my results according to date desc so this will show the recent changes in date descending order.

But if I change my password on the same day twice for instance

1 on the 20th February at 1pm and then a 2nd time on 20th February at 2pm

the 1pm password change is the one that gets echoed out and not my 2pm change?

Why is this? can I order my MySQL results by date and time?

Hope someone can help. thanks

Kevin Parks
  • 95
  • 2
  • 11

1 Answers1

0

Looks like your field is DATE formatted - have a look at http://dev.mysql.com/doc/refman/5.1/en/datetime.html you want to format the field as DATETIME

ALTER TABLE tablename MODIFY columnname DATETIME;

Joe Fitter
  • 1,309
  • 7
  • 11