Jump to content


Photo

Re-Setting the ID number?


  • Please log in to reply
4 replies to this topic

#1 nazca

nazca
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 13 July 2006 - 12:35 AM

I have entered restaurants into my database by city to speed the process up, but when I find another restaurant later and put it with the same city
it comes later and not in-order by name. Since i cannot order by name due to complexities, how can I re-assign the id numbers in my database so that
the restaurants are displayed alphabetically correctly within each city.  I can do a script each time for each city does not bother me.  I want to keep my data of course.  Hope you can help and thanks.

#2 cmgmyr

cmgmyr
  • Members
  • PipPipPip
  • Advanced Member
  • 1,278 posts
  • LocationUSA

Posted 13 July 2006 - 12:50 AM

one thing that you can do is change the id's to show alfa order. (change the first one that is supposed to be the first alphabetically to 1, the second to 2)

It might take a while, I would try re-thinking your database.

-Chris

#3 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 13 July 2006 - 01:36 AM

If the id is an autonumber then DON"T EVEN THINK OF CHANGING IT.  Record ids are something that should be thought of as an intrinsic part of the inner workings of MySQL and nothing to do with how you display the data.  The whole point of a relational database is that you can have table that refer to data in other tables and id is that reference.

Why can't you simply display them in alphabetical order (ORDER by restaurant_name for example)?
Legend has it that reading the manual never killed anyone.
My site

#4 fenway

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

Posted 13 July 2006 - 10:07 PM

AndyB is entirely correct -- don't even dream of touching the auto-increment value.  There must be some way to determine the correct order, so you can always do it at run time.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 13 July 2006 - 10:24 PM

it sounds to me like all you need is a multiple-field ordering clause:

ORDER BY restaurant_city ASC, restaurant_name ASC

this will sort by city first, then among each city, by restaurant name.

(agreed about the importance of leaving a key be)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users