[RESOLVED] Updating one columns with multiple fields

FantasyLink

I want to update column, teamid. I am trying to update all 12 fields for teamid at same time using Form Processor (HTML). When I send it to next page to update the database, I am getting one result for all fields so I am not telling it how to update properly. The row# for each column is fixed and will not increase above 12 rows and the goal is to update team name based on seed #1-12. There is seed column next to teamid column, which is fixed and will be important for other purpose.

Here is the code I used...

"UPDATE tablename SET teamid='$teamOne', teamid='$teamTwo' WHERE seed='r1', seed='r2'";

I ran this code and is not updating all field properly as it is updating all the fields with last data on the form. I tried WHERE id='$_1' and did not getting any result.

Houdini

Could you show the form where seed r1, r2,r3...r12 are getting their values?

FantasyLink

So you are saying that the UPDATE code is corrrect?

krotkruton

Your update looks correct, although I'm not completely sure about the WHERE clause... it may work the way you do it, but I think the general form is this:

"UPDATE tablename SET teamid='$teamOne', teamid='$teamTwo' WHERE seed='r1' AND seed='r2'";

Its hard to tell exactly what is going on from your wording, but it sounds like your problem is in your form, so you will need to post that if you are looking for more help.

Houdini

OK you have a table named lets say 'teams' and it has at least two fields one being 'teamid' and another being 'seed'. Each of these having 12 entries kind of like below

teamid | seed

team01 | 12

team02 | 9

team03 | 1

team04 | 5

team05 | 7

You are using a form to update the rankings of these various teams and want to update them once the form is filled out. What is your primary key the teamid or the rank? The query you have is telling the database to update the 'teamid' fields depending on the entries for the seed value. I guess not knowing your table structure and how your form woks is what is confusing me.

The

"UPDATE tablename SET teamid='$teamOne', teamid='$teamTwo' WHERE seed='r1' AND seed='r2'";

should be what you want but since I really don't know how the above mentioned are set up it is hard to really help a whole lot.

In PHP it would look like

$sql = " UPDATE tablename SET teamid='$teamOne', teamid='$teamTwo' WHERE seed='r1' AND seed='r2'";

Knowing a little more would help unless of course the previous post solved your original question

DoctorWho

seed='r1' AND seed='r2'";

That tells me seed would have to eauql both r1 and r2 for the update to occur.
If it only equals one of those values and not both, the update won't occur.

thus an OR may be in order

FantasyLink

Ok, I mentioned that my seed column is fixed so it is like this:

teamid | seed | id

Team C | r1 | 1

Team Z | r2 | 2

Team P | r3 | 3

Team K | r4 | 4

Team J | r5 | 5

Well, you get the picture now. The goal is to update teamid at anytime while seed field will always remain fixed. And it will allow me to SELECT teamid where ranking is.

So with table above, I developed a page and let's name it includeselect.php for now. The purpose of includeselect.php is to put it on top of all pages so when I use <? php echo $ncaa5; ?> in HTML, then it will display team in the database. It reads like this:

<?php
*connect to mysql & select database code here*

$iLoop = 0;
$query ="SELECT teamid FROM gameresults";
$result =mysql_query($query) or die(mysql_error());
while($res = mysql_fetch_array($result))
{
if($iLoop == 0)
{
$ncaa1 = $res[0];
}elseif($iLoop == 1) {
$ncaa2 = $res[0];
}elseif($iLoop == 2) {
$ncaa3 = $res[0];
}elseif($iLoop == 3) {
$ncaa4 = $res[0];
}elseif($iLoop == 4) {
$afc5 = $res[0];
}elseif($iLoop == 5) {
$ncaa6 = $res[0];
}elseif($iLoop == 6) {
$ncaa7 = $res[0];
}elseif($iLoop == 7) {
$ncaa8 = $res[0];
}elseif($iLoop == 8) {
$ncaa9 = $res[0];
}elseif($iLoop == 9) {
$ncaa10 = $res[0];
}elseif($iLoop == 10) {
$ncaa11 = $res[0];
}elseif($iLoop == 11) {
$ncaa12 = $res[0];
}

$iLoop = $iLoop + 1;
}

include ('endb.php');
?>

For form page: (We will use 2 input value for this instead of 12 for easy finding)

<? php
include ('includeselect.php');
?>
<form action="add_teams.php" method="POST">
<table width="300" cellpadding="0" cellspacing="0" border="0">
<tr>
<td class="text">NCAA #1:</td>
<td><INPUT TYPE="text" NAME="TeamOne" SIZE="25" MAXLENGTH="25" VALUE="<?php echo $ncaa1; ?>"></td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td class="text">NCAA #2:</td>
<td><INPUT TYPE="text" NAME="TeamTwo" SIZE="25" MAXLENGTH="25" VALUE="<?php echo $ncaa2; ?>"></td>
</tr>
</table>
<br>
<input type="submit" name="Submit" value="SUBMIT"></form>

And now for the update page to UPDATE team info into teamid fields:

<?php
include ('includeselect.php');

$sql = "UPDATE tablename SET teamid='$teamOne', teamid='$teamTwo' WHERE seed='r1', seed='r2'";
$result = mysql_query($sql);
   if(!$result){
      echo "Error inserting your information into MySQL' mysql_error()";
   	  }
echo '<center><br><span class="text">Success! NCAA Teams has been updated!</span></br></center>';
include ('endb.php');
?>

FantasyLink

Solved:

<?php
$sql ="UPDATE tablename SET
teamid = CASE
              WHEN seed='r1' THEN '$ncaaOne'
              WHEN seed='r2' THEN '$ncaaTwo'

?>

Houdini

I found that also but the explaination was lacking but here it is:

UPDATE tbl_name SET fld2 = CASE fld1
WHEN val1 THEN data1
WHEN val2 THEN data2
ELSE fld2 END

//The note is: do not forget ELSE. If you do not use it, all rows that are outside the //range of your updated values will be set to blank!

Where you substitued teamid for fld2 and seed='r1...r12' for val1...val12 and $ncaa`...$ncaa for data1 You might want to add the ELSE and END, unless of course it causes another error.

I will have to admit that I just learned some new stuff about MySQL queries and have just joined another forum that is nothing but MySQL at devshed, some of the stuff they get into there is so far beyond what I am used to.

Glad you got your peoblem fixed, now I am gonna play around with the table I made on my server with that and some other stuff I just learned today. 🙂

Houdini

If you do not update all the records in that row the ones not addressed will end up blank.