PHP Inner join to validate multiple users if already exist using PDO

Question

I am trying to validate a username from 3 different tables because I have 3 types of user that has only one login form. No problem with that it works fine. But the problem is what if the username is existing from the other tables. So I have to use INNER JOIN to validate 3 tables if username already exist.

Here is the code below to validate the username of the user INNER JOIN to admin and staff tables.

    <?php

if(isset($_POST["username"]))
{
    if(!isset($_SERVER['HTTP_X_REQUESTED_WITH']) AND strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) != 'xmlhttprequest') {
        die();
    }
    $mysqli = new mysqli('localhost' , 'root', '', 'db');
    if ($mysqli->connect_error){
        die('Could not connect to database!');
    }

    $username = filter_var($_POST["username"], FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_LOW|FILTER_FLAG_STRIP_HIGH);

    $statement = $mysqli->prepare("SELECT users.username FROM users
     INNER JOIN admin ON users.username = admin.username
     INNER JOIN staff ON users.username = staff.username
     WHERE username=?");
    $statement->bind_param('s', $username);
    $statement->execute();
    $statement->bind_result($username);
    if($statement->fetch()){
        die('<p style="color: red; font-weight:bold">Username is already taken! Please write another username.</p>');
    }else{
        die('<p style="color: green; font-weight:bold">Username is available!</p>');
    }
}

?>

here is the script in JS

<script type="text/javascript">
        $(document).ready(function() {
            var x_timer;    
            $("#username").keyup(function (e){
                clearTimeout(x_timer);
                var user_name = $(this).val();
                x_timer = setTimeout(function(){
                    check_username_ajax(user_name);
                }, 500);
            });

            function check_username_ajax(username){
                $("#user-result").html('<img src="ajax-loader.gif" />');
                $.post('validate_username.php', {'username':username}, function(data) {
                    $("#user-result").html(data);
                });
            }


        });
</script>

But it works when I'm not using INNER JOIN for the user table only.

So is there something wrong in my INNER JOIN clause? Appreciate if someone can help. Thanks in advance.

Tomaso Albinoni · Accepted Answer · 2017-09-10T17:03:16.533

2

Because you are using inner joins, the SQL query will only return something if username is present in all three tables. If username is always present in users and may or may not be in the other two tables, you would LEFT JOIN admin and staff. If there is no overlap, you would need a full outer join, which does not exist in MySQL but can be emulated (see Efficient way to simulate full outer join in MySQL?).

But, since you are only checking for availability, you can do simply:

SELECT 1 FROM users WHERE username = ?
UNION
SELECT 1 FROM admin WHERE username = ?
UNION
SELECT 1 FROM staff WHERE username = ?

Then, adjust your PHP slightly:

$statement->bind_param('sss', $username, $username, $username);
$statement->execute();
$result = $statement->get_result();
if ($result->num_rows) {
    // Username is taken
} else {
    // Username is available
}

edited Sep 10 '17 at 17:03

answered Sep 09 '17 at 03:22

Tomaso Albinoni

1,003
1
8
19

Could you elaborate? – Tomaso Albinoni Sep 09 '17 at 22:57
I replaced the sql with the code you give above in the answer. It always tell me that the user is available even if it exist in the table for admin users and staff. – Jaaayz Sep 10 '17 at 05:42
You Need to adjust your PHP to the new SQL, see edit. – Tomaso Albinoni Sep 10 '17 at 17:03
Thankyou so much! It works! I learned a lot from you. Appreciated it! – Jaaayz Sep 11 '17 at 00:42

PHP Inner join to validate multiple users if already exist using PDO

1 Answers1