Cycle Through Only The Rows That Are Changed

[backspc]

If I have the same value in a form ie

 

<? while($row=mysql_fetch_array($result)) {
?>
<tr>
<td width=80px id="subid" name=""></td>
<td><input type="text" size=45 name="item" value="<?=$info['item']?>"> </td>
</tr>
<?}?>

It produces

52 apple

53 orange

54 lemon

 

Using

<? $query3 = "SELECT subid, COUNT(subid) FROM items WHERE id=$thisID ";
$result3 = mysql_query($query3);
// Print out result
while($row = mysql_fetch_array($result3)){
echo "There are ". $row['COUNT(subid)'] ."items.";
echo "<br />";
}?>

I can see there are 3 item but how would I update them if I changed 1 or 2 of the values,

 

How would I pickup the subid value (which is unique to each row) to update and then cycle through only the rows that are changed or do I have to update all rows?

Sorry if this doesn't make sense I am still learning (the hard way)

[Psycho]

Yeah, I'm having a hard time understanding what you are after. Can you please elaborate?

[backspc]

I will try and explain it a little better (sorry it is hard to explain something I don't know)

 

This is my code

<form action="test.php" method="post">
<? while($row=mysql_fetch_array($result)) { ?>

<td width=80px name="subid" value="52">apple</td>

<? } ?>

<input type="submit" />

</form>

the output is

(subid value 52) apple

(subid value 53) orange

(subid value 54) lemon

 

 

The problem is I am trying to pick up the subid value via $_POST["subid"] but subid is different for each one.

Can I cycle through the post data and pick up each subid value or am I going to have to do something like subid1 but then I could have up to 50 rows in the table so it seams like doing this would be wrong

$sql3 = "UPDATE mytable(subid, fruit) WHERE id='.db_input($_POST['subid1']);
$sql3 .= "VALUES('" . $_POST['subid1'] . "','" . $_POST[$i . 'fruit'] . "')";

then again with 2

$sql3 = "UPDATE mytable(subid, fruit) WHERE id='.db_input($_POST['subid2']);
$sql3 .= "VALUES('" . $_POST['subid2'] . "','" . $_POST[$i . 'fruit'] . "')";

then how would I tell it to stop as there might be 3 in that table or 300

 

Does this make it a little clearer?

Thank you for helping

Edited October 15, 2012 by backspc

[Jessica]

$_POST["subid"] - There is no input in your form with the name subid. There is item. You can make an array of input values by doing name="subid[]" and then access it as $_POST['subid'] (an array)

[Christian F.]

Also, if that code is copy-pasted directly from your script, without modification, you really should turn on error reporting. (See the last link in Jessica's signature.)

You should also be using full PHP tags only, not the short tags. They're deprecated and will be removed from the next PHP version, and on most hosts they're turned off by default.

[Psycho]

Well, I'm still unsure of what you are trying to do. You first show some code that will output some data from a query. But, then you ask about how you would access some values from POST data. I'm really not understanding what one has to do with the other. But, based upon bits and pieces of what you've posted I will make an attempt at deciphering what I think you want to do.

 

I think you want to 1) create a form of all the items that the user can edit, then 2) you want the user to POST that form and 3) you want to process that form into appropriate UPDATE queries based upon what the user changed. OK, let's say the table contains three fields: id, name, and qty and you want to allow the user to change the quantity. I would first create the form like so

$query = "SELECT id, name, qty FROM items";
$result = mysql_query($query);
while($row=mysql_fetch_array($result))
{
   echo "<tr>\n";
   echo "<td width=\"80px\"></td>\n";
   echo "<td><input type=\"text\" size='45' name=\"item[{$row['id']}]\" value=\"{$row['qty']}\"></td>\n";
   echo "</tr>\n";
}

 

Now, there is no direct way to know what values changed and which ones did not. You could implement some JS code to set a hidden field or something, but that's a poor solution. Instead you can just run the update query(ies). If nothing changed then no changes are made to the DB. But, the problem here is that you don't want to run unnecessary queries.

 

Personally, I would make a decision based upon how often the page would be used and how many potential records there are. If it is not used all that often (many, many times a day) and there aren't going to be hundreds/thousands of records I would probably just iterate through each record and run an UPDATE query. But, the better method would be to either 1) Run a SELECT query for all the records and process them to figure out which ones changed or 2) create a single update query for all the records.

 

Option #2 is probably better, but is a little complicated. I'll do some research to see if I can find the correct format.

[Psycho]

OK, using the field format above, this is one solution for the processing script:

//Create temp string to hold insert data
$insertClauses = '';
foreach($_POST['item'] as $id => $qty)
{
   $insertClauses[] = "    WHEN '$id' THEN '$qty' \n";
}

//Create single UPDATE query for all records
$query = "UPDATE items
	  SET qty = CASE id
		  $insertClauses
	  END";
$result = mysql_query($query);

 

The resulting query would look like this:

UPDATE items
SET qty = CASE id
  WHEN '1' THEN '25'
  WHEN '2' THEN '205'
  WHEN '3' THEN '2'
  WHEN '4' THEN '155'
  WHEN '5' THEN '62'
END

[backspc]

Also, if that code is copy-pasted directly from your script, without modification, you really should turn on error reporting. (See the last link in Jessica's signature.)

You should also be using full PHP tags only, not the short tags. They're deprecated and will be removed from the next PHP version, and on most hosts they're turned off by default.

It was not copy and past just from memory and thank you for the tip re Full Tag (GOOD) <?php //Good form ?> Short Tag (BAD) <? //=( ?>

[backspc]

Thanks Psycho I will have a bit of a play and will let you know how I get on