-3

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
Cam Lumsden
  • 11
  • 1
  • just for argument's sake; you've a missing semi-colon here `$item = $_POST['item']`. plus why not do it all in one go? `$item = trim($_POST['item']);` – Funk Forty Niner Jul 27 '15 at 16:46
  • How are you fetching it, maybe you are skipping the first result which is the exact match? Also, look into switching drivers and sanitizing input, http://php.net/manual/en/function.mysql-real-escape-string.php (prepared statements are better but `mysql_` has no support.. – chris85 Jul 27 '15 at 16:48
  • 2
    too many unknowns here – Funk Forty Niner Jul 27 '15 at 16:48
  • 1
    Please stop using `mysql_query` - it has been deprecated in php 5.5. More info here: http://stackoverflow.com/a/12860046/309163 – dhh Jul 27 '15 at 16:51
  • try : LIKE '%" . $item . "%' .. hate embedding vars like that (personal preference) – FreudianSlip Jul 27 '15 at 16:53
  • If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Jul 27 '15 at 16:58
  • Thanks for the question updates, this looks good now! I'll vote to reopen (two more votes to go, give it a little while). I've edited the question slightly to remove the notes about providing the full code, etc - it's best for questions to just appear as if they were written like that to start with. Stack Overflow has a great versioning system, so people interested in the history of the question can just look at that. – halfer Jul 28 '15 at 19:28
  • I notice you're not checking the return value from your database calls - has one of them failed? Have you successfully got a connection in code not shown here? See the manual for `mysql_query()` and `mysql_fetch_array()` for what the return values mean, and how you can detect an error condition. – halfer Jul 28 '15 at 19:30

1 Answers1

0

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.

halfer
  • 19,824
  • 17
  • 99
  • 186