Select from 2 tables? Help.

[tebrown]

Hey Guys,

 

After searching on google and other forums for solutions for this warning im still having trouble as to why it wont identify if an email already exsists in either of the 2 tables (managers, players).

 

Warning: mysql_result() expects parameter 1 to be resource, boolean given in /Users/Tim/Sites/DMS/RoutoSMS/functions.php on line 71

 

function emailExists($email) {
$query = mysql_query("SELECT `managers`.`email`, `players`.`email` FROM `managers`, `players` WHERE `email` = '$email'");
return (mysql_result($query, 0) > 0) ? true : false ;
}

 

This is the code where it identifies the function:

 

if(emailExists($email)) {
$errors[] = "Email already registered.";
}

[requinix]

99% of the time that error message means your query failed. There's a problem with it and MySQL couldn't execute it.

 

The actual problem here is moot because your query needs to change. It won't do what you're trying to do. So give it another shot. Try the most obvious solution first, and once that's working you can think about whether there's a better way of doing it.

[tebrown]

Ok i did give it another shot. Although this time, in my database i have an email in the 'managers' table, and when i try to use this function it still inserts a new email into the players table. Where it should say something like 'Email already registered' because of it already in the members table.

 

function emailExistsPlayers($email) {
$query = mysql_query("SELECT COUNT(managers.id) AS mCount, COUNT(players.id) AS pCount
          FROM managers, players
          WHERE managers.email = '$email'
          OR players.email = '$email'");
return (mysql_result($query, 0) > 0) ? true : false ;
}

[Muddy_Funster]

what about this?

function emailExistsPlayers($email){
$query = <<<QUERY
SELECT count(emailCount) as emailTotal FROM
(
(select email as emailCount FROM managers WHERE email='$email')
UNION
(SELECT email as emailCount FROM players WHERE email='$email')
) AS emailTbl
QUERY;
$result = mysql_query($query) or die (mysql_error());
$row = mysql_fetch_assoc($result);
if ($row['emailTotal'] >= 1){
$return = true;
}
else{
$reurn = false;
}
return $return;
}

 

[fenway]

Technically, you should be counting in each subquery and then summing.