This is just a quick Laravel snippet on how to log SQL to the log file. You just need to enable logging for DB , run SQL ( and then turn off or leave on its up to you). Oh and remember to include the classes you’re using (usually at the top of your file).
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 … Read more
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 … Read more
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 )
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 … Read more
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 … Read more
This post is just a place to store handy SQL queries for Woocommerce. Get Order and all its information
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)
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
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 … Read more