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...
ayesha789 7 Posting Pro in Training
almostbob 866 Retired: passive income ROCKS
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
ayesha789 7 Posting Pro in Training
Its nice , but I am little confused, if expiry date is 07-December-09 and I need alert today 3 months ago.
almostbob 866 Retired: passive income ROCKS
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.
ayesha789 7 Posting Pro in Training
in my table there are above 4000 records in expiry field.
and I want to show alerts 3 months before.
digital-ether 399 Nearly a Posting Virtuoso Team Colleague
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 timestampif 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.
almostbob 866 Retired: passive income ROCKS
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
ayesha789 7 Posting Pro in Training
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.
almostbob 866 Retired: passive income ROCKS
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
ayesha789 7 Posting Pro in Training
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
ayesha789 7 Posting Pro in Training
<?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
ayesha789 7 Posting Pro in Training
$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.
almostbob 866 Retired: passive income ROCKS
$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
kvprajapati 1,826 Posting Genius Team Colleague
ayesha789 7 Posting Pro in Training
<?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?
ayesha789 7 Posting Pro in Training
many times I faced this error please guide me.
kvprajapati 1,826 Posting Genius Team Colleague
Try this,
$result = mysql_query("SELECT * FROM lease_center WHERE LAPeriodEnd=(current_date() - INTERVAL 3 MONTH))");
if($result) {
while($row = mysql_fetch_array($result)) {
....
}
} ayesha789 7 Posting Pro in Training
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.
kvprajapati 1,826 Posting Genius Team Colleague
Use >= or <= operator instead of =.
ayesha789 7 Posting Pro in Training
Adatapost please guide me , do u understand my problem?
ayesha789 7 Posting Pro in Training
Use >= or <= operator instead of =.
please guide me. adtatpost please . due to one query my project is near failure.
ayesha789 7 Posting Pro in Training
please guide me. adtatpost please . due to one query my project is near failure.
please check my date alert thread please
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.