the below code is to updata a database with calculated taxes and days late. It results in the message:

error: Maximum execution time of 60 seconds exceeded in C:\xampp\htdocs\invoice

. Can someone help with this?

<?php
$stat = mysql_connect("localhost","root","");
$stat = mysql_select_db("oodb");
$query = "SELECT invnum FROM oocust WHERE payrec = 'R' AND pd = ' '";
$stat = mysql_fetch_assoc(mysql_query($query));
$result= mysql_query("select * from oocust");
while($row=mysql_fetch_array($result))
   {
$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 payrec = 'R' AND pd = ' '"; 
$result = mysql_query($query) or die ("could not execute query"); 
$days_late = ($row['dayslate'] > 0)?$row['dayslate'] . " days":'N/A';  
//echo "<p> Charges: " . $row['charges'] . " Due Date: " . $row['duedate'] .   " Late: $days_late Tax: " . $row['tax'] . "</p>";  
$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";
}
?>

Recommended Answers

All 22 Replies

on line 6 you have $result= mysql_query("select * from oocust") . For the while construct to finish (line 7 on your code), mysql_fetch_array($result) needs to return false. However, within the loop you, on EVERY iteration you execute ANOTHER query (line 14) that "refreshes" $result. So it will never run out of records. Basically you have an infinite loop. What you need to so is give each of your result sets a different name - ex:
on line 6 use $resultA= mysql_query("select * from oocust") so on line 7 you will need while($row=mysql_fetch_array($resultA)) on line 14 use $resultB= mysql_query("select * from oocust") On another note, you do NOT need lines 11 and 12 since you are already connected to the server and using the oodb database. Also, on line 10 you are using a variable named $charges that has not been initialized.

As for line 14, the only thing it seems to be doing is causing an infinite loop. Meaning, I don't see you using that result anywhere. IF you are not using it, then don't bother querying the db.

on line 6 you have $result= mysql_query("select * from oocust") . For the while construct to finish (line 7 on your code), mysql_fetch_array($result) needs to return false. However, within the loop you, on EVERY iteration you execute ANOTHER query (line 14) that "refreshes" $result. So it will never run out of records. Basically you have an infinite loop. What you need to so is give each of your result sets a different name - ex:
on line 6 use $resultA= mysql_query("select * from oocust") so on line 7 you will need while($row=mysql_fetch_array($resultA)) on line 14 use $resultB= mysql_query("select * from oocust") On another note, you do NOT need lines 11 and 12 since you are already connected to the server and using the oodb database. Also, on line 10 you are using a variable named $charges that has not been initialized.

As for line 14, the only thing it seems to be doing is causing an infinite loop. Meaning, I don't see you using that result anywhere. IF you are not using it, then don't bother querying the db.

I believe I changed the code per your suggestions and it cycled correctly and echoed the values. It did not update the tax or dayslate fields?

<?php
$stat = mysql_connect("localhost","root","");
$stat = mysql_select_db("oodb");
$query = "SELECT invnum FROM oocust WHERE payrec = 'R' AND pd = ' '";
$stat = mysql_fetch_assoc(mysql_query($query));
$resultA= mysql_query("select * from oocust");
while($row=mysql_fetch_array($resultA))
   {
$tax = $row['tax'];
$charges = $row['charges'];
$tax = $charges * .06;
$query = "SELECT id,charges,datediff(curdate(),duedate) as dayslate, tax FROM oocust WHERE payrec = 'R' AND pd = ' '"; 
$result = mysql_query($query) or die ("could not execute query"); 
$days_late = ($row['dayslate'] > 0)?$row['dayslate'] . " days":'N/A';  
echo "<p> Charges: " . $row['charges'] . " Due Date: " . $row['duedate'] .   " Late: $days_late Tax: " . $row['tax'] . "</p>";  
$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";
}
?>

Look at line 7.
$row is "tied/belongs" to $resultA.

Now look at line 14. Where does dayslate come from? If it is from the query on line 12, then you need to EXTRACT the data from $result.

Like I said earlier, you are NOT using the $result of the query within the loop.

To clarify, it would help you if you change your while to: while( $row[B]A[/B] = mysql_fetch_array($resultA)) Then AFTER line 12 do:

$result = mysql_query($query) or die ("could not execute query"); 
$row=mysql_fetch_assoc($result);/* this is what you are NOT doing anywhere, so you are really NOT using the result of your inner query at all*/

So you'll need to revise your code and figure out where you meant to use $rowA and where you meant to use just $row (the one for the inner query).

Also, lines 20-23 should be within the while

I really thank you for your help. I hate to seem so obuse but this language is new to me. I have started over and below is the current code. Lines 4 & 13-15 were suggested by another. The values are echoed but no update.

<?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))
   {
 $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: " . $row['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";
}
 ?>

on line 18 you have ...WHERE id='$id'" but I don't see where $id comes from.

Also, line 9 seems useless since on line 11 you are overwriting $tax .

I suggest you CHANGE line 9 to $id=$row['id']; , that way you get rid of your current useless line and at the same time you are initializing $id .

on line 18 you have ...WHERE id='$id'" but I don't see where $id comes from.

Also, line 9 seems useless since on line 11 you are overwriting $tax .

I suggest you CHANGE line 9 to $id=$row['id']; , that way you get rid of your current useless line and at the same time you are initializing $id .

this is the result. the taxes displayed were already there

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

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

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

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

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

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

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

on line 14 you need the newly computed tax value, which is in $tax , NOT $row['tax']

on line 14 you need the newly computed tax value, which is in $tax , NOT $row['tax']

below is the result. of course still no update:

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

try:

<?php
$stat = mysql_connect('localhost','root','') or die('Unable to connect to server: '.mysql_error());
$stat = mysql_select_db('oodb') or die( "Unable to select database: " . mysql_error() );
$query = "SELECT `id`, `tax`, `duedate`, `charges`, datediff(curdate(), `duedate`) AS `dayslate` FROM `oocust` WHERE `pd` = ' '"; 
$result=mysql_query($query) or die("Problems executing query:<br/>{$query}<br />".mysql_error());
while( $row=mysql_fetch_assoc($result) )
{
	$id = $row['id'];
	$tax = $row['charges'] * .06;
	$days_late = ($row['dayslate'] > 0)?$row['dayslate'] . " days":'N/A';  

	//assuming that id is defined as INT in your db use the following:
	$sql = sprintf("UPDATE `oocust` SET `tax` = '%s', `dayslate` = '%s' WHERE `id`=%d"
			,mysql_real_escape_string($tax)
			,mysql_real_escape_string($days_late)
			,mysql_real_escape_string($id)
		);

	/*
	//if id is NOT a numeric datatype, use the following:
	$sql = sprintf("UPDATE `oocust` SET `tax`='%s', `dayslate`='%s' WHERE `id`='%s'"
			,mysql_real_escape_string($tax)
			,mysql_real_escape_string($days_late)
			,mysql_real_escape_string($id)
		);
	*/

	mysql_query($sql) ;
	$err=mysql_error();
	if($err!="")
	{
		echo "Error in $sql: $err\n";
	}
	else
	{
		echo "<p> Charges: " . $row['charges'] . " Due Date: " . $row['duedate'] .   " Days Late: $days_late Tax: " . $row['tax'] . "</p>"; 
	}
}
echo "Invoice Prep completed";
?>

this updates everything except the days late?

<?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']; 
$amtdue = $row['amtdue']; 
$shipamt = $row['shipamt']; 
$charges = $row['charges']; 
$tax = $charges * .06; 
$amtdue = $charges + $tax + $shipamt; 
$days_late = ($row['dayslate'] > 0)?$row['dayslate'] . " days":'N/A'; 
// echo "<p> Charges: " . $row['charges'] . " Due Date: " . $row['duedate'] .   " 
//       Days Late: $days_late Tax: " . $row['tax'] . "</p>"; 
$sql = "UPDATE oocust SET tax = '$tax', amtdue='$amtdue', dayslate = '$days_late' WHERE id='$id'"; 
 mysql_query($sql) ; 
$err=mysql_error(); 
if($err!="") 
   { 
  echo "Error in $sql: $err\n"; 
    } 
  } 
echo "Invoice Prep completed"; 
 ?>

In your database look at the structure of your oocust table. Find the dayslate column. Is it a numeric datatype? If yes, that's your problem. You would need to change it to a text data type.

In your database look at the structure of your oocust table. Find the dayslate column. Is it a numeric datatype? If yes, that's your problem. You would need to change it to a text data type.

it is date 0000-00-00

Aren't you expecting to record something like "3 day"? If yes, then you need to change the datatype to varchar (or text).

As of now you have: $days_late = ($row['dayslate'] > 0)?$row['dayslate'] . " days":'N/A'; which will allow you values like:
3 days OR N/A neither of which IS a date.

what I want is just the # - 3 , 4 , 47.

>>what I want is just the # - 3 , 4 , 47.
OK, then you cannot have it as date or datetime datatype. You need to change the definition of your field to INT if it is going to be JUST a number. However, your PHP code is attempting to write something like '3 days', '4 days', '47 days' and even 'N/A'. These are clearly NOT numbers, so you need to change the field to either varchar and leave your PHP code intact

OR change:

$days_late = ($row['dayslate'] > 0)?$row['dayslate'] . " days":'N/A';

to:

$days_late = ($row['dayslate'] > 0)?$row['dayslate'] : 0;

AND also change your datatype to INT

sorry, I'm fried from this thing. I told you wrong, it's int. I changed the code to"$days_late = ($row > 0)?$row : 0;" as you suggested. below is what is displayed and the result's the same.

Invoice Prep completed$days_late = ($row > 0)?$row : 0;$days_late = ($row > 0)?$row : 0;

did you ALSO change the datatype on your db table to INT?

did you ALSO change the datatype on your db table to INT?

as I posted I told you wrong earlier, it is int(4)

then in your sql you need to get rid of the apostrophes that you have around the value: ...,dayslate = [B]'[/B]$days_late[B]'[/B] WHERE... Instead, use: ...,dayslate = $days_late WHERE...

then in your sql you need to get rid of the apostrophes that you have around the value: ...,dayslate = [B]'[/B]$days_late[B]'[/B] WHERE... Instead, use: ...,dayslate = $days_late WHERE...

when I did that I got the below:

Error in UPDATE oocust SET tax = '9.6', amtdue = '169.6', dayslate = N/A WHERE id='1': Unknown column 'N' in 'field list' Error in UPDATE oocust SET tax = '5.28', amtdue = '110.28', dayslate = N/A WHERE id='2': Unknown column 'N' in 'field list' Error in UPDATE oocust SET tax = '0.6', amtdue = '10.6', dayslate = N/A WHERE id='3': Unknown column 'N' in 'field list' Error in UPDATE oocust SET tax = '18', amtdue = '318', dayslate = N/A WHERE id='4': Unknown column 'N' in 'field list' Error in UPDATE oocust SET tax = '3.6', amtdue = '73.6', dayslate = N/A WHERE id='65': Unknown column 'N' in 'field list' Error in UPDATE oocust SET tax = '3', amtdue = '53', dayslate = N/A WHERE id='61': Unknown column 'N' in 'field list' Error in UPDATE oocust SET tax = '1.908', amtdue = '43.708', dayslate = N/A WHERE id='66': Unknown column 'N' in 'field list' Invoice Prep completed

UPDATE ..., dayslate = N/A ...

clearly, the php code that you are currently executing has NOT been updated to: $days_late = ($row['dayslate'] > 0)?$row['dayslate'] : 0; as previously suggested.

below is my code. the duedate is int(10)

<?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'];
$amtdue = $row['amtdue'];
$shipamt = $row['shipamt'];
$charges = $row['charges'];
$tax = $charges * .06;
$amtdue = $charges + $tax + $shipamt;
$days_late = ($row['dayslate'] > 0)?$row['dayslate'] : 0; 
$sql = "UPDATE oocust SET tax = '$tax', amtdue = '$amtdue', dayslate = '$days_late' WHERE id='$id'";
// WHERE duedate<sysdate
mysql_query($sql) ;
$err=mysql_error();
if($err!="")
   {
  echo "Error in $sql: $err\n";
    }
  }
echo "Invoice Prep completed";
 ?>
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.