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

[nathanpacker]

Hey, I've got a simple little table that has only 3 fields. ID (primary key, auto-increment), cat_name, and cat_num.

I need to know how to get my php script to go into that table, and increase the value in cat_num by one on every row.

So for instance, here is my table:

ID cat_name cat_num
3 Music 1
5 Movies 2
6 Food 3
10 Other 4

I need a script that will change the cat_num for all those rows to be one higher than they already are. I tried using a for loop, and a while loop, but can't seem to get it to work. Anyone know an easy way to do this?

Edit, sorry, just realized the table doesn't look nice and neat like I typed it. But hopefully you get the drift.

[ryan_vietnow]

try this one:

$query="Select cat_num from table";
$result=mysql_query($query)
$num=mysql_num_rows($result);
$i=0;
while($i<$num)
{
$cat_num=mysql_result($result,$i,"cat_num");
$cat_new=$cat_num + 1(or $cat_num++);

$query="UPDATE table SET
             cat_num='$cat_new' ";
mysql_query($query);
$i++;
}

I think that's it,let me know if it solves your problem thanks...

[nathanpacker]

Thanks. Sorry it's taken me a while to reply. I haven't had time to get back to this part until now. Your code make sense, and it looks to me like it should work, but it's not. What it ends up doing is setting cat_num on EVERY row to the same number, in fact, one number higher than the highest number that was in there before.

To make it clear. I had seven entries in the table. The cat_num's were 1,2,3,4,5,6,7. After running the script, it changed all of them to 8. Here is the exact code I used. Anything I've done wrong?

<?php
include 'config.php';
$query="SELECT cat_num FROM categories";
$result=mysql_query($query);
$num=mysql_num_rows($result);
$i=0;
while($i<$num)
{
	$cat_num=mysql_result($result,$i,"cat_num");
	$cat_new=$cat_num+1;
	
	$query="UPDATE categories SET cat_num='$cat_new'";
	mysql_query($query);
	$i++;
}
echo "Done!";
?>

And I tried using $cat_new=$cat_num++ as well, and it made them all 7 instead of 8.

[iamthwee]

This is really simple, just fire up mysql from the command line and play with the commands. You don't need php.

[nathanpacker]

This is really simple, just fire up mysql from the command line and play with the commands. You don't need php.

Well I've been trying that, and playing around with the commands, but can't seem to get it to work quite right. Funny thing is, in my php code, I echoed out the variables immediately before the UPDATE command runs, and it echoes out all the variables correctly. However, what I think is happening is that every time the while loop runs, the UPDATE command is updating ALL of the rows, even though I've told to only UPDATE on a specific row. Can't figure out why. But thanks for your help.

[Dani]

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

[fatihpiristine]

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

this will work if the column type is int.

[nathanpacker]

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:

<?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>";

?>

[fatihpiristine]

why dont you try stored procedures??

sp can do it better and faster

[nathanpacker]

Well, if I knew how to create and use them, and if it were possible through phpMyAdmin, seeing as that is the only access I have to my databases, I might try that. However, I'm not too worried about speed and whatnot, as this procedure will never be updating more than 20, 30 rows max. And even then, to do that, wouldn't I have to have the SQL commands figured out. Which so far, I can't even figure out what SQL commans would do it. If we could find a sequence of SQL commands to do it, my problem would be solved.

Oh, and another point, I will not simply always be updating every row in the database. That's just where I'm starting to figure out the problem. In reality, I will be updating some or most of the rows. For example, say I have 20 rows, with cat_num's 1-20. I will be inserting a row, making it's cat_num say 13, then incrementing all rows with cat_num's 13 and up one integer.

[fatihpiristine]

i have posted here very good stored procedure..
it has a little problem but you can use it as model.
link is here
http://www.daniweb.com/forums/thread94001.html

[nathanpacker]

Thanks, but I've never used stored procedures, and I don't quite understand them or how to implement them yet. Besides, I don't think a SP would work, as a SP is static, and I will changing the rows that need to be updated dynamically through php.

[fatihpiristine]

i ll write u simple sp soon

[fatihpiristine]

your sql code is here.... stored procedure
i tested and works very well.. no problem even you delete any problem from anywhere.
apply same code in php you number of records and while loop. thats all.
anything else?

begin
declare cat_num_upd, total_record, cursore int;
set total_record = ( select count(*) from mytest);
set cursore = 0;
while cursore <= total_record do
set cat_num_upd = ( select cat_num from mytest where id = cursore ) + 1;
update mytest set cat_num = cat_num_upd where id = cursore;
set cursore = ( cursore + 1 );
end while;
end

[iamthwee]

Why do you keep saying row. There is no such thing as a row in databases! Using that term is quite confusing.

[fatihpiristine]

what do you mean?

[iamthwee]

All I'm saying is the term row can be quite misleading. There is a tendency to think of rows like those in a text file. Where row two is below row one etc.

Obviously this topological relationship doesn't hold for databases.

As long as the OP understands this it is ok to use the term row. I was under the impression he might be confused. If I'm incorrect, and he does realize this, then I apologize.

[Utokverek]

Not necro, was looking around mysql and bumped across this.
The problem was that whenever you did an update, you forgot to specify the actual line with a where clause, and in every iteration, it updated the whole table.

I'd use the following code in this case:

$query=mysql_query("SELECT * FROM table");
$rownum=mysql_num_rows($query);
for($i=0;$i<$rownum;$i++)
{
    $query=mysql_query("SELECT * FROM table LIMIT $i,1");
    while($select=mysql_fetch_array($query))
    {
         $update=mysql_query("UPDATE table SET cat_num='$select[cat_num]'+1");
    }
}

[Mulder253]

I thought you just do auto increment on cat num, every entry should increase by one, so long as you have a first.
create a table with id cat_name and cat_num
id is primary and a_i, cat_num is a_i.
now, import this code(fill in the places that are marked with a

[Proboris]

If i am you, i will build the query statemen inside of the while and then i will send the big sentence to MySQL or wherever, but, be carefull, you have a limit of bits to send to MySQL in the query, i guess by default is 2MB, not sure.

$querys = '';
        while($i<=$max)
        {
            $new_cat=$i+1;
            $querys .= "UPDATE categories SET cat_num=$new_cat WHERE cat_num=$i; ";

            $i++;
        }

if($querys != '')
    mysql_query($querys) or die(mysql_error());

[iopsd5]

$query = "UPDATE table SET variable = variable + '1' WHERE variable2 = 'value'";
mysql_query($query) or mysql_close;

This will work just tried it I had the same problem. The similar soultion above forgets to add where therefore all the data in the column changes.

[tgmsocal]

I know this has been dead for a few years, but when doing a google search I found this thread. I solved this issue avoiding any kind of long query string or php loop. Here is my simple solution:

$query = "UPDATE `table_name` SET `order` = `order`-1 WHERE `order` > 2";
@mysql_query($query);

You can also do any other mathematic function (+1, etc) and can use other operators for the WHERE function (<, <=, >=, etc).

Just thought I'd share that there is a very easy solution for this, avoiding complicated while loops or gigantic MySQL queries.