Update data present in database by php

Question

Created a table "test_mysql" in database "test" I have 3 files 1. list_records.php 2. update.php 3. update_ac.php

Databse code

Create database and table using phpMyAdmin

CREATE TABLE `test_mysql` (
`id` int(4) NOT NULL auto_increment,
`name` varchar(65) NOT NULL default '',
`lastname` varchar(65) NOT NULL default '',
`email` varchar(65) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=7 ;
-- 
-- Dumping data for table `test_mysql`
--
INSERT INTO `test_mysql` VALUES (1, 'Billly', 'Blueton', 'bb5@phpeasystep.com');
INSERT INTO `test_mysql` VALUES (2, 'Jame', 'Campbell', 'jame@somewhere.com');
INSERT INTO `test_mysql` VALUES (3, 'Mark', 'Jackson', 'mark@phpeasystep.com');
INSERT INTO `test_mysql` VALUES (4, 'Linda', 'Travor', 'lin65@phpeasystep.com');
INSERT INTO `test_mysql` VALUES (5, 'Joey', 'Ford', 'fordloi@somewhere.com');
INSERT INTO `test_mysql` VALUES (6, 'Sidney', 'Gibson', 'gibson@phpeasystep.com');

---

list_records.php

<?php
$host="localhost"; // Host name 
$username=""; // Mysql username 
$password=""; // Mysql password 
$db_name="test"; // Database name 
$tbl_name="test_mysql"; // Table name 

// Connect to server and select database.
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="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="400" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="4"><strong>List data from mysql </strong> </td>
</tr>

<tr>
<td align="center"><strong>Name</strong></td>
<td align="center"><strong>Lastname</strong></td>
<td align="center"><strong>Email</strong></td>
<td align="center"><strong>Update</strong></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 
<td align="center"><a href="update.php?id=<?php echo $rows['id']; ?>">update</a></td>
</tr>

<?php
}
?>

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

<?php
mysql_close();
?>

---

update.php

<?php
$host="localhost"; // Host name 
$username=""; // Mysql username 
$password=""; // Mysql password 
$db_name="test"; // Database name 
$tbl_name="test_mysql"; // Table name

// Connect to server and select database.
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);
?>

<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<form name="form1" method="post" action="update_ac.php">
<td>
<table width="100%" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>&nbsp;</td>
<td colspan="3"><strong>Update data in mysql</strong> </td>
</tr>
<tr>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
</tr>
<tr>
<td align="center">&nbsp;</td>
<td align="center"><strong>Name</strong></td>
<td align="center"><strong>Lastname</strong></td>
<td align="center"><strong>Email</strong></td>
</tr>
<tr>
<td>&nbsp;</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']; ?>" size="15">
</td>
<td>
<input name="email" type="text" id="email" value="<?php echo $rows['email']; ?>" size="15">
</td>
</tr>
<tr>
<td>&nbsp;</td>
<td>
<input name="id" type="hidden" id="id" value="<?php echo $rows['id']; ?>">
</td>
<td align="center">
<input type="submit" name="Submit" value="Submit">
</td>
<td>&nbsp;</td>
</tr>
</table>
</td>
</form>
</tr>
</table>

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

---

update_ac.php

<?php
$host="localhost"; // Host name 
$username=""; // Mysql username 
$password=""; // Mysql password 
$db_name="test"; // Database name 
$tbl_name="test_mysql"; // Table name 
$id='id';
$name="name";
$lastname="lastname";
$email="email";

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// update data in mysql database 
$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>";
}

else {
echo "ERROR";
}

?>

Now the problem is after applying all this code I am still unable to update any row..PLS HELP ME OUT :( it shows updates succesfully but it doesnot update anything.

Answer 1

update_ac.php

modify

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

also in update.php better to use $id=intval($_GET['id']);

Answer 2

I got your problem and it is here

$id='id';

Instead of giving numeric value you are giving characters which is invalid, Give proper id value to your query.

Currently your query reads like this

$sql="UPDATE test_mysql SET name='name', lastname='lastname', email='email' WHERE id='id'";

here your where clause is failing.