This tutorial will teach you on how to edit data from database table.
This edit system is in PHP/MySQL withy PDO query.
to start this tutorial fallow the steps bellow:
Creating Our Database
First we are going to create our database which stores our data.
To create a database:
1. Open phpmyadmin
2. Then create database and name it as "pdo_ret".
3. After creating a database name, click the SQL and paste the following code.
CREATE TABLE IF NOT EXISTS `members` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fname` varchar(100) NOT NULL,
`lname` varchar(100) NOT NULL,
`age` int(5) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Creating our Database Connection
Next step is to create a database connection and save it as "connect.php". In this Step, we will write our connection script in PDO format.
<?php
/* Database config */
$db_host = 'localhost';
$db_user = 'root';
$db_pass = '';
$db_database = 'pdo_ret';
/* End config */
$db = new PDO('mysql:host='.$db_host.';dbname='.$db_database, $db_user, $db_pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
?>
Creating Our Display With Edit Action
The code bellow will retrieve data from our database table and provide an edit action. Copy the code bellow and save it as "index.php".
<table border="1" cellspacing="0" cellpadding="2" >
<thead>
<tr>
<th> First Name </th>
<th> Last Name </th>
<th> Age </th>
<th> Action </th>
</tr>
</thead>
<tbody>
<?php
include('connect.php');
$result = $db->prepare("SELECT * FROM members ORDER BY id DESC");
$result->execute();
for($i=0; $row = $result->fetch(); $i++){
?>
<tr class="record">
<td><?php echo $row['fname']; ?></td>
<td><?php echo $row['lname']; ?></td>
<td><?php echo $row['age']; ?></td>
<td><a href="editform.php?id=<?php echo $row['id']; ?>"> edit </a></td>
</tr>
<?php
}
?>
</tbody>
</table>
Creating Our Edit Form
The code bellow will be the landing page after we click the edit link in our display page. Copy the code bellow and save it as "editform.php".
<?php
include('connect.php');
$id=$_GET['id'];
$result = $db->prepare("SELECT * FROM members WHERE id= :userid");
$result->bindParam(':userid', $id);
$result->execute();
for($i=0; $row = $result->fetch(); $i++){
?>
<form action="edit.php" method="POST">
<input type="hidden" name="memids" value="<?php echo $id; ?>" />
First Name<br>
<input type="text" name="fname" value="<?php echo $row['fname']; ?>" /><br>
Last Name<br>
<input type="text" name="lname" value="<?php echo $row['lname']; ?>" /><br>
Age<br>
<input type="text" name="age" value="<?php echo $row['age']; ?>" /><br>
<input type="submit" value="Save" />
</form>
<?php
}
?>
Edit Script
This script will edit the data in our database table. Copy the code bellow and save it as "edit.php".
<?php
// configuration
include('connect.php');
// new data
$lname = $_POST['lname'];
$fname = $_POST['fname'];
$age = $_POST['age'];
$id = $_POST['memids'];
// query
$sql = "UPDATE members
SET fname=?, lname=?, age=?
WHERE id=?";
$q = $db->prepare($sql);
$q->execute(array($fname,$lname,$age,$id)); header("location: index.php");
?>
That's it you've been successfully created your edit script using PHP/MySQL with PDO query.
