#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2004
    Location
    Boston, MA USA
    Posts
    579
    Rep Power
    51

    Sorting on two columns


    I have a CRM written in PHP/MySQL

    I would like to export an alphabetized company list, sorted on one of two fields.

    Table 1 contains basic company information which includes a field called "company_name"
    Table 2 includes alternate information about some of the companies in table 1 and includes a field called "alternate_name"

    A simple SELECT query retrieves data from both tables using a JOIN.

    All companies have a "company_name"
    Not all companies have an "alternate_name"
    If a company has an "alternate name" that should be printed in lieu of "company_name"

    Whats an efficient way to generate a list in alphabetical order based on either "company_name" OR "alternate name"

    I.e. If a company has a "company_name" called "Alpha" and the "alternate_name" is "Zulu", I want the company listed with the "Z"s not the "A"s

    Should I write to an array and sort it?

    Or is there a way to do that directly in the query?

    Thanks
  2. #2
  3. Backwards Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,904
    Rep Power
    9646
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2004
    Location
    Boston, MA USA
    Posts
    579
    Rep Power
    51
    Originally Posted by requinix
    Since you can sort it in the query, you might as well sort it in the query.

    I hope the alternate_name is NULL when not set?
    Thanks for the reply.

    I'm not sure how to used two fields in a single "ORDER" statement.

    If I use
    "ORDER BY company_name ASC, alternate_name ASC"
    that won't accomplish the objective.

    How would I sort on one field in some records but a different field in others?

    Thanks again!
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2004
    Location
    Boston, MA USA
    Posts
    579
    Rep Power
    51
    oh, wait... an IF statement in the SELECT list?
  8. #5
  9. Backwards Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,904
    Rep Power
    9646
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2004
    Location
    Boston, MA USA
    Posts
    579
    Rep Power
    51
    Originally Posted by requinix
    I gave you a link.
    Ah, right... didn't notice that was link.

    That seems to work perfectly, happy to know about COALESCE... ya don't know what ya don't know...

    I do have one issue:

    "alternate_name" is set to NULL if not set. However, if user accidentally sets that to blank spaces, that value shows up as the name. Is there a way to filter out such values?

    And... there's a much more basic question:

    If the input text box for "alternate_name" on the entry form is left blank, the database UPDATE statement overwrites the NULL field with an empty string, ''. I can obviously check for that with a "IF ($_POST..." statement but is there a more efficient way to prevent empty strings from overwriting NULL values, generally?

    Thanks tons.
    Last edited by SeanF; November 5th, 2018 at 07:09 AM.
  12. #7
  13. Backwards Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,904
    Rep Power
    9646
    Originally Posted by SeanF
    "alternate_name" is set to NULL if not set. However, if user accidentally sets that to blank spaces, that value shows up as the name. Is there a way to filter out such values?
    Don't let users give an empty value. Validate your inputs. Though you could recognize an empty value and set NULL in the backend.

    Originally Posted by SeanF
    If the input text box for "alternate_name" on the entry form is left blank, the database UPDATE statement overwrites the NULL field with an empty string, ''. I can obviously check for that with a "IF ($_POST..." statement but is there a more efficient way to prevent empty strings from overwriting NULL values, generally?
    Same answer.
  14. #8
  15. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,274
    Rep Power
    4193
    Having your code translate an empty input to a NULL is the best solution.

    For completeness sake, the way to handle that in your query would be to use a CASE statement.
    Code:
    SELECT 
        CASE
            WHEN COALESCE(alternate_name, '') = '' THEN company_name
            ELSE alternate_name
        END as displayName,
        ...
    FROM companies
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  16. #9
  17. Backwards Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,904
    Rep Power
    9646
    I was thinking more like
    Code:
    COALESCE(alternate_name, displayName)

IMN logo majestic logo threadwatch logo seochat tools logo