[RESOLVED] Insert/Replace Question

[twitch]

Well, my friends I have an almost working script on replacing values in a database from a text file.

<?php 
// Make a MySQL Connection
mysql_connect("connection", "username", "pword") or die(mysql_error());
mysql_select_db("dbname") or die(mysql_error());

$nextline = ''; 
$fp = fopen('test.txt','r'); 
while (!feof($fp)) { 
 $mystuff = array(); 
 $nextline = fgets($fp); 
 $mystuff = split(',',$nextline); 
 $casenumber = mysql_real_escape_string(trim($mystuff[0])); 
 $casename = mysql_real_escape_string(trim($mystuff[1])); 
 mysql_query("REPLACE INTO case_numbers 
(casenumber, casename) VALUES('$casenumber', '$casename' ) ") 
or die(mysql_error());  

} 
echo '<p>done!'; 
?>

The text file will be updated with new case numbers and names. When I added a new entry to the bottom of the text file and ran the above script, the new entry was added to the database. However, if I delete the last entry, the above script does not delete the entry from the database. There are only two fields in the table, casenumber and casename with casenumber set to unique. I'm sure it's just a little tweak to the query to see if one is missing and if so delete it. Any help would be much appreciated. It's taken me three days to get this far. Learned a lot doing this script...ha ha

Thanks in advance.
Twitch

[djjjozsi]

INSERT INTO case_numbers  (casenumber,casename) VALUES ('$casenumber','$casename')
  ON DUPLICATE KEY UPDATE casenumber=VALUES(casenumber),casename=VALUES(casename);

casenumber field if really unique, this will update the casename's value if its already in the table.

Lets read a little about this strusture here: ON DUPLICATE UPDATE

[bradgrafelman]

twitch wrote:

I'm sure it's just a little tweak to the query to see if one is missing and if so delete it.

Eh... not really.

Only way I can think about doing this would be to read all of the data in from the file (using [man]fgetcsv/man, perhaps?) into an array where the casenumber (assuming this is a unique entity) is used for the key and casename for the value.

Then, you'd run one SQL query to remove any casenumber values not found in your array (e.g. using the IN (...) syntax). Afterwards, you could do a single INSERT ... ON DUPLICATE KEY UPDATE query (to reduce overhead of multiple queries) to update the data.

Then again, it sounds like you might as well just clear out the entire table and re-insert the data according to the text file... which brings me to ask: Why even have a MySQL database in the first place if you're not going to allow it to manage your data? Why can't you run a DELETE query on the DB instead of keeping a separate text file and trying to constantly monitor it for possible DB changes?

[twitch]

Can't tell you how much I appreciate the quick reply guys. I'm going to look into your suggestions when I get home and let you know how it turns out.

As for why the text file, it's a file that is automatically ftp'd from another company to a folder on our company's server that is used for various things. I suggested simply linking to the other company's database, but the "powers that be" said, "We're already getting the text file from them so just do it that way."

I was able to simply read the text file, but it seemed to be much slower than reading it from the database.

Thanks again, really appreciate. I'll post a follow up for the benefit of anyone else looking for something similar.

Twitch

[twitch]

Thanks for the info on ON DUPLICATE KEY UPDATE, djjjozsi. I'm going to read up on that more.

The code I posted above works great for adding lines to the file and inserting them into the database or even changing the case name it updates in the table, but the delete is the issue. I may end up going with bradgrafelman's suggestion of just dropping and re-creating the table based on the text file. May be the simplest (even if it isn't the best) option cause my eyes are starting to cross...ha ha

Thanks again guys. I'll update when/if I get something working.

[bradgrafelman]

Which method you go with probably depends on the number of entries we're talking about here.

Truncating the table and re-inserting each row in the text file may or may not be better than having PHP process the file into an array, execute a 'DELETE .. WHERE casenumber NOT IN (...)' query, then UPDATE based on what's in the text file. If you really wanted to be nifty about it, you could keep a copy of the previous file, use [man]array_diff/man to see which casenumber's where changed, and only UPDATE those rows.

It may sound complicated, but it isn't that bad. :p

[twitch]

This code does the job. May not be the most efficient way, but the end result is the desired result.

<?php 
// Make a MySQL Connection
mysql_connect("connection", "username", "pword") or die(mysql_error());
mysql_select_db("databasename") or die(mysql_error());
mysql_query("DELETE from case_numbers");
$nextline = ''; 
$fp = fopen('test.txt','r'); 
while (!feof($fp)) { 
 $mystuff = array(); 
 $nextline = fgets($fp); 
 $mystuff = split(',',$nextline); 
 $casenumber = mysql_real_escape_string(trim($mystuff[0])); 
 $casename = mysql_real_escape_string(trim($mystuff[1])); 
 mysql_query("INSERT INTO case_numbers 
(casenumber, casename) VALUES('$casenumber', '$casename' ) ") 
or die(mysql_error());  

} 
echo '<p>done!'; 
?>

[djjjozsi]

its easier hen you think. You don't need to truncate the table, just use a status field in your table(s).

When you start the process, set the already_in field to 0 in your whole records.

ON DUPLICATE UPDATE can set your already_in field=1 for fields which is in the flat file.

Now you have an updated table with records where this already_in field is 0. You can delete them. i'm using this all day. And you don't need to hardcode a search algorithm on each rows from your file.
Truncate has a property where all the auto increment values deleted(reset back to 1)! And this is what you don't really want for a relation database.

[twitch]

The text file is about 49000 lines long so far. The script appears to take about 2 to 3 seconds to complete. Maybe you guys methods are the way to go. Especially since it will only grow. My way does feel like a "cheat" ha ha.

djjjozsi, I create a column in my case_numbers table that is an "already_in" field with values of 0 or 1? Correct? What does my code then look like. I didn't have much luck implementing the ON DUPLICATE KEY UPDATE code you suggested earlier. Nothing happened. I even read the link you posted. What changes to my above code do I make?

Once again guys, you have been amazingly helpful. Once we hash this out, it will probably be useful to many people. I can't imagine I'm the only one who has ever needed to do this...ha ha.

[djjjozsi]

You start with this:
UPDATE case_numbers SET already_in=0;

You read the files with the cycle you have.

INSERT INTO case_numbers  (casenumber , casename , already_in) VALUES ('$casenumber','$casename' , 1 )
  ON DUPLICATE KEY UPDATE casenumber=VALUES(casenumber),casename=VALUES(casename),already_in=1;

Now you have a couple of records where already_in is 0. These rows are missing from the flat file. Is it what you need ?

What is the reason if this isn't working. You forget to set a UNIQUE KEY for your casenumber field.

[twitch]

Ah I think I see what you are doing. That is probably exactly what I'm looking for. As far as I know my case numbers column is set to unique. I'll double check and try this out a little later and let you know how it turned out. Right now I have a 2 year old tugging on me and I can't concentrate....ha ha

Thanks, I'll be in touch.

[djjjozsi]

Its ok! But its suggested to make backup before use batch updates like this.

[twitch]

I appear to be getting a syntax error. I believe it's in here somewhere:

mysql_query("INSERT INTO case_numbers 
(casenumber, casename, already_in) VALUES('$casenumber', '$casename', 1 ON DUPLICATE KEY UPDATE casenumber=VALUES(casenumber),casename=VALUES(casename),already_in=1 ) ")
or die(mysql_error());

[djjjozsi]

'only' the SQL has a syntax error

mysql_query("INSERT INTO case_numbers
(casenumber, casename, already_in) VALUES('$casenumber', '$casename', 1)
 ON DUPLICATE KEY UPDATE casenumber=VALUES(casenumber),casename=VALUES(casename),already_in=1 ")
or die(mysql_error()); 

[twitch]

Once again, you nailed it. That was it. It works like a champ if the added or deleted entry is the last one. If I delete an entry from somewhere other than the end, it doesn't change the database.

I'm going to step back for a little while and come back to it, maybe I can figure it out on my own.

I'll post results or failures...ha ha We're close!

[bradgrafelman]

djjjozsi;10940405 wrote:

Truncate has a property where all the auto increment values deleted(reset back to 1)! And this is what you don't really want for a relation database.

Well yes, but since 'casenumber' is the unique id field here, and since the casenumber is always supplied by the text file, this doesn't really matter. That's why I suggested truncating the table since it would presumably be faster.

[djjjozsi]

bradgrafelman, you're right.

[twitch]

You guys are awesome. I saw bradgrafelman's last post about truncate and decided to try it just now since the database was only updating if the last line in the text file changed with ON DUPLICATE KEY, and TRUNCATE TABLE seems to be working like a champ and faster than DELETE. Logically they seem to do the same thing (except the auto increment difference) but TRUNCATE appears anyway to be faster.

You guys rock. Thanks so much. Hopefully this post will help someone else trying to do the same thing.