Update mysql records

[kurumi]

Hi alls,
I have a db table like the following :

id company address
1 A USA
2 B UK
3 C Canada

And i am using two following script to display and edit the records

display.php
[code]
<?php
// Make a MySQL Connection
mysql_connect("localhost", "ota", "pass") or die(mysql_error());
mysql_select_db("order") or die(mysql_error());

// Get all the data from the "calendar" table
$result = mysql_query("SELECT id,company,address from calendar ORDER BY id ASC")
or die(mysql_error());
echo "<form action='edit.php' method='POST'>";
echo "<div align='center'>";
echo "<table border='1'>";
echo "<tr> <th>Id</th> <th>Company</th> <th>Address</th></tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
//echo "</td><td>";
echo "<input type='text' value='$row[id]'>";
echo "</td><td>";
echo "<input type='text' value='$row[company]'>";
echo "</td><td>";
echo "<input type='text' value='$row[address]'>";
echo "</td></tr>";
}
echo "</table>";
echo "</div>";
echo "<br>";
echo "<div align='center'>";

echo "<input type='submit' value='Save changes!'>";
echo "</div>";
echo "</form>";
?>
[/code]

edit.php

[code]

<?php

$connect=mysql_connect("localhost","ota","pass");
mysql_select_db("order",$connect);
$sql="UPDATE calendar SET address ='$_POST[$row[address]' WHERE id = '$_GET[$row[id]'";
$result=mysql_query ($sql,$connect);
echo $result;
?>
[/code]

As you can see i use $_POST to get the data from display.php and update the records in edit.php but somehow it doesn't work.So i wonder if you could point out my mistakes and help me please?

[ypirc]

You are missing some brackets... $_POST[$row[address] should be $_POST[$row[address]] and $_GET[$row[id] should be $_GET[$row[id]]

[Barand]

First, don't allow id changes, so don't put it in an edit field.

Second, none of your input fields have names so can't be identified in $_POST['fieldname']

I've rewritten it for so it will let you update several records and update them all. Note the field names have [$id] at the end of them so they are posted as an array and matching names and addreses can be identified.

form.php
[code]<?php
mysql_connect("localhost", "ota", "pass") or die(mysql_error());
mysql_select_db("order") or die(mysql_error());

// Get all the data from the "calendar" table
$result = mysql_query("SELECT id,company,address FROM calendar ORDER BY id ASC")
or die(mysql_error());
echo "<form action='edit.php' method='POST'>";
echo "<div align='center'>";
echo "<table border='1'>";
echo "<tr> <th>Id</th> <th>Company</th> <th>Address</th></tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
    // Print out the contents of each row into a table
    $id = $row['id'];
    echo "<tr><td>";
    echo "$id";
    echo "</td><td>";
    echo "<input type='text' name='company[$id]' value='{$row['company']}'>";
    echo "</td><td>";
    echo "<input type='text' name='address[$id]' value='{$row['address']}'>";
    echo "</td></tr>";
}
echo "</table>";
echo "</div>";
echo "<br>";
echo "<div align='center'>";

echo "<input type='submit' value='Save changes!'>";
echo "</div>";
echo "</form>";
?>[/code]

edit.php
[code]<?php
mysql_connect("localhost", "ota", "pass") or die(mysql_error());
mysql_select_db("order") or die(mysql_error());
// process changed records
if (isset($_POST['address'])) {
    foreach ($_POST['address'] as $editID => $newadd) {
             $newcomp = $_POST['company'][$editID]; // get matching company
             $sql="UPDATE calendar SET
                   address = '$newadd',
                   company = '$newcomp'
                   WHERE id = '$editID'";
             $result=mysql_query ($sql,$connect);
    }
}
header("Location: form.php"); //back to form.php
?>[/code]

[kurumi]

i am trying to understand the script you gave ...but it seems to be that there is a problem with the id array... Sorry if i can ask what do mean by
[code]
  foreach ($_POST['address'] as $editID => $newadd)
[/code]

[Barand]

When the edit.php script is called, the data in the $_POST array looks like (in my test data anyway)

[code]Array
(
    [company] => Array
        (
            [1] => A
            [2] => B
            [3] => C
            [4] => D
        )

    [address] => Array
        (
            [1] => USA
            [2] => UK
            [3] => Canada
            [4] => UK
        )

)[/code]

the loop
[code]     foreach ($_POST['address'] as $editID => $newadd) {
             $newcomp = $_POST['company'][$editID]; // get matching company
             $sql="UPDATE company SET
                   address = '$newadd',
                   company = '$newcomp'
                   WHERE id = '$editID'";
             $result=mysql_query ($sql,$connect);
    }[/code]

takes each address element in turn and puts the ID in $editID and the new address value in $newadd.
It then gets the company name for that ID from the company array and updates the record with the values.

[kurumi]

Oh,i got it...Thanks so much!You are so intelligent!It is very nice to meet you!