How do I make Database Updates in a WHILE loop?

[JoeBlood]

I have a page which retrieves and displays an Audit schedule from a database. 

But one field in the database, 'days_til_due' I want to update dynamically, whenever the page is accessed, and  THEN display the updated database value on the page.  Basically: (Days 'til due = (Today's Date - Date Last Done) + Frequency)

 

Everything else works okay, but 'days_til_due' in the database isn't updating.  For error checking, I'm displaying the php calculated value of $daystildue first, and then next to it the database version of this value $row['days_til_due'].  These 2 columns should be identical.

I'm very new to this, so it may be something simple and obvious.  Anything jump out?

 

Thanks!

 

$q = "SELECT auditUI, audit, clause, name, DATE_FORMAT(lastaudited, '%M %d, %Y') AS la, frequency, days_til_due FROM schedule ORDER BY audit, clause ASC";
$r = mysqli_query($dbc, $q);

//  echo table header html removed for brevity 

// fetch all the records

while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)){

$now = time(); // Today's date
     	$last_audited = strtotime($row['la']);
     	$datediff = $last_audited - $now;
     	$frequencyindays = ceil(($row['frequency'] / 12) * 365); // The Frequency of recurrence is a user-determined variable, set in months
     	$datediff = ceil($datediff/(60*60*24));
     	$dtd = $frequencyindays + $datediff; // this calculates how many days until next due - works OK
     	     	    	    
     	$q2 = "UPDATE schedule SET days_til_due='$dtd' LIMIT 1"; // this SHOULD update the field 'days_til_due' with $dtd for every record retrieved, but doesn't work. WHY???
     	
     	$r2 = mysqli_query($dbc, $q2);
     	if (mysqli_affected_rows($dbc) == 1) { // If it ran OK.
     	
	echo '<tr bgcolor="'.$bg.'">
	<td align="center">' . $row['auditUI'] . '</td>
	<td align="left">' . $row['audit'] . '</td>
	<td align="left">' . $row['clause'] . '</td>
	<td align="left">' . $row['name'] . '</td>
	<td align="left">' . $row['la'] . '</td>
	<td align="center">' . $row['frequency'] . '</td>
	<td align="center">' . $dtd . '</td> 
	<td align="center">' . $row['days_til_due'] . '</td> // THIS ROW DISPLAYS THE INCORRECT VALUES
	</tr>
	';
	} else { // If it did not run OK.
			echo '<p class="error">AuditUI ' . $row['auditUI'] .' could not be edited.</p>'; // Public message.
			echo '<p>' . mysqli_error($dbc) . '<br />Query: ' . $q2 . '</p>'; // Debugging message.
		}
} // end of WHILE loop

[Muddy_Funster]

1. you don't use LIMIT in an update - you need to use WHERE - if LIMIT 1 works at all it just meens that you will only update what it thinks is the first record each and every pass through.  You need to realise that you are running a totaly unrelated query doing it the way that you are, the update does not know what record PHP is using from the initial SELECT.

2. you have $dtd wraped in quotes, making it a string value - if you have the field set as int in the database it's going to get upset with you.

[JoeBlood]

OK, thank you very much.  I hate to trouble you further, but:

 

This syntactically fixed the initial error, but now the output is defaulting to the "ELSE" clause, and telling me for each record "AuditUI n could not be edited."

 

You need to realise that you are running a totaly unrelated query doing it the way that you are, the update does not know what record PHP is using from the initial SELECT.

 

So do I need to re-run the SELECT query again prior to displaying the records, or is there a more logical way to go about this?

I really appreciate your help.

[Muddy_Funster]

what did you change the update SQL to? it should look something like this:

 

$q2 = "UPDATE schedule SET days_til_due='$dtd' WHERE auditUI={$row['auditUI']}";

assuming that auditUI is your Primary Key Field

[JoeBlood]

$auditui = $row['auditUI'];

$q2 = "UPDATE schedule SET days_til_due=$dtd WHERE auditUI=$auditui";

 

 

[JoeBlood]

Huh, I just commented-out the If / Else clauses after the Update query, and it displayed perfectly well - correct values and everything.

 

if (mysqli_affected_rows($dbc) == 1) {

 

//Display stuff

 

} else { // If it did not run OK.

echo '<p class="error">AuditUI ' . $row['auditUI'] .' could not be edited.</p>'; // Public message.

echo '<p>' . mysqli_error($dbc) . '<br />Query: ' . $q2 . '</p>'; // Debugging message.

 

Without this portion in there, it updated perfectly well - in fact, it probably was updating the database since your last answer, but then defaulting to the output resulting from that If statement returning "False".

 

Thank you again Muddy for your help.