Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MYSQL Update problem

Posted on 2011-03-02
17
Medium Priority
?
727 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:akalbfell
  • 9
  • 6
  • 2
17 Comments
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 35023136
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

0
 
LVL 8

Author Comment

by:akalbfell
ID: 35023314
Same result. No matter what I try, double quotes, single quotes or no quotes i get the same result.
0
 
LVL 4

Expert Comment

by:te-edu
ID: 35023415
echo sql1;

see is query ok.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 8

Author Comment

by:akalbfell
ID: 35023431
$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.
0
 
LVL 4

Expert Comment

by:te-edu
ID: 35023446
try:

$sql1="UPDATE MLB2010.Pitching SET QS='".$parts[2]."' WHERE playerID='".$parts[1]."' AND yearID='$year'";
0
 
LVL 8

Author Comment

by:akalbfell
ID: 35023484
Same thing. Here is the output of the query with it that way

UPDATE MLB2010.Pitching SET QS='' WHERE playerID='' AND yearID='2009'

0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 35023547
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

0
 
LVL 8

Author Comment

by:akalbfell
ID: 35023563
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
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 35023580
It simply doesn't work that way.  Post your "2009QS.txt" file.
0
 
LVL 8

Author Comment

by:akalbfell
ID: 35023584
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
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 35023639
Actually, I wanted you to post it as a file so it wouldn't get changed when i downloaded it.
0
 
LVL 8

Author Comment

by:akalbfell
ID: 35023648
0
 
LVL 84

Accepted Solution

by:
Dave Baldwin earned 2000 total points
ID: 35023671
I found the problem.  Escape sequences like '\t' are Not recognized in single quotes in PHP, they require double quotes.  Like below.  Works fine when you do that.
$parts = explode("\t", $line_of_text);

Open in new window

0
 
LVL 8

Author Comment

by:akalbfell
ID: 35023712
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

0
 
LVL 8

Author Comment

by:akalbfell
ID: 35023783
Never mind got it. Just had to put single quotes around the playerID. Thanks so much for your help.
0
 
LVL 8

Author Closing Comment

by:akalbfell
ID: 35023785
Thanks!
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 35023861
You're welcome.  Some things are just hard to see in the middle of all that.  Thanks for the points.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question