SQL update script

UnitedWeFall

Hi all,

i'm having a bit of problem with some code i'm writing. Basically I have a while loop creating a series of text boxes which display dates from rows in my database. Users can edit the text boxes, click submit, and save the dates back to the SAME rows they came from.

My problem is the saving back part. I'll post the code.. and if anyone could give me some ideas it would be really appreciated 🙂

This is the first page of code:

<form action='process.php' method='post'>
while($row = mysql_fetch_array($result))
  {
  $date2 = $row['estimatedDelivery'];
  $timestamp2 = strtotime($date2);
  $new_date2 = date("d-m-Y",$timestamp2);
  echo "<input type='text' name='estimatedDelivery' value=".$new_date2.">";
  echo "<p>";
  }
  echo "<input type='submit' name='submit' value='Submit updated delivery dates'></form>";

Now I have absolutely no idea what to put in process.php... any info appreciated!

Thanks in advance.

UnitedWeFall

I tried this but the rows aren't updated as they should be. No errors to speak of.

foreach ($_POST['estimatedDelivery'] as $key => $val) {
          $sql2 = "UPDATE smail SET estimatedDelivery = '".$val."' WHERE estimatedDelivery = '".$key."'";
		  mysql_query($sql2);
   }

johanafm

Change the name attribute of your input to 'estimatedDelivery[]' and it will become an array.

However, in your query, the where clause

WHERE estimatedDelivery = '$key'

will become:
WHERE estimatedDelivery = '0'
WHERE estimatedDelivery = '1'
etc.

If all your estimatedDeliveries are unique in the table, you could use this part in the where clause. If not, then you will (risk an) update to several or even all rows.

Either way, you would need to preserve the old date to use it in the where clause. So you might as well keep track of the primary key which can be done in several ways. e.g.

echo "<input type='hidden' name='id[]' value='".$row['someid']."' >"

Then change the foreach loop to a for loop

if (isset($_POST['estimatedDelivery'])) {
	for ($i = 0; $i < count($_POST['estimatedDelivery']); ++$i) {
		'... SET ... = ' . $_POST['estimatedDeliver'][$i] . ' WHERE someid = ' . $_POST['someid'][$i];
	}
}

UnitedWeFall

Thanks for that.

Yeah the estimatedDelivery values are all unique and everything needs to be saved back in the right order.

So I guess using the $key won't work...

Is there another way to do it? This stuff is getting much more complex than I intended when I started writing this 😛

johanafm

no. Just change the name="id[]" to name="olddate" and set value to the exact same thing as for the shown input. Then use the olddate for the where clause.

UnitedWeFall

johanafm;10924608 wrote:
no. Just change the name="id[]" to name="olddate" and set value to the exact same thing as for the shown input. Then use the olddate for the where clause.

What will happen if there are 2 dates in the DB that are the same though? Won't the WHERE clause update both of them with the same new date?

johanafm

UnitedWeFall;10924667 wrote:
Yeah the estimatedDelivery values are all unique

I understood this as that wouldn't be the case. Reread my previous post. Use id (or whatever you have as primary key). Else, you will indeed update several rows with such a query.