deleting a mysql record in active result

[Bob Bedford]

Hello,

I'm doing a select in wich I retrieve some values.

If the value is in an array, I must delete the record.

As I'm doing a "mysql_fetch_object", may I delete this record inside the
loop ?

when doing a mysql_query, do I have a "local" copy or just a link to the
mysql query result. In the first case I may delete the record as I get an
"image" of the query result, in the second case I shouldn't as the query
result will be changed by the deletion of any record.

Bob

[Jerry Stuckle]

Bob Bedford wrote:

Hello,

I'm doing a select in wich I retrieve some values.

If the value is in an array, I must delete the record.

As I'm doing a "mysql_fetch_object", may I delete this record inside the
loop ?

when doing a mysql_query, do I have a "local" copy or just a link to the
mysql query result. In the first case I may delete the record as I get an
"image" of the query result, in the second case I shouldn't as the query
result will be changed by the deletion of any record.

Bob

Bob,

I'm not sure whether you can do it safely or not - I've never tried.

What I do is something I learned many, many moons ago when I was working on a
mainframe database (DB2). When I want to delete a row, I will save it's id in
an array. Then after I'm done with the result set, I go back and build a delete
command to get rid of all of them at one time. Or, you could issue several
delete commands to get rid of them individually.

It solves any potential problems which might result, especially if you change
databases (not likely - but who knows?).
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[fletch]

I always thought this was ok. It seems simple enough to test, but I
think reasoning can sort this out.

User A and B make same query at same time.

Query is 'select * from big_table limit 10';

Results are printed out for user A but user B deletes in of the 10 A
recieved whilst being called.

If the delete counts A no longer has 10 records, unless the sql is run
again. But then other changes could have happened, and the process
could repeat indefinately.

I'd say it would be as safe as houses. The only danger is you use the
same query resource and lose the loop, which is not a db issue but a
programming one.

[Jerry Stuckle]

fletch wrote:

I always thought this was ok. It seems simple enough to test, but I
think reasoning can sort this out.

User A and B make same query at same time.

Query is 'select * from big_table limit 10';

Results are printed out for user A but user B deletes in of the 10 A
recieved whilst being called.

If the delete counts A no longer has 10 records, unless the sql is run
again. But then other changes could have happened, and the process
could repeat indefinately.

I'd say it would be as safe as houses. The only danger is you use the
same query resource and lose the loop, which is not a db issue but a
programming one.

Fletch,

A completely different scenario. If A is holding locks on the rows, B will not
be able to delete them. However, as owner of the locks, A would still be able
to delete the rows.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[fletch]

Hmmn. I'd not really thought of that. How to distinguish a from b
though? not db login, as user a and b are effectively the same program
run by a web server.

[Jerry Stuckle]

fletch wrote:

Hmmn. I'd not really thought of that. How to distinguish a from b
though? not db login, as user a and b are effectively the same program
run by a web server.

They'll be using different connections.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================