Use PHP to match username & password with HTML drop down

Question

I have a MySQL database called school that is set up like this: schoolID(1), schoolName(school 1), schoolCounty(Buckinghamshire), schoolUsername(school1admin), schoolPassword(school1password)

I currently have a drop down menu that shows the list of schools and when I type any username and password into the HTML login form I can log in.

I can't seem to work out how I can set it so, depending on the school selection will depend on what username and password to use.

For example, if i select school1 then i can ONLY use school1's username and password.

This is what I have so far for index.php:

<?php

require_once 'databaseConnect.php';  // connects to the databse via this file
if ($conn->connect_error) die ($conn->connect_error); // check the connection to the database. If failed, display error


$sql = "SELECT * FROM school";
$result = $conn->query($sql);


$conn->close();
?>  


<html>
    <body>
        <title>EduKode</title>




       <div id="login-form-container">
           <p>Log In:</p>

<?php
echo'<div id="schoolSelection">';
echo '<select name="schoolName">';
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo '<option>'. $row["schoolName"].  "<br>";
}
} else {
echo "0 results";
}
echo '</select>';

echo'</div>';

//http://stackoverflow.com/questions/10009464/fetching-data-from-mysql-database-to-html-dropdown-list
?>

                <form id="login-form" name="contactform" method="post" action="checkSchoolCredentials.php"> <!-- when submitting the form will call the 'authenticate.php' script.-->
                    <div class="contact-form">

                        <label>Username:</label>
                        <input name="username" type="text"> <!-- students created username field-->

                        <label>Password:</label>
                        <input name="password" type="password"> <!-- students created password field-->
                    </div>
                        <div id="submit-button">
                            <input type="submit" name="submit" value="Log In">
                        </div>
                </form>
        </div>

    </body>
</html>

This is for checkSchoolCredentials.php:

<?php
require_once 'databaseConnect.php';  // connects to the databse via this file
if ($conn->connect_error) die ($conn->connect_error); // check the connection to the database. If failed, display error


if(isset($_POST['submit'])) // if submit button is pressed
{
    $username = $_POST['username']; //assigns the value of the input box username to $username
    $password = $_POST['password']; //assigns the value of the input box password to $password

    $query = "SELECT * FROM school WHERE schoolUsername='$username' AND schoolPassword ='$password'"; // Query the database 


    $result=mysqli_query($conn, $query);
    if(mysqli_num_rows($result) ==1)
    {
        session_start(); // start session 
        $_SESSION['auth']='true';
        $_SESSION['username'] = $username; // save session as username
        header('location:taskSelection.php'); // if correct, redirect to taskSelection.php

    }
    else
    {
        header('location:index.php'); // redirect to index.html if incorrect


    }

}


$conn->close();

?>

Oh, and put it inside the `
` element, otherwise it will not submit to the server when the form is submitted — Alon Eitan, Mar 25 '16 at 20:55

rsabir · Accepted Answer · 2016-03-26T19:53:30.160

-1

You were close, what you have to is to send also the schoolname and check if all variable are set:

if (isset($POST['username'],$POST['userpassword'],$POST['schoolName'])

And then just replace :

$query = "SELECT * FROM school WHERE schoolUsername='$username' AND schoolPassword ='$password'"; // Query the database

with :

$query = "SELECT * FROM school WHERE schoolUsername='$username' AND schoolPassword ='$password' AND schoolName='$schoolName'"; //

Now you have to now know that my query is still bad because it's vulnerable to sql injection. You have to use prepare statements instead:

 $sql = "SELECT * FROM school WHERE schoolUsername=? AND schoolPassword = ? AND schoolName=?"; 
 if ($query = $conn->prepare($sql)){
   $query->bind_param("s", $username,$password,$schoolName);
   $stmt->bind_result($result);
   while($stmt->fetch()){
    // you can work with $result which is an array containing a line of the results 
    }

edited Mar 26 '16 at 19:53

answered Mar 25 '16 at 18:51

rsabir

738
1
7
17

Thank you very much for the help!! Very helpful.It seems to be working so far. Are you saying that this method is open to malicious users? – Toby Mar 26 '16 at 14:56
Definitely yes. Anyone who knows sql and document himself for sql injection can do [theses attacks](http://www.w3schools.com/sql/sql_injection.asp). That's why you need to use prepare statement. I've edited my answer. – rsabir Mar 26 '16 at 15:59
I'm not very good with PHP. I had 2 errors" Notice: Undefined variable: dbh and Fatal error: Call to a member function prepare() on null – Toby Mar 26 '16 at 16:52
Where would I place the code and what would I replace it for? Just the $query statement? – Toby Mar 26 '16 at 16:53
Ok, tell me are you using what are you using for connecting to database? print me the php part of 'databaseConnect.php' – rsabir Mar 26 '16 at 17:20
– Toby Mar 26 '16 at 17:41
ok replace the last line with : `$conn= new PDO("mysql:host=$hn;dbname=$db", $un, $pw);` and tell me what you got. – rsabir Mar 26 '16 at 18:45
Unfortunately it didn't work and it's making it even more difficult for me to create and end sessions. I will leave it as it was before as it's not commercial and it's wont matter. Thank you anyway. – Toby Mar 26 '16 at 19:24
It's not commercial, it's a real treat. For session, it will not affect it if you let the `session_start()`.If you don't want to use PDO, you can steak with mysqli but then you have to use [mysqli_prepare] (http://php.net/manual/fr/mysqli.prepare.php). Just look at the link and I will change my code to be compatible with mysqli. Don't give up!! – rsabir Mar 26 '16 at 19:40

score -1 · Answer 2 · answered Mar 25 '16 at 18:51

-1

Add AND schoolName = "$schoolName" to your SQL statement

answered Mar 25 '16 at 18:51

Keith Tyler

719
4
18

Use PHP to match username & password with HTML drop down

2 Answers2