Update From Data File?

Inque187

Ok, I created a script that reads information from a .dat file and then dumps it into the correct fields in my database. I would like to add to the .dat file and be able to run an "update" script that checks the exisiting data table and then adds the lines of data that do not already exist.

Here is part of my original script that has created the table and is supposed to add the data:

$datFile = "../xxxxxx/xxxxxx.dat";
$fh = fopen($datFile, "r") or die( "unable to open $datFile");

while(!feof($fh))
{
$line = fgets($fh,1024);
	if (preg_match('#^\[(.+)\s(.+)\]\s(.+)\s\"(.+)\"\s\"(.+)\"\s(.+)#', $line, $matches)) {
	list(, $date, $time, $guid, $nick, $ip, $viol) = $matches;

$sql = "INSERT INTO xxxxxx (date, time, guid, nick, ip, viol) VALUES ('$date', '$time', '$guid', '$nick', '$ip', '$viol')";
	mysql_query($sql) or die(mysql_error());
}
}

I would like to be able to modify this script to where it checks the exiting table for data that already is there so i do not get multiple inserst of the same data.

Any help will be appreciated!

Roger_Ramjet

You don't need a script. Let the database protect itself from duplicate data, that is always where you do it. You create a unique key on the table using multiple columns if neccessary and that solves your problem. If the only way to identify uniqueness is to use all columns in the index then that is what you do.

Once the index is in place you can run inserts without having to worry about presenting the same data multiple times.

This is basic database design stuff.

Inque187

Well, I tried running the script again and it just added all the lines again so there were 2 of every line of data. Now I checked my "create" script and I have the unique key set to the "id" field. Should that be changed?

EDIT: Ok, changed some things in the "create" script and set the "guid" field as the Unique key. Create script runs fine. But whne I add some more data to the .dat file and thrn run the "update" script - I get the message that there is a duplicate entry and then the script does nothing more.

EDIT AGAIN: NVM, I found the mySQL manual and found the "Ignore" keyword. Thanks for the help!

Roger_Ramjet

Glad to be of help. Remember, always try to ensure data integrity etc in the database itself. Otherwise you'll have to worry about it in every script you writeand end up duplicating loads of unneccessary code. Also, when processing multiple records, sql queries run about 10 times faster than doing the same thing in code, so try to replace code routines with sql queries whenever possible.