can you do update while doing query?

DragonHighLord

Is it possible to do an update on a table while you're in the middle of quering it?

Simplified example:
table has 3 fields: city, color, number
there are multiple records with the same city

$get = db_query( "select * from table_colors where city=$city);

while ( $stuff = db_fetch_array($get) )
{
//check color
if ( $stuff["color"] == "red" )
{
//update number
$update = db_query("update table_colors set number=1 where city=$city" );
}

}

of course, this is not the exact code I'm using and there are tons and tons more stuff in between such as it would check if number was not already 1, but this is wimple enough to carry my point

What I'm doing here is updating that same table that I'm querying in the while lopp, but it's not the same filed taht I'm using as the "where" clause.

Thanks

DragonHighLord

Ok, I just thought about what I wrote a bit there, and looks like I got a big mistake and should add on to my question.....
If it does that update(which I still want to know if I can that), it will update every record where city=$city....how do I make it only update the current record that is has selected in teh while loop...since that's what I'll need to do in my actual code.

Weedpacket

Basically, yes.

The database is finished it's side of the query once the query call has been completed; after that, all the results of the query have been passed to PHP to do with as it and you please. (This is a big reason why you shouldn't ask for more data in a SELECT than you actually intend to use.)

You're free to send other SQL commands to the database while going through the results of your SELECT query. Of course, those results won't be affected by such subsequent commands.

DragonHighLord

ok, that helps a whole bunch,,a nd thanks a lot on my other post too about the game scores, I did that with one table, and it works perfectly.....thanks again

so, I'm guessing that since the query is already done, there is no way to tell it to update the current record that it's on without doint the general where city=$city?