calculating days late

[ckdoublenecks]

I need to calculate the # of days late (dayslate)(# of days from the duedate to the current date. Below is my code.

<?php
$stat = mysql_connect("localhost","root","");
$stat = mysql_select_db("oodb");
$query = "SELECT charges, dayslate FROM oocust Where WHERE payrec = 'R' AND pd = ' '";
$stat = @mysql_fetch_assoc(mysql_query($query));
echo $stat["charges"];
$result= mysql_query("select * from oocust");
while($row=mysql_fetch_array($result))
 {
$id=$row['id'];
$dayslate=$row['dayslate'];
$duedate=$row['duedate'];
$tax=$row['tax'];

// sysdate = date("m/d/y");
// if(strtotime($sysdate) > (strtotime($row["duedate"]) +2600000)

}
$sql = "UPDATE oocust SET
tax = charges * .06,
dayslate = '$dayslate'
WHERE id='$id'";
mysql_query($sql) ;
$err=mysql_error();
if($err!=""){
 echo "Error in $sql: $err\n";
}
}
echo "Records have been updated";
?>

[mbabuskov]

and the point of your message is?

[ckdoublenecks]

I'm trying to learn PHP and I would like some suggestions re. this code. Could I inquire where you're from?

[mbabuskov]

I don't see a question in the whole message. Does the code work? Do you really have dayslate column in the table? If so, I don't see a need to calculate it. It is somewhat unclear what are you actually trying to accomplish.

I'm from Subotica, Serbia.

[ckdoublenecks]

Ok, friend I assumed you were from another culture, the way you communicate. Here we consider your verbage a little curt. The statement

"I need to calculate the # of days late (dayslate)(# of days from the duedate to the current date. Below is my code."

was intended to solicit help. I'm sure the code doesn't work because from the implied question, I'm trying to get someone who knows how, to help me with the code

" to calculate the # of days late (dayslate)(# of days from the duedate to the current date".

The table does, in fact, have the necessary fields in it because, as a neophite, I know that the program can't work otherwise.
What I'm trying to accomplish is to read the duedate in the database,

"calculate the # of days late (dayslate)(# of days from the duedate to the current date"

& insert that value into that same database. How about it? Do you know how & are you willing to disclose that knowledge ?

[mbabuskov]

Well, I'm still not completely sure what exactly you need, but here's a tip: It seems to me that there is need to do all this wiring in the code. You can select and update in the same statement:

UPDATE oocust SET dayslate = (select dayslate from ...)
WHERE ...etc.

HTH

[ckdoublenecks]

thanks for the suggestion. It sounds great. I'm just learning this so I'll do some more research. not sure what HTH stands for?

[mbabuskov]

It's Internet jargon. HTH = Hope this helps.

[ckdoublenecks]

I don't see how your suggestion will help determine the dayslate. Sorry

[ckdoublenecks]

After days of research I found this code which is promising but how do I apply the start date(duedate) and the current date (currdate)? Anyone?

// calculations to determine number of days late current date minus duedate
function networkdays($s, $e) 
 {
// If the start and end dates are given in the wrong order, flip them. 
 if ($s > $e)
  return networkdays($e, $s);
// Find the day of the week for the two dates.
 $sd = date("d", $s);
 $ed = date("d", $e);

// Find the number of weeks between the dates.
 $w = floor(($e - $s)/(86400*7)); # Divide the difference in the two times by seven days to get the number of weeks.
 if ($ed >= $sd) { $w--; }  # If the end date falls on the same day of the week or a later day of the week than the start date, subtract a week.

// Calculate net working days.
 $nwd = max(6 - $sd, 0); # If the start day is Saturday or Sunday, add zero, otherewise add six minus the weekday number.
 $nwd += min($ed, 5); # If the end day is Saturday or Sunday, add five, otherwise add the weekday number.
 $nwd += $w * 5;  # Add five days for each week in between. }

 return $nwd;
 }
$duedate = strtotime("1 January 2010"); // duedate
$currdate = strtotime("13 December 2010"); // current date

[rocknbil]

What mbabuskov is trying to tell you (I think, if not, it's what I'm saying) is that there is no need to do in PHP what is already inherent in mysql. You can do day math right in yuor select statements. Something like this (not debugged, typed on the fly, for example only)

Your second attempt goes down the same road, doing in PHP what can be done in mysql.

$db = mysql_connect("localhost","root",""); 
$db = mysql_select_db("oodb"); 
$query = "SELECT id, charges, datediff(curdate(),duedate) as dayslate, tax FROM oocust Where WHERE payrec = 'R' AND pd = ' '"; 
$result = mysql_query($query)) or die ("could not execute query at line 4"); // see note 
while ($row = mysql_fetch_assoc($result)) { 
 $id = $row['id']; 
 $days_late = ($row['dayslate'] > 0)?$row['dayslate'] . " days":'N/A'; 
 echo "<p> Charges: " . $stat['charges'] . " Due Date: " . $row['duedate'] .  
 " Late: $days_late Tax: " . $row['tax'] . "</p>"; 
 // ADD YOUR UPDATE STATEMENTS TO RECORD $id HERE, within the while loop. 
} 

DateDiff [dev.mysql.com]

returns expr1 – expr2 expressed as a value in days from one date to the other.

Note: Remove @ from your mysql queries. This suppresses any errors you may recieve. You can optionally add mysql_error like this,
$result = mysql_query($query)) or die ("could not execute query at line 4 " . mysql_error());

But remove it from the script before deployment, if triggered it reveals information about your database structure.

Also note how easy it is to combine two selects into one by efficient field selection, * is often uneccessary overhead. Done this way, you almost don't even need to store and maintain the "dayslate" field, it can be dropped.

[ckdoublenecks]

I got this message when I ran the below code:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\invoice\invcalc.php on line 5
could not execute query at line 4:

<?php
$stat = mysql_connect("localhost","root","");
$stat = mysql_select_db("oodb");
$query = "SELECT invnum FROM oocust Where WHERE id = '$id'";
$stat = mysql_fetch_assoc(mysql_query($query));
echo $stat["invnum"];
$result= mysql_query("select * from oocust");
while($row=mysql_fetch_array($result))
 {
$id=$row['id'];
$dayslate=$row['dayslate'];
$charges=$row['charges'];
$duedate=$row['duedate'];
$tax=$row['tax'];
$tax = $charges * .06;
// *****************
// $db = mysql_connect("localhost","root",""); 
$db = mysql_select_db("oodb"); 
$query = "SELECT id, charges, datediff(curdate(),duedate) as dayslate, tax FROM oocust Where WHERE payrec = 'R' AND pd = ' '"; 
$result = mysql_query($query) or die ("could not execute query at line 4"); 
// see note while ($row = mysql_fetch_assoc($result)) 
{ 
$id = $row['id']; 
$days_late = ($row['dayslate'] > 0)?$row['dayslate'] . " days":'N/A'; 
echo "<p> Charges: " . $stat['charges'] . " Due Date: " . $row['duedate'] . " Late: $days_late Tax: " . $row['tax'] . "</p>"; 
// ADD YOUR UPDATE STATEMENTS TO RECORD $id HERE, within the while loop. 
$sql = "UPDATE oocust SET
tax = '$tax', dayslate = '$dayslate'
WHERE id='$id'";
// *******************
} 
mysql_query($sql) ;
$err=mysql_error();
if($err!=""){
 echo "Error in $sql: $err\n";
}
}
echo "Invoice Prep completed";
?>

[rocknbil]

As I said, it's not copy and paste code. Change your execute as mentioned

$result = mysql_query($query)) or die ("could not execute query at line 4 " . mysql_error());

for more information.

But you've got a bigger problem. You have an outer select and a while, then an inner select and your outer $result and $row variables are being overwritten by the inner select. I don't see why you've nested it anyway - you select all from the table, then select specific fields inside the loop that could already be selected by the outer select. (?)

[ckdoublenecks]

Ok, I started over. the current code, below displays the values, although the decimals is awry. still no update. I think I am getting closer.

Charges: 160.00 Due Date: 02/07/2011 Days Late: N/A Tax: 9.6

Charges: 88.00 Due Date: 4/15/2011 Days Late: N/A Tax: 5.28

Charges: 10.00 Due Date: 1/25/2011 Days Late: N/A Tax: 0.6

Charges: 300.00 Due Date: 4/15/2011 Days Late: N/A Tax: 18

Charges: 60.00 Due Date: 2/21/2011 Days Late: N/A Tax: 3.6

Charges: 50.00 Due Date: 5/5/2010 Days Late: N/A Tax: 3

Charges: 31.80 Due Date: 4/13/2011 Days Late: N/A Tax: 1.908

<?php
$stat = mysql_connect(localhost,root,"");
$stat = mysql_select_db(oodb) or die( "Unable to select database");
$query = "SELECT id, tax,charges,datediff(curdate(),duedate) AS dayslate FROM oocust WHERE pd = ' '"; 
$stat = @mysql_fetch_assoc(mysql_query($query));
$result= mysql_query("select * from oocust");
while($row=mysql_fetch_array($result))
 {
$id=$row['id']; 
$tax = $row['tax'];
$charges = $row['charges'];
$tax = $charges * .06;
$days_late = ($row['dayslate'] > 0)?$row['dayslate'] . " days":'N/A'; 
echo "<p> Charges: " . $row['charges'] . " Due Date: " . $row['duedate'] . " 
Days Late: $days_late Tax: $tax</p>"; 
 }
$sql = "UPDATE oocust SET
tax = '$tax', dayslate = '$days_late'
WHERE id='$id'";
 mysql_query($sql) ;
$err=mysql_error();
if($err!=""){
 echo "Error in $sql: $err\n";
echo "Invoice Prep completed";
}
 ?>

[rocknbil]

See my last comment here [webmasterworld.com] (these threads really need to be spliced.) It will always display N/A if the field is a varchar field - you need a valid mysql date format for datediff to work. Otherwise it always evaluates to 0.

[looter]

change mysql row type to date and modify your mysql code to "WHERE duedate<sysdate"