PHP sql with foreach loop variable problem

Question

This is really getting frustrating. I have a text file that I'm reading for a list of part numbers that goes into an array. I'm using the following foreach function to search a database for matching numbers.

$file = file('parts_array.txt');

foreach ($file as $newPart)
{
    $sql = "SELECT products_sku FROM products WHERE products_sku='" . $newPart . "'";
    $rs = mysql_query($sql);
    $num_rows = mysql_num_rows($rs);

    echo $num_rows;
    echo "<br />";
}

The problem is I'm getting 0 rows returned from mysql_num_rows. I can type the sql statement without the variable and it works perfectly. I can even echo out the sql statement from this script, copy and paste the statement from the browser and it works. But, for some reason I'm not getting any records when I'm using the variable. I've used variables in sql statements tons of times, but this really has me stumped.

Answer 1

1. Try trimming and mysql_real_escape_string on your variable.
2. Check the source code of what is being echoed out and try to copy and paste that into PHPMyAdmin or something similar.

Answer 2

file includes newlines in the array elements. This may explain why it works when you copy the browser output but not in the script. You can try either:

$file = file('parts_array.txt', FILE_IGNORE_NEW_LINES);

or:

$sql = "SELECT products_sku FROM products WHERE products_sku='" . trim($newPart) . "'";

Note: Even though you're importing from a file of your own making, you can never be 100% sure that inject-able data hasn't been inserted into it. You should make sure to properly escape any data with mysql_real_escape_string. Even better would be using PDO prepared statements instead.

Answer 3

Obviously your code does something different than you expect. Running a successful query, for one: you don't check the return value of the mysql_query call, so you cannot be sure the query executed ok.

My idea:

1. dump your sql statement from the foreach
2. check the return code of the mysql_query

Answer 4

What does your parts_array.txt file look like? Do SKU numbers contain the ' character?

Can you please try this:

$file = file('parts_array.txt');

foreach ($file as $line_num => $line)
{
    $sql = "SELECT products_sku FROM products WHERE products_sku='$line'";
    echo $sql;
    $rs = mysql_query($sql);
    $num_rows = mysql_num_rows($rs);

    echo $num_rows;
    echo "<br />";
}

Answer 5

You might want to check for a mysql_error. It sounds like you've already verified the variable and have copied the query into a database interface like PHPMyAdmin or Query Browser, but if you haven't, I would recommend that. After, verify that a very basic query will work, like "SELECT * FROM Products". That will tell you if there is a problem outside of the query.

Overall, I would say the strategy would be to break the problem down into possible problem areas, like database, connection, query, errors, etc. Try to eliminate them one at a time until the problem is apparent. In other words, list the possibilities and cross them off one at a time.

I've encountered problems like this before; the trick is usually to start echoing things until you see the problem, and don't work off of assumptions.