How to delete a particular row in database table in wordpress using wpdb?

Question

I tried everything but I dont' know to delete a particular row in table in wordpress.

Some one please help how to delete a particular row in a table and i am doing it wordpress plugin.

Some people said use wpdb, but i dont know how to do it

Here my code

<table class="wp-list-table widefat fixed striped users" id="list" style="margin-top:10px;">
<tr>
<th class="center"><span>ID</span></th>
<th class="center"><span>Title</span></th>
<th class="center"><span>Action</span></th>
</tr>
<?php 
if(!empty($event_list))
{
$arrlen = count($event_list);
for ($i=0; $i<$arrlen; $i++)
{
?>
<tr>
<td><?php echo $event_list[$i]['id'] ?></td>
<td><?php echo $event_list[$i]['title'] ?></td>
<td><a class="delete" href="<?php echo site_url();?>/wp-admin/admin.php?page=newslist">Delete</a></td>
</tr>
<?php
}
}
else
{
?>
<td>No Records Found</td>
<?php }?>
</table>

Answer 1

You can use the $wpdb’s delete method:

global $wpdb;
$wpdb->delete( $wpdb->posts, [ 'ID' => 1 ], [ '%d' ] );
// the code above will delete the post with ID 1
// (this is just an example; if you need to delete a post,
// use wp_delete_post() instead)

It accepts the following arguments:

• Name of the table. Usually, tables in WordPress have a prefix (like something_tablename), and this prefix is set during the installation of WordPress, and will be different in different sites. You can prepend it using $wpdb->prefix (like $wpdb->prefix . 'tablename'). Standard tables can be accessed like $wpdb->tablename (so, $wpdb->posts is the same thing as $wpdb->prefix . 'posts'), but this only works for tables created by WordPress itself, not for tables created by you.
• Array specifying which rows need to be deleted. Each item in the array specifies one condition, the array key is the field name, the array value is the field value. So ['field' => 123] would delete rows if they have a `field` = '123'. The conditions are joined using the AND logics, so if you specify ['field' => 123, 'otherfield' => 456], the row would only be deleted if it has both `field` = '123' AND `otherfield` = '456'.
• The third argument can specify format of the values for the fields. The number of items in the third-argument array should correspond to the number of items in the second-argument array. '%d' means integer, %s means string (this is the default value), and %f means a floating-point value.

The documentation for $wpdb can be found on WordPress class reference page.

If you need more complex delete logics (not just field = 'something', but other comparisons), you might need to write SQL yourself, and execute it with $wpdb->query(...), see Running General Queries in the documentation.

---

About your code.

Currently, your code, the delete link doesn’t store any information about what you want to delete. You need to pass it, like admin.php?page=newslist&eventid=<?php echo esc_attr($event_list[$i]['id']); >. Then, you can access the eventid to delete using $_GET['eventid'], like this:

if (!empty($_GET['eventid'])) {
  $wpdb->delete( $wpdb->prefix . 'events',
                 [ 'ID' => $_GET['eventid'] ],
                 [ '%d' ] );
}

This is not a recommended way to do this, since you’re creating a GET request. GET requests should be used for fetching the information, not for chaning it, and deleting something is definitely a change.

GET requests store all the information in the URL, so it means user can accidentally bookmark a 'delete' page, which is not something we want. Also, security plugins can usually detect and block cross-site POST requests, but not GET requests. So, your site would be vunerable (although this can be mitigated by using nonces).

The recommended way is to make delete actions into POST requests. For this, you can use forms, like this:

<tr>
  <td><?php echo $event_list[$i]['id'] ?></td>
  <td><?php echo $event_list[$i]['title'] ?></td>
  <td>
    <form action="<?php echo admin_url('admin-post.php'); ?>" method="POST">
      <!-- nonce should go here for security -->
      <input type="hidden" name="action" value="my_delete_event">
      <input type="hidden" name="eventid" value="<?php echo $event_list[$i]['id']; ?> ">
      <input type="submit" class="delete" value="Delete" />
    </form>
  </td>
</tr>

For this code to work, you need to register an action my_delete_event (change this with any name you want) that would actually delete events, and then redirect to your page:

//put this code outside of all the functions
//it should be run when plugin is initialised

add_action( 'admin_post_my_delete_event', function () {
  // Remove the event with specified eventid
  if (!empty($_POST['eventid'])) {
    // Nonce verification should go here, for security
    $wpdb->delete( $wpdb->prefix . 'events',
                   [ 'ID' => $_POST['eventid'] ],
                   [ '%d' ] );
  }
  //Redirect to admin.php?page=newslist
  wp_redirect(admin_url('/admin.php?page=newslist'));
  exit;
});

This is better, but still not secure enough. Although an attacker can’t create a link, they can still create the same form on their site, and trick the site admin into clicking on a button in this form. This is not something we want, and to fix this, WordPress has a concept of nonces.

A nonce is a piece of information that is generated by WordPress that attackers don’t know. You attach it to your form (or URL in the link), and check before doing the deletion. If it’s correct, then a real admin is doing the deletion. To add a nonce, you can use something like this:

<tr>
  <td><?php echo $event_list[$i]['id'] ?></td>
  <td><?php echo $event_list[$i]['title'] ?></td>
  <td>
    <form action="<?php echo admin_url('admin-post.php'); ?>" method="POST">
      <?php wp_nonce_field( 'my_delete_event_' . $event_list[$i]['id'] ); ?>
      <input type="hidden" name="action" value="my_delete_event">
      <input type="hidden" name="eventid" value="<?php echo $event_list[$i]['id']; ?> ">
      <input type="submit" class="delete" value="Delete" />
    </form>
  </td>
</tr>

Instead of 'my_delete_event_' . $event_list[$i]['id'] you can use any string you want, but it’s better if it includes the event ID of the deleted event.

And to verify that a correct nonce is passed, you can use the function check_admin_referer:

//put this code outside of all the functions
//it should be run when plugin is initialised

add_action( 'admin_post_my_delete_event', function () {
  // Remove the event with specified eventid
  if (!empty($_POST['eventid'])) {
    $event_id = $_POST['eventid'];
    check_admin_referer( 'my_delete_event_' . $event_id );
    $wpdb->delete( $wpdb->prefix . 'events',
                   [ 'ID' => $event_id ],
                   [ '%d' ] );
  }
  //Redirect to admin.php?page=newslist
  wp_redirect(admin_url('/admin.php?page=newslist'));
  exit;
});

check_admin_referer would check if the same string was used in the form and in the delete code, so if you change your string in wp_nonce_field, make sure you also change it in check_admin_referer. The string can be arbitrary, but it should be same in both cases.

check_admin_referer just stops everything and displays an error if the nonce is wrong, so we don’t need an if (...) { ... } statement.

Actually, check_admin_referer checks not just nonce but also the browsers Referer page, to make sure the request is coming from your site and not from some external page, and wp_nonce_field also inserts the expected Referer field to compare.

I hope this information should be enough for you to complete your plugin. If no, please ask for more information. Good luck! :)