0

Hi,
I am developing a database for Lease management system, I have a field expire date of Lease agreement in my database.
Now if expiry date is 07-December-2009
I need alert about it 3 months ago means today.
So how I can write SQL Query for this purpose.
thanks in advance...

4
Contributors
21
Replies
35
Views
8 Years
Discussion Span
Last Post by ayesha789
0

I dont think you need to query a date 3 months ago, you need to find dates three months from now

strtotime() function
bogus brain dead almost code

$dateone=strtotime(now +3 month);
$datetwo=strtotime(now +4 month);
$result=mysql_query('select * from table where expiry > %s and expiry < %s, $dateone $datetwo');

as a thought process it may help
edit the strtotime strings to actually select the first and last day of the month, else the results may(will) vary throughout each day as "now" is very literal, changes every second
this code assumes the date is stored in a timestamp

if there arent records in your database yet, a timestamp column would be the best way to process dates and times, 10bytes to store the date and time, and much more efficient when extracting records by date or time, the selection is entirely numeric at the server and output easily

Edited by almostbob: n/a

0

Its nice , but I am little confused, if expiry date is 07-December-09 and I need alert today 3 months ago.

0

I am a landlord too..
you need to find expiry dates that are three months FROM now, now is the only date that is known, so search the database relative to now
select records based on the expiry being NOW +3 MONTH
the code scrap (badly) illustrates one method, selecting all records from the table with expiry dates between 3 months and four months from now(now is this absolute second), and suggested modifying the code to select from day 1 of +3 month to day last of +3 months to be able to batch all the month lease renewal / nonrenewal notices
there are code samples all over php.net and mysql.com on selecting date ranges.
I suggest that date will be the most used selector that the best format for date will be the native timestamp, a solely numeric representation that allows direct comparison, without manipulation of every record at every operation.

0

in my table there are above 4000 records in expiry field.
and I want to show alerts 3 months before.

0

I dont think you need to query a date 3 months ago, you need to find dates three months from now

strtotime() function
bogus brain dead almost code

$dateone=strtotime(now +3 month);
$datetwo=strtotime(now +4 month);
$result=mysql_query('select * from table where expiry > %s and expiry < %s, $dateone $datetwo');

as a thought process it may help
edit the strtotime strings to actually select the first and last day of the month, else the results may(will) vary throughout each day as "now" is very literal, changes every second
this code assumes the date is stored in a timestamp

if there arent records in your database yet, a timestamp column would be the best way to process dates and times, 10bytes to store the date and time, and much more efficient when extracting records by date or time, the selection is entirely numeric at the server and output easily

You probably want to set the next date to only one day ahead.

$dateone=strtotime(now +3 month);
$datetwo=strtotime(now +3 month + 1 day);

That way you can just run the query once a day, and send out the notifications.

0

in my table there are above 4000 records in expiry field.
and I want to show alerts 3 months before.

the code sample shows one way to select those records
not the only way
probably not the most efficient way.
I cannot see the database structure, if the date is stored as text (poor design, extra space and processing to make the database human readable -but humans don't read the database), the process may be different
but one way, what you do with the selected array is up to you
your concept of the selection process seems to me to be inverted
cannot go backwards from the expiry date, -all that is can do is give you a reminder of those you should have sent letters to three months ago.
has to find those records expiring three months from Now
looking at the records today, for the ones that expire date is three months in the future counting from today, to print the alert today

Edited by almostbob: n/a

0

Hi, if I select expiry date where month is December and year 2009
and I show this in September , is it right?

Please tell me why we need 2 dates for comparison according to your query.

0

I put two dates, to select a month span, from +3 months to plus 4 months
conceivably that would, if run on the first day of September produce all leases expiring in the month of December,
I have some leases that expire on the 26th of the month for example
then you could batch up a bunch of letters in one query

if(!$dateone} { $dateone=strtotime(+3 month); }
if(!$datetwo} { $datetwo=strtotime(+4 month); }
$result=mysql_query('select * from table where expiry > %s and expiry < %s, $dateone $datetwo');
while($row = mysql_fetch_array($result))  { 
$tenant = $row['name'];
$date = date(D M Y, $row['expiredate']);
  echo "<br />";
Echo "Dear $tenant, <br>";
."your fixed period lease expires on $date, If you would like to remain as a tenant after $date, we request you contact our office to pay the bribe<br>"; // humor 
."If we do not receive answer by the 15th of ".date(M, strtotime( 'Now + 1 month' )).", we will assume you wish to vacate at the end of the lease period and begin to show the property to other persons.<br>";
."thankyou<br>";
."Evil Landlord";
} ?>

only a single date restricts (in my mind) what you can do, the code above includes 2 if not statements at the dates, you could input the dates to choose on a form used to prepare the query, I dunno its 1 in the morning

Edited by almostbob: n/a

0

You probably want to set the next date to only one day ahead.

$dateone=strtotime(now +3 month);
$datetwo=strtotime(now +3 month + 1 day);

That way you can just run the query once a day, and send out the notifications.

it shows error = Parse error

0
<?php
$con = mysql_connect("localhost","root","root");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }


mysql_select_db("onm", $con);

$dateone=strtotime(now +3 month);
$datetwo=strtotime(now +3 month + 1 day);

$result = mysql_query("SELECT * FROM lease_center WHERE LAPeriodEnd > %s and LAPeriodEnd < %s, $dateone $datetwo");

while($row = mysql_fetch_array($result))
  {
  echo "<table cellpadding=2 cellspacing=2 width=100%>
<tr>


</tr>";
  echo "<tr>";
  echo "<th bgcolor=#FFCC00  width=250px>SiteID</th>";
  echo "<td bgcolor=#FEE9A9>" . $row['SiteId'] . "</td>";
    echo "</tr>";
  echo "<tr>";
						
  echo "<th bgcolor=#FFCC00>Owner Name</th>";
  echo "<td bgcolor=#FEE9A9>" . $row['OwnerName'] . "</td>";
  
    echo "</tr>";
	
	echo "<tr>";
	echo "<th bgcolor=#FFCC00>Lease Agreement</th>";
	 echo "<td bgcolor=#9BCB5D><a href=".$row['LA']." target=_blank>".$row['ch']."</a></td>";
	  

	  echo "</tr>";
	  
	
	
	echo "<tr>";
  echo "<th bgcolor=#FFCC00>LA Start Date</th>";
  echo "<td bgcolor=#FEE9A9>" .date("j-F-Y",strtotime($row['LAPeriodStart'])). "</td>";
    echo "</tr>";
	
	echo "<tr>";
  echo "<th bgcolor=#FFCC00>LA End Date</th>";
  echo "<td bgcolor=#FEE9A9>" .date("j-F-Y",strtotime($row['LAPeriodEnd'])). "</td>";
    echo "</tr>";
	
	  echo "<tr>";
	echo "<th bgcolor=#FFCC00>Purchase Order</th>";
	 echo "<td bgcolor=#CB9B5D><a href=".$row['PO']." target=_blank>".$row['ch']."</a></td>";

	  echo "</tr>";
	  
	
	 echo "<tr>";
  echo "<th bgcolor=#FFCC00>PO Start Date</th>";
  echo "<td bgcolor=#FEE9A9>" .date("j-F-Y",strtotime($row['POStartDate'])). "</td>";
    echo "</tr>";
	
	 echo "<tr>";
  echo "<th bgcolor=#FFCC00>PO End Date</th>";
  echo "<td bgcolor=#FEE9A9>" .date("j-F-Y",strtotime($row['POEndDate']))  . "</td>";
    echo "</tr>";
	
	 echo "<tr>";
						
  echo "<th bgcolor=#FFCC00>Bank AC No.</th>";
  echo "<td bgcolor=#FEE9A9>" . $row['BankACNo'] . "</td>";
  
    echo "</tr>";
	
if($row['SPA']!='N.R')
{
	  echo "<tr>";
	echo "<th bgcolor=#FFCC00>SPA</th>";
	 echo "<td bgcolor=#CB9B5D><a href=".$row['SPA']." target=_blank>".$row['ch']."</a></td>";

	  echo "</tr>";
}	
	 $b =$row['SiteId']; 
  }
echo "</table>";


mysql_close($con);

?>

it shows error
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in D:\xampp\htdocs\c.php on line 29

0
$dateone=strtotime(now +3 month);
$datetwo=strtotime(now +4 month);

it also showing parse error.
i copy and paste ur code and change the necessary fields and table name but parse error still there.

0
$dateone=strtotime(now +3 month);
$datetwo=strtotime(now +4 month);

it also showing parse error.
i copy and paste ur code and change the necessary fields and table name but parse error still there.

I messed up
Now is a Mysql/Sql construct not php, strtotime is strings in php the code should work as

$dateone=strtotime('+3 month');
$datetwo=strtotime('+4 month');

Its one in the morning now so I missed the obvious.

Please don't notice that this is code I messed up Yesterday :P

Edited by almostbob: n/a

0
<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }


mysql_select_db("onm", $con);

$result = mysql_query("SELECT * FROM lease_center WHERE LAPeriodEnd=(current_date() - INTERVAL 3 MONTH))");

while($row = mysql_fetch_array($result))
  {
  echo "<table cellpadding=2 cellspacing=2 width=100%>
<tr>


</tr>";
  echo "<tr>";
  echo "<th bgcolor=#FFCC00  width=250px>SiteID</th>";
  echo "<td bgcolor=#FEE9A9>" . $row['SiteId'] . "</td>";
    echo "</tr>";
  echo "<tr>";
						
  echo "<th bgcolor=#FFCC00>Owner Name</th>";
  echo "<td bgcolor=#FEE9A9>" . $row['OwnerName'] . "</td>";
  
    echo "</tr>";
	
	echo "<tr>";
	echo "<th bgcolor=#FFCC00>Lease Agreement</th>";
	 echo "<td bgcolor=#9BCB5D><a href=".$row['LA']." target=_blank>".$row['ch']."</a></td>";
	  

	  echo "</tr>";
	  
	
	
	echo "<tr>";
  echo "<th bgcolor=#FFCC00>LA Start Date</th>";
  echo "<td bgcolor=#FEE9A9>" .date("j-F-Y",strtotime($row['LAPeriodStart'])). "</td>";
    echo "</tr>";
	
	echo "<tr>";
  echo "<th bgcolor=#FFCC00>LA End Date</th>";
  echo "<td bgcolor=#FEE9A9>" .date("j-F-Y",strtotime($row['LAPeriodEnd'])). "</td>";
    echo "</tr>";
	
	  echo "<tr>";
	echo "<th bgcolor=#FFCC00>Purchase Order</th>";
	 echo "<td bgcolor=#CB9B5D><a href=".$row['PO']." target=_blank>".$row['ch']."</a></td>";

	  echo "</tr>";
	  
	
	 echo "<tr>";
  echo "<th bgcolor=#FFCC00>PO Start Date</th>";
  echo "<td bgcolor=#FEE9A9>" .date("j-F-Y",strtotime($row['POStartDate'])). "</td>";
    echo "</tr>";
	
	 echo "<tr>";
  echo "<th bgcolor=#FFCC00>PO End Date</th>";
  echo "<td bgcolor=#FEE9A9>" .date("j-F-Y",strtotime($row['POEndDate']))  . "</td>";
    echo "</tr>";
	
	 echo "<tr>";
						
  echo "<th bgcolor=#FFCC00>Bank AC No.</th>";
  echo "<td bgcolor=#FEE9A9>" . $row['BankACNo'] . "</td>";
  
    echo "</tr>";
	
if($row['SPA']!='N.R')
{
	  echo "<tr>";
	echo "<th bgcolor=#FFCC00>SPA</th>";
	 echo "<td bgcolor=#CB9B5D><a href=".$row['SPA']." target=_blank>".$row['ch']."</a></td>";

	  echo "</tr>";
}	
	 $b =$row['SiteId']; 
  }
echo "</table>";


mysql_close($con);

?>

this shows error
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in D:\xampp\htdocs\c.php on line 19
I am confused why?

0

Try this,

$result = mysql_query("SELECT * FROM lease_center WHERE LAPeriodEnd=(current_date() - INTERVAL 3 MONTH))");

if($result) {
   while($row = mysql_fetch_array($result)) {
    ....   
   }
}
0

Adatapost this query only shows alerts for 09 dec 2009
and today its shows alerts but next day it will miss some alerts, if someone miss one day then next day he will not get alert again.


please check Purchase Order will expire in Decemeber and
I want alert 3 months ago means in september
if its will expire in novemeber then alert in august.

0

Use >= or <= operator instead of =.

please guide me. adtatpost please . due to one query my project is near failure.

0

please guide me. adtatpost please . due to one query my project is near failure.

please check my date alert thread please

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.