PHP MYSQLI number of rows doesnt work no errors

Question

I have a login form when user clicks login checklogin.php is called and it should check username and password matches any record on database if true do something else print wrong password or username

So far i get wrong password username even though it is correct username&&password. I have made somechanges but now no echo, printf or error

how can i fix this issue?

form

<table width="300" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<form name="form1" method="post" action="checklogin.php">
<td>
<table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td colspan="3"><strong>Member Login </strong></td>
</tr>
<tr>
<td width="78">Username</td>
<td width="6">:</td>
<td width="294"><input name="myusername" type="text" id="myusername"></td>
</tr>
<tr>
<td>Password</td>
<td>:</td>
<td><input name="mypassword" type="text" id="mypassword"></td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td><input type="submit" name="Submit" value="Login"></td>
</tr>
</table>
</td>
</form>
</tr>
</table>

checklogin.php

<?php

    $mysqli = new mysqli('localhost', 'root', 'password', 'aiesec');

    /* check connection */
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }


    // username and password sent from form
    $myusername=$_POST['myusername'];
    $mypassword=$_POST['mypassword'];

    // To protect MySQL injection (more detail about MySQL injection)
    $myusername = stripslashes($myusername);
    $mypassword = stripslashes($mypassword);
    $myusername = mysqli_real_escape_string($myusername);
    $mypassword = mysqli_real_escape_string($mypassword);



    // If result matched $myusername and $mypassword, table row must be 1 row


    $sql = "SELECT * FROM members WHERE username='$myusername' and password='$mypassword"; 
    if($result = mysqli->query($sql, MYSQLI_USE_RESULT)) 
    { 
        printf("Errormessage: %s\n", $mysqli->error);
        echo $result->num_rows; //zero 
        while($row = $result->fetch_row()) 
        { 
            printf("Errormessage: %s\n", $mysqli->error);
            echo $result->num_rows; //incrementing by one each time 
        } 
        echo $result->num_rows; // Finally the total count 
    }



    if($row==1){

        echo "correct username and pass";
        // Register $myusername, $mypassword and redirect to file "login_success.php"
       // session_register("myusername");
        //session_register("mypassword");
        //header("location:login_success.php");
    }
    else {
        echo "Wrong Username or Password";
    }


           mysqli_close();     

    ?>

I have also tried

$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";
$result=mysqli_query($sql);

// Mysql_num_row is counting table row
$count=mysqli_num_rows($result);

Answer 1

You are missing a single-quote after the $mypassword on your $sql variable.

This line:

$sql = "SELECT * FROM members WHERE username='$myusername' and password='$mypassword";

Update it to:

$sql = "SELECT * FROM members WHERE username='$myusername' and password='$mypassword'";

Answer 2

First error, you did not close the quote after $mypassword:

$sql = "SELECT * FROM members WHERE username='$myusername' and password='$mypassword"; 

Then, if this if does not get entered, $row will not be defined and will cause trouble later on: you should add at least "$row = 0"

$row = 0;

if($result = mysqli->query($sql, MYSQLI_USE_RESULT)) 
{ 
    printf("Errormessage: %s\n", $mysqli->error);
    echo $result->num_rows; //zero 
    while($row = $result->fetch_row()) 
    { 
        printf("Errormessage: %s\n", $mysqli->error);
        echo $result->num_rows; //incrementing by one each time 
    } 
    echo $result->num_rows; // Finally the total count 
}

Finally, are you sure that $row is 1 here? Did you try

echo "Row is: ";

var_dump($row);

if($row==1){

Edit: $row seems to actually be the last row; you would want $result->num_rows instead.

From an architectural point of view, storing the passwords in the DB like this isn't a really good idea, it would be best to (at least) store a 'salted hash' or use a better algorithm, see:

How do you use bcrypt for hashing passwords in PHP?

Answer 3

To be sure you see all PHP errors, add this code on top of your script:

error_reporting(E_ALL);
ini_set('display_errors', 1);

You must correct your calls to mysqli_real_escape_string. According to the documentation, there must be two parameters, and the first parameter must be a MySQL link. In your case that link would be $mysqli.

Also, replace:

if($row==1){

with:

if($result->num_row==1){

You are misunderstanding what $result->num_rows is: it contains the TOTAL number of rows returned by the query whose result is stored in $result. So, it is useless to check the value of $result->num_rows inside the loop where you retrieve all records returned by the query.

I removed the constant MYSQLI_USE_RESULT from your query() because the documentation for mysqli_query says:
If you use MYSQLI_USE_RESULT all subsequent calls will return error Commands out of sync unless you call mysqli_free_result().

New code:

<?php
    $mysqli = new mysqli('localhost', 'root', 'password', 'aiesec');

    /* check connection */
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }

    // cleanup POST variables
    $myusername = mysqli_real_escape_string($mysqli, stripslashes(trim($_POST['myusername'])));
    $mypassword = mysqli_real_escape_string($mysqli, stripslashes(trim($_POST['mypassword'])));

    // If result matched $myusername and $mypassword, table row must be 1 row
    $sql = "SELECT * FROM members WHERE username='$myusername' and password='$mypassword'"; 
    $result = mysqli->query($sql);
     if($mysqli->errno<>0)
        die("Errormessage: %s\n", $mysqli->error);
    echo $result->num_rows;
    if($result->num_rows==1){
        echo "correct username and pass";
        // Register $myusername, $mypassword and redirect to file "login_success.php"
       // session_register("myusername");
        //session_register("mypassword");
        //header("location:login_success.php");
    }
    else {
        echo "Wrong Username or Password";
    }
    mysqli_close();     
?>