Incrementing specific column entries by 1

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Incrementing specific column entries by 1

Post by mikebr »

I am working with php on a MySQL database that shows a list of entries that are shown in the user specific order which is controlled by a column called "order" in my database, the order is then controlled by returning the entries in order of the "order" column whose entries are 1, 2, 3 ,4 etc. I allow the user to change an order entry by selecting the new order number an entry should be located at, for example if the user wants an entry shown in the order where it will be number 3 in the list and it is number 8 at the monent i change number 8 to "000" then i want to add 1 to all the entries below 8 and above 3, then i change entry "000" to number 3. Problem is I can't seem to find an easy way to increment by 1 these column entries, I guess the code would be something like:

Code: Select all

<?php

$sql = "UPDATE table SET order = '$order++' WHERE order < $new_order AND order >= $new_order";

?>
but this does not work as I need to find the values of the order column without using $order as $order is an existing value and not much use for the "SET order = '$order++'" part of the query?
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

this should do it

Code: Select all

$sql = "UPDATE table SET order=order+1 WHERE .......";
I didn't finish the WHERE clause as I don't see how

Code: Select all

order < $new_order AND order >= $new_order
could be correct!
Post Reply