Select from 2 tables

[tebrown]

Hey Guys,

 

I originally had one table that was called users, which contained both the players and managers of my application. I now want to create separate tables for both managers and players.

 

When i went to change my code from this:

 

function login($email, $password) {
    $query = mysql_query("SELECT COUNT(id) FROM [b]users[/b] WHERE email = '$email' AND password = '$password'");
    return (mysql_result($query, 0)) ? true : false ;
}

 

To this:

 

function login($email, $password) {
    $query = mysql_query("SELECT COUNT(id) FROM [b]users, players[/b] WHERE email = '$email' AND password = '$password'");
    return (mysql_result($query, 0)) ? true : false ;
}

 

It gave me a warning message:

 

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

 

What have i done wrong here?

[The Little Guy]

mysql_query("SELECT COUNT(id) FROM users, players WHERE email = '$email' AND password = '$password'")or die(mysql_error());

[tebrown]

Thanks for that, it worked, but it now says my Column 'email' in where clause is ambiguous. I changed the select part but not sure if im doing it properly...

 

Both tables contain id, email, salt, password.

 

function getSalt($email) {
$query = mysql_query("SELECT users.salt, players.salt FROM users, players JOIN email = '$email'") or die(mysql_error());
$row = mysql_fetch_assoc($query);
return $row['salt'];
}

// This will check the username and password.JOIN tbl_section ON tbl_section.id = tbl_names.id 
function login($email, $password) {
$query = mysql_query("SELECT users.email, players.email FROM users, players WHERE email = '$email' AND password = '$password'") or die(mysql_error());
return (mysql_result($query, 0)) ? true : false ;
}

 

Cheers

[DavidAM]

If you are trying to find ONE record that is in ONE table or the OTHER, you would have to use a UNION and select from BOTH tables independently.

 

Having said that, I must say this: you do not want to do that!

 

You should have ONE table for users -- ALL login information will be in this table. If you have different data requirements for players vs. managers, then you create TWO ADDITIONAL tables players and managers which have the user.id as a foreign key to the users table. The users table would also need a flag to indicate whether the user is a player or a manager.

 

[tebrown]

Thanks for that. That makes sense. I will try that.

 

Cheers

tebrown