SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Guru
    Join Date
    Dec 2001
    Location
    San Diego, CA
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    "The Band" -> "Band, The" when ordering

    OK, so I have a concert venue site. Local bands can submit their name and website, yadda yadda. When names are stored in the database, I want them stored as "The Band" (because we use that same name when display concert events). However, when I list all the bands alphabetically, there are quite a few "The Whatevers". Instead, I want bands like "The Apples" to be listed under A and not T. Is this even possible? Maybe instead of sorting it out with a query, it could be done with PHP instead? It would probably require some kind of regexp (which I don't understand AT ALL).

  2. #2
    Now with customized title Jump's Avatar
    Join Date
    Sep 2002
    Location
    The Restaurant at The End of The Universe
    Posts
    1,423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm just a newb but it seems to me that it would be easier to just store "Band" and then put a "The" before you display it.

  3. #3
    Fully Qualified Fool :) luke-innovative's Avatar
    Join Date
    Jun 2002
    Location
    Kent, UK
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Jump
    I'm just a newb but it seems to me that it would be easier to just store "Band" and then put a "The" before you display it.
    Thats if all the bands have "the" as a prefix. What if there are bands like "Metallica" or "Nirvana", he wouldnt want them as The Metallica or The Nirvana
    Luke
    Luke-Martin.com (work in progress) :: Independent Web Designer's Portal
    Freelance Forums :: Sign Up Here
    "What happens if you get scared half to death twice?"

  4. #4
    SitePoint Guru
    Join Date
    Dec 2001
    Location
    San Diego, CA
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When we show events, we don't want to display concert listings like:

    Apples, The
    Whatevers, The
    Someones, The

    We have a relational database (that even the proper term? it sounds cool) where we have events (event info), bands (band info), event_bands (which bands to which events). So, since we use the same information for both the events page and the full listing of all the bands, we want it stored as "The Band". Either way we store it, it needs to get converted on one of the pages.

  5. #5
    Quake 1 Addict CreedFeed's Avatar
    Join Date
    Feb 2002
    Location
    Milwaukee, WI
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Store the band name like "Apples, The" and then on displaying, check to see if ", The" is in the name and if it is, strip it out and then place in front of "Apples".
    -- Steve Caponetto
    Quake 1 Resurrection :: CreedFeed

  6. #6
    SitePoint Evangelist
    Join Date
    Oct 2001
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use two fields in the database: a field for showing and a field for ordering. This allows you to do other cool stuff in the order-field; you can use it for example to neglect accented characters (they order differently than other characters).

    In your case 'The Band' could be automatically transformed to 'Band, The', but how about when a band is just a single person? Then you probably want 'Lastname, Firstname'. There's no way you can detect this, and by using two fields this is not a problem at all.

    Vincent

  7. #7
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why not use arrays and explode to loop through them?

    PHP Code:
    <?php
    $title 
    "My Test";

    $array explode(" "$title);

    foreach(
    $array AS $key=>$value)
    {
        
    $numarrays++;
        
    // Define how many words...
    }

    for(
    $i $numarrays$i 0$i--)
    {
        
    // $i is now equal to the number of words, and is going to go down the list to 0...
        
    $j $numarrays $i 1;
        
    // $j is indexed to the opposite list of i. (Flipped list)
        
    $final_array[$j] = $array[$i];
    }
    $txt "";
    foreach(
    $final_array AS $key=>$value)
    {
        
    $txt .= $value;
        if (
    $numarrays != $key$txt .= ", ";
        
    // Define $txt to have the value of the previous one, and then a comma and space.
    }
    That'll give you "Test, My"

    Regards,
    Someonewhois

  8. #8
    SitePoint Evangelist
    Join Date
    Oct 2001
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, but the problem is that you don't know in advance at which point the name should be broken off...

    Vincent

  9. #9
    SitePoint Guru
    Join Date
    Dec 2001
    Location
    San Diego, CA
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm thinking that the best idea is to use the first idea suggested: list everything as "Band, The", then on the events page check every name for the occurance of ", The" and move it to the front. This way, they are already in alpha order for when I do the full listing.

  10. #10
    SitePoint Guru
    Join Date
    Dec 2001
    Location
    San Diego, CA
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't get preg_replace stuff and I know that is what I need in order to convert text from "Band, The" to "The Band"

  11. #11
    SitePoint Evangelist
    Join Date
    Oct 2001
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ...but this is not sufficient! There could be bands with no ',' at all in them, so then you shouldn't replace anything. Also, there could be bands with one ore more ',' (e.g. Blood, Sweat and Tears).

    It's is simply impossible to correctly modify ALL possibilities automatically. The ONLY thing that always works is using two lists.

    Vincent

  12. #12
    SitePoint Zealot
    Join Date
    Jan 2002
    Posts
    167
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if you store as The Band, you could use

    $text = eregi_replace("The (.+)","\\1\\, The",$text);



    but im not 2 gud at RegExps, that should be right
    Wilco

  13. #13
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by voostind
    Use two fields in the database: a field for showing and a field for ordering. This allows you to do other cool stuff in the order-field; you can use it for example to neglect accented characters (they order differently than other characters).
    In your case 'The Band' could be automatically transformed to 'Band, The', but how about when a band is just a single person? Then you probably want 'Lastname, Firstname'. There's no way you can detect this, and by using two fields this is not a problem at all.
    Vincent
    Yes, that's the way to do it
    You could have a list with words to ignore when sorting (das, den, der, die, el, het, la, le, the... I've tried it ), but then there is the lastname, firstname problem anyway

  14. #14
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If saving space is important; maybe you could try some system where the order for the words in the name is stored in a separate table

    Like this:
    *** Band
    id, name
    1, "The Band"
    2, "Bob Dylan"
    3, "Blood, Sweat and Tears"

    *** BandNamesSorting
    band_id, word, order
    1, 2, 1
    1, 1, 2
    2, 2, 1
    2, 1, 2
    3, 1, 1
    3, 2, 2 <== treat comma as a word
    3, 3, 3
    3, 4, 4
    3, 5, 5

    The sql statement to get the names sorted is a little bit more complex with this method

  15. #15
    SitePoint Evangelist
    Join Date
    Oct 2001
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When bands can 'add themselves' to your database, you can have them fill in one name, and then simply fill that in on both fields. You *could* use some filters to place 'The' at the end of the name for the ordering field, but you don't have to. Every once in a while the database administrator (or someone else) browses through the list of newly added bands, and sets their ordering-fields correctly. As each name is only stored once in the database, the whole database will then behave correctly.

    Anyway, this is how I do it myself

    Vincent

  16. #16
    SitePoint Evangelist
    Join Date
    Oct 2001
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The sql statement to get the names sorted is a little bit more complex with this method
    ...which is exactly why it doesn't work...

    When you select multiple record from a table, all you really want to do is use an ORDER BY clause to make sure they appear in the right order. That's what the clause is for, and you really don't want to resort to weird 'hacks' to achieve the same.

    Vincent

  17. #17
    SitePoint Guru
    Join Date
    Dec 2001
    Location
    San Diego, CA
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I already have 3 tables for events (events, bands, event_bands). If I did two tables for bands, that would mean another table to query from. A 4 table multiple select query wouldn't be confusing AT ALL [/sarcasm]

  18. #18
    SitePoint Evangelist
    Join Date
    Oct 2001
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Who said anything about using two tables for the bands?

    Vincent

  19. #19
    SitePoint Guru
    Join Date
    Dec 2001
    Location
    San Diego, CA
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Someone suggested having a bands table and a bands sorting table...that was who I was referring to.

    When bands submit their info, it doesn't display until someone approves it. In this case, they can change any names that have "The Band" to "Band, The" and make sure that the formatting is always correct. Then, use a preg_replace and make all "Whatever, The" become "The Whatever". With bands listed as "Whatever, The", they are already in alpha order which makes sorting easier on the full list.

  20. #20
    SitePoint Evangelist
    Join Date
    Oct 2001
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Then, use a preg_replace and make all "Whatever, The" become "The Whatever".
    Okay, whatever floats your boat, I guess. But you do know it's impossible to handle all possible cases with a preg_replace, right?

    Vincent

  21. #21
    SitePoint Guru
    Join Date
    Dec 2001
    Location
    San Diego, CA
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There is only one possible case though ", The". If that is in there at the very end, then replace it. I could do a string search and only search the last 5 characters and see if it matches ", The". If so, then remove it and add "The " to the beginning.

  22. #22
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,608
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Just to clarify a misunderstanding, voostind said ...
    Use two FIELDS in the database: a field for showing and a field for ordering.
    ... and NOT ...
    Use two TABLES in the database: a field for showing and a field for ordering.
    ... (my emphasis). This is where your misconception lies. You don't need another table, just another column in your bands table.
    Ian Anderson
    www.siteguru.co.uk


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •