MySQL return different string based on values in table columns

Question

I have the following function that I created for pulling a user's preferred name:

public function getPreferredName($unique_id) {

    $sql = "SELECT CONCAT(first_name, ' ', last_name) as name FROM `{$this->table}` WHERE `unique_id` = ? LIMIT 1";
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute([$unique_id]);

    if ($stmt->rowCount() == 0) return null;

    $preferredName = $stmt->fetchColumn();

    return $preferredName;

}

This table is a "users" table made up of individuals and companies.

There are some additional fields that I would like to consider in this query:

"display_as" (individual or company)
"company_name" (used when display_as = company)

Now i'd like to modify the method above to return either the first and last name OR the company name based on whether the value of the "display_as" column equals "individual" or "company".

If "display_as" equals "individual" return the first_name and last_name fields.

If "display_as" equals "company" return the company_name field.

How can I modify the method above to return either the first_name and last_name together or the company_name value based on the "display_as" column?

Thanks for the help. Cheers

you need to do that logic outside the query, so add display_as and the other fields and check the data returned — user10051234
– user10051234, Commented Aug 7, 2019 at 23:29
Couple of notes - since you're using fetchColumn now, you don't need the as name alias. fetchColumn will just grab the first column regardless of name. And if unique_id is really unique, LIMIT 1 is redundant. (Neither of those are really problems, just extra code you don't need.) — Don't Panic
– Don't Panic, Commented Aug 7, 2019 at 23:56
@Don'tPanic good points, thank you for the input! I'm having an issue though where the preferred name is not returned for individuals if they do not have a last_name value. Do you know how to fix this by chance? — Joe Shmoe
– Joe Shmoe, Commented Aug 8, 2019 at 0:33

nbk · Accepted Answer · 2019-08-08 07:53:32Z

3

You can use the MySQL IF function for this:

$sql = "Select If(`display_as` = 'individual',
        CONCAT(IFNULL(first_name,''), ' ', IFNULL(last_name,''))
       ,`company_name`) as name FROM `{$this->table}` WHERE `unique_id` = ? LIMIT 1";

This works because you have only two options, individual or company.
If you have more options you will have to use case when construct.

edited Aug 8, 2019 at 7:53

answered Aug 7, 2019 at 23:45

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joe Shmoe Over a year ago

actually @nbk this is not returning anything for display_as = individual when the user does not have a last_name? it seems

nbk Over a year ago

Yes. I added IFNULL, if it encounters a NULL Value the statement uses an empty string instead.

nbk Over a year ago

you can of course use instead of IFNULL CONCAT_WS see her dev.mysql.com/doc/refman/8.0/en/…

Collectives™ on Stack Overflow

MySQL return different string based on values in table columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related