SQL+PHP - Order by..??

[venet]

Hey team,

I want to know how I should go about re-ordering records sent from a db table - but I want to order the data in a custom manner, i.e. allowing a user to change the position of the records.

I have a table (for example sakes lets call this table TABLE1).
TABLE1 has 4 fields:

ID, TITLE, CONTENT [and] VISIBLE. (all these fields are self explanatory - with TITLE and CONTENT being text feilds and VISIBLE being a varchar of either '1' or '0'.)

See, simply adding another field in the table is the way I'm thinking. We'd call this feild 'POSITION'. If I assign an unique value to each record's POSITION feild, then on a click of a button, my script would somehow +1 the POSITION of this record, and -1 the position of the previous record.

I'm sure if I thought long and hard about this, I could probably write a script to reflect this. Trouble is, I think ahead; what happens when a record is DELETED, or a records VISIBLE = 0?!?

I can't think what to do! :scared:

Please help me develop this script! All ideas would be greatly appreciated!

EDIT: PS. I'm not necessarily asking for the entire code to be written, I'm more interested in the logic behind it. I'm seriously perplexed!

[pritaeas]

I use a similar construct. I do not physically delete records though. I have a deleted column. The position column of a "deleted" row is -1 and always excluded from my results. Hidden rows I keep ordered between the others.

[venet]

OK regardless of deleting. making invisible or setting POSITIONING to -1, this method will not work yet - in my head at least. But we'll use your method of 'deleting' in my explanation, as physically deleting rows is of course bad practice.

So, lets say we have 5 records in our TABLE1:

(remember format of: ID, TITLE, CONTENT, VISIBLE, POSITION)

1, Mr, Jared White, 1, 1
2, Mrs, Emily White, 1, 2
3, Master, Elijah White, 1, 3
4, Ms, Brooke Harvey, 1, 4
5, Wing Commander, Neil White, 1, 5

Now lets "DELETE" (ie. set POSITION to -1) two records (rows 3 and 4):

1, Mr, Jared White, 1, 1
2, Mrs, Emily White, 1, 2
3, Master, Elijah White, 1, -1
4, Ms, Brooke Harvey, 1, -1
5, Wing Commander, Neil White, 1, 5

the viewer will see:

1, Mr, Jared White, 1, 1
2, Mrs, Emily White, 1, 2
5, Wing Commander, Neil White, 1, 5

SO

When they go to click [move up + 1] button on record 5, the record positioning will appear to have been unchanged; POSITION 5 would now be POSITION 4, but position 4 obviously is still more than position 2, so they would still appear to be in this order.
The user would have to click two more times before he would get the desired result.

Thats what I was trying to explain before. Any ideas for a solution to 'refresh' all of the POSITION fields on the DB?

[LethargicCoder]

For deletion (either full delete or changing position to -1), execute an update to change all positions greater than that by -1. UPDATE table1 set POSITION = POSITION -1 where POSITION is > $deletedpositon Do this for each position removed, one at a time. If you allow multiples to be deleted, it's a bit more complicated. Loop over array of deleted positions starting with the highest first, running this update for each one.

[LethargicCoder]

For deletion (either full delete or changing position to -1), execute an update to change all positions greater than that by -1. UPDATE table1 set POSITION = POSITION -1 where POSITION is > $deletedpositon Do this for each position removed, one at a time. If you allow multiples to be deleted, it's a bit more complicated. Loop over array of deleted positions starting with the highest first, running this update for each one.

[venet]

Genius. Of course it is!
Then to move around I could have a link update.php?id=$id

And on update.php the code would sort of look like:
(sorry in advance for the erroneous coding, i'm at work lol)

$primary_id = $_get;

Select position from table1 where id = '$primary_id';
{
$pos_one = mysql_fetcharray row;
}

$pos_two = $pos_one-1;

Update table1 set position = '$pos_one' where position = '$pos_two';

Update table1 set position = '$pos_two' where id = '$primary_id';

[sallecpt]

simple example for custom / dynamic fields/order by.

post your field and field value in the $_GET format (in other words, in the address bar) ie. domain.com/index.php?field=fieldvalue

in your SQL : select * table where ".$_GET." = ".$_GET."