如何缩短功能查询？

Question

hello i have this function:

function mail_exists($email){
    global $db;
    $email = sanitize($email);
    $query = $db->query("SELECT Email FROM table1 WHERE Email= '$email' ");
    $check = $query->num_rows;
    $query2 = $db->query("SELECT Email FROM table2 WHERE Email= '$email' ");
    $check2 = $query->num_rows;
    return ($check > 0 || $check2 > 0) ? true : false;
}

first of all i would like to know how i can shorten it by using only one query and second thing is, why this does not work when using two queries? both tables have a different structure. in table1 the field email is no. 16 and on table2 it is field no.6.

thanks alot.

Answer 1

First of all you made a logic error *here in the last lines: $query->num_rows; should be: $query2->num_rows; and then resulting into:

function mail_exists($email){
    global $db;
    $email = sanitize($email);
    $query = $db->query("SELECT Email FROM table1 WHERE Email= '$email' ");
    $check = $query->num_rows;
    $query2 = $db->query("SELECT Email FROM table2 WHERE Email= '$email' ");
    $check2 = $query2->num_rows; // *here
    return ($check > 0 || $check2 > 0) ? true : false;
}

Second, you should be using two different queries if you are dealing with two completely different contexts. Don't join queries when you don't need to. If you are just counting rows you can easily do:

function mail_exists($email){
    global $db;
    $email = sanitize($email);
    $query = $db->query("SELECT COUNT(*) FROM table1 WHERE Email= '$email' ");
    $query2 = $db->query("SELECT COUNT(*) FROM table2 WHERE Email= '$email' ");
    $count1 = $query->fetch_row();
    $count2 = $query2->fetch_row();
    return ($count1[0] || $count2[0]);
}

The SQL COUNT() function is there to give you the most performant way to count rows.