How to update oldest row

bch95

Hey I managed to write a script to update the oldest row in a database that has a certain value inside of it.

this is my code:

$change_value = "SELECT dtime FROM news WHERE type=2 ORDER BY dtime LIMIT 1";
$getrow = mysql_query($change_value) or die(mysql_error());
$changerow = mysql_fetch_array($getrow) or die(mysql_error());
$time=$changerow['dtime'];
mysql_query("UPDATE `news` SET type='3' WHERE type=`2` AND dtime = `.$time.` LIMIT 1") or die(mysql_error());

For some reason its not working, Meaning its not updating the oldest row with the oldest date in dtime.

The dtime is formatted like this:

2011-03-15 23:56:17

Can someone show me the correct way to do this?

All of the table names, seem correct.

bradgrafelman

Since you aren't using a multi-table UPDATE, there's no need to have the SELECT query at all.

Just add the ORDER BY and LIMIT clauses to the UPDATE query and get rid of the first query.

bch95

So what would the code look like exactly?

I am not relly understanding.

bch95

Thanks, one more thing, how do i do it so it only updates if there are only 3 rows of the certain type?

what if statemtnt would it be?

if amount of rows that contain the value 2 in 'type' equals exactly to 2
//add update query
else
//do not update

How would I code that?

johanafm

Put your old SELECT query back in, but change it to ORDER DESC instead of ORDER (ASC). Use mysql_num_rows.