Tim Tim - 3 months ago 11
MySQL Question

MySQL efficient querying

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?

Answer

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
Comments