homepage Welcome to WebmasterWorld Guest from 54.146.190.193
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
“no sort” clause for mysql table columns?
neophyte

10+ Year Member



 
Msg#: 4197555 posted 9:50 am on Sep 7, 2010 (gmt 0)

Hello All –

I ***thought*** I came up with this interesting way to build a navigation system with drop down and fly out menus but now that I’m pulling this from the database via php, I’m thinking that maybe I’ve shot myself in the foot instead.

The basic scenario is that I’ve got a mixed order varchar column – designated as the primary key – that looks like this:

03.00.00
03.00.01
03.01.00
03.01.01
03.01.02
03.01.03
03.00.02
03.00.03

This is all fine as I’m inputting the data directly into the table (not via an insert or update), but after I finish hand-editing and then – then save the table and re-open it – that column has now resorted – without me doing anything – to:

03.00.00
03.00.01
03.00.02
03.00.03
03.01.00
03.01.01
03.01.02
03.01.03

I wasn’t aware that a varchar field would automatically sort but obviously I was wrong. I’ve even taken OFF the primary key designation and it still sorts.

Is there anything that I can do to stop this from happening?

Do I need to have another column – that’s a PK and that holds the correct numeric sort order – in order to retain the order of this column?

Any insight greatly appreciated!

Neophyte

 

phranque

WebmasterWorld Administrator phranque us a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



 
Msg#: 4197555 posted 12:33 pm on Sep 7, 2010 (gmt 0)

"result rows are displayed in no particular order".

you might try ORDER BY NULL to see if that "unsorts" the results.

Demaestro

WebmasterWorld Senior Member demaestro us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4197555 posted 1:56 pm on Sep 7, 2010 (gmt 0)

I think in MySql you can order by RAND() but not sure that is what you want.

Just create a new field on the table that is the sort_order

Then put the order you want in that field and sort on it.

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4197555 posted 4:07 pm on Sep 7, 2010 (gmt 0)

A primary key is basically an index, so the data will be sorted according to it. Removing the index will not automatically re-order the data, so the behavior you experienced sounds right to me.

then save the table and re-open it

It sounds like the client interface you are using to open the table may have some underlying default sort that it uses, which may or may not be the order the data is stored in the table.

If you do not want the data sorted by the designated column, then adding an additional sort field, as Demaestro suggested, should get you what you want.

neophyte

10+ Year Member



 
Msg#: 4197555 posted 8:42 am on Sep 8, 2010 (gmt 0)

phranque, Demaestro and LifeinAsia

Thanks to you all for weighing in on my issue.

LifeinAsia -

I think that you're right about my client interface (Navicat 8) probably has some default sorting routine, but I couldn't find any documentation of same or how to disable it.

Demaestro and LifeinAsia -

Adding an an additional sort field - primary key - does seem the path of least resistance (at least for the time being, as doing this will lead to re-numbering headaches - on the sort field - when nav updates/alterations are needed). However, doing this has solved my initial mystery sorting problem.

Thanks to you all.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved