Switching records

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ace2600
Forum Commoner
Posts: 30
Joined: Fri Jun 21, 2002 12:12 am

Switching records

Post by ace2600 »

2 questions:
1) When using the update command in MySql, is it ok to replace my primary key with another number?
2) I am trying to create a sort program, is there any sql or php commands that will switch 2 records (I want the db to be in alphabetical order for display purposes)?
Thank you,
Ace
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Answering your second question first, why not just use an "ORDER BY columnnam" clause to to return the results in the order you like. The rows will still be in the database in some "random" order, but you get to see them in the order you want.

Yes its possible to manually update a primary key, but I strongly suggest not doing it.
ace2600
Forum Commoner
Posts: 30
Joined: Fri Jun 21, 2002 12:12 am

Post by ace2600 »

I was thinking about using order by, but I first want to sort alphabetically by state and then alphabetically by county for states that are the same. Can order by do this?
Does anyone have a link for more information on using order by?
Thanx
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

you can do "ORDER BY state, county" which means that it will first sort by the state column and then by country within states that are the same. You can use just about as many fields as you like to sort by.
ace2600
Forum Commoner
Posts: 30
Joined: Fri Jun 21, 2002 12:12 am

Post by ace2600 »

Wow! If only I would have used ORDER BY b4 I spent so much time on a sort program. Order by worked great, but how could I make it do the opposite when ordering say from z-a or 10-1.
Also is order by case sensitive or does it order all the ones alphabetically no matter if caps or not.
Thanks a lot!
-Ace
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

ORDER BY state DESC, county ASC ... for descending/ascending sorts.

Order by is case-senstive, but in PostGreSQL, I can do ORDER BY lowercase(state) to fake a case-insensitive sort. I would assume MySQL has a similar function, possibly with a different name.
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post by Takuma »

Cool neilsene, thanks for the ORDER tip! :D
Post Reply