Link to home
Start Free TrialLog in
Avatar of akalbfell
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

 
<?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);


?>

Open in new window

Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

I notice that you use single quotes in the version with the variables and not in the other version.  Try this.
$sql1="UPDATE MLB2010.Pitching SET QS=$parts[2] WHERE playerID='$parts[1]' AND yearID=$year";

Open in new window

Avatar of akalbfell
akalbfell

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.
$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.
try:

$sql1="UPDATE MLB2010.Pitching SET QS='".$parts[2]."' WHERE playerID='".$parts[1]."' AND yearID='$year'";
Same thing. Here is the output of the query with it that way

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.
<!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>

Open in new window

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
It simply doesn't work that way.  Post your "2009QS.txt" file.
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
Actually, I wanted you to post it as a file so it wouldn't get changed when i downloaded it.
ASKER CERTIFIED SOLUTION
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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


<?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);


?>

Open in new window

Never mind got it. Just had to put single quotes around the playerID. Thanks so much for your help.
Thanks!
You're welcome.  Some things are just hard to see in the middle of all that.  Thanks for the points.