select on updated value during the fetch

[cdtsly]

Hi i have a table with all value at 4
i select all lines in a fetch
i update one with a value of 7

i update all the row in the fetch with a value 5

the result is that all my row are at 5
and the only line which should be at 7 is at 5 too.

i see that the problem is at the first mysql_db_query, and i don t know
how to solve it

thanks
$query="SELECT * FROM val WHERE str='4'";
$result=mysql_db_query($datamysql,$query);
$query7="UPDATE val SET str='7',updated='1' WHERE val='2'";
$result7=mysql_db_query($datamysql,$query7);

while ($row=mysql_fetch_array($result))
{

$str=$row["str"];
$val=$row["val"];
$query2="UPDATE val SET str='5' WHERE val='$val'";
$result2=mysql_db_query($datamysql,$query2);
}

[Kimmo Laine]

<cd****@relayeur.com> wrote in message
news:11*********************@c74g2000cwc.googlegro ups.com...

Hi i have a table with all value at 4
i select all lines in a fetch
i update one with a value of 7
This is where it goes wrong. You update nothing, see explanation below.
i update all the row in the fetch with a value 5

the result is that all my row are at 5
and the only line which should be at 7 is at 5 too.

i see that the problem is at the first mysql_db_query, and i don t know
how to solve it

thanks
$query="SELECT * FROM val WHERE str='4'";
$result=mysql_db_query($datamysql,$query);
$query7="UPDATE val SET str='7',updated='1' WHERE val='2'";
You update all rows whose val=2 (should this be 4 maybe?), that is none,
because all rows val=4. Nothing is updated. Even if it was, the old record
that you fetched earlier are intact, only the database is updated but the
updates do not reflect on already fetched records. So when you select
something, then update something, the old values are still inside $result,
so the updating means nothing again.
$result7=mysql_db_query($datamysql,$query7);

while ($row=mysql_fetch_array($result))
{

$str=$row["str"];
$val=$row["val"];
$query2="UPDATE val SET str='5' WHERE val='$val'";
$result2=mysql_db_query($datamysql,$query2);
}

[cdtsly]

the update works for the updated column
but as you say the prefetch row are already there
so the mysql_fetch_array command will take a line where there is not
anymore the str=4 value

i know why i have this problem and i like to know how not have it
:-)

thx

Kimmo Laine a écrit :

<cd****@relayeur.com> wrote in message
news:11*********************@c74g2000cwc.googlegro ups.com...

Hi i have a table with all value at 4
i select all lines in a fetch
i update one with a value of 7

This is where it goes wrong. You update nothing, see explanation below.

i update all the row in the fetch with a value 5

the result is that all my row are at 5
and the only line which should be at 7 is at 5 too.

i see that the problem is at the first mysql_db_query, and i don t know
how to solve it

thanks
$query="SELECT * FROM val WHERE str='4'";
$result=mysql_db_query($datamysql,$query);
$query7="UPDATE val SET str='7',updated='1' WHERE val='2'";

You update all rows whose val=2 (should this be 4 maybe?), that is none,
because all rows val=4. Nothing is updated. Even if it was, the old record
that you fetched earlier are intact, only the database is updated but the
updates do not reflect on already fetched records. So when you select
something, then update something, the old values are still inside $result,
so the updating means nothing again.

$result7=mysql_db_query($datamysql,$query7);

while ($row=mysql_fetch_array($result))
{

$str=$row["str"];
$val=$row["val"];
$query2="UPDATE val SET str='5' WHERE val='$val'";
$result2=mysql_db_query($datamysql,$query2);
}

[Kimmo Laine]

<cd****@relayeur.com> wrote in message
news:11**********************@c74g2000cwc.googlegr oups.com...

the update works for the updated column
but as you say the prefetch row are already there
so the mysql_fetch_array command will take a line where there is not
anymore the str=4 value

i know why i have this problem and i like to know how not have it
:-)

Run the queries in different order. First update, then select.

like this:

$query7="UPDATE val SET str='7',updated='1' WHERE val='2'";
$result7=mysql_db_query($datamysql,$query7);

$query="SELECT * FROM val WHERE str='4'";
$result=mysql_db_query($datamysql,$query);

while ($row=mysql_fetch_array($result))
{
$str=$row["str"];
$val=$row["val"];
$query2="UPDATE val SET str='5' WHERE val='$val'";
$result2=mysql_db_query($datamysql,$query2);
}

Well actually, to keep it very very very simple, you could just do this:

$query7="UPDATE val SET str='7',updated='1' WHERE val='2'";
$result7=mysql_db_query($datamysql,$query7);
$query2="UPDATE val SET str='5' WHERE str='4'";
$result2=mysql_db_query($datamysql,$query2);

And nothing else. If you want to update all rows where str='4', then use
that as the condition, you don't need to first select something with one
condition, and then one by one row update all the rows. You can do it all in
one update query.

--
"ohjelmoija on organismi joka muuttaa kofeiinia koodiksi" -lpk
sp**@outolempi.net | Gedoon-S @ IRCnet | rot13(xv***@bhgbyrzcv.arg)

[cdtsly]

in fact, i need this order because i am rebuilding a table and i need
to do this way

i have a workaround with generating no fetch of all the table, but to
select the rows one by one
which give a stupid number of query( 200 000 rows=>200 000 query) and
it take a lot of time naturally :-)

i made this example to show my problem

is not there a possibility to fetch a special way which can take in
consideration freshness?
Kimmo Laine a écrit :

<cd****@relayeur.com> wrote in message
news:11**********************@c74g2000cwc.googlegr oups.com...

the update works for the updated column
but as you say the prefetch row are already there
so the mysql_fetch_array command will take a line where there is not
anymore the str=4 value

i know why i have this problem and i like to know how not have it
:-)

Run the queries in different order. First update, then select.

like this:

$query7="UPDATE val SET str='7',updated='1' WHERE val='2'";
$result7=mysql_db_query($datamysql,$query7);

$query="SELECT * FROM val WHERE str='4'";
$result=mysql_db_query($datamysql,$query);

while ($row=mysql_fetch_array($result))
{
$str=$row["str"];
$val=$row["val"];
$query2="UPDATE val SET str='5' WHERE val='$val'";
$result2=mysql_db_query($datamysql,$query2);
}

Well actually, to keep it very very very simple, you could just do this:

$query7="UPDATE val SET str='7',updated='1' WHERE val='2'";
$result7=mysql_db_query($datamysql,$query7);
$query2="UPDATE val SET str='5' WHERE str='4'";
$result2=mysql_db_query($datamysql,$query2);

And nothing else. If you want to update all rows where str='4', then use
that as the condition, you don't need to first select something with one
condition, and then one by one row update all the rows. You can do it allin
one update query.

--
"ohjelmoija on organismi joka muuttaa kofeiinia koodiksi" -lpk
sp**@outolempi.net | Gedoon-S @ IRCnet | rot13(xv***@bhgbyrzcv.arg)

[Jerry Stuckle]

cd****@relayeur.com wrote:

in fact, i need this order because i am rebuilding a table and i need
to do this way

i have a workaround with generating no fetch of all the table, but to
select the rows one by one
which give a stupid number of query( 200 000 rows=>200 000 query) and
it take a lot of time naturally :-)

i made this example to show my problem

is not there a possibility to fetch a special way which can take in
consideration freshness?

No. Once rows are fetched, MySQL (or any other RDB) will not update an already
fetched value just because it has been updated. That's a big reason why you
shouldn't update tables while fetching data, unless you're updating the data you
just fetched.

P.S. Please don't top post.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================