Update form not updating data

[Gazz1982]

I have been trying to use some code to update a simple table on multiple columns.

For some reason the code below will present the data but if I alter the details the data is not updated, it just refreshes in the view with the old data.

The original code came from here, I've made some minor adjustments to make it 'work': http://www.phpeasystep.com/mysql/10.html

 

Thank you for any help

 

Gary

<?php
 $host="xxxx"; // Host name
 $username="xxxx"; // Mysql username
 $password="xxxx"; // Mysql password
 $db_name="xxxx"; // Database name
 $tbl_name="xxxx"; // Table name // Connect to server and select databse.

 
 mysql_connect("$host", "$username", "$password")or die("cannot connect");
 mysql_select_db("$db_name")or die("cannot select DB"); 
 $sql="SELECT * FROM $tbl_name";
 $result=mysql_query($sql); // Count table rows
 $count=mysql_num_rows($result);
 ?> 

 <table width="500" border="0" cellspacing="1" cellpadding="0"> <form name="form1" method="post" action="">
   <tr>
     <td>
       <table width="500" border="0" cellspacing="1" cellpadding="0">
         <tr>
           <td align="center"><strong>Id</strong></td>
           <td align="center"><strong>Name</strong></td>
           <td align="center"><strong>Lastname</strong></td>
           <td align="center"><strong>Email</strong></td>
         </tr> 

 <?php
   while($rows=mysql_fetch_array($result)){
 ?> 
          <tr>
   <td align="center"> 
     <?php $id[]=$rows['id']; echo $rows['id']; ?> 
   </td> 
   <td align="center"> 
     <input name="name[]" type="text" id="name" value="<?php echo $rows['name']; ?>">
   </td> 
   <td align="center"> 
     <input name="lastname[]" type="text" id="lastname" value="<?php echo $rows['lastname']; ?>">
   </td> 
   <td align="center"> 
     <input name="email[]" type="text" id="email" value="<?php echo $rows['email']; ?>">
   </td> </tr> <?php
 }
 ?> <tr>
   <td colspan="4" align="center">
     <input type="submit" name="Submit" value="Submit">
   </td>
 </tr>
 </table>
 </td>
 </tr> </form>
 </table> 

<?php // Check if button name "Submit" is active, do this
 if($Submit){
 for($i=0;$i<$count;$i++){
 $sql1="UPDATE $tbl_name SET name='".$name[$i]."', lastname='".$lastname[$i]."', email='".$email[$i]."' WHERE id='".$id[$i]."'";

 $result1=mysql_query($sql1);
 }
 }
 if($result1){
 header("location:update_multiple.php");
 }
 mysql_close();

[Barand]

http://uk1.php.net/manual/en/language.variables.external.php

[Gazz1982]

Problem solved, I decided to only allow the editing of single records, the coding from the example seemed wrong so here is what I did:

It still needs alteration and tidying.

 

list_records.php

$username="xxxx"; // Mysql username
$password="xxxx"; // Mysql password
$db_name="xxxx"; // Database name
$tbl_name="test_mysql"; // Table name
 mysql_connect("$host", "$username", "$password")or die("cannot connect");
 mysql_select_db("$db_name")or die("cannot select DB"); 

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
?>

<table width="400" border="1" cellspacing="0" cellpadding="3">
 <tr>
  <td >Name</td>
  <td >Lastname</td>
  <td >Email</td>
  <td >Update</td>
 </tr>

<?php
while($rows=mysql_fetch_array($result)){
?>

<tr>
<td><?php echo $rows['name']; ?></td>
<td><?php echo $rows['lastname']; ?></td>
<td><?php echo $rows['email']; ?></td>

// link to update.php and send value of id


<?php
echo "<td><a href='update.php?id=". $rows["id"] ."'>update</a></td>";

?>
</tr>
<?php
}
?>

</table>
</td>
</tr>
</table>

<?php
mysql_close();

update.php

<?php

$host="xxxx"; // Host name
$username="xxxx"; // Mysql username
$password="xxxx"; // Mysql password
$db_name="xxxx"; // Database name
$tbl_name="test_mysql"; // Table name
 mysql_connect("$host", "$username", "$password")or die("cannot connect");
 mysql_select_db("$db_name")or die("cannot select DB"); 


// get value of id that sent from address bar
$id=$_GET['id'];


// Retrieve data from database
$sql="SELECT * FROM $tbl_name WHERE id='$id'";
$result=mysql_query($sql);

$rows=mysql_fetch_array($result);?>


<?php
echo " 
<form name='form1' method='post' action='update_ac.php'>
<table width='400' border='1' cellspacing='1' cellpadding='0'>
<tr>
  <td>Name</td>
  <td>Lastname</td>
  <td>Email</td>
</tr>

";
?>
<?php
echo "<tr>";
echo "<td ><input name='name' type='text' id='name' value='". $rows['name'] ."'></td>";
echo "<td ><input name='lastname' type='text' id='lastname' value='". $rows['lastname'] ."'></td>";
echo "<td ><input name='email' type='text' id='email' value='". $rows['email'] ."'></td>";
?>

</tr>
<tr>

<?php
echo "<td><input name='id' type='hidden' id='id' value='". $rows['id'] ."'></td>";
echo "<td ><input type='submit' name='Submit' value='Submit'></td>";
?>

<?php

echo "  
</tr>
</table>
</td>
</form>
"; ?>





<?php
// close connection
mysql_close();
?>

update_ac.php

<?php
//include '../connect.php';
$host="xxxx"; // Host name
$username="xxxx"; // Mysql username
$password="xxxx"; // Mysql password
$db_name="xxxx"; // Database name
$tbl_name="test_mysql"; // Table name
 mysql_connect("$host", "$username", "$password")or die("cannot connect");
 mysql_select_db("$db_name")or die("cannot select DB"); 

$id=$_POST["id"];
$name=$_POST["name"];
$lastname=$_POST["lastname"];
$email=$_POST["email"];

// update data in mysql database

//$sql="UPDATE $tbl_name SET name='".$name."', lastname='".$lastname."', email='".$email."' WHERE id='$id'";
$sql="UPDATE $tbl_name SET name='$name', lastname='$lastname', email='$email' WHERE id='$id'";

$result=mysql_query($sql);

// if successfully updated.

if($result){
echo "Successful";
echo "<BR>";
echo "<a href='list_records.php'>View result</a>";
print "$name";
}

else {
echo "ERROR";
}

?> 

<?php
mysql_close();
?>

[benanamen]

The Mysql code you are using is obsolete and insecure. It has also been removed from Php ver 7. You need to be using PDO with prepared statements or at the least Mysqli.

 

Your connection code should only be in one place and included where you need it. As is, you need to do updates in three different places if anything changes.

 

Your code is vulnerable to SQL injection. You are sending user supplied data directly to the database. You should not be using this code at all.

 

There is also no need to create extra variables on your insert. Additionally, there is no need to close the connection. It is automatically closed after the script runs.