Solved

MYSQL Update problem

Posted on 2011-03-02
17
718 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 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
Comment Utility
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
Comment Utility
echo sql1;

see is query ok.
0
 
LVL 8

Author Comment

by:akalbfell
Comment Utility
$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
Comment Utility
try:

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

Author Comment

by:akalbfell
Comment Utility
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 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 82

Expert Comment

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

Author Comment

by:akalbfell
Comment Utility
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 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
Comment Utility
0
 
LVL 82

Accepted Solution

by:
Dave Baldwin earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks!
0
 
LVL 82

Expert Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now