PHP, MySQL - Simple search with unwanted result

Question

I have a basic search option on my local site which uses unique 8 digit user number for each user in the MySQL and it works (eg: if the user number is matched to user it will display all of the records associated with that user number) its just I get unwanted result when the search is concluded, and the unwanted result is that if for example I do a search for a user with the following no 12345678 and user exits it will show the result but lets say I do a search for 12345677 and user does not exist I will get the message User fount but no data displayed.

Search form code:

<form action="search.php" method="GET">
   <input type="text" name="query" placeholder="Student Number" autofocus/>
   <input type="submit" value="Search" />
</form>

search.php

include_once("config.php");
$query = $_GET['query']; 
$result = mysql_query("SELECT * FROM loan WHERE userno=$query");
 if(empty($result)) {

 //  } else{

echo "<center><table border='0' id='3'>";
echo "<tr id='2'>";
echo "<td><center>System Message</center></td></tr>";
echo "<tr id='loan4'><br />";
echo "<td><center><h1>No Record Found</h1>";
echo "<br/><a href='index.php' class='button button1 link'>Go back</a>";
echo "<br/>OR</a>";
echo "<br/><a href='add_new_loan.php' class='button button1 link'>Add New Loan</a>";
echo "</center></td>";
echo "</tr>";
echo "</table></center>";}

  else{  
?>
    <table width='95%' border='0' id='loan1'>

    <tr id='loan2'>
        <td width="120">User No.</td>
        <td width="130">Full Name</td>
        <td width="90">Amount</td>
        <td width="100">aken</td>
        <td width="100">Due</td>
        <td width="248">Notes</td>
        <td width="120" align="center">Options</td>

    </tr>


    <?php 

    while($res = mysql_fetch_array($result)) {  

        echo "<tr id='4'>";
        echo "<td>".$res['userno']."</td>";
        echo "<td><a href=\"receipt.php?id=$res[id]\" target=\"_blank\">".$res['name']."&nbsp;".$res['surname']."</a></td>";
        echo "<td>".$res['loana']."</td>";
        echo "<td>".$res['datet']."</td>";
        echo "<td>".$res['dated']."</td>";
        echo "<td>".$res['notes']."</td>";  
        echo "</tr>";   
    }

     echo "<center><table border='0' id='loan3'>";
echo "<tr id='loan2'>";
echo "<td><center>System Message</center></td></tr>";
echo "<tr id='4'><br />";
echo "<td><center><h1>Record Found</h1>";
echo "<br/><a href='index.php' class='button button1 link'>Go back</a>";

Also I have tried !empty but no joy, Any help is greatly appreciated.

Answer 1

The $result might be not completely empty.

Try to use mysql_num_rows().

Example:

    if (mysql_num_rows($result)==0) { 
       //PERFORM ACTION
    }

Answer 2

Fix your current code by using mysql_num_rows() like below:-

<?php
include_once("config.php");
$query = $_GET['query']; 
$result = mysql_query("SELECT * FROM loan WHERE userno=$query");
 if(mysql_num_rows($result) >0) {
?>
    <table width='95%' border='0' id='loan1'>

    <tr id='loan2'>
        <td width="120">User No.</td>
        <td width="130">Full Name</td>
        <td width="90">Amount</td>
        <td width="100">aken</td>
        <td width="100">Due</td>
        <td width="248">Notes</td>
        <td width="120" align="center">Options</td>

    </tr>


    <?php 

    while($res = mysql_fetch_array($result)) {  

        echo "<tr id='4'>";
        echo "<td>".$res['userno']."</td>";
        echo "<td><a href=\"receipt.php?id=$res[id]\" target=\"_blank\">".$res['name']."&nbsp;".$res['surname']."</a></td>";
        echo "<td>".$res['loana']."</td>";
        echo "<td>".$res['datet']."</td>";
        echo "<td>".$res['dated']."</td>";
        echo "<td>".$res['notes']."</td>";  
        echo "</tr>";   
    }
}else{
        echo "<center><table border='0' id='3'>";
        echo "<tr id='2'>";
        echo "<td><center>System Message</center></td></tr>";
        echo "<tr id='loan4'><br />";
        echo "<td><center><h1>No Record Found</h1>";
        echo "<br/><a href='index.php' class='button button1 link'>Go back</a>";
        echo "<br/>OR</a>";
        echo "<br/><a href='add_new_loan.php' class='button button1 link'>Add New Loan</a>";
        echo "</center></td>";
        echo "</tr>";
        echo "</table></center>";
}?>

Note:-

Stop using deprecated+removed mysql_* library. Use mysqli_* or PDO along with prepared statements to prevent from SQL INJECTION.

REFREENCE:-

mysqli_* with prepared statements

PDO with prepared statements

Answer 3

First thing you need to stop using the mysql_* functions, they are depreciated and completely removed as of PHP 7 user mysqli_* or PDO with prepared statements.

See the links below :

Why shouldn't I use mysql_* functions in PHP?

Then make use of prepared statements to prevent sql injections.

How can I prevent SQL injection in PHP?

Follow the link above to use prepared statements.

this is how your code should look :

config.php

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
?>

search.php

<?php


include_once("config.php");
$query = $_GET['query']; 

$sql = "SELECT * FROM loan WHERE userno= $query "; // use prepared statements from the link I provided above

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    //Records found print the table
    //OUTPUT THE TABLE AND HEADINGS HERE BEFORE THE LOOP
    while($row = $result->fetch_assoc()) {
        //fetch and output each data of row to your table td's

    }


}else{

//user data not found print message

}