?
Solved

Date Compare

Posted on 2005-03-07
12
Medium Priority
?
383 Views
Last Modified: 2008-02-26
Hi experts,

I'm having a bit of a problem comparing a date in the past to dates in MySQL database. Here's what i'm trying to do:

$LastMonth = date("m")-1;
$Previous = date("Y-$LastMonth-d");
echo($Previous)
$QRY = mysql_query("SELECT * FROM Questions WHERE Created>$Previous ORDER BY Created DESC,Time DESC LIMIT $offset,$Limit ",$db) or die($errMsg);

The format of date in database is DATE as YYYY-MM-DD

It works it's just pulling up all entries in the database. Rather than those which have been entered within the last month. Help greatly appreciated.

Thanks

Craig
0
Comment
Question by:Newt6398
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 32

Expert Comment

by:Batalf
ID: 13475407
Try to replace

$LastMonth = date("m")-1;

with

$LastMonth = date("m",strtotime("LAST MONTH"));

0
 
LVL 49

Expert Comment

by:Roonaan
ID: 13475413
Try:

$Previous = date('Y-m', srttotime('-1 MONTHS'));

-r-
0
 
LVL 32

Expert Comment

by:Batalf
ID: 13475562
or

$Previous = date("Y-m-d",srttotime('LAST MONTH'));
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Newt6398
ID: 13477614
None have worked. Thanks 4 trying though. Any other ideas?????

cheers
0
 
LVL 49

Expert Comment

by:Roonaan
ID: 13477646
Try:

$Previous = date('Y-m-d', mktime(date('m')-1, 1, date('Y'));
$QRY = mysql_query("SELECT * FROM Questions WHERE Created>$Previous ORDER BY Created DESC,Time DESC LIMIT $offset,$Limit ",$db) or die($errMsg);
0
 
LVL 32

Expert Comment

by:Batalf
ID: 13477738
None of the works?

Then there has to be something else that's wrong. This is working when I test it.

$LastMonth = date("m",strtotime("LAST MONTH"));
$Previous = date("Y-$LastMonth-d");
echo($Previous);
$QRY = mysql_query("SELECT * FROM Questions WHERE Created>$Previous ORDER BY Created DESC,Time DESC LIMIT $offset,$Limit ",$db) or die($errMsg);
0
 
LVL 1

Author Comment

by:Newt6398
ID: 13478105
Here follows my full code. The database is functioning properly as it should with everything else. I tried your above examples but to no avail thus follows my code:






<html>
<head>
 <title>ASite.com</title>

</head>

<body>

<?

include("standard.inc");

?>


<table width=100%>
       
  <tr valign=top> <td bgcolor=E7EFF7><img src="images/spacer.gif" width=220 height=1></img>

<?



if (isset($SessUID) AND isset($SessPASS))
  {    
$ID = $SessUID;
$PWD = $SessPWD;

$db = mysql_connect("localhost", "username", "password");
 mysql_select_db("coders00_users",$db);
$SQL = mysql_query("SELECT * FROM users WHERE uid='$ID' AND pwd='$PWD'",$db);

     $NumRows = mysql_num_rows($SQL);

      if($NumRows > 0)
        {  include("UsrLoggedOn.inc");  }
      else
    {   include("UsrLoggedOff.inc"); }

  }
else
  {   include("UsrLoggedOff.inc"); }
?>


</td>
<td width=100%>
<table width=100%>
<td width=5%></td>
    <td width=90%><img src="images/spacer.gif"></img>


<?
//initialise variables
$Topic = preg_replace("/[^0-9]/","",$_GET['Topic']);
$TopicName = "None";
$db = 0;
$QRY = 0;
$File = 0;
$Title = 0;
$Subcat = $_GET['Subcat'];
$Subname = "General";
$Points = 0;


$Previous = date("m",strtotime("LAST MONTH"));
$LastMonth = date("Y-$Previous-d");
echo($LastMonth);


$db = mysql_connect("localhost", "coders00_craig", "s044336") or die($errMsg);
   mysql_select_db("coders00_users",$db) or die($errMsg);


$MaxRows = mysql_query("SELECT * FROM Questions WHERE Created>$LastMonth",$db) or die($errMsg);
$MaxPages = ceil(mysql_num_rows($MaxRows)/10);

if (isset($page) AND $page <= 10 AND $page <= $MaxPages)
{
$Page = preg_replace("/[^0-9]/","",$_GET['page']);
}
else
{
$Page = 1;
}

$Limit = 10;
$offset = ($Page-1)*$Limit;

 switch ($Topic)
 {
   case 1: $TopicName = "Topic1"; break;
   case 2: $TopicName = "Topic2"; break;
   case 3: $TopicName = "Topic3"; break;
   case 4: $TopicName = "Topic4";  break;
   case 5: $TopicName = "Topic5"; break;
   case 6: $TopicName = "Topic6";  break;
   case 7: $TopicName = "Topic7"; break;
   case 8: $TopicName = "Topic8"; break;
   case 9: $TopicName = "Topic9"; break;
   case 10: $TopicName = "Topic10"; break; break;
   case 11: $TopicName = "Topic11"; break;
   case 12: $TopicName = "Topic12";
 }


$errMsg = "<center>An error has occured please contact support with all relevant details.<br><a href='#' onclick=\"window.history.go(-1)\">GO BACK</a></center>";


?>

<table id='boxed' align='center' width=90%>
   

       <td><img src="images/spacer.gif"></img>

<table width=100%>
<tr><td align=right><pre>Page <?echo($Page)?> of <?echo($MaxPages);?></pre></td>
<tr><td>
<center><U>Current Topics</center></U><br><br>
<Form name="getquestion" value="0" action="view.php" METHOD='POST'>
<input type=Hidden name="view" value='0'></input>
</td></tr></table>

<?
$QRY = mysql_query("SELECT * FROM Questions WHERE Created>$LastMonth ORDER BY Created DESC,Time DESC LIMIT $offset,$Limit ",$db) or die($errMsg);

if ($offset > $MaxRows-10)
{
$offset = 0;
}

if (mysql_num_rows($QRY) > 0)
 {
echo("
<table  align=center width=90%>
  <tr>

    <td width=300 align=middle>
     <u>Title</u>
    </td>

    <td align=middle>
     <u>Points</u>
    </td>

    <td width=40></td>
 
    <td align=middle>
     <u>Author</u>
    </td>


</tr>
<tr height=5><td colspan=5><hr></td></tr>

<tr>
  <td>
    &nbsp
   </td>
</tr>
");

   while ($myrow = mysql_fetch_array($QRY))
   {
     $File = $myrow["File"];
     $Title = $myrow["Title"];
     $Points = $myrow["points"];
     $Author = $myrow["Uid"];
$Cr = $myrow["Created"];
echo("
<tr>

    <td>
        <a href='view.php?view=$File&Topic=$Topic&Subcat=$Subcat'>
         $Title</a>
    </td>
   
    <td align=middle>
     $Points $Cr
    </td>
   
    <td width=40></td>

    <td align=middle>
     <a href=\"http://www.ASite.com/profile.php?member=$Author\">$Author</a>
    </td>


</tr>
");

   }
echo("</table><br>");
?>

<table width=100%><tr width=100%><td align=middle>

<?
$i=0;

if (!($Page == 1))
{
$Prev = ($Page-1);
echo("<a href=\"http://www.Asite.com/selectTopic.php?Topic=$Topic&Subcat=$Subcat&page=$Prev\">&#171 previous</a> &nbsp ");
}

while ($i < $MaxPages)
{
 $i = $i+1;
  if (!($i == $Page))
  {
  echo(" <a href=\"http://www.ASite.com/selectTopic.php?Topic=$Topic&Subcat=$Subcat&page=$i\">$i</a> ");
  }
 }

if (!($Page == $MaxPages))
{
$Next = ($Page+1);
echo(" &nbsp <a href=\"http://www.ASite.com/index.php?page=$Next\"> next &#187</a>");
}

?>
</td></tr><tr><td> &nbsp </td></tr></table>

<?
 
}
else
 {
  echo("There are no topics currently available<br><br><br>");
 }
?>

</td>

  </table>

</form>

</td><td width=5%></td></table>

</td><td width=100% bgcolor=E7EFF7><img src="images/spacer.gif" width=200 height=1></img>

<?include("incright.inc");?>
</td>


</tr>
</table>


</body>
</html>
0
 
LVL 32

Expert Comment

by:Batalf
ID: 13478270
What do you see in your browser from this line

echo($LastMonth);

??
0
 
LVL 5

Expert Comment

by:prsupriya
ID: 13478280
Hello!

Try this...

1. Make sure that "Created" datatype is date.
2. Add 0 to the month if it's less than 10
3. Add quotes to the '$previous' in the query.

$LastMonth = date("m")-1;
if($LastMonth<=9){
 $LastMonth="0".$LastMonth; //add leading 0 if the month is less than or EQ 9
}
$Previous = date("Y-$LastMonth-d");

$LastMonth = date("m")-1;
$Previous = date("Y-$LastMonth-d");
echo($Previous);
$QRY = mysql_query("SELECT * FROM Questions WHERE Created>'$Previous' ORDER BY Created DESC,Time DESC LIMIT $offset,$Limit ",$db) or die($errMsg);

Hope this solves your problem.

Thanks,
S:
0
 
LVL 32

Accepted Solution

by:
Batalf earned 200 total points
ID: 13478316
You need more fixing than that if you go for that approach

$year = date("Y");
$LastMonth = date("m")-1;
if($lastMonth==0){
  $lastMonth=12;
  $year--;
}
if($LastMonth<=9){
  $LastMonth="0".$LastMonth; //add leading 0 if the month is less than or EQ 9
}

$Previous = date("$year-$LastMonth-d");
0
 
LVL 49

Assisted Solution

by:Roonaan
Roonaan earned 200 total points
ID: 13478348
Have you looked into mysql functions like

$QRY = '
   SELECT *
   FROM `Questions`
   WHERE `Created` > DATE_SUB(now(), INTERVAL 1 MONTHS)
   ORDER BY `Created` DESC, `Time` DESC
   LIMIT '.intval($offset).','.intval($Limit);

-r-
0
 
LVL 1

Author Comment

by:Newt6398
ID: 13478654
Must be something wrong with the data coming from the database. However i have managed to just put an if clause in so if created greated than the previous month then echo the information to screen this seems to be working. Abandoning question as i no longer have time to spend on this problem thank you for your help batalf and Roonaan. Points split.

Cheers
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question