Insert/Replace help

[Twitch]

I have a text file with rows that look like below:

123245,Name of Case

263746,Another Name of Case

 

and so on.....

 

My goal is to have a table called case_numbers update each night from this text file.  I'm not worried about the automated part yet, I can't even get the data to insert yet...ha ha

 

I basically want the data in the table replaced with the data in the text file as the text file will have new case numbers added often.

 

After looking and looking on the net, I have this code:

<?php 
mysql_connect ("localhost","username","password"); 
mysql_select_db ("database_name"); 

// Path to the data file 
$path="test.txt"; 

// Get the site of the file 
$filesize=filesize($path); 

// Open file, read its data and close it 
$filenum=fopen($path,"r"); 
$arrlen = count($filenum); 
fclose($filenum); 

// Listing the array inside a loop, adding it to the database and echo about it. 
for($i=0; $i<$arrlen ; $i++){
$bits=explode(",",$filenum[$i]);

$sql_insert = mysql_query("REPLACE INTO case_numbers (casenumber,casename) VALUES ('$bits[0]','$bits[1]'");

$result = mysql_query ($sql_insert); 
// reports an error if insert fails. 
if (!$result){ echo mysql_error();} 
}else{
// All done, let's go home! 
echo "All rows imported successfully!"; 
}
?> 

Of course I put my actual connection settings for the connections.

 

When I run it I don't get an error, but the data in the table isn't replaced.  Nothing happens.

 

Any idea where I'm going wrong.

 

Thanks in advance,

Twitch

 

Made an edit cause I noticed my explode statement was missing the ","  still doesn't work..ha ha

[jl5501]

fopen() opens a text file and returns a pointer to the file.

 

What running count() against a file handle will do is not certain, but it is certainly not what you are expecting it to do

[harristweed]

As jl5501 pointed out, you are not actually reading the file....try

mysql_connect ("localhost","username","password"); 
mysql_select_db ("database_name"); 
// Path to the data file 
$path="test.txt"; 

// Get the site of the file 
$filesize=filesize($path); 
// Open file, read its data and close it 
$filenum=fopen($path,"r"); 

$contents = fread($filenum, filesize($filesize));
fclose($filenum); 

$bits=explode(",",$contents);
$arrlen = count($bits);

// Listing the array inside a loop, adding it to the database and echo about it. 
for($i=0; $i<$arrlen ; $i++){

$sql_insert = mysql_query("REPLACE INTO case_numbers (casenumber,casename) VALUES ('$bits[0]','$bits[1]'");

$result = mysql_query ($sql_insert); 
// reports an error if insert fails. 
if (!$result)echo mysql_error(); 
}
// All done, let's go home! 
echo "All rows imported successfully!"; 

[Twitch]

Thanks so much for the replies guys.  This site never disappoints.  I tried the code above and I get "Query was emptyAll rows imported successfully!"  I tried to echo $result; but get nothing.  Evidently it's not grabbing the data from the file.

 

I know I must be missing something simple.  Maybe when my coffee kicks in it will be apparent...haha

 

 

[Twitch]

Well, my friends I started from scratch and have an almost working script.

<?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 the 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.  Learned a lot so far doing this script.

 

Thanks in advance.