order by 1

[dereckreynolds]

HI

I've been getting problems with the way data is extracted from a table. The table contains a column called 'name' which is a varchar(255). Each row contains strings of text...

01.jpg
02.jpg
03.jpg
04.jpg
05.jpg
06.jpg
07.jpg
08.jpg
09.jpg
10.jpg
11.jpg
12.jpg
13.jpg
14.jpg
..
100.jpg
101.jpg
102.jpg
103.jpg
..
110.jpg
111.jpg
112.jpg
..
213.jpg
214.jpg

HOWEVER, when I....

$query = "SELECT `name` FROM `tablename` where `col1` = 30 order by `name` DESC";
$result = mysql_query($query) or die("Query Query failed : " . mysql_error());

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
   foreach ($row as $array_row) {
		
			echo "<font size=\"3\">File name: $array_row <br>";
   }
}

The data is ordered like this...

File name: 185.jpg 
File name: 184.jpg 
File name: 183.jpg 
File name: 182.jpg 
File name: 181.jpg 
File name: 180.jpg 
[COLOR=Blue]File name: 18.jpg [/COLOR] 
File name: 179.jpg 
File name: 178.jpg 
File name: 177.jpg 
File name: 176.jpg 
File name: 175.jpg 
File name: 174.jpg 
File name: 173.jpg 
File name: 172.jpg 
File name: 171.jpg 
File name: 170.jpg 
[COLOR=Blue]File name: 17.jpg [/COLOR] 
File name: 169.jpg 
File name: 168.jpg 
File name: 167.jpg 
File name: 166.jpg 
File name: 165.jpg 
File name: 164.jpg 
File name: 163.jpg 
File name: 162.jpg 
File name: 161.jpg 
File name: 160.jpg 
[COLOR=Blue]File name: 16.jpg [/COLOR] 
File name: 159.jpg 
File name: 158.jpg 
File name: 157.jpg 
File name: 156.jpg 
File name: 155.jpg 
File name: 154.jpg 
File name: 153.jpg 
File name: 152.jpg 
File name: 151.jpg 
File name: 150.jpg 
[COLOR=Blue]File name: 15.jpg [/COLOR] 
File name: 149.jpg 
File name: 148.jpg 
File name: 147.jpg 
File name: 146.jpg 
File name: 145.jpg 
File name: 144.jpg 
File name: 143.jpg 
File name: 142.jpg 
File name: 141.jpg 
File name: 140.jpg 
[COLOR=Blue]File name: 14.jpg [/COLOR] 
File name: 139.jpg 
File name: 138.jpg 
File name: 137.jpg 
File name: 136.jpg 
File name: 135.jpg 
File name: 134.jpg 
File name: 133.jpg 
File name: 132.jpg 
File name: 131.jpg 
File name: 130.jpg

But what I need is the following:

File name: 185.jpg 
File name: 184.jpg 
File name: 183.jpg 
File name: 182.jpg 
File name: 181.jpg 
File name: 180.jpg 
File name: 179.jpg 
File name: 178.jpg 
File name: 177.jpg 
File name: 176.jpg 
File name: 175.jpg 
File name: 174.jpg 
File name: 173.jpg 
File name: 172.jpg 
File name: 171.jpg 
File name: 170.jpg 
File name: 169.jpg 
File name: 168.jpg 
File name: 167.jpg 
File name: 166.jpg 
File name: 165.jpg 
File name: 164.jpg 
File name: 163.jpg 
File name: 162.jpg 
File name: 161.jpg 
File name: 160.jpg 
File name: 159.jpg 
File name: 158.jpg 
File name: 157.jpg 
File name: 156.jpg 
File name: 155.jpg 
File name: 154.jpg 
File name: 153.jpg 
File name: 152.jpg 
File name: 151.jpg 
File name: 150.jpg 
..
File name: 09.jpg 
File name: 08.jpg 
File name: 07.jpg 
File name: 06.jpg 
File name: 05.jpg 
File name: 04.jpg 
File name: 03.jpg 
File name: 02.jpg 
File name: 01.jpg

I think the problem is to do with how mySQL handles a varchar(255) column in the ORDER BY clause.

Can anyone help me achieve what I need to achieve please? I guess this is not the first time a new PHP coder has hit this problem...?

Your help will be hugely appreciated because I have no hair left!!!!

 

[r937]

order by 0+substring(name,1,locate('.',name)-1) desc

will not work if there's no period in the name

rudy

http://r937.com/

SQL Consulting

 

[cLFlaVA]

What about the following:

...
ORDER BY CAST(LEFT(name, LOCATE('.', name) - 1)  AS UNSIGNED) DESC

The same as r937's solution, but just a different way of looking at it...

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[r937]

except that CAST doesn't work below version 4.0.2



rudy

http://r937.com/

SQL Consulting

 

[cLFlaVA]

quite true. there was no version specified [tt][/tt]

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[cdlvj]

I think the problem is to do with how mySQL handles a varchar(255) column in the ORDER BY clause.

This is typical of all string ordering. Probably will have to store the names as "nnnn.jpg" and zero fill spaces to make 18 come out as 0018.jpg.

 

[r937]

cdlvj, did you try the solution suggested by myself and cLFlaVA?

rudy

http://r937.com/

SQL Consulting

 

[dereckreynolds]

Ladies & Gentlemen,

I can confirm that r937's advice proved successful and now my site is working great.

Many thanks to all of you that replied. I think I've found a community that I'll come back to time and time again.

Cheers

 

[davejazz]

A solution which works well for me is to renumber my original data. Instead of:

File name: 154.jpg
File name: 153.jpg
File name: 152.jpg
File name: 151.jpg
File name: 150.jpg
File name: 15.jpg
File name: 149.jpg
File name: 148.jpg
File name: 147.jpg
File name: 146.jpg

I would rename 15.jpg to 015.jpg and the problem is solved.