if no results returned from first query then use second query

Question

I am making a system in which i need to check if a email exist in first table or second table.

    $results = mysqli_query($db,"SELECT * FROM table_1 WHERE email='$email'");
    if(count($results) == 0)
    {
        $results = mysqli_query($db,"SELECT * FROM table_2 WHERE email='$email'");
    }

I wanted to make one mysql so that there is no need for two. Since both table structures are different UNION is not not giving proper results. Is there a way without UNION or JOINS

I have been trying it with UNION

SELECT * FROM ( SELECT *, 1 as preference FROM table_1 WHERE email = 'doc@demo.com' UNION SELECT *, 2 as preference FROM table_2 WHERE email = 'doc@demo.com' ) T ORDER BY preference LIMIT 1

But the problem is if table 2 case satisfy then table 1 fields are considered and all values gets mismatched

Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's — RiggsFolly, Jul 10 '18 at 21:43
@RiggsFolly Thank you for pointing that out. Its a important part of code. To simplify i have just written in the simplest form here. I am using a mysql prepared statements framework otherwise for these. — user1001176, Jul 10 '18 at 21:46
[Someone asked the same question](https://stackoverflow.com/questions/8008765/mysql-merge-two-different-tables-without-join-or-union) check it out! — ClaudiusDan, Jul 10 '18 at 21:48
@ClaudiusDan thank you for your reply. I have checked the answer. It is more of a merging of two tables but here i want to compare if first query returns empty results then run the second one as both have different fields. apart from one which is email and that is what i am checking in where — user1001176, Jul 10 '18 at 21:58
You can do joins of tables having different fields...so yes you can do a single request...can you give us the full sql 2 requests for which you didn't succeed to make JOINS ? And why are you talking about the table structure difference, as you search on the the email field, so we don't have to bother to the other fields ? — PierreN, Jul 10 '18 at 21:58
I have been trying it with union `SELECT * FROM ( SELECT *, 1 as preference FROM table_1 WHERE email = 'doc@demo.com' UNION SELECT *, 2 as preference FROM table_2 WHERE email = 'doc@demo.com' ) T ORDER BY preference LIMIT 1` — user1001176, Jul 10 '18 at 22:26
Doesn't answer your question, but, sometimes less code is not as good. You might get tightly written SQL all-in-one, but does it fowl MySQL's great use of indexes? Does it make it less readable? If at some point you needed to add a 3rd potential, or more, it can become tricky, even an entire refactor needed. KISS is often the way. IF/ELSE :) Just food for thought — James, Jul 10 '18 at 22:27
Does this help? https://stackoverflow.com/questions/27366107/second-select-query-if-first-select-returns-0-rows — James, Jul 10 '18 at 22:37

score 0 · Answer 1 · answered Jul 11 '18 at 22:24

I found a solution to my own question so just wanted to share it

SELECT * FROM (
  SELECT email,password,user_role, id as customer_id,id as user_id, 1 as preference 
    FROM table1 WHERE email = 'raj@demo.com'
   UNION 
  SELECT email,password,user_role, customer_id,id, 2 as preference 
    FROM table2 WHERE email = 'doc@demo.com' 
) T ORDER BY preference
LIMIT 1

if no results returned from first query then use second query

1 Answers1