using mysql query to reverse fields?

[michaellunsford]

Okay I can do this in PHP, but I don't have the mysql password for this database (which is associated with an application on the server).

I guess I could create another user with access, but I thought this would be a fun experiment (if it's even possible).

Okay, enough schtuff. I inserted data in a phpbb bulliten board in the order I want it to appear. SURPRISE, it appears in reverse order -- most recent post being at the top of the list. (yes, I can be a big dummy sometimes).

in myPhpAdmin, I'd like to "reverse" the timestamp field for all of the posts so that the last post has the earliest timestamp and the first post has the oldest. I can SELECT * FROM `table` ORDER BY `keyfield` ASC -- to get it in the order I want, but how do I flip the timestamp fields?

hmm..

[fenway]

And why can't use sort by timestamp DESC?

[michaellunsford]

The phpBB bulletin board automatically sorts the way it does. I'm trying to alter the field it is sorting by.

[fenway]

I don't understand... you want it sorted the way it is, but then flipped?  I'm confused.

[michaellunsford]

phpbb, a bulliten board (much like this one) automatically sorts by `timestamp` DESC (most recent first). The order I'd like them to be in is the reverse of this. The solution I had come up with was to take the `timestamp` of `keyfield` 1, and apply it to `keyfield` 30. here's a breakdown:

keyfield 1, timestamp 1152806367
keyfield 2, timestamp 1153256253
keyfield 3, timestamp 1153256277
keyfield 4, timestamp 1153256299
keyfield 5, timestamp 1153256318
etc...

phpbb is ordering by timestamp DESC. So, reversing the value in the timestamp fields would do the trick.

in PHP, I'd create something kind of like this:
[code]
$result1=mysql_query("SELECT * FROM `table` ORDER BY `keyfield` ASC");
$result2=mysql_query("SELECT * FROM `table` ORDER BY `keyfield` DESC");

while($mykey=mysql_fetch_assoc($result1)) {
    $mydate=mysql_fetch_assoc($result2);
    mysql_query("UPDATE `table` SET `timestamp` = '".$mydate['timestamp']."' WHERE `keyfield` = '".$mykey['keyfield']."'");
}
[/code] the code might have some syntax errors, but you get the general idea.

[fenway]

I still must be missing something... why can't you simply change the query itself, instead of every single timestamp for all time?  At worst, you can always iterate through the rows backwards if you're really stuck.

[michaellunsford]

If I change the query, all posts on all boards will be reverse order. So, that post from 2004 will always be first..

Think of this like the FAQ section here. The FAQs are in order... like step 1, step 2, step 3... I went into the bulletin board and entered them in order from first to last -- not realizing that the most recent posts show up first. So, now I have all of the FAQs entered, but they're backward: Step 3, then step 2, then step 1. So this is a quick fix for my bobo, not a permanent change to the way all forum topics will appear.

[fenway]

Oh, now I get it.  Sorry about being so thick.  Well, if you can rely on the UIDs matching the timestamp order, you could always "cheat" by updating the timestamp by subtracting, say, X seconds from each one, where x = uid.  That would reverse the order.

[michaellunsford]

well the bad news is the UIDs start with 1 and increment from there. They took no less than 15 seconds each to post, and some of them are separated by days.

[fenway]

Ok... well, then pull them back in whatever order they're current in, unshift onto an array, and then run though them again and update the timestamp accordingly... that should do the trick.

[michaellunsford]

is there a way to do this with a mysql query, or will I need to create a php function?

[fenway]

I'd recommend just doing it in PHP, since it's just a one-off fix.