SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2001
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Moving "The" from search results

    On my site I have a page where I list the nick names of the members to my site and what I would like to do is some of the names start with "The" and I want to move that to the end of the name.

    Example: When my visitors click on names starting with "T" it comes up with: The Man, The ****, The PHP Programmer etc.
    I would like the name "The Man" to come up when they click on names starting with "M" as "Man, The" and "The PHP Programmer" to come up on names starting with "P" as "PHP Programmer, The".

    I hope this makes sence, any help is apreaciated.
    Thanks, Chris
    Thanks, Chris
    -------------------------
    http://spotlyrics.com
    -------------------------

  2. #2
    Custom User Title v1.0 FireFly's Avatar
    Join Date
    Aug 2001
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT IF(SUBSTRING(Username,1,4)='The ', CONCAT(SUBSTRING(Username,5),', The'), Username) AS Username FROM tblName ORDER BY Username;
    This is much easier to do in PHP, but since you want the results ordered by the name, it's better to do it right in the query and not in PHP because then you'll need to sort the array again and you don't want that.

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2001
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply.. I tried incerting the code (below) and it moved the "The" to the end of the name but all the "The ..." names are still only on the page with the names srarting with "T" and not on the page that the 'word' after "The" starts with...
    Example: The username "The Man" now comes up as "Main ,The" but it is on the page with the names that start with the letter "T" insted of the page with names starting with "M"... Any suggestions?

    This is the code that I put on my page.

    PHP Code:
    SELECT IF(SUBSTRING(name,1,4)='The 'CONCAT(SUBSTRING(name,5),', The'), name) AS name FROM users WHERE name like '$letter%' ORDER BY name 
    Thanks, Chris
    Thanks, Chris
    -------------------------
    http://spotlyrics.com
    -------------------------

  4. #4
    Custom User Title v1.0 FireFly's Avatar
    Join Date
    Aug 2001
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use this query:
    Code:
    SELECT IF(SUBSTRING(name,1,4)='The ', CONCAT(SUBSTRING(name,5),', The'), name) AS fixedname FROM users WHERE fixedname LIKE '$letter%' ORDER BY fixedname
    Note that I've changed the alias to fixedname, so you need to use this in your scripts as well.

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2001
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Still Not Working..

    I copy and pasted the code above into my MYSQL statement and tryed to run the page but it gave me the error "Unknown column 'fixedartist' in 'where clause'" do I have to add a "fixedartist" colum to my table or something?

    Thank for the help.
    Chris
    Thanks, Chris
    -------------------------
    http://spotlyrics.com
    -------------------------

  6. #6
    Custom User Title v1.0 FireFly's Avatar
    Join Date
    Aug 2001
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT IF(SUBSTRING(name,1,4)='The ', CONCAT(SUBSTRING(name,5),', The'), name) AS fixedartist FROM users WHERE fixedartist LIKE '$letter%' ORDER BY fixedartist

  7. #7
    SitePoint Enthusiast
    Join Date
    Nov 2001
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Same Error

    sorry about that, I'm getting two sites mixed, it should be 'fixedname' but I copied the error from my other site where it's artist... Anyways.. I'm still gettting the same error "Unknown column 'fixedartist' in 'where clause'"?
    Thanks, Chris
    -------------------------
    http://spotlyrics.com
    -------------------------

  8. #8
    SitePoint Enthusiast
    Join Date
    Nov 2001
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Any Ideas

    Anyone have any ideas? Please help!
    Thanks, Chris
    -------------------------
    http://spotlyrics.com
    -------------------------


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
  •