Jump to content


Photo

login script - multiple mysql tables


  • Please log in to reply
10 replies to this topic

#1 centenial

centenial
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 20 June 2006 - 09:11 PM

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?

        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>?";
        }
Thanks!

#2 joecooper

joecooper
  • Members
  • PipPipPip
  • Advanced Member
  • 358 posts

Posted 20 June 2006 - 09:57 PM

just run an if statement to check if the key = 0, then set one session varible, else set something else for the session varible
Signature:
[/a]
[a href="http://www.planet-so...=1999&lngWId=8" target="_blank"]EzLogin 1.0[/a]
[a href="http://www.essexracers.com" target="_blank"]Essexracers.com[/a]
Msn Messenger: joe@joeyjoe.co.uk

#3 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 20 June 2006 - 09:57 PM

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] * FROM companies, employees WHERE companies.email = '$_POST[email]' AND companies.password = '$_POST[password]' [!--sql2--][/div][!--sql3--]

#4 centenial

centenial
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 20 June 2006 - 10:03 PM

[!--quoteo(post=386162:date=Jun 20 2006, 05:57 PM:name=SemiApocalyptic)--][div class=\'quotetop\']QUOTE(SemiApocalyptic @ Jun 20 2006, 05:57 PM) View Post[/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] * FROM companies, employees WHERE companies.email = '$_POST[email]' AND companies.password = '$_POST[password]' [!--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?

#5 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 20 June 2006 - 10:06 PM

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] * FROM companies, employees WHERE companies.email = '$_POST[email]' AND companies.password = '$_POST[password]' AND companies.password = employees.password [!--sql2--][/div][!--sql3--] Someone could come along and put me in my place with a "Ooohhh thats the long way around it, you can do it like this in half the space..." [img src=\"style_emoticons/[#EMO_DIR#]/smile.gif\" style=\"vertical-align:middle\" emoid=\":smile:\" border=\"0\" alt=\"smile.gif\" /]

#6 centenial

centenial
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 20 June 2006 - 10:16 PM

[!--quoteo(post=386166:date=Jun 20 2006, 06:06 PM:name=SemiApocalyptic)--][div class=\'quotetop\']QUOTE(SemiApocalyptic @ Jun 20 2006, 06:06 PM) View Post[/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] * FROM companies, employees WHERE companies.email = '$_POST[email]' AND companies.password = '$_POST[password]' AND companies.password = employees.password [!--sql2--][/div][!--sql3--] Someone could come along and put me in my place with a "Ooohhh thats the long way around it, you can do it like this in half the space..." [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?

#7 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 20 June 2006 - 10:28 PM

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.

#8 centenial

centenial
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 20 June 2006 - 10:39 PM

[!--quoteo(post=386170:date=Jun 20 2006, 06:28 PM:name=SemiApocalyptic)--][div class=\'quotetop\']QUOTE(SemiApocalyptic @ Jun 20 2006, 06:28 PM) View Post[/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?

#9 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 20 June 2006 - 10:54 PM

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


Post the code you got so far ok!
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#10 centenial

centenial
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 20 June 2006 - 11:01 PM

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

        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>?";
        }

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 :)

#11 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 20 June 2006 - 11:14 PM

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


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



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

echo $_SESSION[login_id];

?>

Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users