Jump to content

Archived

This topic is now archived and is closed to further replies.

centenial

login script - multiple mysql tables

Recommended Posts

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!

Share this post


Link to post
Share on other sites
just run an if statement to check if the key = 0, then set one session varible, else set something else for the session varible

Share this post


Link to post
Share on other sites
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--]

Share this post


Link to post
Share on other sites
[!--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?

Share this post


Link to post
Share on other sites
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\" /]

Share this post


Link to post
Share on other sites
[!--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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[!--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?

Share this post


Link to post
Share on other sites
[!--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!

Share this post


Link to post
Share on other sites
[!--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 :)

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.