CODEWITHSUNDEEP
phpmysql
user1162243
user1162243

Reputation: 11

Joining tables in MySql with one common field but different values for the common field

how to join 3 tables with one common field like

in this c_role field is common here i assigned

for giving rights to 3 different users like corres, principal and for a staff when they login.. Now when they login it should identify the user and take them to their page..

Upvotes: 1

Views: 1016

Answers (3)

user1160249
user1160249

Reputation: 13

session and include functions are given:

 session_start();
 include("config.php"); 
 if(isset($_POST['T_UserName']) && isset($_POST['T_Password']) &&         !empty($_POST['T_UserName']) && !empty($_POST['T_Password']))
 {

username and password sent from form:

 $T_UserName=$_POST['T_UserName'];
 $T_Password=$_POST['T_Password'];

To protect MySQL injection:

 $T_UserName = stripslashes($T_UserName);
 $T_Password = stripslashes($T_Password);
 $T_UserName= mysql_real_escape_string($T_UserName);
 $T_Password = mysql_real_escape_string($T_Password); 

  $sql="SELECT * FROM login WHERE username='$T_UserName' and password='$T_Password'";     $result=mysql_query($sql);

Mysql_num_row is counting table row:

  $count=mysql_num_rows($result);

If result matched $T_UserName and $T_Password, table row must be 1 row :

  if($count==1)
        {

Register $T_UserName, $T_Password and redirect to file "correspindex.php" : 

         session_register("T_UserName");
         session_register("T_Password");

redirect to error page or display error message then :

         if(isset($_POST['emp_role'])
              { 
                $userinfo = mysql_fetch_assoc($sql);
                $emp_role = $userinfo['emp_role'];
         if($emp_role == 1)
            { 
               header("location:corrrespondindex.php"); 
            } 
              elseif($emp_role == 2 )
               {
               header("location:principalindex.php");
               } 
                elseif($emp_role == 3)
                {
               header("location:staffindex.php");
                } 
           closes out if the user DOES exist:

                header("location:loginhome.php"); 
                 } 
                else
                 { 
                 echo "Wrong Username or Password"; 
                 }
                }
             } 
 php is closed

       this is the php code im getting so many error
       i create 1table with id,username,password and role(ENUM,values as'1','2','3' –
       any else shud i do in the code???

Upvotes: 0

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

The only joins would be on username (and password ?) unless c_id, p_id and s_id are all the same id?

You can't join on role seeing as they are different in each table, aside from an utterly daft role = 1 in Table1 is the equivalent of role = 2 in table2.

Do you mean a union? As in you want

User Password Role
Fred Fr3d     1
Fred ???      2
Fred ???      3

Not sure what you are trying to achieve with this schema, but it breaks near every rule in the book, and doesn't seem to meet your needs....

Based on your comment, one way you might look at is.

Is 

Users (UserID, UserName, Password etc) Key UserID
Roles (RoleID, RoleName etc) Key RoleID
UserRoles(UserID,RoleID) Key UserID,RoleID

You need to learn a bit about databases particularly normalisation, first three forms should do for most things.

Then

Select UserName, Password,RoleName From Users 
inner join UserRoles on Users.UserID = UserRoles.UserID
inner join Roles on UserRoles.RoleID = Roles.RoleId

and such like become possible and efficient.

Upvotes: 1

bowlerae
bowlerae

Reputation: 944

You should have one table that contains the following columns:

id (unique, primary), username, password, role (INT, would be assigned 1-3 for principal, staff or corre depending on the user)

Additionally

You could have another table called roles if you want set up like this:

id (unique, primary), title (options for title would be principal, staff, or corres)

When the user logs in just do something like

if($role == 1){
// redirect to principal page
}
elseif($role == 2){
// redirect to staff page
}
elseif($role == 3){
// redirect to corres page
}

I'm not certain but I believe this would work as well. Try this query.

(SELECT '1' AS role FROM table1 WHERE username = $username AND password = $password)
UNION ALL
(SELECT '2' AS role FROM table2 WHERE username = $username AND password = $password)
UNION ALL
(SELECT '3' AS role FROM table3 WHERE username = $username AND password = $password)

Assuming the user is only located in ONE of those three tables then it should match the username and password and find out which table the user is coming from. Then you can pull out the role by using 

$role = $row['role'];

If the username/password combination is incorrect then $role would be empty or you could fetch the number of rows [using $iscorrectuser = mysql_num_rows($query)] where there is a match and the number of rows would be 0. You could then redirect the user trying to log in with a "Unsuccessful login" error message.

Upvotes: 1

Related Questions