Read text file insert new values into mysql

[sankster]

$connection = mysql_connect($db_host, $db_user, $db_password) or die("error with connection");
mysql_select_db($db_name, $connection);


$file=fopen("daily_user_summary.txt","r");

while (!feof($file_handle) ) {
$line_of_text = fgets($file);
$parts = explode(' ', $line_of_text);
print $parts[0]. $parts[1]. $parts[2] . $parts[3]. "<BR>";
$query = ("SELECT name, newcredit, sum(total), team FROM tb1 WHERE NAME = '$parts[0]' and TEAM = '$parts[3]'");
$row = mysql_fetch_row($query);
echo $row['name'] = $name;
echo $row['newcredit'] = $oldcredit;
echo $row['sum(total)'] = $oldwu;
echo $row['team'] = $team;

$updatequery = ("UPDATE tb1 SET newcredit='$parts[1]' AND sum(total)='$parts[2]' WHERE name = '$parts[0]' AND team = '$parts[3]'");
mysql_query($updatequery, $connection) or trigger_error("SQL", E_USER_ERROR);
}
 fclose($file);
 ?>

</body>
</html>

I am trying to read a text file and insert values from the text file there are four values for each line that will be inserted into the database, there are 10000 of lines of information I need to insert and be able to update with new values. The code above works for reading the text file but when I try to use the updatequery the code craps out. I also am not sure if this code will work if there is a new user added to the text file if it will create its new information in the database on a new table insert.

[minitauros]

Can you perhaps show us the actual error that you receive? :) I don't quite understand what is going wrong right now.

[dpste]

You can not update a sum of values with a single update statement. The database has no way of knowing which rows to change in your statement.

Also you do not use "AND" but a "," when updating different columns:

AND sum(total)='$parts[2]'

So the above code needs to be rewritten.

[sankster]

$connection = mysql_connect($db_host, $db_user, $db_password) or die("error with connection");
mysql_select_db($db_name, $connection);


$file=fopen("daily_user_summary.txt","r");

while (!feof($file_handle) ) {
$line_of_text = fgets($file);
$parts = explode(' ', $line_of_text);
$name = $parts[0];
$oldcredit = $parts[1];
$oldwu = $parts[2];
$team = $parts[3];
//$query = ("SELECT name, newcredit, sum(total), team FROM tb1 WHERE NAME = '$name', TEAM = '$team'");
//$row = mysql_fetch_array($query, $connection);
//echo $row['name'] = $name1;
//echo $row['newcredit'] = $oldcredit1;
//echo $row['sum(total)'] = $oldwu1;
//echo $row['team'] = $team1;

//$updatequery = ("UPDATE tb1 SET newcredit='$parts[1]'  WHERE name = '$parts[0]', team = '$parts[3]'");
//$updatequery2 = ("UPDATE tb1 SET sum(total)='$parts[2]' WHERE name = '$parts[0]', team = '$parts[3]'");
//mysql_query($updatequery, $connection) or trigger_error("SQL", E_USER_ERROR);
//mysql_query($updatequery2, $connection) or trigger_error("SQL", E_USER_ERROR);
}
 fclose($file);
 ?>

This is all the code I have, the text file look like this

joe 3726526013  28383564    0
bob 2715768086  11228263    0
cat 2029377984  390530  181223
hs  1362576372  189782  213904

So If I am understanding this right, as this loop reads line by line I am taking the 4 parts of each line and splitting them up into variables. From the variable I am creating I should be able to use this to query the mysql database and check to see if the users is already recored and then be able to update the values and return them back to the database with the update mysql command. I am really looking for some help here as I am a real newbie at php, I am not sure if I am skipping a bunch of steps or what needs to be done to do this.