CODEWITHSUNDEEP
phpmysql
Md Kowsar Hossain
Md Kowsar Hossain

Reputation: 137

UPDATE all rows after 2nd row,by ADDING 1 to these rows

I have a table like this:

id  subject        position         visible

1   Home               1               1
2   About us           2               1
3   Our partners       3               1
18  Products           4               1
19  Extra              5               0

I want to add number 1 to all rows after position=2, like this:

id  subject        position         visible

1   Home               1               1
2   About us           2               1
3   Our partners       4               1
18  Products           5               1
19  Extra              6               0

I tried like this, but this doesnt work:

$row=mysql_query("SELECT * FROM table");
$count=mysql_num_rows($row);
$position=3;
$add=($position+1);
while($position<=$count)
{
    $sql="UPDATE subjects SET position=$add WHERE position=$position";
    mysql_query($sql);
    $add++;
    $position++;
}

After applying above code, my table looks like this:

id  subject        position         visible

1   Home               1               1
2   About us           2               1
3   Our partners       6               1
18  Products           6               1
19  Extra              6               0

Is there any solution?

Upvotes: 1

Views: 185

Answers (3)

kasavbere
kasavbere

Reputation: 6003

 update subjects set position = position +1 where position >2

Upvotes: 1

juergen d
juergen d

Reputation: 204854

UPDATE subjects SET position=position + 1 WHERE position >= 3

Upvotes: 3

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324750

Why not just:

UPDATE subjects SET position=position+1 WHERE position>2

Upvotes: 11

Related Questions