Jump to content

Login goes wrong INNER JOIN


Nic727

Recommended Posts

Hi,

I have multiple tables in my database and it's why I did an INNER JOIN.
Bellow is the tables and one table is "Association" which mean that it's the starting point to link to other tables.

table54.png

In association tables we have username or number for 3 différents categories of users. (Students - Stagiaires, employers - employeurs and supervisors - superviseurs).

 

Each kind of user will go on a different page when login and it's where I have a problem.

 

Here is my code for the login.

<?php
//Variables
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name="stages2017"; // Database name 
//$tbl_name="associations"; // Table name
 
//$join="SELECT * FROM associations INNER JOIN etudiant ON stagiaires.noetudiant = associations.noedutiant INNER JOIN etudiant ON superviseurs.noemploye = associations.noemploye INNER JOIN etudiant ON employeurs.noemployeur = associations.noemployeur";
 
//Connexion à la base de données
$con = mysql_connect("localhost","root","");
mysql_select_db("stages2017", $con);
$sql = "SELECT * FROM associations INNER JOIN stagiaires ON stagiaires.noetudiant = associations.noetudiant INNER JOIN superviseurs ON superviseurs.noemploye = associations.noemploye INNER JOIN employeurs ON employeurs.noemployeur = associations.noemployeur";
$result=mysql_query($sql,$con);
 
//Connexion de l'usager
$login=$_POST['login'];
$password=$_POST['pwd'];
 
//Permet de vérifier l'utilisateur
while($row=mysql_fetch_array($result)){
  
    if(($login == $row['noetudiant']) && ($password == $row['mdpetudiant'])){
        session_start ();
        // On 
        $_SESSION["active"] = true;
        $_SESSION["acces"] = "stagiaire";
        /*$_SESSION["login"] = $_POST["login"];*/
        $_SESSION["nom"] = $row["nometudiant"];
        header("Location: stagiaire.php");
          
    }if(($login == $row['noemploye']) && ($password == $row['mdpemploye'])){
        session_start ();
        // On 
        $_SESSION["active"] = true;
        $_SESSION["acces"] = "employe";
        /*$_SESSION["login"] = $_POST["login"];*/
        $_SESSION["nom"] = $row["nomemploye"];
        header("Location: superviseur.php");
          
    }if(($login == $row['noemployeur']) && ($password == $row['mdpemployeur'])){
        session_start ();
        // On 
        $_SESSION["active"] = true;
        $_SESSION["acces"] = "employeur";
        /*$_SESSION["login"] = $_POST["login"];*/
        $_SESSION["nom"] = $row["personnecontact"];
        header("Location: employeur.php");
          
    }if(($login == 'timcegep') && ($password == 'stages2017')){
        session_start ();
        // On 
        $_SESSION["active"] = true;
        $_SESSION["acces"] = "admin";
        /*$_SESSION["login"] = $_POST["login"];*/
        header("Location: admin.php");
      
    }
    }
      
    echo "User doesn't exist.";
 
      
?>

And that's two examples of my 4 other pages (because 3 + 1 for admin) :

 

employer :

<?php
session_start();
ob_start();
?>
 
<!DOCTYPE HTML>
<html>
<head>
<meta charset="UTF-8">
<title>Employeur</title>
 
</head>
<body>
<?php
if (!$_SESSION["active"]|| $_SESSION["acces"]!="employeur"){
    header("location: index.php");
}else{
 
?>
 
<h1>Employeur</h1>
 
<h2>Bienvenue <?php echo $_SESSION["nom"] ?></h2>
 
<p>Vous êtes maintenant connecté!</p>
 
<a href="logout.php">Déconnexion</a>
 
 
 
<?php
}
?>
 
 
</body>
</html>

student

<?php
session_start();
ob_start();
?>
 
<!DOCTYPE HTML>
<html>
<head>
<meta charset="UTF-8">
<title>Stagiaire</title>
 
</head>
<body>
<?php
if (!$_SESSION["active"]|| $_SESSION["acces"]!="stagiaire"){
    header("location: index.php");
}else{
 
?>
 
<h1>Stagiaire</h1>
 
<h2>Bienvenue <?php echo $_SESSION["nom"] ?></h2>
 
<p>Vous êtes maintenant connecté!</p>
 
<a href="logout.php">Déconnexion</a>
 
 
 
<?php
}
?>
 
 
</body>
</html>

So my problems are :

 

1. If I connect with employer 0004 and password 0004 which should allow me to login to employer page, it login me as student 4 on student page.

2. If I connect as a student, it login me on employer page.

3. Some pages are not working at all.

 

So maybe my Inner Join is wrong or I need to remove "Else" from each other page and have an else if like :

if (!$_SESSION["active"]|| $_SESSION["acces"]!="stagiaire"){
    header("location: index.php");
}else if ($_SESSION["active"] && $_SESSION["acces"]="stagiaire){

I don't know what's wrong with my pages... I tried to fixe that for 2 days and I really want it fixed.

 

Thank you

Link to comment
Share on other sites

There are several problems. The major one is that your code will not work in the current version of PHP because you're still using the old mysql extension, which has been removed. You should use PDO to connect to databases, and you should use prepared statements instead of putting data directly into a query.

 

Why doesn't your query have a WHERE clause where you're looking up a specific user instead of getting all users and looping through them to look for a match? The database should do the work there, you should look up the specific user based on the username.

 

Your database structure doesn't make a lot of sense to me. It seems like it would be a more simple design if you had one table for logins, which listed the username and password for each login, an auto-increment ID, and another column to list the type of user. You look them up in that table to see if they entered a correct username and password and, if so, then you look at what kind of user they are and use the auto-increment ID to look up the rest of their information in the other table for that user type. There's another way to design the database where you wouldn't have separate tables for each user type, where you could keep basic user information all in a single table and have one other table to list the properties for all users of any type. That would only require 2 tables to handle the user information. I think that either of those designs would make more sense than the table structure you have now.

Link to comment
Share on other sites

Your school requires you to use functions like mysql_connect and mysql_query? They specifically say that? You're not allowed to use something newer like mysqli or PDO? Mysqli is 12 years old, but you're not allowed to use that? PHP 7 does not support the old mysql extension that mysqli replaced 12 years ago, so you're learning things that you will immediately have to forget and re-learn if you expect to get a job doing this kind of thing. It would just make sense to learn it the right way instead of learning something old and useless and then learning the right way later.

 

what do you mean about the WHERE clause?

Your query gets all users. Why not get only the user corresponding to the entered username, if one exists?

 

Does every user in the system have an entry in all 3 of those tables, or are there users that would only be an employer or supervisor?

Link to comment
Share on other sites

Correct me if I'm wrong, BUT! comparing varchar id num of '0000001' with a basically another string login '00001' value using '==' in a if condition, results in it comparing '1' and '1' with leading zeros removed, meaning login WILL match student and employer id. You be should use '===' instead, but i totally agree that this is totally the wrong way to do this.

Link to comment
Share on other sites

example

<?php

$stuid = "0000001";
        $thisid = "00001";

        echo '<h1>Comparing  ' . $stuid . ' with ' . $thisid . '</h1>';


        echo '<h3>Using "==" </h3>';
        if ($stuid == $thisid) {

            echo "they match <br>";
        } else {
            echo "nope don't match <br>";
        }
        echo '<hr>';

        echo '<h3>Using "===" </h3>';
        if ($stuid === $thisid) {

            echo "they match <br>";
        } else {
            echo "nope don't match <br>";
        }
?>
Link to comment
Share on other sites

You ran that and it claimed they match with a loose comparison? I considered that also but I was thinking that, Shirley, PHP wouldn't convert to numbers when both operands are strings. It wouldn't really shock me if it did though, that would be one of the weaknesses of PHP. Even so, that comparison should work fine if done by MySQL instead of PHP.

Link to comment
Share on other sites

Could it work by doing that?

l_fetch_array($result)){
  
    if(($login === $row['noetudiant']) && ($password === $row['mdpetudiant'])){
        session_start ();
        // On 
        $_SESSION["active"] = true;
        $_SESSION["acces"] = "stagiaire";
        /*$_SESSION["login"] = $_POST["login"];*/
        $_SESSION["nom"] = $row["nometudiant"];
        header("Location: stagiaire.php");
Link to comment
Share on other sites

Or another solution could be to work with lenght of number with String, because each type of users have a different lenght for name and password. I'm not good with string, so how could it be added? Before $row?

Link to comment
Share on other sites

The only way to get it to work with '==' it to make sure the text is not read as a number which results in leading zeros being removed with

 

if ('#'. $stuid == '#'. $thisid)

{

...rest of code

}

 

It works thank you very much :)

 if(('#'.$login == '#'.$row['noetudiant']) && ('#'.$password == '#'.$row['mdpetudiant'])){
        session_start ();
        // On 
        $_SESSION["active"] = true;
        $_SESSION["acces"] = "stagiaire";
        /*$_SESSION["login"] = $_POST["login"];*/
        $_SESSION["nom"] = $row["nometudiant"];
        header("Location: stagiaire.php");
Link to comment
Share on other sites

Thank you.

 

Now how to get some informations related to one user?

 

For example, if I connect with one user, I would like to be able to get informations related to this user. (email, name, his superior, his students, etc.) In the table above, you can see that noemployeur 0001 is here two times.

 

I don't know if I have to rewrite INNER JOIN on each pages or not...

 

I'm trying to achieve this table :

$con = mysql_connect("localhost", "root", "");
mysql_select_db("stages2017", $con);
$sql = "SELECT * FROM associations INNER JOIN stagiaires ON stagiaires.noetudiant = associations.noetudiant INNER JOIN superviseurs ON superviseurs.noemploye = associations.noemploye INNER JOIN employeurs ON employeurs.noemployeur = associations.noemployeur";
$result=mysql_query($sql,$con);

echo "<table border='1'>";
echo "<tr><th>Student Number</th><th>Student Name</th><th>Student Email</th><th>Supervisor Name</th><th>Supervisor Email</th></tr>";
echo "<tr>";
while($row = mysql_fetch_array(SOMETHING HERE!!!))
  {
  echo "<td>".$row['noetudiant']."</td><td>".$row['nometudiant']."</td><td>".$row['courrieletudiant']."</td><td>".$row['nomemploye']."</td><td>".$row['courrielemploye']."</td></tr>";
  }
mysql_close($con);
echo "</table>";

The example above is for employer page.

 

Do I have the possibility to just take what was in my login page instead of rewriting all SQL stuffs and just row related to connected user?

 

Thank you

Link to comment
Share on other sites

An inner join will only find records that exist in every table, that's what an inner join does. If there isn't a record in the association table then it won't find that user. That's one reason I suggested a different database structure. I suppose your alternative is to run a query on each individual table to find where they are, or you could use a union to get records from any of the other tables that match the username.

Link to comment
Share on other sites

An inner join will only find records that exist in every table, that's what an inner join does. If there isn't a record in the association table then it won't find that user. That's one reason I suggested a different database structure. I suppose your alternative is to run a query on each individual table to find where they are, or you could use a union to get records from any of the other tables that match the username.

I tried a FULL OUTER JOIN, but it's not working.

Do you have a solution to check all tables?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...