PHP Introduction | Database Access with PDO | Deleting and Updating Table Data with PDO

Tags:

Other necessary operations include deleting records and updating the contents of existing records. These are summarized below.

Deleting Records

delete from table where condition

Deletion is executed in the form delete from table. However, if you use only this, all records in the specified table will be deleted, so you need to add information about which records should be deleted. The syntax needed for this is the where clause, which was also used for searching.

When you set a where condition, records matching the condition are searched for and deleted. Be careful, because changing the condition can unexpectedly delete records.

As an actual usage example, let us write code that deletes records by specifying a name.

Submission form in index.php

        <table>
        <form method="post" action="./remove.php">
            <tr><td>Search word:</td><td><input type="text" name="name"></td></tr>
            <tr><td></td><td><input type="submit" value="Send"></td></tr>
        </form>
        </table>

remove.php

<?php
$name = htmlspecialchars($_POST['name']);
try {
    $pdo = new PDO("mysql:host=localhost:3306;dbname=mysampledata;charset=utf8", "root","1234");
    $query = "delete from sampletable where name = '$name'";
    $pdo->exec($query);
    //echo $query;
} catch(PDOException $e){
    echo "<html><body><h1>ERR:" . $e->getMessage() + "</h1></body></html>";
}
$pdo = null;
header('Location: index.php');

Prepare the form in index.php, enter the name of the record to delete, and the record with that name will be deleted.

Updating Records

update table set column = value where condition

Record updates use update table. After set, write the column name and the value to set in the form column = value. To change multiple columns, continue writing them separated by commas. For example, write something like set name = 'foo', mail = 'foo@ foo'.

This update also uses a where condition to specify which records should be updated. Then the columns of the found records are changed to the values set by set. If multiple records are found, all of their columns will be changed to the specified values, so make sure you do not make a mistake in the search condition.

Now you can perform basic operations such as creating, searching, updating, and deleting records. As you can see by reading through the examples again, most operations work through SQL queries rather than PHP itself. PDO is only executing SQL queries.

If you want to use databases seriously, you should study not only PDO but also how to use SQL properly. Once you can handle SQL well, it is no exaggeration to say that you have almost mastered the basics of using PDO.