resultsets - deleting from while processing safe?

[Jim Michaels]

Is it safe to do what is below? deleting from a resultset while you are
processing it? I don't know how dynamic the SQL database is. I assume you
get a cursor to live dataset. Even if it is a cursor as opposed to a table,
will the cursor's integrity be ruined if the current record is deleted?

$q2=mysql_query ("SELECT * FROM table1 WHERE id1=5", $link);
while ($row2=mysql_fe tch_array($q2)) {
//do some stuff with resultset
mysql_query("DE LETE FROM table1 WHERE id1=5 AND id2=$row2[id2]", $link);
}
mysql_free_resu lt($q2);

[Nicholas Sherlock]

Re: resultsets - deleting from while processing safe?

Jim Michaels wrote:[color=blue]
> Is it safe to do what is below? deleting from a resultset while you are
> processing it? I don't know how dynamic the SQL database is. I assume you
> get a cursor to live dataset. Even if it is a cursor as opposed to a table,
> will the cursor's integrity be ruined if the current record is deleted?
>
> $q2=mysql_query ("SELECT * FROM table1 WHERE id1=5", $link);
> while ($row2=mysql_fe tch_array($q2)) {
> //do some stuff with resultset
> mysql_query("DE LETE FROM table1 WHERE id1=5 AND id2=$row2[id2]", $link);
> }
> mysql_free_resu lt($q2);[/color]

AFAIK, it'll be totally fine. But isn't your code equivalent to this?

$q2=mysql_query ("SELECT * FROM table1 WHERE id1=5", $link);
while ($row2=mysql_fe tch_array($q2)) {
//do some stuff with resultset
}
mysql_free_resu lt($q2);
mysql_query("DE LETE FROM table1 WHERE id1=5", $link);

Cheers,
Nicholas Sherlock

--

http://www.sherlocksoftware.org

[Jerry Stuckle]

Re: resultsets - deleting from while processing safe?

Nicholas Sherlock wrote:[color=blue]
> Jim Michaels wrote:
>[color=green]
>> Is it safe to do what is below? deleting from a resultset while you
>> are processing it? I don't know how dynamic the SQL database is. I
>> assume you get a cursor to live dataset. Even if it is a cursor as
>> opposed to a table, will the cursor's integrity be ruined if the
>> current record is deleted?
>>
>> $q2=mysql_query ("SELECT * FROM table1 WHERE id1=5", $link);
>> while ($row2=mysql_fe tch_array($q2)) {
>> //do some stuff with resultset
>> mysql_query("DE LETE FROM table1 WHERE id1=5 AND id2=$row2[id2]",
>> $link);
>> }
>> mysql_free_resu lt($q2);[/color]
>
>
> AFAIK, it'll be totally fine. But isn't your code equivalent to this?
>
> $q2=mysql_query ("SELECT * FROM table1 WHERE id1=5", $link);
> while ($row2=mysql_fe tch_array($q2)) {
> //do some stuff with resultset
> }
> mysql_free_resu lt($q2);
> mysql_query("DE LETE FROM table1 WHERE id1=5", $link);
>
> Cheers,
> Nicholas Sherlock
>[/color]

Jim,

I'm not sure if it's safe or not - typically I've tried to stay away
from this construct. And I haven't been able to find any doc talking
about it one way or the other.

If I were doing it, I'd save the id's in an array and delete them after
I'm done with the result set. But that's just me.

And Nicholas, no, it's not the same. Jim is deleting from the result
set (potentially multiple rows based on other selection criteria).
You're deleting all rows with id1=5, which may or may not be what he
wants (he may only want to delete some of the rows, for instance).

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

[Chung Leong]

Re: resultsets - deleting from while processing safe?


Jim Michaels wrote:[color=blue]
> Is it safe to do what is below? deleting from a resultset while you are
> processing it? I don't know how dynamic the SQL database is. I assume you
> get a cursor to live dataset. Even if it is a cursor as opposed to a table,
> will the cursor's integrity be ruined if the current record is deleted?
>
> $q2=mysql_query ("SELECT * FROM table1 WHERE id1=5", $link);
> while ($row2=mysql_fe tch_array($q2)) {
> //do some stuff with resultset
> mysql_query("DE LETE FROM table1 WHERE id1=5 AND id2=$row2[id2]", $link);
> }
> mysql_free_resu lt($q2);[/color]

With MySQL I believe it's safe, as the client buffers the whole result
set. It's more efficient though to save the ids into an array, then
delete all the applicable rows in a single operation with a "id2 IN (
.... )" condition.

[Jim Michaels]

Re: resultsets - deleting from while processing safe?


"Chung Leong" <chernyshevsky@ hotmail.com> wrote in message
news:1142704883 .623831.317580@ z34g2000cwc.goo glegroups.com.. .[color=blue]
>
> Jim Michaels wrote:[color=green]
>> Is it safe to do what is below? deleting from a resultset while you are
>> processing it? I don't know how dynamic the SQL database is. I assume
>> you
>> get a cursor to live dataset. Even if it is a cursor as opposed to a
>> table,
>> will the cursor's integrity be ruined if the current record is deleted?
>>
>> $q2=mysql_query ("SELECT * FROM table1 WHERE id1=5", $link);
>> while ($row2=mysql_fe tch_array($q2)) {
>> //do some stuff with resultset
>> mysql_query("DE LETE FROM table1 WHERE id1=5 AND id2=$row2[id2]",
>> $link);
>> }
>> mysql_free_resu lt($q2);[/color]
>
> With MySQL I believe it's safe, as the client buffers the whole result
> set. It's more efficient though to save the ids into an array, then
> delete all the applicable rows in a single operation with a "id2 IN (
> ... )" condition.
>[/color]

thx for the tip. Hadn't thought of that. Makes sense.