MySQL搜索没有返回LIKE'complete_string'的结果

I have a chemical database where users can search for items. If I search for a test item such as "heroin" (which exists as a test row in my database), I get no results returned, however if the search is for "her", then the appropriate results are generated.

When I run the SQL script in the database (Navicat mysql), then I get the correct result, so I believe the problem is with my PHP code. (Yes, I understand that I should upgrade to PDO structures, but that doesn't solve the current problem.)

My PHP script has a search field returning $item.

When $item is only a partial match, I get expected results, however, when $item is the same as the entire string, there are no matches returned.

$item = $_POST['item'];
$log_data = 'Searched for ' . $item;
$user_name = $user_data['username'];
user_log($user_name, $log_data);
$item=trim($item);
if($item !== ""){
    $count = 0;
    $chem = mysql_query("SELECT *
        FROM Chemicals
        WHERE Chemicals.Name_Chem1 LIKE '%{$item}%'
        ORDER BY Chemicals.Name_Chem1 ASC");
    while ($row = mysql_fetch_array($chem)) {
        echo "<table border='0'>
            <tr class='content'>
                <th>CAS</th>
                <th>Name</th>
                <th>IUPAC Name</th>
                <th>Common Name</th>
                <th>German Chemical</th>
                <th>German Common</th>
            </tr>";
        while ($row = mysql_fetch_array($chem)) {
            $count ++;
            echo "<tr>";
            echo "<td class='content'>" . $row['CAS'] . "</td>";
            echo "<td class='content'>" . "<a href='item_result.php?CAS="
                . $row['CAS'] . "'>" . $row['Name_Chem1'] . "</a>" . "</td>";
            echo "<td class='content'>" . $row['Name_IUPAC'] . "</td>";
            echo "<td class='content'>" . $row['Name_Common'] . "</td>";
            echo "<td class='content'>" . $row['Name_German_Chemical'] . "</td>";
            echo "<td class='content'>" . $row['Name_German_Common'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
    echo  "There are ", $count, "results.";
}

The query works fine in MySQL, but not in the PHP script.

Here is the problem. Your fetch code is structured like this:

while ($row = mysql_fetch_array($chem)) {
    while ($row = mysql_fetch_array($chem)) {
        // Do something
    }
}

Each time you call the fetch function, it "consumes" a row and moves the internal result pointer by one. Thus, when you do a search for which you expect exactly one result, by the time the inner loop is run, you have already consumed the row in the outer loop.

The outer loop should be replaced with an if() that checks the result count instead.