Sorting alpha number query results

(@bloke)

I have this query and I want the files to be displayed in order. The problem is they are Alpha numeric. I have tried sorting them also and nothing seems to work. Sometimes a few files that should be at the top are at the bottom. Notice some have spaces also. The files follow this format.

ABC123AB.gif
ABC123AB.jpg
DCG567CD.gif
DCG567CD.jpg
FG LMN WST.gif
FG LMN WST.gif

global $wpdb;
$out = "";
$query = "SELECT * FROM files WHERE prod_id = '". $this->curProduct ."' ORDER BY file_name ASC";
$files = $wpdb->get_results($query, ARRAY_A);
if(count($files) >0) {
$out .= "<h2>Title Here</h2>";
$out .= "<table id='xxx'>\n";
$row = 0;
foreach($files as $file) {
if($row % 2 == 1)
$out .= '<tr>';
	else
$out .= '<tr class="even">';
$out .= '<td width="50"><a href="...../media/'. trim($file['file_name']) .'" target="_blank"><img src="/_image.png" alt="icon" /></a></td>';
$out .= '<td>'. $file['file_name'] . '</td>';
$out .= '</tr>';
$row++;
}
$out .= "</table>";
}
return $out;
}

Viewing 12 replies - 16 through 27 (of 27 total)

← 1 2

s_ha_dum
(@apljdi)

13 years, 8 months ago

No I was given the file names as they are and now I need to order them.

Well, computers order somewhat stupidly and it is very difficult to do it differently. I am pretty sure that is why vtxyzzy suggested trying to name the files as consistently as possible.

Thread Starter Bloke
(@bloke)

13 years, 8 months ago

AB51MN50.jpg
should come before AB51MN100.jpg second in the list.

vtxyzzy
(@vtxyzzy)

13 years, 8 months ago

A sort compares character by character until it either finds a difference or the end of the string.

‘AB51MN50.jpg’ and ‘AB51MN100.jpg’ are the same in the first six characters: ‘AB51MN’. The seventh character is different: ‘5’ versus ‘1’. One is less than 5, so ‘AB51MN100.jpg’ comes first.

Does the list ever change? Do you add new files, or remove any?

Thread Starter Bloke
(@bloke)

13 years, 8 months ago

Yes new files are added and I have no control of the file names.
AB51MN175 w.jpg has 11 characters (note the space) AB51MN50.jpg should come before it right?

s_ha_dum
(@apljdi)

13 years, 8 months ago

No I was given the file names as they are and now I need to order them.

No it shouldn’t. That isn’t how alphabetization works. Even ‘human’ alphabetization doesn’t work like that. Why do you think that ‘5’ should come before ‘1’?

I think you have imagined some odd alphabetization scheme. If you can explain it, it might be possible, but I don’t know what you going for at this point.

vtxyzzy
(@vtxyzzy)

13 years, 8 months ago

Can you add a new field to the files table? If so it should be possible to add a sort order column.

If you can’t add a field to the files table, can you add a new table to define a sort order?

s_ha_dum
(@apljdi)

13 years, 8 months ago

Yes new files are added and I have no control of the file names.
AB51MN175 w.jpg has 11 characters (note the space) AB51MN50.jpg should come before it right?

No. The comparison is character by character from the left. Length is not a factor. This is exactly the way ordinary human alphabetization works.

a
ab
abc
abd
ac
acd
acdc
b
ba
bac
bbc
c

Spaces will count as the first possible character… basically the order you see in an ASCII table.
s_ha_dum
(@apljdi)

13 years, 8 months ago
If you want string length to matter, try this:
```
$query = "SELECT * FROM files WHERE prod_id = '". $this->curProduct ."' ORDER BY LENGTH(file_name),file_name ASC";
```
No guarantees. I don’t know if that will give you the order you want and I don’t know what it will do to query speed, either.
Thread Starter Bloke
(@bloke)

13 years, 8 months ago

Thanks I tried that above with all the other queries.

s_ha_dum
(@apljdi)

13 years, 8 months ago

oops… so you did.

Do you have so many files that you can’t sort them with PHP?

s_ha_dum
(@apljdi)

13 years, 8 months ago

The problem you are having is with the numbers. You want “100” and “5”, for example, to be treated like numbers and not like characters. This is what PHP calls natcasesort(). I don’t know it will work in your case because of the mix of the letters and numbers that you have. I don’t think MySQL does that kind of sorting at all.

vtxyzzy
(@vtxyzzy)

13 years, 8 months ago

I repeat: There is no way to do this given the information that we have now!

It will be necessary to add a sort order field in some way, for instance in the files table, or in a separate table that can be linked.