How to return in a INSERT-SELECT query which conditional in WHERE statement existed

Question

you see I have this query:

 $sql = "INSERT INTO visitor( visitor_username, email, PASSWORD ) 
       SELECT * FROM ( SELECT '$username', '$email','$password') 
       AS tmp WHERE NOT EXISTS 
      (SELECT admin.admin_username, admin.email FROM admin AS admin 
      WHERE admin.admin_username = '$username' OR admin.email = '$email' 
      UNION SELECT staff.staff_username, staff.email FROM staff 
      AS staff WHERE staff.staff_username = '$username' OR 
      staff.email = '$email' ) LIMIT 1";

basically if the values (visitor_username, email) does not exist on the admin / staff table, we insert the values, it works pretty good and fast, but a good solution doesnt always bring rainbows.

if we could not insert any values in the table I return this error message

echo "User already exists. Please choose a different email address or username.";

Since I cannot determine which one existed, I would like to have specific case return error for the username or the email address.

Should I split the query (one select, then insert) or is there any other way I'm not finding online to do this in the same query?

My goal is to return a specific error message with this query for example:

"Error: Username already exists" or "Error: Email already exists."

Thanks for checking and trying to help me in advance!.

I would create two queries or one MySQL function which will return some response. — Aleksandar Rakic, Nov 29 '18 at 12:24
Please show fuller PHP code block (not line snippets) as that appears to be a user-defined error message. We need to see conditional logic. — Parfait, Dec 02 '18 at 20:38

Aleksandar Rakic · Answer 1 · 2018-11-29T12:47:03.747

I would create two queries or one MySQL function which will return some response. In case you decide to use MySQL function this is the way how to do it (adjust following query for your purpose):

delimiter $$;
  create function `function_name`() returns integer deterministic
  begin
    select count(*) into a from `table` where 1 = 1;
    if a > 0 then
      insert into `table`(`col1`, `col2`) values('val1', 'val2');
      select if(last_insert_id() > 0, 'response_200', 'response_500') into b from dual; 
      return b;
    else
      return 'response_501';
    end if;
  end $$
delimiter ;

This function you will call like query below and you will get only one record with one of the above outputs:

select `function_name`() as `response`;

thats similar of what I'm doing in my query, it returns 1 record, but I cannot define which was existed if the "user" or "email" existed — Hex, Dec 02 '18 at 18:24

How to return in a INSERT-SELECT query which conditional in WHERE statement existed

1 Answers1