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_ob ject", 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]

Re: deleting a mysql record in active result

Bob Bedford wrote:[color=blue]
> 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_ob ject", 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
>
>
>[/color]

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.
jstucklex@attgl obal.net
=============== ===

[fletch]

Re: deleting a mysql record in active result

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]

Re: deleting a mysql record in active result

fletch wrote:[color=blue]
> 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.
>[/color]

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.
jstucklex@attgl obal.net
=============== ===

[fletch]

Re: deleting a mysql record in active result

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]

Re: deleting a mysql record in active result

fletch wrote:[color=blue]
> 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.
>[/color]

They'll be using different connections.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===