A A A

Please consider registering
guest

Log In Register

Register | Lost password?
Advanced Search:

— Forum Scope —



— Match —



— Forum Options —




Wildcard usage:
*  matches any number of characters    %  matches exactly one character

Minimum search word length is 3 characters - maximum search word length is 84 characters

Topic RSS
How to export listings from BD using SQL
bizdirectorysupport
Member
February 8, 2013 - 5:10 pm
Member Since: July 18, 2012
Forum Posts: 5042
Offline

Exporting entries from WP_BUSINESS_DIRECTORY Plugin.

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

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

Explanation:
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.)

 

NOTE: Line numbers should be removed before running the query.

Forum Timezone: UTC 0

Most Users Ever Online: 103

Currently Online: TallMeerkat, giraffeweb
14 Guest(s)

Currently Browsing this Page:
1 Guest(s)

Top Posters:

Member Stats:

Guest Posters: 1699

Members: 660

Moderators: 0

Admins: 1

Forum Stats:

Groups: 1

Forums: 4

Topics: 3364

Posts: 14584

Newest Members: test222

Moderators:

Administrators: Business Directory Support (1313)

Directory powered by Business Directory Plugin