MySQL efficient querying

Question

I am fairly new to MySQL. Considering this table:

 ID INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  User_name VARCHAR(35) NOT NULL,
  Full_name VARCHAR(55) NOT NULL,
  User_vars VARCHAR(255) NOT NULL,
  BirthDay DATE NOT NULL,
  password CHAR(70) NOT NULL,  
  Security_hint VARCHAR(27) NOT NULL,
  Email VARCHAR(225) NOT NULL,
  userType ENUM ('a','b','c','d') NOT NULL DEFAULT 'a',
  Signup_Date TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
  activation   TINYINT(1) UNSIGNED NOT NULL,
  Ip BINARY(16) NOT NULL,
  PRIMARY KEY (ID),
  UNIQUE KEY User_name (User_name,Email)

I am using the following queries to check whether an email or user name exists in this table:

    $query1 ="SELECT id FROM signup WHERE Email='$Email_Input' LIMIT 1";            
$result1 = mysql_query($query1) 

$query2 = "SELECT id FROM signup WHERE User_name='$User_name' LIMIT 1";         
$result2 = mysql_query($query2);

if (mysql_num_rows($result2)>0){    
echo '1';
}
else if (mysql_num_rows($result1)>0){   

echo '1';
}

My question: Is there an efficient way to issue just one query to check the existence of both?

WHERE Email= AND User_name= would be best since you also have a composed index on those columns. — Mihai
– Mihai, Commented Aug 28, 2016 at 9:46
I believe that your code is vulnerable to SQL injection. Use prepared statements instead of putting input value right into the query string. — Kamil Gosciminski
– Kamil Gosciminski, Commented Aug 28, 2016 at 9:56
All my variables hold filtered and sanitized data against XSS and SQL injections — Tim
– Tim, Commented Aug 28, 2016 at 15:16

sagi · Accepted Answer · 2016-08-28 09:45:32Z

2

Yes, you can do this:

SELECT ID FROM signup WHERE Email ='$Email_Input' LIMIT 1
UNION ALL 
SELECT id FROM signup WHERE User_name='$User_name' LIMIT 1

That is, if this is two different records. If its the same record, that should answer both condtions, simply :

 SELECT ID FROM signup
 WHERE Email ='$Email_Input'
   AND User_name='$User_name' 
 LIMIT 1

answered Aug 28, 2016 at 9:45

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL efficient querying

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related