1. Home
  2. Knowledge Base
  3. Customizations
  4. How to export listings using SQL

How to export listings using SQL

Exporting entries from Business Directory Plugin is easiest using the built in exporting options. If you'd like to get the listings using SQL, here's an example to get you started.

SELECT t.name 'Category', p.post_title 'Company', GROUP_CONCAT(pm.meta_value) 'Contact', p.post_content 'Description'
FROM wp_terms t, wp_term_taxonomy tx, wp_posts p, wp_postmeta pm, wp_term_relationships tr
WHERE pm.post_id = p.ID AND
tr.object_id = p.id AND
p.post_type = 'wpbdp_listing' AND
pm.meta_key in ('_wpbdp[fields][6]','_wpbdp[fields][8]','_wpbdp[fields][10]','_wpbdp[fields][11]','_wpbdp[fields][12]') AND
tr.term_taxonomy_id = tx.term_taxonomy_id AND
tx.term_id = t.term_id
GROUP BY pm.post_id order by t.name, pm.meta_key

Line 1: pm.meta_value represents the fields that are in the form used for entering the directory date, e.g., address, city, state, etc.
the GROUP_CONCAT command strings together the data from individual rows into a string separated by a comma.
Line 2: These are the tables that hold the data to be extracted
Line 3-8: These are the columns used to link the tables together and also contain the data to be extracted. Note that the meta_key field contents may differ in your case; the above are the values that correspond to the address, city, state, zip, etc. in my database.
Line 9: This command groups the extracted data by post_id and sorts the output by category and meta_key (address, city, state, etc.)

Related Articles

Need Support?

Can't find the answer you're looking for?
Contact Support