[SOLVED] mysql update all the record...which i only wanna update one

[zgkhoo]

<?php $result = mysql_query("SELECT * FROM transaction where DrawNumber='$_POST[drawnumber]'") or die('Query failed: ' . mysql_error()); 

while($row = mysql_fetch_array($result,MYSQL_ASSOC)){
	$TotalWon=0;
		if($row[PurchaseNo]==$_POST[first])
		{
			echo "</br>first won";
			$TotalWon=$TotalWon+1000;

		}

		if($row[PurchaseNo]==$_POST[second])
		{
			echo "</br>second won";
			$TotalWon=$TotalWon+1000;
		}	

		if($row[PurchaseNo]==$_POST[third])
		{
			echo "</br>third won";
			$TotalWon=$TotalWon+1000;
		}	
        mysql_query("UPDATE transaction SET WinLost = $TotalWon");   

}
?>

mysql_query("UPDATE transaction SET WinLost = $TotalWon");  <--this will update all the query.and set WinLost field to $TotalWon for ALL the record SELECT * FROM transaction where DrawNumber

which i only intend to update which the player won the first prize,second prize n third prize.

now the system update the Winlost For All record..even for the player not won the prize. ???

anyone know how to solve

[Simon Moon]

You update all records, you have no "WHERE" in that query, so it updates the whole table. Without more information I can't really say what that WHERE clause would look like, but thats your trouble there

[zgkhoo]

if put

 

UPDATE transaction SET WinLost = $TotalWon where DrawNumber='$_POST[drawnumber]'"

 

seem make no sense

 

cos

$result = mysql_query("SELECT * FROM transaction where DrawNumber='$_POST[drawnumber]'")

 

 

 

 

 

[zgkhoo]

or need use $variable to control it?

[teng84]

i dont understand explain more

 

hmm update really needs where clause or it will update the whole table 

[Simon Moon]

I need more information in order to be able to help you. For example the structure of the table would be good. Then what exactly you want to do, as it is not exactly clear. If you can write down what should be updated where and for whom, this might help me and others here to actually help you along with your query. As far as i see it, you lack a few principles in SQL, so i suggest you look for some good tutorials (i think there are some linked in the stickies here). Pay close attention to the update part.

 

This is now just a guess, i think you want to update the records for 3 different people who won, right? Then you most likely will need 3 slightly different queries.

[zgkhoo]

	$sql = "CREATE TABLE transaction
(
	TransactionID varchar(6),
	PurchaseNo integer(4),
	TransactionDate Date,
	UserID varchar(10),
	Category varchar(10),
	BetMode varchar(9),
	Amount integer(10),
	DrawDate Date,		
	DrawNumber varchar(25),
	DrawStatus varchar(6),
	WinLost Double(6,2)




)";

 

 

wat i want is set the total money won for the user if their's number is match with the top three prize.

know wat i meant?

thanks..

 

[zgkhoo]

solve already

 

 	 $result = mysql_query("SELECT * FROM transaction where DrawNumber='$_POST[drawnumber]'") or die('Query failed: ' . mysql_error());
	 $whowon="";//initialize


while($row = mysql_fetch_array($result,MYSQL_ASSOC)){


		echo "</br>row bet amount=".$row[Amount];
		echo "</br>purchase no=".$row[PurchaseNo];
		echo "</br>post first =".$_POST[first];
		$TotalWon=0;



		//set winlost for evenOdd
		//set winlost for bigsmall
		//set winlost for flower10
		//set winlost for flowerr100
		//set winlost for 4d sure win



		//set winlost for 4d
		//echo "</br>matched";

		echo "</br>row purchase no=".$row[PurchaseNo];
  		echo "</br>post  first no=".$_POST[first];		

		if($row[PurchaseNo]==$_POST[first])
		{
			echo "</br>first won";
			$TotalWon=$TotalWon+1000;

		}

		if($row[PurchaseNo]==$_POST[second])
		{
			echo "</br>second won";
			$TotalWon=$TotalWon+1000;
		}	

		if($row[PurchaseNo]==$_POST[third])
		{
			echo "</br>third won";
			$TotalWon=$TotalWon+1000;
		}	

		echo "</br>TotalWon=".$TotalWon;
		echo "</br>row userID".$row[userID];
		$whowon=$row[userID];//initialize
		echo "</br>whowon".$row[userID];



        mysql_query("UPDATE transaction SET WinLost = $TotalWon
        where UserID='$whowon'");