login script - multiple mysql tables

[centenial]

Hi,

I have two tables in my MySQL database:
1. companies
2. employees

Each table has some different fields, but they each have the three same login fields:
1. email
2. password
3. company_admin (Has a value of either 1 or 0)

I want to be able to use one login script for both types of users, and assign them a session value of 1 or 0 depending on whether they have "Company_Admin" powers.

Here's what I have so far, but it keeps giving me a "Column 'email' in where clause is ambiguous" error - Can someone please help?

[code]        include 'db.php';        
        $check = "SELECT * FROM companies, employees WHERE email = '$_POST[email]' AND password = '$_POST[password]'";
        $check_result = mysql_query($check,$conn) or die(mysql_error());

        if (mysql_num_rows($check_result) > 0) {
            while ($res = mysql_fetch_array($check_result)) {
                $_SESSION[login_id] = $res['id'];
                $_SESSION[company_admin] = $res['company_admin'];
            }
        header("Location: index.php");
        exit;

        } else {
                $display_block = "We're sorry, but your login information appears to be incorrect. Would you like to <a href=\"$_SERVER[PHP_SELF]\">try again</a>?";
        }[/code]
Thanks!

[joecooper]

just run an if statement to check if the key = 0, then set one session varible, else set something else for the session varible

[zq29]

You need to define which table the columns are in...
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * [color=green]FROM[/color] [color=orange]companies,[/color] employees [color=green]WHERE[/color] companies.email [color=orange]=[/color] [color=red]'$_POST[email]'[/color] [color=blue]AND[/color] companies.password [color=orange]=[/color] [color=red]'$_POST[password]'[/color] [!--sql2--][/div][!--sql3--]

[centenial]

[!--quoteo(post=386162:date=Jun 20 2006, 05:57 PM:name=SemiApocalyptic)--][div class=\'quotetop\']QUOTE(SemiApocalyptic @ Jun 20 2006, 05:57 PM) [snapback]386162[/snapback][/div][div class=\'quotemain\'][!--quotec--]
You need to define which table the columns are in...
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * [color=green]FROM[/color] [color=orange]companies,[/color] employees [color=green]WHERE[/color] companies.email [color=orange]=[/color] [color=red]'$_POST[email]'[/color] [color=blue]AND[/color] companies.password [color=orange]=[/color] [color=red]'$_POST[password]'[/color] [!--sql2--][/div][!--sql3--]
[/quote]
Thanks! But won't that only check to see if the password equals the fields in the company table? I need something that checks if it matches in both the company table and the employee table... any ideas?

[zq29]

I'm not as fluent in SQL, so I'd probably go about it like this:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * [color=green]FROM[/color] [color=orange]companies,[/color] employees [color=green]WHERE[/color] companies.email [color=orange]=[/color] [color=red]'$_POST[email]'[/color] [color=blue]AND[/color] companies.password [color=orange]=[/color] [color=red]'$_POST[password]'[/color] [color=blue]AND[/color] companies.password [color=orange]=[/color] employees.password [!--sql2--][/div][!--sql3--] Someone could come along and put me in my place with a [i]"Ooohhh thats the long way around it, you can do it like this in half the space..."[/i] [img src=\"style_emoticons/[#EMO_DIR#]/smile.gif\" style=\"vertical-align:middle\" emoid=\":smile:\" border=\"0\" alt=\"smile.gif\" /]

[centenial]

[!--quoteo(post=386166:date=Jun 20 2006, 06:06 PM:name=SemiApocalyptic)--][div class=\'quotetop\']QUOTE(SemiApocalyptic @ Jun 20 2006, 06:06 PM) [snapback]386166[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I'm not as fluent in SQL, so I'd probably go about it like this:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * [color=green]FROM[/color] [color=orange]companies,[/color] employees [color=green]WHERE[/color] companies.email [color=orange]=[/color] [color=red]'$_POST[email]'[/color] [color=blue]AND[/color] companies.password [color=orange]=[/color] [color=red]'$_POST[password]'[/color] [color=blue]AND[/color] companies.password [color=orange]=[/color] employees.password [!--sql2--][/div][!--sql3--] Someone could come along and put me in my place with a [i]"Ooohhh thats the long way around it, you can do it like this in half the space..."[/i] [img src=\"style_emoticons/[#EMO_DIR#]/smile.gif\" style=\"vertical-align:middle\" emoid=\":smile:\" border=\"0\" alt=\"smile.gif\" /]
[/quote]

:( The errors gone now, but it's getting session data for a different user than the one supplied in the html form. Any idea why this is?

[zq29]

You haven't got more than one user with the same email and password have you? If you have, your code will assign the last user of those selected to your session variables.

[centenial]

[!--quoteo(post=386170:date=Jun 20 2006, 06:28 PM:name=SemiApocalyptic)--][div class=\'quotetop\']QUOTE(SemiApocalyptic @ Jun 20 2006, 06:28 PM) [snapback]386170[/snapback][/div][div class=\'quotemain\'][!--quotec--]
You haven't got more than one user with the same email and password have you? If you have, your code will assign the last user of those selected to your session variables.
[/quote]
No, I don't - Any ideas?

[redarrow]

[!--quoteo(post=386175:date=Jun 20 2006, 10:39 PM:name=centenial)--][div class=\'quotetop\']QUOTE(centenial @ Jun 20 2006, 10:39 PM) [snapback]386175[/snapback][/div][div class=\'quotemain\'][!--quotec--]
No, I don't - Any ideas?
[/quote]


Post the code you got so far ok!

[centenial]

[!--quoteo(post=386180:date=Jun 20 2006, 06:54 PM:name=redarrow)--][div class=\'quotetop\']QUOTE(redarrow @ Jun 20 2006, 06:54 PM) [snapback]386180[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Post the code you got so far ok!
[/quote]

[code]
        include 'db.php';        
        $check = "SELECT * FROM companies cmpy, employees empl WHERE (cmpy.email = '$_POST[email]' AND cmpy.password = '$_POST[password]') OR
(empl.email = '$_POST[email]' AND empl.password = '$_POST[password]')";
        $check_result = mysql_query($check,$conn) or die(mysql_error());

        if (mysql_num_rows($check_result) > 0) {
            while ($res = mysql_fetch_array($check_result)) {
                $_SESSION[login_id] = $res['id'];
                $_SESSION[company_admin] = $res['company_admin'];
            }
        header("Location: index.php");
        exit;

        } else {
                $display_block = "We're sorry, but your login information appears to be incorrect. Would you like to <a href=\"$_SERVER[PHP_SELF]\">try again</a>?";
        }
[/code]

Thanks - The problem I'm having is that it is assigning session values for a different user than was put into the login form. your help is GREATLY appreciated :)

[redarrow]

when you goto one page to another use a link like this and you should see the user logeed in
on the url.

[code]

<a href='whatever.php?&login_id=$_SESSION[login_id]'>what_ever</a>

[/code]


debug, echo this out to test correct session is set.
[code]
<? session_start();

echo $_SESSION[login_id];

?>
[/code]