“no sort” clause for mysql table columns?

[neophyte]

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]

"result rows are displayed in no particular order".

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

[Demaestro]

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]

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]

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.