RSS

Re: MYSQL: update all fields in a table (increase value by one)

Please support our PHP advertiser: Lunarpages PHP Web Hosting

MYSQL: update all fields in a table (increase value by one)

•

Join Date: May 2005

Posts: 230

Reputation: nathanpacker is an unknown quantity at this point

Rep Power: 4

Solved Threads: 0

nathanpacker nathanpacker is offline

Offline

Posting Whiz in Training

Re: MYSQL: update all fields in a table (increase value by one)

Nov 1st, 2007

•

Originally Posted by cscgal

Can't you do UPDATE table SET col_value = col_value + 1?

You'd think, but I tried that and it didn't work either. It seems that any UPDATE I do in a loop, just makes the changes to every row, even if I specify only one row. There's got to be a simple solution to this problem. And to be honest cscgal, I thought yours was it! Anyway, here's a little script I wrote just to troubleshoot this issue. It prints out all the table values before it runs the UPDATE command, then echoes out the UPDATE commands with their values, and then echoes out the values after the operation is complete.

It all looks like it should go fine, but when you see the final values, it's just screwy. I also included a link at the bottom to a script to set all the values back to normal, so anyone looking at the thread can see it as well.

To run the script, just go to www.packerworld.com/budget/upcat.php

Here is the script:

Help with Code Tags

(Toggle Plain Text)

<?php
include 'config.php';

//Printing out values before the change
$query="SELECT * FROM categories";
	$result=mysql_query($query) or die(mysql_error());
	while($row=mysql_fetch_array($result))
	{
	echo"ID " . $row['id'] . ": " . $row['cat_num'] . "<br>";
	}
//Getting the highest cat_num there is
$query = "SELECT MAX(cat_num) FROM categories";  
	$result = mysql_query($query) or die(mysql_error());
	while($row = mysql_fetch_array($result))
	{
		$max=$row['MAX(cat_num)'];	
	}
	
//Initialize the counter
$i=1;
	//Loop through all the cat_num's...
	while($i<=$max)
	{
		$new_cat=$i+1;
		echo "UPDATE categories SET cat_num=$new_cat WHERE cat_num=$i...<br>";
		mysql_query("UPDATE categories SET cat_num=$new_cat WHERE cat_num=$i") or die(mysql_error());
		$i++;
	}
	//Print out the values after the process is complete...
	$query="SELECT * FROM categories";
	$result=mysql_query($query) or die(mysql_error());
	while($row=mysql_fetch_array($result))
	{
	echo"ID " . $row['id'] . ": " . $row['cat_num'] . "<br>";
	}
	
echo "<a href='normal.php'>Click here to reset cat_num's back to their original incremental orders.</a>";

?>

DaniWeb Home > Forums > Web Development > PHP > MYSQL: update all fields...

Re: MYSQL: update all fields in a table (increase value by one)

MYSQL: update all fields in a table (increase value by one)

Re: MYSQL: update all fields in a table (increase value by one)