WordPress.org

Ready to get started?Download WordPress

Forums

Sorting alpha number query results (28 posts)

  1. Bloke
    Member
    Posted 1 year 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;
    }
  2. vtxyzzy
    Member
    Posted 1 year ago #

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

  3. Bloke
    Member
    Posted 1 year 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.

  4. vtxyzzy
    Member
    Posted 1 year ago #

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

  5. Bloke
    Member
    Posted 1 year 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)";

  6. vtxyzzy
    Member
    Posted 1 year 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";
  7. Bloke
    Member
    Posted 1 year ago #

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

  8. vtxyzzy
    Member
    Posted 1 year 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?

  9. Bloke
    Member
    Posted 1 year ago #

    ABC123AB.gif
    ABC123AB.jpg

    should appear before

    CAD435FG.gif
    CAD435FG.jpg

  10. vtxyzzy
    Member
    Posted 1 year 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.

  11. Bloke
    Member
    Posted 1 year 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

  12. s_ha_dum
    Member
    Posted 1 year 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.

  13. vtxyzzy
    Member
    Posted 1 year 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?

  14. Bloke
    Member
    Posted 1 year 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.

  15. s_ha_dum
    Member
    Posted 1 year 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?

  16. vtxyzzy
    Member
    Posted 1 year 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?

  17. s_ha_dum
    Member
    Posted 1 year 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.

  18. Bloke
    Member
    Posted 1 year ago #

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

  19. vtxyzzy
    Member
    Posted 1 year 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?

  20. Bloke
    Member
    Posted 1 year 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?

  21. s_ha_dum
    Member
    Posted 1 year 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.

  22. vtxyzzy
    Member
    Posted 1 year 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?

  23. s_ha_dum
    Member
    Posted 1 year 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.

  24. s_ha_dum
    Member
    Posted 1 year 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.

  25. Bloke
    Member
    Posted 1 year ago #

    Thanks I tried that above with all the other queries.

  26. s_ha_dum
    Member
    Posted 1 year ago #

    oops... so you did.

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

  27. s_ha_dum
    Member
    Posted 1 year 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.

  28. vtxyzzy
    Member
    Posted 1 year 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.

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.