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
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'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

nbk nbk · Accepted Answer · 2019-08-07T23:45:56

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.

MySQL return different string based on values in table columns

1 Answers