Matching data from a text file and database

bormuff98

I've been trying a few variations on this but don't seem to quite get the code to do exactly what I want. Essentially, I have a list of IP addresses in a text file. I also have a database and there is a field in one of the tables which stores IP addresses. Using the textfile I want to iterate through it checking for a match against the database and return a list of those IPs with "match" or "no match" next to it. Here's my code so far:

$file = "/usr/local/www/data/aup/nachi-03-12";

//Define the database name and connection here so that we only do it once
$db_name = "access";
include("/usr/local/www/inc/connect.inc");

//Open that file for reading
$handle = fopen($file, "r");

//While the file is open we need to read it line by line
while (!feof($handle)) {
    $string = trim(fgets($handle));

if ($string != "") {

    //Try matching the IP
    $select = mysql_query("
            SELECT *
            FROM hosts
            WHERE ipAddr='$string'
        ",$connection) or die(mysql_error());

    if (mysql_num_rows($select) != 0) {

        while ($row = mysql_fetch_array($select)) {

            echo $string."\tMatches<br/>\n";

        }

    } else {

        while ($row = mysql_fetch_array($select)) {

            echo $string."\tNot in DB<br/>\n";

        }

    } //end of mysql_num_rows()


} //end of 'if string is empty' query

} //end of 'while not at end of file' loop

fclose($handle);

I have a feeling I may be approaching this back to front.

AstroTeg

I'm not sure how large either the text file list or database list is. My thought would be to take the smallest of the two and put all the IPs found in the smaller list in an array.

If the text file is the smaller of the two, then I'd convert the array to a comma delimited list that is ready to be included in a SQL statement.

$IPlist = implode(',', $IParray);

Now you could do something like:

// get IP addresses in the list:
$sql = 'SELECT IPaddress FROM IPListTable WHERE IPaddress IN (' . $IPlist . ')';

// get IP addresses not in the list:
 $sql = 'SELECT IPaddress FROM IPListTable WHERE IPaddress NOT IN (' . $IPlist . ')';

If the file list is monster-size, then this may not work so well.

If the database list is the smallest, then load all the IP addresses you have in the database.

Read in the text file line by line and get the IP address. Test if the IP address from the text file is in the array from the database (implement your favorite array searching algorithm here).

I doubt this is the best way of doing it, but its one that comes to mind. If both lists are super-huge and your server doesn't have the RAM memory to store one of the lists in memory, then you may have to resort to some other tricks like reading in one IP address and then querying the database to see if it exists or not and then move on to the next IP in the file - rinse and repeat until you're at the end of the file. Here you just lose a lot of time since for every IP address you have to query the database.

Although, if performance is an issue, you might look into hashing the IP address. I understand databases love hashes...

Weedpacket

You could also consider putting both lists into separate arrays, and then using array_intersect to find all the IPs in both.

Then for the match/nomatch bit, go through each IP in the textfile array and use in_array() to see if it's in that intersection.

If you're not worried about keeping the IPs in the same order as they appear in the text file, you can use array_diff() to subtract the intersection from the textfile array; that will return a list of all the IPs in the text file but not in the database. Then just whack out the intersection as "MATCH" and the difference as "NO MATCH".

But as AstroTeg observes, if the lists are really big (on the order of several hundred thousand items) you could run into trouble that way.