Jump to content


Photo

using mysql query to reverse fields?


  • Please log in to reply
11 replies to this topic

#1 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 18 July 2006 - 09:55 PM

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

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 18 July 2006 - 11:18 PM

And why can't use sort by timestamp DESC?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 18 July 2006 - 11:30 PM

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

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 18 July 2006 - 11:58 PM

I don't understand... you want it sorted the way it is, but then flipped?  I'm confused.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 19 July 2006 - 01:06 AM

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:
$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']."'");
}
the code might have some syntax errors, but you get the general idea.

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 19 July 2006 - 01:10 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 19 July 2006 - 01:18 AM

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.

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 19 July 2006 - 01:49 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 19 July 2006 - 01:54 AM

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.

#10 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 19 July 2006 - 02:20 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#11 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 19 July 2006 - 02:41 AM

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

#12 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 19 July 2006 - 03:49 PM

I'd recommend just doing it in PHP, since it's just a one-off fix.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users