At 17:56 +0000 12/18/02, Jeff Snoxell wrote:
>I'm selecting a group of records from my database. I then loop
>through the selected records and do some work based on what I find.
>But what I also want to do as I interrogate each record is update
>some of its fields with new values... but won't that screw up the
>outer loop? I mean if I try to execute a query whilst looping around
>the result set of a former query will I not screw up my result set
>that I'm looping through?
No, for two reasons:
If your retrieval is based on the mysql_store_result() model, all the rows
in the result set will have been pulled to the client side by the time you
see any of them. So when you perform your updates, you're not going to be
affecting the actual rows that you're looking at.
If your retrieval is based on the mysql_use_result() model, then you pull
each row from the server one at a time, and you *do* see it as you retrieve
it. But this also leaves the table read-locked for the duration of the
retrieval, and so you can't update it anyway. (And besides, the nature of
of the client/server protocol in this case prohibits you from issuing any
queries until you terminate your retrieval operation.)
mysql_store_result() and mysql_use_result() are C API functions that you
can read about in the MySQL manual. Most other APIs are built on the C API
and some of them allow you to retrieve in one mode or the other.
>Also, is it possible to update specific fields of certain records
>within a SELECT query? ie can I do something like this:
>SELECT * FROM my_table WHERE Age > 50 AND UPDATE Status = "OLD"
No. That's goofy anyway. Why wouldn't you just use a regular
UPDATE mytable Status = "OLD" WHERE Age > 50;