Call   0208 3131682   Mon-Fri 9am-6pm
Category

SQL

Exporting Forum data from BBPress

By | 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.

 

 

SQL for use in migrating a Drupal website to WordPress

By | 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’

 

WordPress SQL snippets

By | 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  )
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

outside of loop

based on this post on stackexchange

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

By | 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:

 

 

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

 

 

Handy Woocommerce SQL queries

By | PHP, SQL, Woocommerce | 4 Comments

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

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

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)