With this (testing) code I create a form to update prices in my database. (colums: id, omschrijving, prijs). The textboxes have the same id as the id-number in the database.
I would like to receive some help to write the update query. The query must handle all the id's, not row by row (id1 = field1, id2 =field2 etc), because the number of items is changing in the coming time.
I'm just a beginner, any help would be highly appriciated.

<?php
include 'connect.php';
$query = 'SELECT * FROM prijzen';
$sql = mysql_query($query) or die ( mysql_error( ) );
echo'<form method="POST" action="test3.php"><table width="100%" border="0">' ;
while($record = mysql_fetch_object($sql)){
echo'<tr><td>'.$record->omschrijving.'</td><td>€&nbsp;<input type="text" name="'.$record->id.'" size="6" value="'.$record->prijs.'"></td></tr>';
}
echo'</table><p><input type="submit" value="send" ></form>';
?>

    This is how I would do it:

    <?php
include 'connect.php';
$query = 'SELECT * FROM prijzen';
$sql = mysql_query($query) or die ( mysql_error( ) );
echo'<form method=\"POST\" action=\"test3.php\"><table width=\"100%\" border=\"0\">' ;

while($record = mysql_fetch_object($sql)){
echo'<tr><td>'.$record->omschrijving.'</td><td>€&nbsp;<input type="text" name="newprice[]" size="6" value="'.$record->prijs.'">
<input type=\"hidden\" name=\"id[]\" value=\"".$record->id."\" />
</td></tr>';
}
echo'</table><p><input type=\"submit\" value=\"send\" ></form>';
?>

    test3.php:

    <?php

$newprice = $_POST['newprice'];
$id = $_POST['id'];

$price_count = count($newprice);

for ($i=0; $i < $price_count; $i++) { 
          $query = "UPDATE prijzen
                           SET prijs = '".$newprice[$i]."' 
                           WHERE id = '".$id[$i]."'";
          mysql_query($query) or die(mysql_error());
}
echo "Records updated";
?>

    I changed the input name from your form to newprice[] so that it will build an array of the prices. Also a hidden field in yor form to hold the record's id.

    Then on the second page, count() produces the number of prices so the 'for loop' can go through and add each one as it goes.

    You also had not escaped your quote marks using \

    NOTE: You can not return an empty field with this method as the id and newprice arrays will lose sync.

    Probably not the most efficient way but it works for me.

    Will

      Write a Reply...