Edit/Update Data Using PHP/MySQL with PDO Query

[theargonath]

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.

1. CREATE TABLE IF
   
   NOT EXISTS `members` (
   
2. `id` int(
   
   11
   
   )
   
   NOT NULL
   
   AUTO_INCREMENT,
   
3. `fname` varchar(
   
   100
   
   )
   
   NOT NULL
   
   ,
   
4. `lname` varchar(
   
   100
   
   )
   
   NOT NULL
   
   ,
   
5. `age` int(
   
   5
   
   )
   
   NOT NULL
   
   ,
   
6. PRIMARY KEY
   
   (
   
   `id`)
   
7. )
   
   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.

1. <?php
   
2. /* Database config */
   
3. $db_host
   
   =
   
   'localhost'
   
   ;
   
4. $db_user
   
   =
   
   'root'
   
   ;
   
5. $db_pass
   
   =
   
   ''
   
   ;
   
6. $db_database
   
   =
   
   'pdo_ret'
   
   ;
   
7. 
   
8. /* End config */
   
9. 
   
10. $db
    
    =
    
    new
    
    PDO(
    
    'mysql:host='
    
    .
    
    $db_host
    
    .
    
    ';dbname='
    
    .
    
    $db_database
    
    ,
    
    $db_user
    
    ,
    
    $db_pass
    
    )
    
    ;
    
11. $db
    
    ->
    
    setAttribute
    
    (
    
    PDO::
    
    ATTR_ERRMODE
    
    ,
    
    PDO::
    
    ERRMODE_EXCEPTION
    
    )
    
    ;
    
12. ?>
    

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".

1. <table border="1" cellspacing="0" cellpadding="2" >
   
2. <thead>
   
3. <tr>
   
4. <th> First Name </th>
   
5. <th> Last Name </th>
   
6. <th> Age </th>
   
7. <th> Action </th>
   
8. </tr>
   
9. </thead>
   
10. <tbody>
    
11. <?php
    
12. include
    
    (
    
    'connect.php'
    
    )
    
    ;
    
13. $result
    
    =
    
    $db
    
    ->
    
    prepare
    
    (
    
    "SELECT * FROM members ORDER BY id DESC"
    
    )
    
    ;
    
14. $result
    
    ->
    
    execute
    
    (
    
    )
    
    ;
    
15. for
    
    (
    
    $i
    
    =
    
    0
    
    ;
    
    $row
    
    =
    
    $result
    
    ->
    
    fetch
    
    (
    
    )
    
    ;
    
    $i
    
    ++
    
    )
    
    {
    
16. ?>
    
17. <tr class="record">
    
18. <td><?php
    
    echo
    
    $row
    
    [
    
    'fname'
    
    ]
    
    ;
    
    ?>
    
    </td>
    
19. <td><?php
    
    echo
    
    $row
    
    [
    
    'lname'
    
    ]
    
    ;
    
    ?>
    
    </td>
    
20. <td><?php
    
    echo
    
    $row
    
    [
    
    'age'
    
    ]
    
    ;
    
    ?>
    
    </td>
    
21. <td><a href="editform.php?id=<?php
    
    echo
    
    $row
    
    [
    
    'id'
    
    ]
    
    ;
    
    ?>
    
    "> edit </a></td>
    
22. </tr>
    
23. <?php
    
24. }
    
25. ?>
    
26. </tbody>
    
27. </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".

1. <?php
   
2. include
   
   (
   
   'connect.php'
   
   )
   
   ;
   
3. $id
   
   =
   
   $_GET
   
   [
   
   'id'
   
   ]
   
   ;
   
4. $result
   
   =
   
   $db
   
   ->
   
   prepare
   
   (
   
   "SELECT * FROM members WHERE id= :userid"
   
   )
   
   ;
   
5. $result
   
   ->
   
   bindParam
   
   (
   
   ':userid'
   
   ,
   
   $id
   
   )
   
   ;
   
6. $result
   
   ->
   
   execute
   
   (
   
   )
   
   ;
   
7. for
   
   (
   
   $i
   
   =
   
   0
   
   ;
   
   $row
   
   =
   
   $result
   
   ->
   
   fetch
   
   (
   
   )
   
   ;
   
   $i
   
   ++
   
   )
   
   {
   
8. ?>
   
9. <form action="edit.php" method="POST">
   
10. <input type="hidden" name="memids" value="<?php
    
    echo
    
    $id
    
    ;
    
    ?>
    
    " />
    
11. First Name<br>
    
12. <input type="text" name="fname" value="<?php
    
    echo
    
    $row
    
    [
    
    'fname'
    
    ]
    
    ;
    
    ?>
    
    " /><br>
    
13. Last Name<br>
    
14. <input type="text" name="lname" value="<?php
    
    echo
    
    $row
    
    [
    
    'lname'
    
    ]
    
    ;
    
    ?>
    
    " /><br>
    
15. Age<br>
    
16. <input type="text" name="age" value="<?php
    
    echo
    
    $row
    
    [
    
    'age'
    
    ]
    
    ;
    
    ?>
    
    " /><br>
    
17. <input type="submit" value="Save" />
    
18. </form>
    
19. <?php
    
20. }
    
21. ?>
    

Edit Script

This script will edit the data in our database table. Copy the code bellow and save it as "edit.php".

1. <?php
   
2. // configuration
   
3. include
   
   (
   
   'connect.php'
   
   )
   
   ;
   
4. 
   
5. // new data
   
6. $lname
   
   =
   
   $_POST
   
   [
   
   'lname'
   
   ]
   
   ;
   
7. $fname
   
   =
   
   $_POST
   
   [
   
   'fname'
   
   ]
   
   ;
   
8. $age
   
   =
   
   $_POST
   
   [
   
   'age'
   
   ]
   
   ;
   
9. $id
   
   =
   
   $_POST
   
   [
   
   'memids'
   
   ]
   
   ;
   
10. // query
    
11. $sql
    
    =
    
    "UPDATE members
    
12. SET fname=?, lname=?, age=?
    
13. WHERE id=?"
    
    ;
    
14. $q
    
    =
    
    $db
    
    ->
    
    prepare
    
    (
    
    $sql
    
    )
    
    ;
    
15. $q
    
    ->
    
    execute
    
    (
    
    array
    
    (
    
    $fname
    
    ,
    
    $lname
    
    ,
    
    $age
    
    ,
    
    $id
    
    )
    
    )
    
    ;
    
16. header
    
    (
    
    "location: index.php"
    
    )
    
    ;
    
17. 
    
18. ?>
    

That's it you've been successfully created your edit script using PHP/MySQL with PDO query.

Download