asked on

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

Batalf

Try to replace

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

with

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

Roonaan

Try:

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

-r-

Batalf

or

$Previous = date("Y-m-d",srttotime('LAST MONTH'));

Newt6398

ASKER

None have worked. Thanks 4 trying though. Any other ideas?????

cheers

Roonaan

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);

Batalf

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);

Newt6398

ASKER

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>

Batalf

What do you see in your browser from this line

echo($LastMonth);

??

prsupriya

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:

ASKER CERTIFIED SOLUTION

Batalf

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Roonaan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Newt6398

ASKER

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