Jump to content

Archived

This topic is now archived and is closed to further replies.

michaellunsford

using mysql query to reverse fields?

Recommended Posts

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..

Share this post


Link to post
Share on other sites
And why can't use sort by timestamp DESC?

Share this post


Link to post
Share on other sites
The phpBB bulletin board automatically sorts the way it does. I'm trying to alter the field it is sorting by.

Share this post


Link to post
Share on other sites
I don't understand... you want it sorted the way it is, but then flipped?  I'm confused.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
I'd recommend just doing it in PHP, since it's just a one-off fix.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.