while loop to update dates in DB only updates first row?

Answer 1

When you run update, it invalidates the hidden statement hander in your db and the results associated with it.

db->query("SELECT …") -- creates the handler

-- 1st iteration
db->fetchassoc() -- fetches the first row
db->query("UPDATE …") -- creates another handler and destroys the old one

-- 2nd iteration
db->fetchassoc() -- no rows returned by `UPDATE`, nothing to fetch

What you are trying to do, can be done more easily using a single statement:

UPDATE  accounts
SET     installed = DATE_FORMAT(STR_TO_DATE(installed, @current_format_string), '%Y-%d-%m')

, where @current_format_string is how your dates are formatted now.

Update:

Try running this query:

UPDATE  accounts
SET     installdate = DATE_FORMAT(STR_TO_DATE(installdate , '%m/%d/%Y'), '%Y-%d-%m')

Before you run UPDATE query, you may want to check the results with SELECT:

SELECT  DATE_FORMAT(STR_TO_DATE(installdate, '%m/%d/%Y'), '%Y-%d-%m')
FROM    accounts