akalbfell
asked on
MYSQL Update problem
Maybe someone can tell me what I am doing wrong here. Been trying every way I know to make this work and it just wont.
What I am trying to do is pull data from a txt file which is setup like so..
Player Name TAB playerID TAB QS
I am reading the data in from a text file and then trying to update the QS category based on the player ID. At the bottom I am outputting all the data which works fine so I know these variables do contain the correct data. For whatever reason though the UPDATE query just ignores the values I am trying to supply to it. If I hard code an UPDATE query there it works.
Any help would be greatly appreciated
What I am trying to do is pull data from a txt file which is setup like so..
Player Name TAB playerID TAB QS
I am reading the data in from a text file and then trying to update the QS category based on the player ID. At the bottom I am outputting all the data which works fine so I know these variables do contain the correct data. For whatever reason though the UPDATE query just ignores the values I am trying to supply to it. If I hard code an UPDATE query there it works.
Any help would be greatly appreciated
<?php
error_reporting(E_ALL ^ E_NOTICE);
$file_handle = fopen("2009QS.txt", "r");
while (!feof($file_handle) ) {
$line_of_text = fgets($file_handle);
$parts = explode('\t', $line_of_text);
$username="uname";
$password="password";
$database="MLB2010";
$year="2009";
mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$sql1="UPDATE MLB2010.Pitching SET QS='$parts[2]' WHERE playerID='$parts[1]' AND yearID='$year'";
//$sql1="UPDATE MLB2010.Pitching SET QS=21 WHERE playerID='arroybr01' AND yearID=2009";
$result1=mysql_query($sql1);
mysql_close();
print $parts[0] . $parts[1] . $parts[2]. "<BR>";
}
fclose($file_handle);
?>
ASKER
Same result. No matter what I try, double quotes, single quotes or no quotes i get the same result.
echo sql1;
see is query ok.
see is query ok.
ASKER
$parts[2] and $parts[1] are empty but the year is working right
but the print statement is printing the correct data in those same variables.
but the print statement is printing the correct data in those same variables.
try:
$sql1="UPDATE MLB2010.Pitching SET QS='".$parts[2]."' WHERE playerID='".$parts[1]."' AND yearID='$year'";
$sql1="UPDATE MLB2010.Pitching SET QS='".$parts[2]."' WHERE playerID='".$parts[1]."' AND yearID='$year'";
ASKER
Same thing. Here is the output of the query with it that way
UPDATE MLB2010.Pitching SET QS='' WHERE playerID='' AND yearID='2009'
UPDATE MLB2010.Pitching SET QS='' WHERE playerID='' AND yearID='2009'
The code below gives:
UPDATE MLB2010.Pitching SET QS=21 WHERE playerID='arroybr01' AND yearID=2009
which is the string you need.
UPDATE MLB2010.Pitching SET QS=21 WHERE playerID='arroybr01' AND yearID=2009
which is the string you need.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled</title>
</head>
<body>
<?php
$parts = array (2009, "arroybr01", 21);
$year = 2009;
$sql1 = "UPDATE MLB2010.Pitching SET QS=$parts[2] WHERE playerID='$parts[1]' AND yearID=$year";
echo $sql1;
?>
</body>
</html>
ASKER
Doesn't produce the result i need when used in my code though. i get this output
UPDATE MLB2010.Pitching SET QS= WHERE playerID='' AND yearID=2009Bronson Arroyo arroybr01 21
Where you have the query followed by $parts[0] which in this example is Bronson Arroyo and then $parts[1] is arroybr01 and then $parts[2] which is 21
UPDATE MLB2010.Pitching SET QS= WHERE playerID='' AND yearID=2009Bronson Arroyo arroybr01 21
Where you have the query followed by $parts[0] which in this example is Bronson Arroyo and then $parts[1] is arroybr01 and then $parts[2] which is 21
It simply doesn't work that way. Post your "2009QS.txt" file.
ASKER
Bronson Arroyo arroybr01 21
Mark Buehrle buehrma01 19
A.J. Burnett burneaj01 14
Chris Carpenter carpech01 25
Ryan Dempster dempsry01 23
R.A. Dickey dickera01 19
Jon Garland garlajo01 19
Roy Halladay hallaro01 25
Livan Hernandez hernali01 22
Tim Hudson hudsoti01 25
Ted Lilly lillyte01 22
Rodrigo Lopez lopezro01 15
Derek Lowe lowede01 15
Kevin Millwood millwke01 14
Roy Oswalt oswalro01 24
Carl Pavano pavanca01 21
Johan Santana santajo01 17
Randy Wolf wolfra02 20
Barry Zito zitoba01 19
Brett Myers myersbr01 24
John Lackey lackejo01 21
Cliff Lee leecl02 18
Jeremy Bonderman bondeje01 12
Edwin Jackson jacksed01 15
Jeremy Guthrie guthrje01 20
Zack Greinke greinza01 21
Gavin Floyd floydga01 18
Joe Blanton blantjo01 16
Justin Verlander verlaju01 22
Jason Vargas vargaja01 21
C.J. Wilson wilsocj01 20
Josh Johnson johnsjo09 23
Joe Saunders saundjo01 15
Paul Maholm maholpa01 16
Kyle Davies davieky01 14
Adam Wainwright wainwad01 25
Matt Cain cainma01 25
Scott Baker bakersc02 12
Felix Hernandez hernafe02 30
Francisco Liriano liriafr01 20
Wandy Rodriguez rodriwa01 23
Ervin Santana santaer01 21
Matt Garza garzama01 18
Jered Weaver weaveje02 27
Jon Lester lestejo01 20
Chad Billingsley billich01 18
Chris Narveson narvech01 12
Cole Hamels hamelco01 21
Anibal Sanchez sanchan01 20
Fausto Carmona carmofa01 22
James Shields shielja02 17
Jonathan Sanchez sanchjo01 14
Jason Hammel hammeja01 14
Mike Pelfrey pelfrmi01 20
Ubaldo Jimenez jimenub01 25
John Danks danksjo01 21
Dallas Braden bradeda01 17
Tim Lincecum linceti01 22
Kyle Kendrick kendrky01 14
Yovani Gallardo gallayo01 17
Clay Buchholz buchhcl01 19
Ian Kennedy kenneia01 15
Johnny Cueto cuetojo01 18
Hiroki Kuroda kurodhi01 21
Randy Wells wellsra01 18
Jeff Niemann niemaje01 16
Justin Masterson masteju01 12
Max Scherzer scherma01 18
Clayton Kershaw kershcl01 23
Chris Volstad volstch01 15
Clayton Richard richacl01 20
Gio Gonzalez gonzagi01 23
Jonathon Niese niesejo01 14
David Price priceda01 25
Trevor Cahill cahiltr01 21
Ricky Romero romerri01 20
Rick Porcello porceri01 13
Brad Bergesen bergebr02 12
Brett Cecil cecilbr01 18
Tommy Hanson hansoto01 19
Mat Latos latosma01 21
Brian Matusz matusbr01 18
Doug Fister fistedo01 13
Wade Davis daviswa01 15
Mark Buehrle buehrma01 19
A.J. Burnett burneaj01 14
Chris Carpenter carpech01 25
Ryan Dempster dempsry01 23
R.A. Dickey dickera01 19
Jon Garland garlajo01 19
Roy Halladay hallaro01 25
Livan Hernandez hernali01 22
Tim Hudson hudsoti01 25
Ted Lilly lillyte01 22
Rodrigo Lopez lopezro01 15
Derek Lowe lowede01 15
Kevin Millwood millwke01 14
Roy Oswalt oswalro01 24
Carl Pavano pavanca01 21
Johan Santana santajo01 17
Randy Wolf wolfra02 20
Barry Zito zitoba01 19
Brett Myers myersbr01 24
John Lackey lackejo01 21
Cliff Lee leecl02 18
Jeremy Bonderman bondeje01 12
Edwin Jackson jacksed01 15
Jeremy Guthrie guthrje01 20
Zack Greinke greinza01 21
Gavin Floyd floydga01 18
Joe Blanton blantjo01 16
Justin Verlander verlaju01 22
Jason Vargas vargaja01 21
C.J. Wilson wilsocj01 20
Josh Johnson johnsjo09 23
Joe Saunders saundjo01 15
Paul Maholm maholpa01 16
Kyle Davies davieky01 14
Adam Wainwright wainwad01 25
Matt Cain cainma01 25
Scott Baker bakersc02 12
Felix Hernandez hernafe02 30
Francisco Liriano liriafr01 20
Wandy Rodriguez rodriwa01 23
Ervin Santana santaer01 21
Matt Garza garzama01 18
Jered Weaver weaveje02 27
Jon Lester lestejo01 20
Chad Billingsley billich01 18
Chris Narveson narvech01 12
Cole Hamels hamelco01 21
Anibal Sanchez sanchan01 20
Fausto Carmona carmofa01 22
James Shields shielja02 17
Jonathan Sanchez sanchjo01 14
Jason Hammel hammeja01 14
Mike Pelfrey pelfrmi01 20
Ubaldo Jimenez jimenub01 25
John Danks danksjo01 21
Dallas Braden bradeda01 17
Tim Lincecum linceti01 22
Kyle Kendrick kendrky01 14
Yovani Gallardo gallayo01 17
Clay Buchholz buchhcl01 19
Ian Kennedy kenneia01 15
Johnny Cueto cuetojo01 18
Hiroki Kuroda kurodhi01 21
Randy Wells wellsra01 18
Jeff Niemann niemaje01 16
Justin Masterson masteju01 12
Max Scherzer scherma01 18
Clayton Kershaw kershcl01 23
Chris Volstad volstch01 15
Clayton Richard richacl01 20
Gio Gonzalez gonzagi01 23
Jonathon Niese niesejo01 14
David Price priceda01 25
Trevor Cahill cahiltr01 21
Ricky Romero romerri01 20
Rick Porcello porceri01 13
Brad Bergesen bergebr02 12
Brett Cecil cecilbr01 18
Tommy Hanson hansoto01 19
Mat Latos latosma01 21
Brian Matusz matusbr01 18
Doug Fister fistedo01 13
Wade Davis daviswa01 15
Actually, I wanted you to post it as a file so it wouldn't get changed when i downloaded it.
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
much better, the query looks good now. Only problem is the data isn't going into the table.
here is the code I am using now and the first couple lines of output
UPDATE MLB2010.Pitching SET QS=21 WHERE playerID=arroybr01 AND yearID=2009Bronson Arroyoarroybr0121
UPDATE MLB2010.Pitching SET QS=19 WHERE playerID=buehrma01 AND yearID=2009Mark Buehrlebuehrma0119
UPDATE MLB2010.Pitching SET QS=14 WHERE playerID=burneaj01 AND yearID=2009A.J. Burnettburneaj0114
UPDATE MLB2010.Pitching SET QS=25 WHERE playerID=carpech01 AND yearID=2009Chris Carpentercarpech0125
here is the code I am using now and the first couple lines of output
UPDATE MLB2010.Pitching SET QS=21 WHERE playerID=arroybr01 AND yearID=2009Bronson Arroyoarroybr0121
UPDATE MLB2010.Pitching SET QS=19 WHERE playerID=buehrma01 AND yearID=2009Mark Buehrlebuehrma0119
UPDATE MLB2010.Pitching SET QS=14 WHERE playerID=burneaj01 AND yearID=2009A.J. Burnettburneaj0114
UPDATE MLB2010.Pitching SET QS=25 WHERE playerID=carpech01 AND yearID=2009Chris Carpentercarpech0125
<?php
error_reporting(E_ALL ^ E_NOTICE);
$file_handle = fopen("2009QS.txt", "r");
while (!feof($file_handle) ) {
$line_of_text = fgets($file_handle);
$parts = explode("\t", $line_of_text);
$username="uname";
$password="pass";
$database="MLB2010";
$year="2009";
$var2 = trim($parts[2]);
$var1 = trim($parts[1]);
mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$sql1 = "UPDATE MLB2010.Pitching SET QS=$var2 WHERE playerID=$var1 AND yearID=$year";
echo $sql1;
//$sql1="UPDATE MLB2010.Pitching SET QS=21 WHERE playerID='arroybr01' AND yearID=2009";
$result1=mysql_query($sql1);
mysql_close();
print $parts[0] . $parts[1] . $parts[2]. "<BR>";
}
fclose($file_handle);
?>
ASKER
Never mind got it. Just had to put single quotes around the playerID. Thanks so much for your help.
ASKER
Thanks!
You're welcome. Some things are just hard to see in the middle of all that. Thanks for the points.
Open in new window