How to delete listings via SQL

How do I delete a large number of listings in Business Directory?

Business Directory has an admin interface to handle listing administration but sometimes, you need to delete everything (for re-import if you made a mistake) and start over. Here’s how you handle that with SQL queries.

NOTE: These instructions assume that you are familiar with SQL and database queries and a database admin tool such as phpMyAdmin. If you are not comfortable with this, you should consult someone who is or hire someone to help you if you don’t know of a SQL expert.

Removing BD-related content from the database

This guide is only for those who want to remove a big number of BD-created content from their databases without having to use the admin screens available on the backend.
Please backup your database before executing any of the SQL queries from this guide.

From this point forward we assume your WordPress tables are prefixed with wp_ which is the default setting. If not, adjust the queries accordingly.

Introduction

If you are trying to remove content (listings, categories, tags, fields, fees, etc.) created by BD from your database, the most secure way to do is to use the delete functionality that is available on the relevant admin screens.

BD takes care of removing all information from the database and also from the file system (images and file attachments).

In some cases you might want to remove data directly via a SQL query due to the volume of data to be deleted. The SQL queries below will help you with that.

Delete all listings and their associated metadata

DELETE a, b, c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id )
LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
WHERE a.post_type = 'wpbdp_listing';

DELETE FROM wp_wpbdp_payments;
DELETE FROM wp_wpbdp_payments_items;

Please keep in mind that these queries don’t remove images or attachments associated to listings, since those are not stored inside the database but in the file system.

Delete all listing categories and tags

DELETE a, b
FROM wp_term_taxonomy a
LEFT JOIN wp_terms b ON (a.term_id = b.term_id)
WHERE a.taxonomy IN ( 'wpbdp_tag', 'wpbdp_category' );

Delete all BD data (with the plugin installed)

If you intend to remove all BD data (listings, categories, tags, settings, etc.) and associated tables, you might want to run the uninstall procedure available via Directory → Settings → Miscellaneous → Uninstall.

This makes sure your WP setup is as if BD was never installed.

Delete all BD data (after the plugin has been removed)

Uninstalling the plugin as explained above removes all BD tables from your database but if, for some reason, you removed the plugin without uninstalling it first, you might want to completely delete the content it created and its tables manually.

To do so, follow the steps below:

1. Delete all content from the database. See Delete all listings and their associated metadata and Delete all listing categories and tags.
2. Delete all BD settings by running this query:
   
DELETE FROM wp_options WHERE option_name LIKE 'wpbdp%';

3. Remove all tables prefixed wp_wpbdp from the database.

Delete Regions data

Here is the SQL script to delete all data associated to regions, the only thing to consider here is table prefix wp_ depending on hostings the prefix may change, for instance a shared hosting plan uses an specific prefix to prevent different sites point to same db.

-- Delete regionmeta table

DROP TABLE IF EXISTS wp_wpbdp_regionmeta;

-- Delete Regions Fields
DELETE FROM `wp_wpbdp_form_fields` WHERE association = 'region';

-- Delete Region Terms
DELETE FROM `wp_terms` WHERE term_id IN (SELECT term_id FROM `wp_term_taxonomy` WHERE taxonomy = 'wpbdm-region');

-- Delete Regions term relationships
DELETE FROM `wp_term_relationships` WHERE term_taxonomy_id IN (SELECT term_taxonomy_id FROM `wp_term_taxonomy` WHERE taxonomy = 'wpbdm-region');

-- Delete Regions Term taxonomies
DELETE FROM `wp_term_taxonomy` WHERE taxonomy = 'wpbdm-region';