SQL Archives - Wordpress Web Development Agency in London specialise in helping Charities

Aug 10

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

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

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

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

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

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