Welcome to WebmasterWorld Guest from 107.20.34.173

Forum Moderators: open

Message Too Old, No Replies

no sort clause for mysql table columns?

     

neophyte

9:50 am on Sep 7, 2010 (gmt 0)

10+ Year Member



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 Im pulling this from the database via php, Im thinking that maybe Ive shot myself in the foot instead.

The basic scenario is that Ive 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 Im 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 wasnt aware that a varchar field would automatically sort but obviously I was wrong. Ive 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 thats 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

12:33 pm on Sep 7, 2010 (gmt 0)

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



"result rows are displayed in no particular order".

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

Demaestro

1:56 pm on Sep 7, 2010 (gmt 0)

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



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

4:07 pm on Sep 7, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

8:42 am on Sep 8, 2010 (gmt 0)

10+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month