Date Compare

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
LVL 1
Newt6398Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
BatalfConnect With a Mentor Commented:
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
 
BatalfCommented:
Try to replace

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

with

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

0
 
RoonaanCommented:
Try:

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

-r-
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
BatalfCommented:
or

$Previous = date("Y-m-d",srttotime('LAST MONTH'));
0
 
Newt6398Author Commented:
None have worked. Thanks 4 trying though. Any other ideas?????

cheers
0
 
RoonaanCommented:
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
 
BatalfCommented:
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
 
Newt6398Author Commented:
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
 
BatalfCommented:
What do you see in your browser from this line

echo($LastMonth);

??
0
 
prsupriyaCommented:
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
 
RoonaanConnect With a Mentor Commented:
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
 
Newt6398Author Commented:
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
All Courses

From novice to tech pro — start learning today.