470,815 Members | 1,350 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,815 developers. It's quick & easy.

deleting a mysql record in active result

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

Apr 12 '06 #1
5 1322
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
==================
Apr 12 '06 #2
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.

Apr 13 '06 #3
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
==================
Apr 13 '06 #4
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.

Apr 13 '06 #5
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
==================
Apr 13 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Rob | last post: by
3 posts views Thread by Nathan Bloom | last post: by
46 posts views Thread by DP | last post: by
1 post views Thread by webandwe | last post: by
6 posts views Thread by keeps21 | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.