update statement (this may just be to do with sql)

[Clarkey_Boy]

Hi,

I have hit a slight problem - I have an update statement as follows:

$conn->Execute("UPDATE units SET $res = $newrescost WHERE ID = $x");

The problem is that it comes up with an error about the syntax - I am sure I have everything covered that I need to though. The statement works but I have a text box which appears for each resource for the unit which I want to edit - it only shows the first one, then if you click it and hit enter it shows the first 2, then the first 3, then all 4 (there are only 4 resources in this game at the moment). Can anyone see what the problem could be, given that $conn is the connection to the database, $res is the resource name taken from the resources table, $newrescost is the new cost (written into the textbox) and $x is the ID of the unit to be edited?

Thanks,

Clarkey Boy

[thorpe]

What field type is $res? You may need to surround its value in quotes.

$conn->Execute("UPDATE units SET $res = '$newrescost' WHERE ID = $x");

[Clarkey_Boy]

I have tried that - the field type is number (all the fields represented by the variable $res are the resource costs for the unit being edited). The fields are called things like "main_gold_cost", "main_food_cost", "main_stone_cost" etc etc. $res represents these names - and when I put quotation marks around $res it says something about not being able to find the field "'main_gold_cost'" etc. I think it may be a datatype problem - $newrescost is from a TEXT box and I have never found out how to set a string as a number.

[thorpe]

You might try...

$newrescost = (int) $newrescost;

Doubt that will effct it though. Does your query work when you hardcode values into it instead of using $res and $newrescost?

[Clarkey_Boy]

ok... I have figured out that bit - the statment decided to work properly when I put the line $newrescost = (int) $newrescost. The next problem is that, when I load the page it resets all the values for the specified unit to 0 - so if I do not change anything and go back to the purchase units page it says that they cost nothing for any resource. I have tried using an if statement for if $newrescost is not equal to the current resource cost then change the units but if $newrescost is equal to the current resource cost then do nothing, but that does not work for some reason.

Edit: Thanks for the suggestion, thorpe. I didn't see the code bit when I read the post but I had already tried that by the time you posted.

Well it sort of works now - it does the job but it does the "transaction" (editing the unit) everytime the page is viewed - doesn't matter whether the values in the textboxes are the same as the current costs or not.

[Clarkey_Boy]

ok... I am almost giving up here - I found that I could not change a value to 0 due to the if statements. I tried taking the if statements away and it then decided to go back to my last problem - resetting ALL the values to 0. I have no idea WHY it does it either.

[thorpe]

Post your actual code.

[Clarkey_Boy]

$x = 1; //Sets x = 1
include "connect.php"; //Connection File
$unitedit = $_GET["uid"]; //The unit to edit - value is taken from a link on the units page - the links are only available to me.
echo "<form method=post action=viewalldetails.php?uid=$unitedit><table width=100%>";
$z = 0; //Number of errors is 0 (I tend to use z to collect errors.
while($x <= $things1) { //$things1 = number of units overall.
	if($_GET["uid"] == "unit".$x) { //unit$x is the value of uid submitted in the link.
		$selectresource = $conn->Execute("SELECT resource FROM resources");
		while(!$selectresource->EOF) {
			$resource = $selectresource->Fields("resource"); //The resource - cycles through all the resources one by one.
			$res = "main_".$resource."_cost"; //The field in the units table which represents the resource cost.
			$selectunit = $conn->Execute("SELECT $res FROM units WHERE ID = $x");
			while(!$selectunit->EOF) {
				$rescost = $selectunit->Fields("$res"); //The current unit cost for the current resource.
				echo "<tr><td width=50% align=right>";
				echo $resource." cost: ".$rescost;
				echo "</td><td width=50%>";
				echo "<input type=text name=$resource value=$rescost>";
				echo "</td></tr>";
				$selectunit->MoveNext(); //Move to next unit (there is only one which can be specified at any time.
			}
			$newrescost = $_POST["$resource"]; //Takes the value in the input box for the specified resource.
			$newrescost = (int) $newrescost; //Sets $newrescost to an integer.
			$conn->Execute("UPDATE units SET $res = $newrescost WHERE ID = $x");
			$selectresource->MoveNext(); //Move to the next resource.
		}
	}
	$x++; //Increment x.
}

This, I think, is where the error is located - the only other code on the page is the hidden input box for the unit id.