Page 1 of 2
Date calcul
Posted: Thu Aug 19, 2004 3:08 pm
by Draco_03
I have a db that gets different information., one of em is when a piece has been sent and when it came back.
Since we're charging for the piece I need a mail to be sent 7 days after the piece has been sent.
So the best way to do this (i don't know really yet)
Is it to set my field in the db to datetime? Or simply put it to varchar.
Either way to calculate the time i guess i'll be using mktime to add 7 days to auto sent the email..
Nyways not really sure..
Posted: Thu Aug 19, 2004 3:39 pm
by feyd
I'd use a date/time type or an integer type, I'd also set up a daily/hourly cron job to run, that'd just query the database for anything that's a week old and hasn't been sent an email reminder yet

Posted: Thu Aug 19, 2004 4:13 pm
by Draco_03
thx

i'll give it a try
Posted: Fri Aug 20, 2004 2:06 pm
by Draco_03
Okay here I check if any datetimeout
This works, basically it chek if I have sent anything in the last 7 days
if I did so, well it'll send me a mail.
Here's the code
Code: Select all
<?php
include ("../includes/db.php");
$sql = "SELECT * FROM loan_clients WHERE (TO_DAYS(NOW())-TO_DAYS(`loanerout`))>7";
$result = mysql_query($sql);
$numrows = mysql_num_rows($result);
$count = 0;
if ($numrows == 0) {
echo ("No loaner has been sent since last time days");
}else{
for ($i=0, $numrows=mysql_num_rows($result); $i<$numrows; $i++){
$row = mysql_fetch_row($result);
//mail information here
if (!mail($to, $subject, $message, $headers)) {
echo("bleh");
}
$count++;
}
}
if ($count == 1){
echo ("You have been sent $count email");
}else{
echo ("You have been sent $count emails");
}
mysql_close($connect);
?>
Okay now What I think it's that I should add a field named flag (int) with 0 default value
When i would Send and item it would Update my field flag to 1
I make a check if the field has 1 meaning it has already been sent last time i cheked.
Then I could simply create a cronjob that runs every monday at 7am.
am I right ?
Next thing is when I received the loaner (field name loanerin)
It will also update flag to one. So it'll never send me an email every week about a loaner already been received.
I'm doig that so each time the recipient receive a mail he knows he has to charge an additonal fee for the loaner (since it hasn't been returned and we charge per week)
Dunno if I forgot something
thx
Posted: Mon Aug 23, 2004 8:28 am
by Draco_03
//bump
Posted: Mon Aug 23, 2004 9:53 am
by feyd
it's probably better to run it everyday, and only ask for stuff that's a multiple of 7 days old..
Posted: Mon Aug 23, 2004 12:27 pm
by Draco_03
Thx okay then To check multiplier of 7 is this code will work ?
Code: Select all
$sql = "SELECT * FROM loan_clients WHERE MOD((TO_DAYS(NOW())-TO_DAYS(`loanerout`)), 7)=0";
Posted: Mon Aug 23, 2004 12:31 pm
by feyd
would want to make sure it's not from today.. so
Code: Select all
SELECT * FROM loan_clients WHERE (TO_DAYS(NOW()) - TO_DAYS(`loaderout`)) >= 7 AND MOD((TO_DAYS(NOW())-TO_DAYS(`loanerout`)), 7)=0
Posted: Mon Aug 23, 2004 12:32 pm
by Draco_03
THX

Posted: Mon Aug 23, 2004 4:10 pm
by Draco_03
humm if I wanted to do the the time that the client kept the loaner
Code: Select all
$sql_math = "SELECT * FROM loan_clients WHERE (TO_DAYS(`loanerin`) - TO_DAYS(`loanerout`))";
$calcul = mysql_query($sql_math);
is that good ?
If it is how do I echo it properly ?
EDIT :
I found the DATEDIFF()
I'll try that
EDIT again :
<span style='color:blue' title='I'm naughty, are you naughty?'>smurf</span> :/
DATEDIFF() was added in MySQL 4.1.1.
I have my server uses 4.0.18
I found
Code: Select all
$sql_math = "SELECT * FROM loan_clients WHERE DATE_SUB(`loanerin`,INTERVAL `loanerout` DAY)";
but it output ressource id#4
Posted: Tue Aug 24, 2004 8:40 am
by Draco_03
mhh i know there s something wrong in the way im putting it, it s not logical but i don t know eactly how to put it in code :/
Posted: Tue Aug 24, 2004 9:55 am
by Draco_03
Code: Select all
$sql_math = "SELECT DATE_SUB(`loanerin`,INTERVAL `loanerout` DAY) FROM loan_clients";
err still output
Posted: Tue Aug 24, 2004 10:16 am
by feyd
did you try to fetch the result, instead of just echoing the result from query?
Posted: Tue Aug 24, 2004 10:35 am
by Draco_03
I just echoed the result of the string
Code: Select all
$sql_math = "SELECT DATE_SUB(`loanerin`,INTERVAL `loanerout` DAY) FROM loan_clients";
$calcul = mysql_query($sql_math);
and i echo $calcul
Posted: Tue Aug 24, 2004 10:39 am
by feyd
add
Code: Select all
$blah = '';
while($row = mysql_fetch_assoc($calcul)) $blah .= var_export($row,true)."\n";
echo '<pre>' . ( empty($blah) ? '<i>no results</i>' : $blah ) . '</pre>';