homepage Welcome to WebmasterWorld Guest from 54.145.209.77
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
calculating days late
ckdoublenecks



 
Msg#: 4299057 posted 6:54 pm on Apr 16, 2011 (gmt 0)

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



 
Msg#: 4299057 posted 8:54 pm on Apr 16, 2011 (gmt 0)

and the point of your message is?

ckdoublenecks



 
Msg#: 4299057 posted 9:19 pm on Apr 16, 2011 (gmt 0)

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

mbabuskov



 
Msg#: 4299057 posted 9:28 pm on Apr 16, 2011 (gmt 0)

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



 
Msg#: 4299057 posted 11:01 pm on Apr 16, 2011 (gmt 0)

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



 
Msg#: 4299057 posted 11:20 pm on Apr 16, 2011 (gmt 0)

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



 
Msg#: 4299057 posted 12:44 am on Apr 17, 2011 (gmt 0)

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



 
Msg#: 4299057 posted 7:00 am on Apr 17, 2011 (gmt 0)

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

ckdoublenecks



 
Msg#: 4299057 posted 4:24 pm on Apr 17, 2011 (gmt 0)

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

ckdoublenecks



 
Msg#: 4299057 posted 12:59 am on Apr 18, 2011 (gmt 0)

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

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4299057 posted 5:54 pm on Apr 18, 2011 (gmt 0)

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



 
Msg#: 4299057 posted 5:03 pm on Apr 19, 2011 (gmt 0)

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

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4299057 posted 5:42 pm on Apr 19, 2011 (gmt 0)

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



 
Msg#: 4299057 posted 10:20 pm on Apr 20, 2011 (gmt 0)

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

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4299057 posted 12:29 am on Apr 21, 2011 (gmt 0)

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



 
Msg#: 4299057 posted 4:11 am on Apr 21, 2011 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved