PHP: Login system, querying a second table?

Question

I have a very basic PHP login system which I'm altering right now for a product. The user is able to register with a username/password and the information is stored into a database. However, I have a separate table called "act_codes" which has a row called "act". I want to store activation codes in that table. For example, I would have "Code1" "Code2" and "Code3" in the act row. The user would login with their username and password, but they'd also need to enter their activation code which is generated via my ecommerce platform and emailed to them.

So far I have this for validating username/password which works fine:

    if(empty($username_err) && empty($password_err) && empty($code_err)){
    $sql =   "SELECT username, password FROM users WHERE username = ?";

    if($stmt = mysqli_prepare($link, $sql)){
        mysqli_stmt_bind_param($stmt, "s", $param_username);
        $param_username = $username;
        if(mysqli_stmt_execute($stmt)){
            mysqli_stmt_store_result($stmt);
            if(mysqli_stmt_num_rows($stmt) == 1){                    
                mysqli_stmt_bind_result($stmt, $username, $hashed_password);
                if(mysqli_stmt_fetch($stmt)){
                    if(password_verify($password, $hashed_password)){
                        session_start();
                        $_SESSION['username'] = $username;  
                        $_SESSION['code'] = $code;                          
                        header("location: welcome.php");
                    } else{
                        $password_err = 'The password you entered was not valid.';
                    }
                }
            } else{
                $username_err = 'No account found with that username.';
            }
        }   
    }
    mysqli_stmt_close($stmt);
}

I can't seem to figure out how to alter this code to also include the validation of the activation code from my second table. Could someone help me out by chance? Thank you very much!

What is the exact problem you have? You already know how to send a query and read the data from it, so what changed? — Progman, May 13 '18 at 11:29

Martin · Answer 1 · 2018-05-13T12:08:45.590

Some points:

as a side point: put session_start() at the top of the script
And include a die statement immediately after your header("Location:...);

Are these two lines in the wrong order?

mysqli_stmt_bind_param($stmt, "s", $param_username);
$param_username = $username;

They should be set frst and then bound.

To answer your query

You seem to want a MySQL JOIN such as :

 SELECT users.username, users.password act.var_code 
 FROM users 
 LEFT JOIN act ON users.userid = act.userid
 WHERE username = ?

This is a rough result as I can't see your table structre, but feel free to read other Stack Overflow questions on a similar vain.

What type of MySQL join you use is entirely up to you. I have used LFET JOIN simply as an example.

As also referenced by d3t0x in their answer you will need to bind the additional data returned by MySQL, as well.

score 0 · Answer 2 · answered May 13 '18 at 11:43

0

Why not use an inner join and this will mean you can keep using just the one query:

$sql1 ="SELECT users.username, users.password, codes.code FROM users INNER JOIN codes ON users.username = codes.username";

You will also need to bind the returned code to a variable so you can compare it with the code in the session.

Something like:

mysqli_stmt_bind_result($username, $hashed_password, $code);

This is presuming your table structure is:

users table = username, password codes table = username, code

answered May 13 '18 at 11:43

d3t0x

432
4
15

But with an Inner join if no auth code exists for whatever reason then there will be no results. I a not saying this is wrong but we simply don't know enough about the OPs intended flow structure. Good point re: returning the code value, however – Martin May 13 '18 at 11:59

PHP: Login system, querying a second table?

2 Answers2

Some points:

To answer your query