simple else with while in loop not working

Question

My code is working as for my needs. But the only thing bugging me is the "else" is not working. When i search for a correct record the record will appear and it was running fine. But if i Incorrectly search a record nothing will happen. i am expecting "Records not Found" will echo but nothing happen.

}else{
       echo "Records not found";
    }

This is the whole code.

      <?php
$conn = mysqli_connect("localhost", "root", "", "my1stdb") or die("could not connect");

$set = $_POST['search'];


if ($set) {
    $show   = "SELECT * FROM users where email='$set'";
    $result = mysqli_query($conn, $show);
    while ($rows = mysqli_fetch_array($result)) {
        echo "Registrant Found";
        echo "<tr>";
        echo "<td>";
        echo $rows['username'];
        echo "</td>";
        echo "<td>";
        echo $rows['fullname'];
        echo "</td>";
        echo "<td>";
        echo $rows['password'];
        echo "</td>";
        echo "<td>";
        echo $rows['email'];
        echo "</td>";
        echo "</tr>";
        echo "<br/>";
    }

} else {
    echo "Records not found";
}

?>
</table>

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Jan 12 '18 at 20:29
**Never store plain text passwords!** Please use ***PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html)*** to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). ***It is not necessary to [escape passwords](http://stackoverflow.com/q/36628418/1011527)*** or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. — Jay Blanchard, Jan 12 '18 at 20:29

Alive to die - Anant · Accepted Answer · 2018-01-12T20:38:26.387

You need to use mysqli_num_rows() along with mysqli_fetch_assoc():-

<?php
    $conn=mysqli_connect("localhost","root","","my1stdb") or die("could not connect");

    $set = $_POST['search']; 


    if($set) {
        $show="SELECT * FROM users where email='$set'";
        $result=mysqli_query($conn,$show) or die(mysqli_error($conn));
        if(mysqli_num_rows($result)>0){ // check data present or not
            while($rows=mysqli_fetch_assoc($result)){ // for lighter array due to associative indexes only
                echo "Registrant Found";
                echo "<tr>";
                echo "<td>";
                echo $rows['username'];
                echo "</td>";
                echo "<td>";
                echo $rows['fullname'];
                echo "</td>";
                echo "<td>";
                echo $rows['password'];
                echo "</td>";
                echo "<td>";
                echo $rows['email'];
                echo "</td>";
                echo "</tr>";
                echo "<br/>";
            }
        }else{
            echo "Records not found";
        }
    }else{
        echo "Please insert search term";
    }

?>
</table>

Note:- Your code is wide-open for SQL INJECTION. to prevent from it use prepared statements

Reference:-

mysqli prepared statements

PDO prepared statements

OMG THAT WAS AWESOME! It works! Thank you ! your code was the best. Cheers! — kelvin gallos, Jan 12 '18 at 20:28
What if there is an error in the query? No row number will be returned. — Jay Blanchard, Jan 12 '18 at 20:31
Turn the tide against teaching/propagating sloppy and dangerous coding practices. If you post an answer without prepared statements [you may want to consider this before posting](http://meta.stackoverflow.com/q/344703/). Additionally [a more valuable answer comes from showing the OP the right method](https://meta.stackoverflow.com/a/290789/1011527). — Jay Blanchard, Jan 12 '18 at 20:32
@kelvingallos please check my edited answer. I have added some note and instruction which is most important for you. Please check and try to convert code in that way. It will prevent your code from SQL INJECTION. — Alive to die - Anant, Jan 12 '18 at 20:40

score 0 · Answer 2 · answered Jan 12 '18 at 20:23

You could count the number of results returned.

if($set) {
    $show="SELECT * FROM users where email='$set'";
    $result=mysqli_query($conn,$show);

    $recordCount = 0;

    while($rows=mysqli_fetch_array($result)){
        $recordCount++;
            echo "Registrant Found";
            echo "<tr>";
            echo "<td>";
            echo $rows['username'];
            echo "</td>";
            echo "<td>";
            echo $rows['fullname'];
            echo "</td>";
            echo "<td>";
            echo $rows['password'];
            echo "</td>";
            echo "<td>";
            echo $rows['email'];
            echo "</td>";
            echo "</tr>";
            echo "<br/>";
    }
    if($recordCount==0){
        echo "Records not found";
    }

}

simple else with while in loop not working

2 Answers2