CODEWITHSUNDEEP
javascriptmysql
cloud soft
cloud soft

Reputation: 195

difference between given date and current date

I have a table message, in which fields are: id,username,message,lastdate. I want to compare from these dates (which are in my table) to current date. If difference is 30 days then respected username should be show in admin page. I tried for date difference, but its not working.

 <?php
    include("connection.php");
    $queryd=mysql_query("select * from message") or die(mysql_error());
    while($resultd=mysql_fetch_array($queryd))
    {
    extract($resultd);
    $date1=date('Y-m-d');
    $date2=$lastdate;

    $diff=date_diff($date2,$date1);
    echo $diff->format("%R%a days");
    }
       ?>

Also I tried from this code. But nothing happend

<?php
    include("connection.php");
    $queryd=mysql_query("select * from message") or die(mysql_error());
    while($resultd=mysql_fetch_array($queryd))
    {
    extract($resultd);
    $date1=date('Y-m-d');
    $date2=$lastdate;


    $query12=mysql_query("SELECT username,DATEDIFF($date1,$date2) FROM message WHERE DATEDIFF($date1,$date2)<30") or die(mysql_error());
    while($result12=mysql_fetch_array($query12))
    {
        if($result12)
    echo $username; 
    else
    echo"record not";
    }
    }
?

My third solution is here. I think it is working. But It is repeating values.

<?php
    include("connection.php");
    $queryd=mysql_query("select * from message") or die(mysql_error());
    while($resultd=mysql_fetch_array($queryd))
    {
    extract($resultd);
    $date1=date('Y-m-d');
    $date2=$lastdate;
    $days = (strtotime($date2) - strtotime($date1)) / (60 * 60 * 24);

    $result123=mysql_query("select username from message where '$days'<30");
    while($query123=mysql_fetch_array($result123))
  {
if($query123)
echo $username."&nbsp".$days."<br>";
else
echo mysql_error();
    }
    }

?>

Upvotes: 0

Views: 70

Answers (1)

O. Jones
O. Jones

Reputation: 108651

You can do this date comparison inside your MySQL DBMS.

Try this query:

     SELECT username, message FROM message WHERE lastdate <= CURDATE() - INTERVAL 30 DAY

It should return just the rows of interest.

Upvotes: 1

Related Questions