chose another column if the first column is null

Question

how to chose another column if the first one is null i have users table who have

email phone and other data i have registration form built with php what i want is check if user insert an email or phone if he insert an email and phone check if the email is in MySQL DB if he doesn't insert email in the form and just insert phone number i want to check if the phone number is in the MySQL DB

what i'm trying to say is like this flowchart :

select email, phone where email =$email 
if email is null 
    select phone where phone =$phone 
else 
   select email where email = $email

NOTE the phone number is required in the form so it cant be the both email and phone null

i tried if statements COALESCE() but i always not getting what i expected which is the num_rows

$chk_query = " SELECT email,phone FROM users WHERE email = '$email' IF (email IS NULL)
                SELECT phone WHERE phone = '$phone' ";
                $result = $mysqli->query($chk_query);
                if ($result->num_rows > 0 ){
                    $_SESSION['message'] = 'The Email or Phone number you enter it is exist ! ';
                }
                else{
                    // insert data to the database after checking

Please check you can achieve using `case` https://www.w3schools.com/sql/func_mysql_case.asp — jishan siddique, Nov 11 '19 at 10:02
@Strawberry hi, i take a look at it , it seems the function that i want but didn't get the expected result . i'm sure its somthing in the syntax — Mohammed Breky, Nov 11 '19 at 11:38
@jishansiddique i tied but nothing actually give me what i want — Mohammed Breky, Nov 11 '19 at 11:39
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Nov 11 '19 at 17:40

score 0 · Answer 1 · answered Nov 11 '19 at 09:41

In variable take value of email or phone which is set

$check_with  ='';
if(isset($email) && $email!=null){
  $check_with  =$email;
}

if(isset($phone) && $phone!=null){
  $check_with  =$phone;
}

In query check that email or phone is exist or not.

$chk_query = 'SELECT email,phone FROM users WHERE phone = "'.$check_with.'" OR email = "'.$check_with.'"';

2nd way :

Change your query according to value.

if(isset($email) && $email!=null){
  $chk_query = 'SELECT email,phone FROM users WHERE email = "'.$email.'"';
}

if(isset($phone) && $phone!=null){
  $chk_query = 'SELECT email,phone FROM users WHERE phone = "'.$phone.'"';
}

yes, actually i think about making it in PHP but i sure there a simple way to make like a function COALESCE() in SQL query but Thanks anyway :) — Mohammed Breky, Nov 11 '19 at 11:36

alwalms · Accepted Answer · 2019-11-11T12:27:27.840

I have two options depending on one question - if email and phone are not null and phone exists in the database, do you want to raise the error or enter the new data?

If you always want to check if the email or phone exists then could you just use OR?

$chk_query = " SELECT coalesce(email,phone) FROM users WHERE email = '$email' OR phone = '$phone' ";
            $result = $mysqli->query($chk_query);
            if ($result->num_rows > 0 ){
                $_SESSION['message'] = 'The Email or Phone number already exists';
            }
            else{
                // insert data to the database after checking

If you want to ignore the phone when email has been entered then add an extra condition (whilst only returning the first non-null column

$chk_query = " SELECT coalesce(email,phone) FROM users WHERE email = '$email' OR (email IS NULL AND phone = '$phone') ";
            $result = $mysqli->query($chk_query);
            if ($result->num_rows > 0 ){
                $_SESSION['message'] = 'The Email or Phone number already exists';
            }
            else{
                // insert data to the database after checking

The coalesce will choose the first non-null value in the list of columns. Note this will only work if values are truly null, not if they are empty strings.

hi, in fact its similar to the next code i tried it in the phpmyadmin consol ``` SELECT email,phone FROM users WHERE email = ' ' OR (email IS NULL AND phone = '95') ``` in this case i get all the email that are null and their phone numbers what i want is if the email is null just select the phone number from the form which is the $phone variable and if email is not null then we check if its in the db check again if the phone is exist — Mohammed Breky, Nov 11 '19 at 11:47
Answer update to include the coalesce function in the select statement. — alwalms, Nov 11 '19 at 12:28

chose another column if the first column is null

2 Answers2