[Resolved] natsort() equivalent

ucbones

Hi,

I want to order my results in the way a human being would (i.e. 9 before 10). In PHP there is the function natsort(), is there an equivalent with MySQL, or a workaround that is not too complicated. Just curious really, not particularly necessary!

It's for the page that is shown when I'm rebuilding my search engine, it lists the pages I'm rebuilding current with ORDER BY url, but this prints out things like:

Indexing: modules.php?page=1 (query took 0.135 sec)
Indexing: modules.php?page=10 (query took 0.137 sec)
Indexing: modules.php?page=11 (query took 0.137 sec)
Indexing: modules.php?page=2 (query took 0.139 sec)
Indexing: modules.php?page=3 (query took 0.146 sec)
Indexing: modules.php?page=4 (query took 0.147 sec)
Indexing: modules.php?page=5 (query took 0.155 sec)
Indexing: modules.php?page=6 (query took 0.149 sec)
Indexing: modules.php?page=7 (query took 0.158 sec)
Indexing: modules.php?page=8 (query took 0.157 sec)
Indexing: modules.php?page=9 (query took 0.152 sec)

What I'd really like is:

Indexing: modules.php?page=1 (query took 0.135 sec)
Indexing: modules.php?page=2 (query took 0.139 sec)
Indexing: modules.php?page=3 (query took 0.146 sec)
Indexing: modules.php?page=4 (query took 0.147 sec)
Indexing: modules.php?page=5 (query took 0.155 sec)
Indexing: modules.php?page=6 (query took 0.149 sec)
Indexing: modules.php?page=7 (query took 0.158 sec)
Indexing: modules.php?page=8 (query took 0.157 sec)
Indexing: modules.php?page=9 (query took 0.152 sec)
Indexing: modules.php?page=10 (query took 0.137 sec)
Indexing: modules.php?page=11 (query took 0.137 sec)

As only I see this, it doesn't matter too much, but it gives me an idea of what's going on on my site!

Many thanks,

ucbones

stolzyboy

how exactly are you getting this:

to showup

ucbones

calling a while loop to parse through my table. For each url, it makes a microtime tstamp, it processes the page, makes another microtime tstamp, prints out the URL, subtracts the later time from the earlier time, and prints that out.

the php that gets the url's is

mysql_query("DELETE FROM search");
mysql_query("ALTER TABLE search AUTO_INCREMENT= 1");
$geturl=mysql_query("SELECT url FROM searchurl ORDER BY url ASC");
echo "<ol>";
while ($row = mysql_fetch_array($geturl)){
	$url=$environment["absolute"].$row['url'];
	populate($url,true);
}
echo "</ol>";

where populate($url,$output=false) is the function that shows what I placed in my above post.

ucbones

stolzyboy

does $row['url'] hold all of this "modules.php?page=9"

ucbones

indeed 🙂

stolzyboy

hmmmm.... well that is why ORDER BY isn't working the way you are hoping it to, it will always do

page=1
page=10
page=11
page=2

cuz it only looks one digit/integer in when using varchar's, if you had just

1, 2, 3......9, 10, 11, etc...

it would work correctly

you just may have to use php to do it for you if you need to

johnnyv

It has always been a problem
if they were named 001 002 003 ... 011 .. 099 100 etc then it would work.

But alas that require thinking before starting to code which i am not capable of usually 😉

barand

If you have a mysql table with column 'page'

          

             page
row1       1
row2       10
row3       2

You can simulate a natural order sort with

SELECT page FROM table ORDER BY LPAD(page,4,'0')

hth

ucbones

thanks barand,

that won't work for me unfortunately, because my results are stored as e.g. events/index.php?ID=10, or modules.php?ID=7, so LPAD() won't work.

🙁

Looks like I'll just have to accept it. Like I said, it really didn't matter, just sensed a challenge, and didn't quite like to back down :rolleyes:

barand

Read data in to an array and use [man]usort[/man]. You sort function could use [man]strnatcmp[/man] to give correct sequence.

hth

ucbones

works a charm using natsort, having passed the information into an array.

barand, you are my hero 😉