What Is Wrong With Number Of Rows Count Function?

PHP
1

Guys & My Ladies,

This LOGIN.php was working lastnight and so I don’t know why not now.

The ELSE at 53 gets triggered even though I have typed the right password!

What do you think of line 16 ? …

if($numrows >1)

I tried the following but no luck:

if($numrows)

if($numrows !=0)

if($numrows ==2)

In the past, they worked, though. What is wrong, do you reckon ?

<?php
session_start();
require "conn.php";
require "site_details.php";

if(isset($_POST["member_login_submit"]))
{
    if(!empty($_POST["member_login_username_or_email"]) && !empty($_POST["member_login_password"]))
    {
        $member_login_username_or_email = trim(strip_tags(strtolower(mysqli_real_escape_string($conn,$_POST["member_login_username_or_email"]))));
        $member_login_password = trim(strip_tags(mysqli_real_escape_string($conn,$_POST["member_login_password"])));
        
        $sql = "SELECT * FROM users WHERE usernames='".$member_login_username_or_email."' OR emails='".$member_login_username_or_email."' AND passwords='".$member_login_password."'";
        $result = mysqli_query($conn,$sql);
        $numrows = mysqli_num_rows($result);
        if($numrows >1)
        {        
            while ($row = mysqli_fetch_assoc($result))
            {
                $db_username = $row["usernames"];
                $db_password = $row["passwords"];
                $db_email = $row["emails"];
                                        
                if  ($member_login_username_or_email == $db_username && $member_login_password == $db_password || $member_login_username_or_email == $db_email && $member_login_password == $db_password)            
                {
                    $_SESSION["user"] = $member_login_username_or_email;           
                    if(!empty($_POST["member_login_remember"]))
                    {
                        setcookie("member_login_username_or_email", $member_login_username_or_email, time()+ (10 * 365 * 24 * 60 * 60));
                        setcookie("member_login_password", $member_login_password, time()+ (10 * 365 * 24 * 60 * 60));                        
                    }
                    else
                    {
                        if(isset($_COOKIE["member_login_username_or_email"]))
                        {    
                            setcookie("member_login_username_or_email", "", "");
                        }
                        if(isset($_COOKIE["member_login_password"]))
                        {    
                            setcookie("member_login_password", "", "");
                        }        
                    }
                    header("location:home.php");            
                }
                else
                {
                    $message = "Invalid login!";
                }    
            }
        }
        else
        {
            $message = "Something is wrong! Try again later!";
        }        
    }
    else
    {
        $message = "You must input your Username and Password!";    
    }
}    

?>
<!DOCTYPE html>
<html>
<head>
   <title><?php $site_name?> Member Login Page</title>
   <meta charset="utf-8">
</head>
<body>
   <div class = "container">
      <form method="post" action="">
         <center><h3><?php $site_name ?> Member Login Form</h3></center>
         <div class="text-danger">
         <?php
            if(isset($message))
            {
               echo $message;
            }
         ?>
            <div class="form-group">
               <center>
                  <label>Username/Email:</label>
                  <input type="text" placeholder="Enter Username or Email" name="member_login_username_or_email" value="<?php if(isset($_COOKIE["member_login_username_or_email"])) echo $_COOKIE["member_login_username_or_email"]; ?>"
                </center>
            </div>
            <div class="form-group">
               <center>
                  <label>Password:</label>
                  <input type="password" placeholder="Enter password" name="member_login_password" value="<?php if(isset($_COOKIE["member_login_password"])) echo $_COOKIE["member_login_password"]; ?>">
               </center>
           </div>
           <div class="form-group">
               <center>
                  <label>Remember Login Details:</label>
                  <input type="checkbox" name="member_login_remember" />
               </center>
            </div>
            <div class="form-group">
               <center>
                  <input type="submit" name="member_login_submit" value="Login" class="button button-success" />
               </center>
           </div>
           <div class="form-group">
              <center>
                 <font color="red" size="3"><b>Forgot your password ?</b><br><a href="member_login_password_reset.php">Reset it here!</a></font>
              </center>
              <center>
                 <font color="red" size="3"><b>Not registered ?</b><br><a href="member_register.php">Register here!</a></font>
               </center>
            </form>
        </div>
</body>
</html>
2

have a look at all variables and results with var_dump()

3

if($numrows >1) is wrong for sure
why do you expect 2 rows returned for the single user?

4

Slightly away from the question, this seems to be a poor way to deal with remembering user details:

<input type="password" placeholder="Enter password" name="member_login_password" value="<?php if(isset($_COOKIE["member_login_password"])) echo $_COOKIE["member_login_password"]; ?>">

If one of your users logs out but leaves their browser at the login screen, a quick “view source” will reveal the password. While the page itself will show it as hidden text, I don’t think the source will.

But I can’t see why

if($numrows !=0)

would not work. As @colshrapnel asked, why would you expect two rows from the usernames table for a login? And if you got two rows, you don’t handle both of them anyway, your code just processes the first one and then does a header redirect.

You could also save yourself a bit of code in that checking loop, too. You run a query that returns all the rows in the users table where the usernames and passwords fields are equal to the form variables that the user types in. Then you run a while() loop through the results, assign the results to some variables starting with $db_, and then have an if() statement that checks the very same condition that the query checked in the first place. If you get any rows back at all from the query, then they will match the conditions you put in the query, otherwise there would be no point having those conditions in the query.

If you quote line numbers, could you mark them with a comment in your code please? It’s hard to count lines with various scrolling things going on.

5

I suspect it is a precedence issue and just needs some parens:

$sql = 'SELECT * FROM users WHERE (usernames=? OR emails = ? ) AND passwords = ?';

Of course you should shift to prepared statements and don’t store plain text passwords unless you put a big warning on your site telling users to stay far far away.

2 Likes
6

I am looking for one match for username and one match for password and so that makes to inputs:

$sql = "SELECT * FROM users WHERE usernames='".$member_login_username_or_email."' OR emails='".$member_login_username_or_email."' AND passwords='".$member_login_password."'";

So, user either enters his username or password (one input check) and then user enters his password and so another input check. Hence, looking for 2 matches. Therefore, the followings should work:

if($numrows)
if($numrows >1)
if($numrows == 2)
if($numrows !=1)

Do you agree now ?

7

Don’t worry, I will md5 the password. Thanks for the suggestion, though!

8

Will get into PDO later. Right now, wanna finish learning how to walk first with mysqli. Then, I’ll start hopping with pdo.
Currently, in the experimental and learning stages.

9

which is nowadays practically the same as a plain text password.

1 Like
10

Gosh, man! That was a FAST reply!
So, what do you suggest then if md5 is no good (apart from prepared statements ofcourse as will get to that when I’m intermediate. Right now beginner.) ?

11

Are you really saying you’re looking for 2 users, one with a matching username, one with a matching password and it doesn’t matter if they are not the same user?

12

The cookie codes you see are not mine atall. Copied from a youtube tut.
Care to show an example, how you’d code it, then ?

Thanks in advance!

13

http://php.net/manual/en/function.password-hash.php
Used with:-
http://php.net/manual/en/function.password-verify.php

2 Likes
14

You seem to not like these lines:


$sql = "SELECT * FROM users WHERE usernames='".$member_login_username_or_email."' OR emails='".$member_login_username_or_email."' AND passwords='".$member_login_password."'";
        $result = mysqli_query($conn,$sql);
		$numrows = mysqli_num_rows($result);
        if($numrows >1)
		{		
            while ($row = mysqli_fetch_assoc($result))
		    {
			    $db_username = $row["usernames"];
			    $db_password = $row["passwords"];
			    $db_email = $row["emails"];
			                			
                if  ($member_login_username_or_email == $db_username && $member_login_password == $db_password || $member_login_username_or_email == $db_email && $member_login_password == $db_password)

I’m not quite sure I understand what you are trying to tell me. Are you saying, UNLESS I want to echo the Username and Password then the following code should do:


$sql = "SELECT * FROM users WHERE usernames='".$member_login_username_or_email."' OR emails='".$member_login_username_or_email."' AND passwords='".$member_login_password."'";
        $result = mysqli_query($conn,$sql);
		$numrows = mysqli_num_rows($result);
        if($numrows >1)

And no need for the WHILE>LOOP (lines 18-22):

while ($row = mysqli_fetch_assoc($result))
		    {
			    $db_username = $row["usernames"];
			    $db_password = $row["passwords"];
			    $db_email = $row["emails"];

Anyway, this is the LOGIN.php we are dealing with. And if I do not want to echo the Username And Password in the login page but the homepage.php (the page you login to) then I guess you will say I should run the WHILE->LOOP on the homepage.php (lines 18-22) instead. Right ?

while ($row = mysqli_fetch_assoc($result))
		    {
			    $db_username = $row["usernames"];
			    $db_password = $row["passwords"];
			    $db_email = $row["emails"];

Am I right ? Did I understand you correctly or did I completely miss your point ? And, if I missed your point, then do you suggest I only use the WHILE->Loop in the homepage.php where the script would echo the user the Username & Password ?
I thought since we are using SESSION here then it doesn’t matter if I get the script to grab the Username & Password in the Login.php even though they would be echoed in the homepage.php. I thought maybe, I won’t need to get the script to While->Loop again on the homepage.php. Am I wrong in any of these assumptions ?
Actually, if I’m not gonna echo the Username & Password in the Login.php then no good While->Looping there. Best to do it in the homepage.php where the Username & Password would be echoed.

One last comment, I got all these codes from youtube tuts. Mixed and matched from a few vids. One tut would show you how to build a reg & login page. Another would teach you how to remember logins with cookies. Another would show you how to multiple delete mysql data and so on. I just PAUSE video->Play video and type copy the codes and put them all in one basket to suit my puspose.

I am happy. I getting a lot of response and learning from this community. :wink:

15

It’s the combination of

$sql = "SELECT * FROM users WHERE usernames='".$member_login_username_or_email."' OR emails='".$member_login_username_or_email."' AND passwords='".$member_login_password."'";

and then

if  ($member_login_username_or_email == $db_username && $member_login_password == $db_password || $member_login_username_or_email == $db_email && $member_login_password == $db_password)

What I mean is, because the query only retrieves records where either the login name is the username or the email address, and the password matches, then the if() will always be true. So you’re really just checking in your code to see whether MySQL has applied the conditions correctly. It has nothing to do with whether you’re echoing the username and password. All you need to do is:

// run the query
// are there any results?
// no - invalid login name or password, display error, ask for details again
// yes - set session var or cookie, display whatever you display for valid users.

I’m still not sure I can see why you’d ever get two rows back from your query. But my point was that you only deal with the first row anyway. On reading it a second time, that’s not actually true - you loop through the result set, set some cookies if needed, then do a header redirect. However you don’t follow that with an exit(), so it just carries on around the loop. But, can you post a pair of records, along with the user inputs, that would cause two records to be returned? If the query only finds one user where the password matches and the other string matches either the username or the email address, it’ll still only return one row. Run the query in phpmyadmin and see what you get.

1 Like
16

Looking for 1 user. His username match and his password match. That makes 2 of his inputs matches I’m looking for. All the youtube tuts showed to code like this. Are you saying, my code will check the Username column and Password column and if it finds any matches atall regardless of how many users then the code will log the user in ? I tested like that but it didn’t log me in and so I thought my code was safe. Eg. I inputted 2 users like so in mysql “users” tbl:

1st_user_username
1st_user_password

2nd_user_username
2nd_user_password

I tried logging in using the 1st_user_username and 2nd_user_password but it didn’t log me in. Hence, I thought my code was correct. WHat is your conclusion now to all this ?

17

They sound like weird tutorials to me. Usually you have something along the lines (pseudo, not real SQL) SELECT * from users WHERE username=“fred” and encrypted_password=“xxx” - and you get 1 user.

18

Folks,

Have you seen on gmail or yahoomail or something you can either enter your username or email and then the password and it would log you in ? Trying to build like that so user gets a choice to either use his username or email to login. Script should log user in aslong as “either the username or email” is a match in the username column and the password is a match too that is relevant to the username/email.
So, db is like this:

Username|Email|Pass

If the username is a match on row position 5 then the password should be a match on row position 5 too.
Or,
If the email is a match on row position 5 then the password should be a match on row position 5 too.

I think you understand. Pretty basic, really. Nothing complicated.

19

I do not understand what you mean.

20

Yes, but the result is still only a single row being returned from the database. The only way I could think of you getting two records back would be if your registration process allows people to create usernames that are email addresses (or perhaps enter email addresses that aren’t proper email addresses), and then don’t check to see that entry is unique in both username and email address columns. So the following two users would give you a problem

id  username        email               password
1   fred@test.com   fmoore@gml.local    mypass
2   fred            fred@test.com       mypass

as would these two

id  username        email               password
1   mrmoore         fred                mypass
2   fred            fred@test.com       mypass

So if someone tries to login with “fred@test.com” and “mypass”, or in the second table if they login as “fred” and “mypass”, you will get both of these rows back and won’t know which is the correct account. But, only because you allowed someone to register a username that confuses matters. If you’re going to allow an either/or login like this, you need to check during registration whether it will cause you a problem later. If you are already validating the username to make sure it’s not also present in the email address column, I can’t see how more than one row will match your selections.

2 Likes