 Mysql Update Everything, Bit confusing |
|
|
|
|
  |
  Apr 8 2005, 04:20 PM
Post
#1
|
|
|
Super Member  Group: Members Posts: 204 Joined: 6-October 04 From: London, uk Member No.: 1,444  |
i need to make a script that will take a number from every record in a mysql table, takeaway 1 from them all then replace them all in the right places. so if it looked like
Record 1 5 Record 2 8 Record 3 9 then i exectue the script then it turns into: Record 1 4 Record 2 7 Record 3 8 i cant think of a way to do this so if you can help id be very greatful. Thanks in advance Andre |
 |
 
|
|
Apr 8 2005, 08:56 PM
Post
#2
|
|
|
Newbie [Level 2] Group: Members Posts: 29 Joined: 6-April 05 Member No.: 5,318 |
I'm not sure if this will work, and there is probably a better way to do this but you might as well give it a try.
ID is the primary key of the table. C1 is the column with the number to be subtracted. C2 is the number you are subjection from. T1 is the name of the table. CODE <?php $result = mysql_query("select ID, C1, C2 from T1"); while($row = mysql_fetch_array($result)) { $row[2] = $row[2] - $row[1]; mysql_query("update T1 set C2 = '$row[2]' where ID = '$row[0]"); } ?> Let me know if I screwed up and what the error is and I'll see if I can fix it.... |
|
|
|
|
Apr 10 2005, 12:55 AM
Post
#3
|
|
|
Privileged Member Group: Members Posts: 629 Joined: 26-February 05 Member No.: 3,995 |
If the column is set as a number (INT, FLOAT, DOUBLE, etc.), then I think you can just do this:
CODE mysql_query("UPDATE `tablename` SET `colname`=`colname`-1");
|
|
|
|
|
Apr 10 2005, 01:52 PM
Post
#4
|
|
|
Owner of Sub-Zero Group: Members Posts: 159 Joined: 17-November 04 Member No.: 2,325 |
Uhh.. That's just a simple query like beeseven posted..
CODE mysql_query("UPDATE tablename SET columnname=columnname - 1") or die(mysql_error()); It would just update the column as the column - 1. |
|
|
|
|
Apr 14 2005, 10:24 PM
Post
#5
|
|
|
Super Member Group: Members Posts: 378 Joined: 8-January 05 Member No.: 3,174 |
Yep, beeseven's and mike's querys should work, it only becomes tricky if the field is some sort of text, in which case you'd be forced to pull all data, convert the text to numbers, subtract one, and then store it (which would autoconvert it back to text).
|
|
|
|
|
Similar Topics
|
Lo-Fi Version | Time is now: 6th October 2008 - 11:26 AM |