Replace field value inside a "While" loop

[RCurtis]

Showing my ignorance again...

 

I have a while loop that steps through every record in a table, looks at a given field and does some manipulations on that one field.  I now need to write that single field back.  Most all of the examples I've seen take the form of:

 

1. UPDATE table SET
2. field_data = $new_data
3. WHERE id=1

I may be visualizing what MySQL is doing incorrectly, but using a WHILE loop would seem to me that you are currently "on that record at this moment in time"...can you not just somehow write that new fields' data right then and then allow the loop to continue?  Do you really have to deal with a record id & such?  (Not sure if I am explaining myself clearly...in other words, I have my new field data ready to do an immediate write but it is ONLY for that certain field...I have not read the entire row.)  I know about REPLACE INTO but that seems like overkill given that all I want to do is change one field.  Am I looking at this the wrong way?

[DavidAM]

The (mySql) database is completely separate from the PHP process. Unless you are using an Asynchronous Query (which I do NOT recommend), PHP has already retrieved ALL of the data and the database is done with the query. Unlike MS Access, there is no concept of the "current row". You will need to include the row's unique identifier in then original query, and the issue the update using that identifier (as in your example above).

 

Note that executing queries in a loop is resource intensive. If you have to do it, then you have to do it. But the Relational Database is designed around SETs (of data). If you can express the conditions of the updates in SQL, you would be better off using an UPDATE statement to update multiple rows at a time rather than walking the entire table.

 

For instance: UPDATE Employees SET Salary = Salary * 1.05 WHERE IsActive = 1 AND IsManager =1 followed by UPDATE Employees SET Salary = Salary * 1.01 WHERE IsActive = 1 and IsManager = 0, would give everybody a raise -- a 5% raise to the managers and a 1% raise to the others.

[RCurtis]

As I suspected...I am trying to imply a process from other languages/databases.  Thanks!