PHP，MySQL - 简单搜索不需要的结果[重复]

Question

This question already has an answer here:

• check if row exists with mysql 3 answers

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.

</div>

Answer 1

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