SQL Archives - Freelance Full Stack Developer in London, UK

May 12

Exporting Forum data from BBPress

By louie171 | BBPress, Migration, SQL | No Comments

This is how to export data from BBPress, which would be useful if you wanted to migrate from BBPress to another forum platform.

Its straight SQL export direct from the mysql database, with things like forum posts (topics ) and forum replies, as well as some other data including users ( and their user ids etc ).

I’ve exported the forum data into a table, then escaped the quote marks and finally output into a csv (comma separated list ) file that can be used to suck into the relevant system , where you’re moving the BBPress forum to.

# get the data into a temporary table 
drop table export_for_new_site;
CREATE TABLE export_for_new_site SELECT
    forum.post_title AS forum,
    p.id AS topic_post_id,
    p.post_title AS topic,
    p.post_date as topic_date,
    p.post_content AS topic_content,
    topic_poster.ID AS poster_user_id,
    topic_poster.user_email AS poster_email,
    topic_poster.display_name AS poster_name,

    topic_poster.user_login AS poster_user_login,
    topic_poster.user_nicename AS poster_user_nicename,
    topic_poster.user_registered AS poster_user_registered,

    replies.id AS reply_post_id,
    replies.post_content AS reply,
    replies.post_date,
    replier_name.display_name AS repliter_name,
    replier_name.ID AS replier_user_id,
    replier_name.user_email AS replier_email,

    replier_name.user_login AS replier_user_login,
    replier_name.user_nicename AS replier_user_nicename,
    replier_name.user_registered AS replier_user_registered,


    p.ID,
    p.post_type
FROM
    wp_posts p
INNER JOIN wp_postmeta ON(p.ID = wp_postmeta.post_id)
LEFT JOIN wp_posts replies ON
    (
        p.ID = replies.post_parent AND replies.post_type = 'reply' AND replies.post_status = 'publish'
    )
LEFT JOIN wp_posts forum ON
    (
        p.post_parent = forum.ID AND forum.post_type = 'forum'
    )
LEFT JOIN wp_users topic_poster ON
    (
        p.post_author = topic_poster.ID
    )
LEFT JOIN wp_users replier_name ON
    (
        replies.post_author = replier_name.ID
    )
LEFT JOIN wp_posts topic_file ON
    (
        p.ID = topic_file.post_parent AND topic_file.post_type = 'attachment' AND topic_file.post_status = 'inherit'
    )
LEFT JOIN wp_posts reply_file ON
    (
        replies.ID = reply_file.post_parent AND reply_file.post_type = 'attachment' AND reply_file.post_status = 'inherit'
    )
WHERE
    1 = 1 AND(
        wp_postmeta.meta_key = '_bbp_last_active_time'
    ) AND p.post_type = 'topic' AND(p.post_status = 'publish')
ORDER BY
    forum.id
DESC;
    


# escape  double quotes  ( so when csv export = good )
UPDATE export_for_new_site SET topic_content = REPLACE( topic_content ,'"','""');
UPDATE export_for_new_site SET reply = REPLACE( reply ,'"','""');

# generate the csv                                                                                                 
SELECT * from export_for_new_site INTO OUTFILE 'YOUR PATH HERE/forum_stuff2.csv' FIELDS TERMINATED BY ',' ESCAPED BY '' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

# get the data into a temporary table

drop table export_for_new_site;

CREATE TABLE export_for_new_site SELECT

forum.post_title AS forum,

p.id AS topic_post_id,

p.post_title AS topic,

p.post_date as topic_date,

p.post_content AS topic_content,

topic_poster.ID AS poster_user_id,

topic_poster.user_email AS poster_email,

topic_poster.display_name AS poster_name,

topic_poster.user_login AS poster_user_login,

topic_poster.user_nicename AS poster_user_nicename,

topic_poster.user_registered AS poster_user_registered,

replies.id AS reply_post_id,

replies.post_content AS reply,

replies.post_date,

replier_name.display_name AS repliter_name,

replier_name.ID AS replier_user_id,

replier_name.user_email AS replier_email,

replier_name.user_login AS replier_user_login,

replier_name.user_nicename AS replier_user_nicename,

replier_name.user_registered AS replier_user_registered,

p.ID,

p.post_type

FROM

wp_posts p

INNER JOIN wp_postmeta ON(p.ID = wp_postmeta.post_id)

LEFT JOIN wp_posts replies ON

(

p.ID = replies.post_parent AND replies.post_type = 'reply' AND replies.post_status = 'publish'

)

LEFT JOIN wp_posts forum ON

(

p.post_parent = forum.ID AND forum.post_type = 'forum'

)

LEFT JOIN wp_users topic_poster ON

(

p.post_author = topic_poster.ID

)

LEFT JOIN wp_users replier_name ON

(

replies.post_author = replier_name.ID

)

LEFT JOIN wp_posts topic_file ON

(

p.ID = topic_file.post_parent AND topic_file.post_type = 'attachment' AND topic_file.post_status = 'inherit'

)

LEFT JOIN wp_posts reply_file ON

(

replies.ID = reply_file.post_parent AND reply_file.post_type = 'attachment' AND reply_file.post_status = 'inherit'

)

WHERE

1 = 1 AND(

wp_postmeta.meta_key = '_bbp_last_active_time'

) AND p.post_type = 'topic' AND(p.post_status = 'publish')

ORDER BY

forum.id

DESC;

# escape double quotes ( so when csv export = good )

UPDATE export_for_new_site SET topic_content = REPLACE( topic_content ,'"','""');

UPDATE export_for_new_site SET reply = REPLACE( reply ,'"','""');

# generate the csv

SELECT * from export_for_new_site INTO OUTFILE 'YOUR PATH HERE/forum_stuff2.csv' FIELDS TERMINATED BY ',' ESCAPED BY '' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

Aug 10

0

SQL for use in migrating a Drupal website to WordPress

By louie171 | Drupal, Drupal to Wordpress Migration, SQL | No Comments

Currently I’m working on migrating a Drupal 7 website to WordPress, in the process I’ve learned alot about the Drupal database schema. So this post is a place for me to store handy queries and notes. Its a Work in Progress so I’ll add more over the coming weeks.

Content types in Drupal are similar to post types in WordPress where fields can be added, however in the database how the fields are handled is very different from WordPress. In Drupal fields are added as separate tables.

SQL to get multiple images ( stored as a collection item field in Drupal)

The field is called field_images stored in the table field_data_field_images table. Because the field is collection there is only 1 value stored in the field_data_field_images table ( the collection id). To get the individual items of the collection (images in this case), we need to join to file usage table on that id then to file_managed table on the fid. Then we have the information about the images ( filenames etc ).

select n.nid, n.title, m.* from node n
left join field_data_field_images f on n.nid = f.entity_id
left join file_usage u on f.field_images_value = u.id
left join file_managed m on u.fid = m.fid
where n.nid = node_id

Get all fields on a content_type

eg for content type ‘bobs_tyre’

SELECT field_name FROM field_config_instance ci WHERE ci.bundle = ‘bobs_tyre’

Jan 22

0

WordPress SQL snippets

By louie171 | PHP, SQL, Wordpress | No Comments

A place to keep snippets of useful WordPress SQL.

Do a select based on some meta field of a post ( i.e. search for a post that has a particular metafield )

SELECT  p.ID,p.post_title,
        MAX(CASE WHEN pm1.meta_key = 'id_vim_node' then pm1.meta_value ELSE NULL END) as field_i_want
 FROM    wp_posts p LEFT JOIN wp_postmeta pm1 ON ( pm1.post_id = p.ID)

where pm1.meta_key = 'id_vim_node'

GROUP BY
   p.ID, p.post_title

1

2

3

4

5

6

7

8

SELECT p.ID,p.post_title,

MAX(CASE WHEN pm1.meta_key = 'id_vim_node' then pm1.meta_value ELSE NULL END) as field_i_want

FROM wp_posts p LEFT JOIN wp_postmeta pm1 ON ( pm1.post_id = p.ID)

where pm1.meta_key = 'id_vim_node'

GROUP BY

p.ID, p.post_title

In the example above I’m searching for posts with a value for id_vim_node. This is based on this code from stackexchange

Output WP_Query sql code (very useful for debugging WP_Query )

if in the loop

<?php echo $GLOBALS['wp_query']->request; ?>

1	<?php echo $GLOBALS['wp_query']->request; ?>

outside of loop

$customPosts = new WP_Query($yourArgs);
echo "Last SQL-Query: {$customPosts->request}";

1 2	$customPosts = new WP_Query($yourArgs); echo "Last SQL-Query: {$customPosts->request}";

based on this post on stackexchange

Apr 05

0

Importing a MAMP mysql database on mac (OS X Yosemite)

By louie171 | Mysql, PHP, SQL | No Comments

How to import a MAMP mysql database (bypassing phpmyadmin problems)

Recently I’ve being have a problem importing a large database in MAMP Mysql via phpmyadmin (and after altering various php variables in php.ini, I still couldn’t get it to import).

So I tried another approach to import it from the command line a quick google turned up this post on the MAMP site which got me started.

I managed to import the database fine from the command line like this:

sudo /Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot  your_database_name < your_sql_import_file.sql

1	sudo /Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot your_database_name < your_sql_import_file.sql

it might warn you about password being insecure via the command line.

I hope you find this helpful to overcome problems importing MAMP mysql databases using Phpmyadmin ( simply by using the command line).

Oct 05

0

Handy Woocommerce SQL queries

By louie171 | PHP, SQL, Woocommerce | 4 Comments

This post is just a place to store handy SQL queries for Woocommerce.

Get Order and all its information

select
    p.ID as order_id,
    p.post_date,
    pm.*
from
    wp_posts p 
    join wp_postmeta pm on p.ID = pm.post_id
    join wp_woocommerce_order_items oi on p.ID = oi.order_id
where
    post_type = 'shop_order' and
    p.ID = 14223

1

2

3

4

5

6

7

8

9

10

11

select

p.ID as order_id,

p.post_date,

pm.*

from

wp_posts p

join wp_postmeta pm on p.ID = pm.post_id

join wp_woocommerce_order_items oi on p.ID = oi.order_id

where

post_type = 'shop_order' and

p.ID = 14223

Generate an Orders report and specific information about the orders (eg things like order date, shipping address etc)

select
   p.ID as order_id,
   p.post_date,
   pm.*
from
    wp_posts p 
    join wp_postmeta pm on p.ID = pm.post_id
    join wp_woocommerce_order_items oi on p.ID = oi.order_id
where
    post_type = 'shop_order' 
    and p.id > 50457
    and pm.meta_key in (    
'_kookeli_tracking_number','_billing_email','_billing_first_name','_billing_last_name','_billing_city','_billing_country','_billing_phone','_order_total','_shipping_address_index','_payment_method_title',
        '_completed_date')        
        group by p.id, pm.meta_key

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

select

p.ID as order_id,

p.post_date,

pm.*

from

wp_posts p

join wp_postmeta pm on p.ID = pm.post_id

join wp_woocommerce_order_items oi on p.ID = oi.order_id

where

post_type = 'shop_order'

and p.id > 50457

and pm.meta_key in (

'_kookeli_tracking_number','_billing_email','_billing_first_name','_billing_last_name','_billing_city','_billing_country','_billing_phone','_order_total','_shipping_address_index','_payment_method_title',

'_completed_date')

group by p.id, pm.meta_key

Get Users and billing and shipping addresses in Woocommerce

SELECT u.id, u.user_login, u.user_email, 

max( CASE WHEN m.meta_key = 'billing_email' and u.ID = m.user_id THEN m.meta_value END   ) as billing_email,

    max( CASE WHEN m.meta_key = 'billing_first_name' and u.id = m.user_id THEN m.meta_value END ) as billing_first_name,
    max( CASE WHEN m.meta_key = 'billing_last_name' and u.id = m.user_id THEN m.meta_value END ) as billing_last_name,
    max( CASE WHEN m.meta_key = 'billing_address_1' and u.id = m.user_id THEN m.meta_value END ) as billing_address_1,
    max( CASE WHEN m.meta_key = 'billing_address_2' and u.id = m.user_id THEN m.meta_value END ) as billing_address_2,
    max( CASE WHEN m.meta_key = 'billing_city' and u.id = m.user_id THEN m.meta_value END ) as billing_city,
    max( CASE WHEN m.meta_key = 'billing_state' and u.id = m.user_id THEN m.meta_value END ) as billing_state,
    max( CASE WHEN m.meta_key = 'billing_postcode' and u.id = m.user_id THEN m.meta_value END ) as billing_postcode,
    max( CASE WHEN m.meta_key = 'shipping_first_name' and u.id = m.user_id THEN m.meta_value END ) as shipping_first_name,
    max( CASE WHEN m.meta_key = 'shipping_last_name' and u.id = m.user_id THEN m.meta_value END ) as shipping_last_name,
    max( CASE WHEN m.meta_key = 'shipping_address_1' and u.id = m.user_id THEN m.meta_value END ) as shipping_address_1,
    max( CASE WHEN m.meta_key = 'shipping_address_2' and u.id = m.user_id THEN m.meta_value END ) as shipping_address_2,
    max( CASE WHEN m.meta_key = 'shipping_city' and u.id = m.user_id THEN m.meta_value END ) as shipping_city,
    max( CASE WHEN m.meta_key = 'shipping_state' and u.id = m.user_id THEN m.meta_value END ) as _shipping_state,
    max( CASE WHEN m.meta_key = 'shipping_postcode' and u.id = m.user_id THEN m.meta_value END ) as _shipping_postcode

FROM wp_users u
LEFT JOIN wp_usermeta m
ON  u.ID = m.user_id
group by u.ID

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

SELECT u.id, u.user_login, u.user_email,

max( CASE WHEN m.meta_key = 'billing_email' and u.ID = m.user_id THEN m.meta_value END ) as billing_email,

max( CASE WHEN m.meta_key = 'billing_first_name' and u.id = m.user_id THEN m.meta_value END ) as billing_first_name,

max( CASE WHEN m.meta_key = 'billing_last_name' and u.id = m.user_id THEN m.meta_value END ) as billing_last_name,

max( CASE WHEN m.meta_key = 'billing_address_1' and u.id = m.user_id THEN m.meta_value END ) as billing_address_1,

max( CASE WHEN m.meta_key = 'billing_address_2' and u.id = m.user_id THEN m.meta_value END ) as billing_address_2,

max( CASE WHEN m.meta_key = 'billing_city' and u.id = m.user_id THEN m.meta_value END ) as billing_city,

max( CASE WHEN m.meta_key = 'billing_state' and u.id = m.user_id THEN m.meta_value END ) as billing_state,

max( CASE WHEN m.meta_key = 'billing_postcode' and u.id = m.user_id THEN m.meta_value END ) as billing_postcode,

max( CASE WHEN m.meta_key = 'shipping_first_name' and u.id = m.user_id THEN m.meta_value END ) as shipping_first_name,

max( CASE WHEN m.meta_key = 'shipping_last_name' and u.id = m.user_id THEN m.meta_value END ) as shipping_last_name,

max( CASE WHEN m.meta_key = 'shipping_address_1' and u.id = m.user_id THEN m.meta_value END ) as shipping_address_1,

max( CASE WHEN m.meta_key = 'shipping_address_2' and u.id = m.user_id THEN m.meta_value END ) as shipping_address_2,

max( CASE WHEN m.meta_key = 'shipping_city' and u.id = m.user_id THEN m.meta_value END ) as shipping_city,

max( CASE WHEN m.meta_key = 'shipping_state' and u.id = m.user_id THEN m.meta_value END ) as _shipping_state,

max( CASE WHEN m.meta_key = 'shipping_postcode' and u.id = m.user_id THEN m.meta_value END ) as _shipping_postcode

FROM wp_users u

LEFT JOIN wp_usermeta m

ON u.ID = m.user_id

group by u.ID

this bit of sql is based on this post at http://codecharismatic.com/

Get Order Objects (not strictly sql, but seems a good place to drop it)

    global $woocommerce;						
    $args = array(  'post_type'=> 'shop_order', 
                    'post_status' => 'publish', 
                    'posts_per_page' => -1, 
                    'tax_query' => array(  
                                            array(  'taxonomy' => 'shop_order_status',
                                                    'field' => 'slug',
                                                    'terms' => array('processing')
                                                )
                                        )
                  );	
    
    $orders = array();
    

    $loop = new WP_Query( $args );						
    while ( $loop->have_posts() ) : $loop->the_post(); 

        	$order_id = $loop->post->ID;								
                $order = new WC_Order($order_id);
                
                
                echo "id order = ".$order_id;
                
                $orders[] = $order;
    
    
    endwhile;
    
    
    
    return $orders;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

global $woocommerce;

$args = array( 'post_type'=> 'shop_order',

'post_status' => 'publish',

'posts_per_page' => -1,

'tax_query' => array(

array( 'taxonomy' => 'shop_order_status',

'field' => 'slug',

'terms' => array('processing')

)

);

$orders = array();

$loop = new WP_Query( $args );

while ( $loop->have_posts() ) : $loop->the_post();

$order_id = $loop->post->ID;

$order = new WC_Order($order_id);

echo "id order = ".$order_id;

$orders[] = $order;

endwhile;

return $orders;

SQL

Exporting Forum data from BBPress

SQL for use in migrating a Drupal website to WordPress

WordPress SQL snippets

Do a select based on some meta field of a post ( i.e. search for a post that has a particular metafield )

Output WP_Query sql code (very useful for debugging WP_Query )

Importing a MAMP mysql database on mac (OS X Yosemite)

Handy Woocommerce SQL queries

Get Order and all its information

Generate an Orders report and specific information about the orders (eg things like order date, shipping address etc)

Get Users and billing and shipping addresses in Woocommerce

Get Order Objects (not strictly sql, but seems a good place to drop it)

Archives

Find it here

Fresh from the blog

Recent Comments

Follow us on Twitter

SQL

Exporting Forum data from BBPress

SQL for use in migrating a Drupal website to WordPress

WordPress SQL snippets

Do a select based on some meta field of a post ( i.e. search for a post that has a particular metafield )

Output WP_Query sql code (very useful for debugging WP_Query )

Importing a MAMP mysql database on mac (OS X Yosemite)

Handy Woocommerce SQL queries

Get Order and all its information

Generate an Orders report and specific information about the orders (eg things like order date, shipping address etc)

Get Users and billing and shipping addresses in Woocommerce

Get Order Objects (not strictly sql, but seems a good place to drop it)

Archives

Tags

Find it here

Tag Cloud

Fresh from the blog

Recent Comments

Follow us on Twitter

Tags