num row isnt detecting row in table

Question

I'am currently making a login system for news website as part of my coursework. For some reason when I use $rows->num_rows == 1 in an if statement, it always runs the "else" code. Basically this means that it doesnt detect a row in my table that corresponds with the correct user information being inputed... Here is the PHP code that is ran when any information is input into the html form.

<?php
error_reporting(E_ALL);  
ini_set('display_errors', 1); 

//Connect to DB
    include_once("db_connect.php")or die ("Couldnt connect to DB");
$username = $_POST['user'];
$password = $_POST['password'];

session_start();

if(trim($username) != '' and trim($password) != ''){

//Sanitizes whatever is entered 
    $username=stripslashes($username);
    $password=stripslashes($password);

    $username=strip_tags($_POST['user']);
    $password=strip_tags($_POST['password']);

    $username=mysqli_real_escape_string($conn,$username);
    $password=mysqli_real_escape_string($conn,$password);

//Checks whether Username exists        
 $query = mysqli_query($conn, "SELECT * FROM user WHERE users='$username'  
 AND password = '$password' ")
 or die(mysqli_error($conn));

$numrows=mysqli_num_rows($query);

if($numrows > 0){

// echo "Record exists.";

$_SESSION['login_user']=$username; // Initializing Session

header("location: index.php"); // Redirecting To Other Page
exit;
}   

else {
    echo "Username or password is incorrect.";
}
}else{  
    echo "Please enter information";
}
?>

The problem occurs at the last if statement as it never detects a row. And yes, my table is populated with 1 row of user information (user,password) and my HTML form also uses POST.

I have researched this issue for at least 3 hours and still cant find a resolution.

Here are the current error logs:

Warning: include_once(1): failed to open stream: No such file or directory   in /home/vol9_7/byethost4.com/b4_18083024/htdocs/loginAuth.php on line 6

Warning: include_once(): Failed opening '1' for inclusion  
(include_path='.:/usr/share/pear/') in     
/home/vol9_7/byethost4.com/b4_18083024/htdocs/loginAuth.php on line 6

Notice: Undefined variable: conn in     
/home/vol9_7/byethost4.com/b4_18083024/htdocs/loginAuth.php on line 22

Warning: mysqli_real_escape_string() expects parameter 1 to be mysqli, null    
given in /home/vol9_7/byethost4.com/b4_18083024/htdocs/loginAuth.php on line   
22

Notice: Undefined variable: conn in  
/home/vol9_7/byethost4.com/b4_18083024/htdocs/loginAuth.php on line 23

Warning: mysqli_real_escape_string() expects parameter 1 to be mysqli, null   
given in /home/vol9_7/byethost4.com/b4_18083024/htdocs/loginAuth.php on line 
23

Notice: Undefined variable: conn in  
/home/vol9_7/byethost4.com/b4_18083024/htdocs/loginAuth.php on line 42

Warning: mysqli_query() expects parameter 1 to be mysqli, null given in     
/home/vol9_7/byethost4.com/b4_18083024/htdocs/loginAuth.php on line 42

Notice: Undefined variable: conn in 
/home/vol9_7/byethost4.com/b4_18083024/htdocs/loginAuth.php on line 43

Warning: mysqli_error() expects parameter 1 to be mysqli, null given in 
/home/vol9_7/byethost4.com/b4_18083024/htdocs/loginAuth.php on line 43

EDIT: Using Fred -ii- answer. include_once("db_connect.php")or die ("Couldnt connect to DB"); has now been moved to the top of the code.

Secondly, a new if statement has been added to replace the older version. This statement can also be found in Fred -ii- answer.

Thirdly, SQL statement has been fixed since I was mixing up the table and column name.

Lastly, error_reporting(E_ALL); ini_set('display_errors', 1); has been added to help find errors, again courtesy of Fred -ii- answer.

Answer 1

The below answer was posted as per your original post https://stackoverflow.com/revisions/37284594/1 and without marking it as an edit and moved the include at the top of your code twice, and without marking them as additional edits.

---

You're putting the carriage before the horse here

include_once("db_connect.php")or die ("Couldnt connect to DB");

it needs to be placed before you call any function that requires a db connection, being mysqli_real_escape_string().

You should also use if ($rows->num_rows >= 1) or if ($rows->num_rows > 0) should there be more than one person bearing the same username later on as your database grows; it can happen. As a matter of fact, I was testing something to that effect yesterday.

Plus, use exit; after each header, otherwise your code may want to continue to execute.

You should also check for errors against your query; you're not doing that.

If that still doesn't work, then some of the functions you're using against the POST arrays could have adverse effects and may be getting rid of valid characters. You may need to remove them.

Using a prepared statement will do away from all of those.

• http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

Error checking (your query failed).

Consult these following links http://php.net/manual/en/mysqli.error.php and http://php.net/manual/en/function.error-reporting.php and apply that to your code.

---

Passwords

I also noticed that you may be storing passwords in plain text. This is not recommended.

Use one of the following:

• PHP 5.5's password_hash() function.
• Compatibility pack (if PHP < 5.5) https://github.com/ircmaxell/password_compat/

Important sidenote about column length:

If and when you do decide to use password_hash() or the compatibility pack (if PHP < 5.5) https://github.com/ircmaxell/password_compat/, it is important to note that if your present password column's length is anything lower than 60, it will need to be changed to that (or higher). The manual suggests a length of 255.

You will need to ALTER your column's length and start over with a new hash in order for it to take effect. Otherwise, MySQL will fail silently.

Other links of interest:

• CRYPT_BLOWFISH
• crypt()
• bcrypt()
• scrypt()
• On OPENWALL
• PBKDF2
• PBKDF2 on PHP.net
• PBKDF2 For PHP

---

Edit:

Change this block: (your method may be failing for $rows->num_rows)

$query="SELECT * FROM user WHERE users='$username' AND password = '$password'";
$rows = mysqli_query($conn, $query);

if ($rows->num_rows == 1){

    $_SESSION['login_user']=$username; // Initializing Session

    header("location: index.php"); // Redirecting To Other Page
}

to:

$query = mysqli_query($conn, "SELECT * FROM user WHERE users='$username' AND password = '$password' ")
or die(mysqli_error($conn))
;

$numrows=mysqli_num_rows($query);

if($numrows > 0){

// echo "Record exists.";

    $_SESSION['login_user']=$username; // Initializing Session

    header("location: index.php"); // Redirecting To Other Page
    exit;
}

and place this at the top of your file:

<?php 
error_reporting(E_ALL);  
ini_set('display_errors', 1); 

// rest of your code

---

NOTA:

I'm questioning this though SELECT * FROM user WHERE users

Make sure you chose the right table and that you didn't inverse those by chance.

Answer 2

I suggest you a different code

use this:

if ($result = mysqli_fetch_array($rows,MYSQLI_NUM)){

instead of this:

if ($rows->num_rows == 1){