To clarify with a code sample :
create table myTable (a int, b int)
insert into myTable (a, b) values (20, 0)
insert into myTable (a, b) values (15, 0)
insert into myTable (a, b) values (10, 0)
insert into myTable (a, b) values ( 5, 0)
insert into myTable (a, b) values ( 0, 0)
insert into myTable (a, b) values (-5, 0)
select * from myTable
a b
-- --
20 0
15 0
3 0
0 0
-5 0
update myTable set a = a - 1, b = b + 1 where a > 0
select * from myTable
a b
-- --
19 1
14 1
4 1
0 0
-5 0
drop table myTable
-----Original Message-----
From: Svensson, B.A.T. (HKG)
To: 'PHP Mailing List '
Sent: 31-5-2004 15:45
Subject: RE: Which is more ideal for updating each row at a time
in a MySQL database?
Why do you want to update one row at a time? And why do you
want to bounce this information between the web server and
the database manager?
You should let the RDBMS work as much internal as possible for
you (it is optimized to do update/insert/etc while php isn't).
If you want to substact and add a value at the same time
from all or some rows, or one row, the syntax is:
UPDATE <your table name>
SET <col name 1> = <col name 1> - <your value>,
<col name 2> = <col name 2> + <your value>
WHERE <your row filter conditions>
And then just call mysql_query() with this string.
-----Original Message-----
From: Daniel Anderson
To: PHP Mailing List
Sent: 31-5-2004 14:04
Subject: Which is more ideal for updating each row at a time
in a
MySQL database?
Hi,
can anyone recommend which of these is better for loading and updating
one row at a time in a MySQL database?
for()
foreach()
while()
if...then()
other...?
What I am trying to do is make a PHP code open a table in a MySQL
database, then it loads the information needed like Column 1 and Column
2 for example.
Such as:
Column 1 has 20 in one row and 15 in the next, Column 2 has 0 in both
rows.
What I got it to do is to subtract one value from Column 1 and add a
value to Column 2.
So after running this script once, it will end up as Column 1 having 19
for the first row and 14 for the row below it. And both rows in Column 2
having 1 in each.
The problem starts when I try updating the database, rather than saving
one at a time in each database it will replace all other rows with the
first one loaded. So instead of row 2 having 14 it will have the same as
the first row, which is 19.
Thanks to Ross and hubo for the cookie advice, I have managed to get it
working fine.
If you can recommend anything that can get my one step closer to getting
this script to parse successfully then it will be a great help,
Many thanks again,
Dan
--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
