Listing Names as SURNAME, FIRSTNAME

middleman666

Hi people,

I have a list of band names and solo artists names.
Currently the band names that start with 'The' are sorted so the 'The' is displayed after the other words in the band name and then listed alphabetically.
This is done using this query:

  $query = "SELECT *, 
  IF(Band LIKE 'The %', CONCAT(SUBSTRING(Band, 5), ', The'), Band) AS list_item 
  FROM BandNames 
  ORDER BY list_item";

Now I want to display solo acts for example: Elton John, as John, Elton. I have done this using like this:

        if ($Solo == 'yes') { 
          $SoloExploded = explode(' ', $Band);
          $NumWords = count($SoloExploded);
          $LastWord = $SoloExploded[$NumWords-1];
          echo $LastWord.', ';
          for($i=0;$i < $NumWords-1;$i++) {
            echo $SoloExploded[$i].' ';
          }
        }
        else {
          echo $Band;
        }

Trouble is, by doing it that way, I have forgot that the list will no longer be alphabetical, which is the reason I'm here.

I wondered, is there a way to do what I did with the Solo Acts, but within the MySQL Query?

Or is the only way of achieving this, by reading all the band names and solo names into an array and then arrange that alphabetically?

Thanks.

Note: My host only has MySQL 4.0.27 installed.

paulnaj

Hi there,

I've had this problem a few times but with artists rather than bands. All combinations were possible ... some had aliases, some just first names, some just last names, etc.

In the end, I just created another field in the db table called 'listname' and had some buttons on the entry form that did the usual formatting rules as you've described.

At the worst you can type whatever you want.

I know it sorta breaks a 'database' rule but it's a simple solution since all queries just get sorted by 'listname'.

Hope that helps!

Paul.

middleman666

paulnaj wrote:
Hi there,
In the end, I just created another field in the db table called 'listname' and had some buttons on the entry form that did the usual formatting rules as you've described.

That does seem like a simpler solution, but I think I prefer having the one field and then manipulating it on the read out.

I think I'm just going to have to try the array way, I'm sure I'll have some trouble so I'll be back with some more queries.

Thanks for your reply.