Sorting alpha number query results

  • Bloke

    (@bloke)


    13 years, 8 months ago

    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 15 replies - 1 through 15 (of 27 total)
1 2
  • vtxyzzy

    (@vtxyzzy)

    13 years, 8 months ago

    Please show the list you actually get and what you would like to get.

    Thread Starter Bloke

    (@bloke)

    13 years, 8 months ago

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

    No matter how I sort it it will put most of it in order like this except ABC123AB.gif, ABC123AB.jpg which should be at the top. I did have some files in the database that had a space in the first position. I cleaned that up but still not working.

    vtxyzzy

    (@vtxyzzy)

    13 years, 8 months ago

    I don’t know of a sort that will do that. I suggest renaming the files so they will sort correctly.

    Thread Starter Bloke

    (@bloke)

    13 years, 8 months ago

    I have tried the following queries:

    $query = “SELECT * FROM files WHERE prod_id = ‘”. $this->curProduct .”‘ ORDER BY LENGTH”;
    $query = “SELECT * FROM files WHERE prod_id = ‘$this->curProduct’ ORDER BY CAST(file_name as unsigned), file_name asc”;
    $query = “SELECT * FROM files WHERE prod_id = ‘$this->curProduct’ ORDER BY LENGTH(file_name), file_name asc”;
    $query = “SELECT * FROM files WHERE prod_id = ‘$this->curProduct’ ORDER BY CAST(file_name as signed), file_name ASC”;
    $query = “SELECT * FROM files WHERE prod_id = ‘$this->curProduct’ ORDER BY CAST(file_name as alphanumeric) ASC”;
    $query = “SELECT * FROM files WHERE prod_id = ‘”. $this->curProduct .”‘ ORDER BY LENGTH(alphanumeric)”;

    vtxyzzy

    (@vtxyzzy)

    13 years, 8 months ago

    Actually I don’t know why the first code you showed does not work.

    Did you show the actual list of file names, or are some of the names lower case? If so, try the following:

    $query = "SELECT * FROM files WHERE prod_id = '". $this->curProduct ."' ORDER BY UPPER(file_name) ASC";
    Thread Starter Bloke

    (@bloke)

    13 years, 8 months ago

    No that didn’t work. The upper/lowercase isn’t an issue. Its the combination of letters, numbers, and spaces.

    vtxyzzy

    (@vtxyzzy)

    13 years, 8 months ago

    I don’t think that is the problem based on what you showed. Anything starting with ‘A’, no matter what follows, should always sort before anything starting with ‘C’.

    Can you post a link to your site where I can see the actual output?

    Thread Starter Bloke

    (@bloke)

    13 years, 8 months ago

    ABC123AB.gif
    ABC123AB.jpg

    should appear before

    CAD435FG.gif
    CAD435FG.jpg

    vtxyzzy

    (@vtxyzzy)

    13 years, 8 months ago

    I understand that. And that is exactly what I said earlier. The query you originally showed should work that way.

    To help find out why it is not happening, I need to see the site.

    Thread Starter Bloke

    (@bloke)

    13 years, 8 months ago

    My site is not live unfortunately. I have tried so many things so I will post the current list of actual list of files I am getting. See file AB51MN50.jpg is where is goes out of order.

    AB51HPS70.gif
    AB51MN100.jpg
    AB51MN100.gif
    AB51MN175 w.jpg
    AB51MN175 w.gif
    AB51MN175 w.jpg
    AB51MN175 w.gif
    AB51MN175 w.jpg
    AB51MN175 w.gif
    AB51MN175.jpg
    AB51MN175.gif
    AB51MN175eue.jpg
    AB51MN175eue.gif
    AB51MN50.jpg
    AB51MN50.gif
    AB51MN70.jpg
    AB51MN70.gif
    AB51PS150.jpg
    AB51PS150.gif

    s_ha_dum

    (@apljdi)

    13 years, 8 months ago

    Agreed with vtxyzzy. Your query looks right. Are you sure there are no leading spaces, for example, in some of your file names?

    Also, spaces in file names (while supported by most OSes now) are not reliable online unless the spaces are encoded. If you create a URL with an unencoded space, chances are that it won’t work. Just be aware of that.

    vtxyzzy

    (@vtxyzzy)

    13 years, 8 months ago

    In order to get this to work at all, the format of the file names must be consistent.

    For example, in what you just showed, all file names beginning with ‘A’ should have 2letters, 2 numgers, 2 letters, 3 numbers, 3 letters, and the file extension. So instead of ‘AB51MN50.jpg’, you would have ‘AB51MN050AAA.jpg’.

    Is that possible?

    Thread Starter Bloke

    (@bloke)

    13 years, 8 months ago

    No I was given the file names as they are and now I need to order them. At one time I did find leading spaces in the file names but they have been removed.

    s_ha_dum

    (@apljdi)

    13 years, 8 months ago

    See file AB51MN50.jpg is where is goes out of order.

    In what way is that out of order? It seems to be following the alphabetization rules I am familiar with, except that ‘jpg’ is coming before ‘gif’, which is odd.

    AB51MN175eue.gif
    AB51MN50.jpg

    Where do you think it should be?

    vtxyzzy

    (@vtxyzzy)

    13 years, 8 months ago

    There is no way to order the list based on what has been shown.

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

Viewing 15 replies - 1 through 15 (of 27 total)
1 2

The topic ‘Sorting alpha number query results’ is closed to new replies.