Jump to content

Archived

This topic is now archived and is closed to further replies.

kurumi

Update mysql records

Recommended Posts

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?

Share this post


Link to post
Share on other sites
You are missing some brackets... $_POST[$row[address] should be $_POST[$row[address]] and $_GET[$row[id] should be $_GET[$row[id]]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Oh,i got it...Thanks so much!You are so intelligent!It is very nice to meet you!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.