Jump to content


Photo

RE-SETTING ID - the correct posting


  • Please log in to reply
7 replies to this topic

#1 nazca

nazca
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 15 July 2006 - 08:50 PM

I have over 1000 restaurants with an ID attached to each.  The ID is the primary key and not the auto-increment assigned by mysql.
I cannot order by restaurant_name, because of how the data is accessed and displayed -- confusing and wish it was that easy.
I have to first re-set the restaurant-names in alpha order in the database itself and than re-set the ID's to match so that Pizza Hut does not come before Burger King for example.  I have tried to export the data to csv and csv excel, but the error i get everytime is illegal statement terminator or something of that sort.
My only option is to re-set this table, but have no idea how.  I also tried SQLyog, but guess what... phpmyadmin will not allow it.

Hope you can help....

#2 fenway

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

Posted 15 July 2006 - 08:53 PM

The PK and _not_ auto-increment? Huh?  And still, how can you re-set the names if you can't ask the DB to do the same?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 nazca

nazca
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 15 July 2006 - 08:59 PM

Funny right, but I am using an application that assigns the ID automatically so a restaurant would have this id assigned..
Hope that clears it up a little.

#4 fenway

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

Posted 16 July 2006 - 06:12 AM

OK... but I'm still unclear about how you can magically figure out the correct order of the names, but MySQL can't.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 nazca

nazca
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 17 July 2006 - 06:37 AM

I am not saying Mysql can't, but need to know how mysql can help me to do it. When you input data into mysql it does not automatically alphabetize the restaurants for me. What it does is attached the next ID number to the next restaurant I put in and keeps the ID order correct.

#6 AndyB

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

Posted 17 July 2006 - 01:25 PM

Without an example of your data and its structure, this is very confusing.  MySQL doesn't care, and nor you should, about the order (or disorder) in which the data exist in the database.  What's important is being able to retrieve the data sorted in whatever particular order you want it processed or displayed. Without knowing any more about the data, data structure, and your query result requirements, it's hard to know what to suggest as a workable solution to your problem.
Legend has it that reading the manual never killed anyone.
My site

#7 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 17 July 2006 - 02:47 PM

Are you saying that you cannot sort the restaurant names alphabetically because the names are stored in another table? If so you will probably want to do a join or subquery to get the name from the index and then ORDER BY it. If not I would look at redesigning your database so that they are.
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#8 fenway

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

Posted 17 July 2006 - 06:11 PM

Yeah... we'll need to see some sample data, but I find it hard to imagine that some sort of ORDER BY clause won't do the trick.
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