Jump to content


Photo

Update mysql records


  • Please log in to reply
5 replies to this topic

#1 kurumi

kurumi
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 25 April 2006 - 06:33 PM

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
 <?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>";
?> 

edit.php


<?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;
?>

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?



#2 ypirc

ypirc
  • Members
  • PipPipPip
  • Advanced Member
  • 54 posts

Posted 25 April 2006 - 07:01 PM

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

#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 25 April 2006 - 08:09 PM

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
<?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>";
?>

edit.php
<?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
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 kurumi

kurumi
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 26 April 2006 - 05:50 AM

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
  foreach ($_POST['address'] as $editID => $newadd)


#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 26 April 2006 - 06:14 AM

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

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

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

)

the loop
     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);
    }

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 kurumi

kurumi
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 26 April 2006 - 06:38 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users