Custom field sorting based on an int

Question

If I have a list of database fields:

id    order    title
1     3        This is a post
2     1        This is another post
3     2        This is also a post
4     4        This is still a post

And I want change the order of these based on order.
If I set order on id #1 to 2 I want order on id #3 to be set to 3.
If I set order on id #4 to 1 I want order on id #1 to be 4, order on id #2 to be 2, order on id #3 to be 3 etc. etc.

How can I do this? I think it should be quite simple but I can't figure it out.
I have searched but I don't know what to search for...

unable to understand the question!!!!! which ids you r referring to id or order — Vipin Jain
– Vipin Jain, Commented Apr 30, 2012 at 7:29
the process you trying to achieve is not possible as there is no pattern specified according to your question. if it was a simple swap it can be done but this cant be done by using simple php, This requires a very high level of understanding with Artificial intelligence — Vipin Jain
– Vipin Jain, Commented Apr 30, 2012 at 7:53

eggyal · Accepted Answer · 2012-04-30 07:59:12Z

3

You can do it in SQL with two UPDATE statements:

START TRANSACTION;
SELECT order FROM t WHERE order BETWEEN $new_order AND $old_order FOR UPDATE;

UPDATE t SET order = order + 1  WHERE order BETWEEN $new_order AND $old_order;
UPDATE t SET order = $new_order WHERE id = $id;

COMMIT;

I have locked the table during these updates to prevent concurrency issues (however, note that transactions/locks are only applicable to InnoDB tables).

edited Apr 30, 2012 at 7:59

answered Apr 30, 2012 at 7:46

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Patrik Over a year ago

Like this? LOCK TABLES posts WRITE

eggyal Over a year ago

@Patrik: I've shown a lock (using SELECT ... FOR UPDATE) in my updated answer. Note that only InnoDB tables can be locked.

kmkaplan · Accepted Answer · 2012-04-30 07:42:27Z

1

First you have to find the $other_row, the one that has the order value you want to reuse. Then change each row’s value.

$id_to_set = 1;
$order_to_set = 2;

// Find the order that will need to be swapped
$result = mysql_query("select order from xxx where id = $id_to_set");
$row = mysql_fetch_row($result);
// Find the other row that will be modified
$result = mysql_query("select id from xxx where order = $order_to_set");
$other_row = mysql_fetch_row($result);

// Do the actual swapping
mysql_query("update xxxx set order = $order_to_set where id = $id_to_set");
mysql_query("update xxxx set order = " . $row['order'] . " where id = " . $other_row['id']);

answered Apr 30, 2012 at 7:42

kmkaplan

19k4 gold badges55 silver badges65 bronze badges

Comments

artragis · Accepted Answer · 2012-04-30 07:23:24Z

0

there are two ways : read SQL doc and use the ORDER BY close in your query

read PHP doc and use usort function

answered Apr 30, 2012 at 7:23

artragis

3,7031 gold badge21 silver badges32 bronze badges

Collectives™ on Stack Overflow

Custom field sorting based on an int

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related