Jump to content

login script - multiple mysql tables


centenial

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!
Link to comment
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--]
Link to comment
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?
Link to comment
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\" /]
Link to comment
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?
Link to comment
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?
Link to comment
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!
Link to comment
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 :)
Link to comment
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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.