Page 1 of 1

while loop

Posted: Thu Aug 14, 2003 9:17 am
by superman
this can be done when only one record in the table, if more than one record in the table, it show the error message.
Warning: Supplied argument is not a valid MySQL result resource in c:\phpweb/test5.php on line 6
how can i change this code to select the first row first, run the script, and then select the second row, run the scriptand so on.
then update the the different result in different rows.

+-------+------+------+
| User | Year | Days |
+-------+------+------+
| Ken | 2002 | 5 |
| Peter | 2003 | 9 |
+-------+------+------+

Code: Select all

<?
$query = "SELECT Year, Days FROM user"; 
$result = mysql_query($query); 
while($row = mysql_fetch_array($result)) 
{
  $CurrentYear = date("Y");
  $Diff = $CurrentYear - $Year;
  $Year = $row['Year'];
  $Days = $row['Days'];
    if ($Diff <= 2){
	$Year = $Year + 1;
	$result = mysql_query("UPDATE user SET Year=$Year");
  }

.
.
.
.
.
}
?>

Posted: Thu Aug 14, 2003 10:07 am
by JAM
I think that this line...

Code: Select all

$result = mysql_query("UPDATE user SET Year=$Year");
...breaks it. Setting $result to a this query, will make the $result in the while-loop above choke...

Posted: Thu Aug 14, 2003 10:10 am
by superman
if i remove the $result, it can update the table, but with the same $Year in 2 rows

Posted: Thu Aug 14, 2003 10:33 am
by RFairey
Just use a different variable for the sql query within the loop:

$result2 = mysql_query("UPDATE etc......

Posted: Thu Aug 14, 2003 10:56 pm
by superman
i've tried, but the same problem arised.

Code: Select all

<?
$Connect = mysql_connect("localhost","root","");
mysql_select_db("mw");
$query = "SELECT Entitlement, YearStarted FROM user"; 
$result = mysql_query($query); 
while($row = mysql_fetch_array($result))
{
	$YearStarted = $row['YearStarted'];
	$Entitlement = $row['Entitlement'];
	$CurrentYear = date("Y");
	$YearsWorked = $CurrentYear - $YearStarted;
	if ($YearsWorked <= 2) {
		if ($Entitlement >= 10){
		$CarryForward = 10;
		$Entitlement = $CarryForward + 18;
		$result1=mysql_query("UPDATE user SET Entitlement=$Entitlement") or die(mysql_error().":<br>".$query);
		}
		else {
		$Entitlement = $Entitlement + 18;
		$result1=mysql_query("UPDATE user SET Entitlement=$Entitlement") or die(mysql_error().":<br>".$query);
		}
		}
	else {
		if ($Entitlement >= 10){
		$CarryForward = 10;
		$Entitlement = $CarryForward + 19;
		$result1=mysql_query("UPDATE user SET Entitlement=$Entitlement") or die(mysql_error().":<br>".$query);
		}
		else {
		$Entitlement = $Entitlement + 19;
		$result1=mysql_query("UPDATE user SET Entitlement=$Entitlement") or die(mysql_error().":<br>".$query);
		}
	}
}	
?>

Posted: Fri Aug 15, 2003 5:00 am
by tylerdurden
Try using

Code: Select all

UPDATE user SET Entitlement='$Entitlement'
(with single quotes).

Posted: Fri Aug 15, 2003 7:59 am
by superman
i want it to check each row and update each row, how?
i have a table:

+--------+------------+-----------+
| StaffID | YearStarted | Entitlement |
+--------+------------+-----------+
| Ken | 2003 | 7 |
| Peter | 2002 | 17 |
+--------+------------+-----------+


when i run the script, it suppose to show this:

+--------+------------+-----------+
| StaffID | YearStarted | Entitlement |
+--------+------------+-----------+
| Ken | 2003 | 25 |
| Peter | 2002 | 28 |
+--------+------------+-----------+


but it show this:????

+--------+------------+-----------+
| StaffID | YearStarted | Entitlement |
+--------+------------+-----------+
| Ken | 2003 | 28 |
| Peter | 2002 | 28 |
+--------+------------+-----------+