Date calcul

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Date calcul

Post 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..
Last edited by Draco_03 on Mon Aug 23, 2004 4:09 pm, edited 2 times in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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 :)
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Post by Draco_03 »

thx :) i'll give it a try
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Post 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
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Post by Draco_03 »

//bump
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it's probably better to run it everyday, and only ask for stuff that's a multiple of 7 days old..
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Post 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";
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Post by Draco_03 »

THX :)
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Post 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&#39;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
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Post 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 :/
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Post by Draco_03 »

Code: Select all

$sql_math = "SELECT DATE_SUB(`loanerin`,INTERVAL `loanerout` DAY) FROM loan_clients";
err still output

Code: Select all

Resource id #4
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

did you try to fetch the result, instead of just echoing the result from query?
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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>';
Post Reply