Jump to content

Update mysql records


kurumi

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?

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.