Nic727 Posted May 6, 2016 Share Posted May 6, 2016 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. 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 More sharing options...
justsomeguy Posted May 6, 2016 Share Posted May 6, 2016 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 More sharing options...
Nic727 Posted May 6, 2016 Author Share Posted May 6, 2016 Hi, I use mysql, because Its what we are learning now at school. what do you mean about the WHERE clause? The table was provided by my teacher, so can't change that. Link to comment Share on other sites More sharing options...
justsomeguy Posted May 6, 2016 Share Posted May 6, 2016 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 More sharing options...
dsonesuk Posted May 6, 2016 Share Posted May 6, 2016 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 More sharing options...
dsonesuk Posted May 6, 2016 Share Posted May 6, 2016 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 More sharing options...
justsomeguy Posted May 6, 2016 Share Posted May 6, 2016 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 More sharing options...
Nic727 Posted May 6, 2016 Author Share Posted May 6, 2016 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 More sharing options...
justsomeguy Posted May 6, 2016 Share Posted May 6, 2016 Does it work when you run it? Link to comment Share on other sites More sharing options...
dsonesuk Posted May 6, 2016 Share Posted May 6, 2016 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 } Link to comment Share on other sites More sharing options...
Nic727 Posted May 6, 2016 Author Share Posted May 6, 2016 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 More sharing options...
justsomeguy Posted May 6, 2016 Share Posted May 6, 2016 Just use the strict comparison so that it checks both the value and type. You can use strlen to get the length of a string. Link to comment Share on other sites More sharing options...
Nic727 Posted May 6, 2016 Author Share Posted May 6, 2016 Just tought that its a bad idea to check lenght, because If I change username lenght it will just break everything. Link to comment Share on other sites More sharing options...
Nic727 Posted May 7, 2016 Author Share Posted May 7, 2016 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 More sharing options...
dsonesuk Posted May 8, 2016 Share Posted May 8, 2016 It will work just as well using strict comparison '===' without the need for adding a text character prefix. Link to comment Share on other sites More sharing options...
Nic727 Posted May 9, 2016 Author Share Posted May 9, 2016 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 More sharing options...
justsomeguy Posted May 9, 2016 Share Posted May 9, 2016 Your original query returned all of the information for that user, you can save it in the session or elsewhere if you want to access it on other pages. Link to comment Share on other sites More sharing options...
Nic727 Posted May 10, 2016 Author Share Posted May 10, 2016 I found another problem in my login. I can connect with users inside the association table, but other users existing in other tables like 0004 can't be logged in. Link to comment Share on other sites More sharing options...
justsomeguy Posted May 10, 2016 Share Posted May 10, 2016 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 More sharing options...
Nic727 Posted May 10, 2016 Author Share Posted May 10, 2016 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 More sharing options...
justsomeguy Posted May 10, 2016 Share Posted May 10, 2016 I'm just talking about individual queries where you check each table individually. Or, you could use a union to combine the results of several queries (checking each table). Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now