what is the wrong with this query?

Question

I have a problem with this query and hope someone will help me to fix this. I am trying to check username and email address are available to register when registering a new user to my site. username is coming from login table and email address is coming from contact table. Now I need to make a query to check given username and email by new users are available to register. If those are not available I want to print error messages. I am trying to make this query something like this but its not working as I expect.

$q = "SELECT username, email FROM login 
      INNER JOIN contact 
      WHERE login.username = '$username' OR contact.email = '$email'";

Then I am checking this query in PHP like this

$r = mysqli_query ($dbc, $q);

// Get the number of rows returned:
$rows = mysqli_num_rows($r);

if ($rows == 0) { // No problems!

   // register new user 

} else { // The email address or username is not available.

    if ($rows == 2) { // Both are taken.

    $reg_errors['email'] = 'This email address has already been registered.1';          
    $reg_errors['username'] = 'This username has already been registered.2';            

    } else { // One or both may be taken.

                // Get row:
                $row = mysqli_fetch_array($r, MYSQLI_NUM);

                if( ($row[0] == $_POST['email']) && ($row[1] == $_POST['username'])) { // Both match.
                    $reg_errors['email'] = 'This email address has already been registered.3';  
                    $reg_errors['username'] = 'This username has already been registered with this email address.4';
                } elseif ($row[0] == $_POST['email']) { // Email match.
                    $reg_errors['email'] = 'This email address has already been registered.5';                      
                } elseif ($row[1] == $_POST['username']) { // Username match.
                    $reg_errors['username'] = 'This username has already been registered.6';            
                }

} // End of $rows == 2 ELSE.

my problem is PHP script always going to this code. query not checking individually username and email. I trying something like this.. username not available and email available, email not available and username available. But always going to this

if ($rows == 2) { // Both are taken.

$reg_errors['email'] = 'This email address has already been registered.1';          
$reg_errors['username'] = 'This username has already been registered.2';            

}

EDIT: Table structure..

# --------------
# Login Table 
# --------------

CREATE TABLE login (
login_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(80) NOT NULL, 
password VARBINARY(32) NOT NULL,
PRIMARY KEY (login_id),
UNIQUE(username) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# --------------
# Contact Table
# --------------

CREATE TABLE contact (
contact_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
telephone VARCHAR(60) DEFAULT NULL, 
mobile CHAR(10) NOT NULL, 
email VARCHAR(80) DEFAULT NULL, 
PRIMARY KEY (contact_id),
UNIQUE (email)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Answer 1

Currently, your query selects every row from both tables as long as there is a single match for one or the other. You can get matching rows from both tables simultaneously:

SELECT username FROM login WHERE username = '$username'
UNION ALL SELECT email FROM contact WHERE email = '$email'

...and also with separate queries.

Your queries are vulnerable to SQL injection.

Answer 2

You must provide ON clause which define the relationship on how the two tables are related with each other.

SELECT  username, email 
FROM    login 
        INNER JOIN contact 
            ON login.colname = b.colName  // change to your orignal colName
WHERE   login.username = '$username' OR 
        contact.email = '$email'

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

• How to prevent SQL injection in PHP?

An alternative way to do this without checking on the value on the tables is by enforcing UNIQUE constraints on column of the table, ex

ALTER TABLE login ADD CONSTRAINT tb_uq UNIQUE (username);
ALTER TABLE contact ADD CONSTRAINT tb_uq1 UNIQUE (email);

when the two alter statements has been successfully executed,you cannot insert value if it already exists on that column.

UPDATE 1

SELECT COUNT(*)
FROM
(
   SELECT userName as Value FROM Login
   UNION
   SELECT email as Value FROM contact
) s
WHERE VALUE IN ('$username','$email')

if the query above will return greater than 0, it means that value(s) already exists.

UPDATE 2

SELECT *
FROM
(
   SELECT userName, NULL AS email FROM Login
   UNION
   SELECT NULL AS username, email FROM contact
) s
WHERE username = '$username' OR email = '$email'

Answer 3

You are really checking 2 different things. A single query doesn't make sense, at least not a join. I suggest union instead:

select 'username' as exists from login 
    where username = '$username'
union all
select 'email' as exists from contact
    where email = '$email'

This will return a table with a column called exists and a row for each element that exists. Here is what you would get back if both username and email exist:

EXISTS
username
email

Where you run this query, you already know what the username and email they entered are, so there is no point in returning those values from the table.

As others have pointed out, you have a big security hole if $username and $email are being passed in directly from the user. You definitely have to handle that somehow.

Answer 4

Every Inner join clause needs to have a predicate or "ON" condition to specify the rule or rules to be enforced when Joining the two tables...

the query needs an "ON" clause after the Inner Join. I'm not sure what that condition should be, but, as an example....

    $q = "SELECT username, email FROM login 
           INNER JOIN contact 
               On contact.username = login.userName 
           WHERE login.username = '$username' OR contact.email = '$email'";

Answer 5

your join have a problem , because you should determine column wich you want join on it!

for example NNER JOIN contact On contact.id= login.contactId