php - MySQL Order By Version Number + Name -

php - MySQL Order By Version Number + Name -

i have table of version numbers programme in mysql database, organized this:

versionnumber ---- 1.4.0 1.5.0 1.6 1.6.1 1.6.2 1.7devbuild1 1.7devbuild2 1.7devbuild3 1.7 1.8

i looked how order version numbers, dots, in mysql queries, , found pretty creative solution elsewhere on so.

select * versions order inet_aton(substring_index(concat(versionnumber,'.0.0.0'),'.',4))

this works ordering version number if ip address in inet_aton. works. sort of. organizes version numbers correctly, doesn't recognize dev builds, instead putting them top , ignoring them because contain text, ip addresses should not contain.

1.7devbuild1 1.7devbuild2 1.7devbuild3 1.4.0 1.5.0 1.6 1.6.1 1.6.2 1.7 1.8

so had idea: replace letters a-z nothing, , way contain numbers , dots. mysql can't regexp in replace. there way or have sort returned array in php?

now, might suggest alter "devbuild3" ".3", users looking old version see ".3" instead of "devbuild3".

if can split versionnumber 2 columns, versionnumber , versionsuffix, can use:

order inet_aton(substring_index(concat(versionnumber,'.0.0.0'),'.',4)), versionsuffix

php mysql