Page 1 of 1

Updating sql from a file

Posted: Mon Mar 26, 2007 2:04 pm
by dada44
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi everybody, 

I'm trying to write a script that reads information from a txt file and updates a mysql table. 

The table has 4 fields: test_id, buyid, amount and date. 

And the txt looks like: 
8965,13.80,20070315
12673,4.15,20070315
6363,10.85,20070315
.
.
In the first line 8965 is buyid, 13.80 is amount, and the rest is the date.

And this is what I’m trying to do: I need to compare buyid in the file and in the database, if they are the same then I have to update amount in the database using the amount info in the file. 

I seemed not very complicated, but now I’m totally lost. Probably my approach is failing from the beginning. Can anybody please tell me what’s wrong with the following code, and what should I do to get it right?? 

Thanks a million in advance!

Code: Select all

<?

# database connection
mysql_connect("localhost", "user", "pass");
mysql_select_db("database");

# read the file into an array

$lines = file_get_contents("file.txt");

// split the phrase by any number of commas or space characters

$pieces = preg_split("/[\s,]+/", $lines);


// read the table
$query = "SELECT test_id, buyid, amount, date FROM test";

  $result = MYSQL_QUERY($query);
	$howmany = MYSQL_NUMROWS($result);


		for ($i=0; $i < $howmany; $i++)	{

	$buyid = mysql_result($result,$i,"buyid");	
$amount = mysql_result($result,$i,"amount");	


	for ($n=1; $n < $howmany; $n+=3)	{

  $a= $pieces[$n];
if ($buyid == $a){
mysql_query("UPDATE ztest SET  amount='$a'" );
}
}

}
 
?>

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Mon Mar 26, 2007 2:13 pm
by John Cartwright
You've already been shown how to format the code on our forums..