Relevanssi: How to Exclude WooCommerce Product Variations From WordPress Search Results

Posted on by jibran Posted in Code Samples, Development

By default, Relevanssi doesn’t un-include search results that are draft, pending, private, etc. This is problematic since you may not want products to be available via search.

This solution takes a product variation’s parent post status into account. If the variation’s parent in question is anything but ‘publish’ it de-indexes the product variation.

Add it to your functions.php and rebuild your index.

add_filter( 'relevanssi_do_not_index', 'relevanssi_search_do_not_index', 10, 2 );

function relevanssi_search_do_not_index( $exclude, $post_id ) {

	// get post
	$post = get_post( $post_id );

	// exclude if post is variation and it's parent post status is not publish OR posts status is not publish
	if( ( $post->post_type == 'product_variation' && get_post_status( $post->post_parent )  != 'publish' ) || $post->post_status != 'publish' ) {

		$exclude = true;

	} 

	return $exclude;

}
Posted in: Code Samples, Development  |  Tagged with: , , ,  |  Leave a comment

VaultPress Is Awesome, But Not Realtime For Plugins Like WooCommerce

Posted on by jibran Posted in Development, News

vaultpress

Update 13 July 2016: According to VaultPress support, WooCommerce tables are now backed up realtime just like core WordPress tables. However, non-WooCommerce and non-WordPress core tables are still only backed up once a day. So if you have custom tables there is still potential for data loss if you need to restore from VaultPress. I will update this post after I verify this new information.

VaultPress Is Awesome

A few months ago, I was researching WordPress backup services for a client of mine. VaultPress came up in many searches. I really liked the fact that they offered real-time backup; not daily, not hourly, but real-time. Their system is notified of any change that occurs in the database and is instantly backed up.

Pretty bomb if you ask me. Even cooler is that you can drill down to a specific hours backup and restore an entire site, a specific file or folder or even a specific database table. Talk about robust and feature rich with great usability.

So fast forward to this week. My clients wanted to test and document what it would take to do a restore from a catastrophic server failure. Another forward thinking feature of VaultPress is the ability to restore to an alternate site. This proved to be invaluable to test out their service. I simply created a test restore site on my clients VPS and well, as you might have guessed, restored the site.

The restore went very smooth… until I verified the data.

The Issue

My clients site is a WooCommerce site with tens of thousands of orders and users. And the first thing I checked was that all the orders (and other mission critical data) were safe and sound. And yes all orders aside from that days orders were fine. Roughly ten orders had no order items. I had the orders but no information about what those users purchased. I compared with the production site and sure enough those orders had order items.

Red flags were going off. What’s going on here?

I had to verify these findings. So I ran the restore two more times and each time the most recent orders had no order items. I needed to get to the bottom of this.

After some backup and forth with VaultPress’ support staff the issue lies in how often they back up non-WordPress core tables, such as custom plugin tables. Simply put, orders are backed up in real-time, but the actual orders items are only backed up once a day. From the horses mouth:

“Please note, though, that plugin tables are only indexed (and backed up) once per day, so you might have to wait 24 hours to see the order items appear in them.”

To get geeky and for those interested, WooCommerce stores master order records in wp_posts, while order items are stored in wp_woocommerce_order_items. Since the later table is backed up only once a day, using VaultPress you’re guaranteed to have data loss.

To reiterate this isn’t limited to WooCommerce. Any plugin using custom tables will suffer from this issue.

High Horse (Sorry)

The nature of this issue is bothersome as it’s an incomplete picture of your database. It’s not only data loss (in the event of a catastrophic failure), but it’s a mix match of data from different moments in time. So my question is: can VaultPress really claim real-time? If you’re talking about WordPress core tables, then yes it’s real-time. But it’s really not because custom plugin tables are so prevalent. I suggested VaultPress to my clients in good faith only to find we were at risk the whole time.

Recourse

Every situation is different. If you’re not using any plugins with custom tables, then you don’t have to worry. If you are and a full backup of your site every 24 hours is sufficient then keep on chugging. Otherwise it might be good to research another solution or service. It’s just that VaultPress is such a well built service that I don’t want to go anywhere else. At least they are aware of the issue and are working to solve it:

“However, as you mentioned, the restore inconsistencies arised because the WooCommerce plugin tables are only indexed daily. Our developers are working directly with WooCommerce (also a part of Automattic) to bring live sync to the WooCommerce tables. There is no definitive timeline related to this yet, but it is in the works!”

End transmission.

Posted in: Development, News  |  Tagged with: , , , ,  |  Leave a comment

SQL Script To Get All WooCommerce Orders Including Metadata

Posted on by jibran Posted in Code Samples, Development

A client of mine has a rather large WooCommerce database. We’ve been trying to run reports using the WooCommerce Customer/Order CSV Export plugin but have been running into timeout issues—the glorious WordPress white screen of death.

I got fed up and wrote the following script to export the needed information. Customize as you see fit. Enjoy.

Update 26 Aug 2016: By popular demand, I added a way to query for orders based on product name. Querying by product id is the right way but makes my head hurt. Suggestions welcome.

select
    p.ID as order_id,
    p.post_date,
    max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
    max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
    max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
    max( CASE WHEN pm.meta_key = '_billing_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1,
    max( CASE WHEN pm.meta_key = '_billing_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_2,
    max( CASE WHEN pm.meta_key = '_billing_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city,
    max( CASE WHEN pm.meta_key = '_billing_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state,
    max( CASE WHEN pm.meta_key = '_billing_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_postcode,
    max( CASE WHEN pm.meta_key = '_shipping_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_first_name,
    max( CASE WHEN pm.meta_key = '_shipping_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_last_name,
    max( CASE WHEN pm.meta_key = '_shipping_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_1,
    max( CASE WHEN pm.meta_key = '_shipping_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_2,
    max( CASE WHEN pm.meta_key = '_shipping_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_city,
    max( CASE WHEN pm.meta_key = '_shipping_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_state,
    max( CASE WHEN pm.meta_key = '_shipping_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_postcode,
    max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
    max( CASE WHEN pm.meta_key = '_order_tax' and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax,
    max( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
    ( select group_concat( order_item_name separator '|' ) from wp_woocommerce_order_items where order_id = p.ID ) as order_items
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
    post_date BETWEEN '2015-01-01' AND '2015-07-08' and 
    post_status = 'wc-completed' and
	oi.order_item_name = 'Product Name'
group by
    p.ID
Posted in: Code Samples, Development  |  Tagged with: , , ,  |  Leave a comment

Well Don’t You Just Have Some Nice Manners (Music)

Posted on by jibran Posted in Portfolio

Nice Manners Music Logo

Ok here’s the deal, kids. There’s this pretty rad recording studio based out of LA with the most badass of names: they’re called Nice Manners. Not only do they have a badass name they also do badass work. And not only that, they’re run by one of the most badass people (Joanna Katcher) I’ve had the pleasure to work with over phone and electronic mail. I’m not downplaying the working friendship we’ve formed, in fact, I’m saying just the opposite. It speaks mountains to me that we’ve never met in person and have formed such a solid rapport.

Most of the projects I land are via word of mouth. And this project was no different. The power of word of mouth cuts through all the bullshit; there is an innate sense of trust already in place. I’m not a very good sales man so this strategy of letting whatever comes my way works well for me. And everyday I get to work with amazing people, who have amazing visions and together we make amazing stuff. Blah blah blah… insert more emotional hype.

Meet Nice Manners Music

Joanna came to me with a solid vision of a genre-based music search engine and player. Her design was simple, clean and elegant. This project was perfect for me.

The site is built on WordPress using Underscores, which is a very barebones starter theme. To support the main features of song playback and autocomplete genre and song title search we carefully chose, jPlayer and select2, respectively. Joanna actually suggested select2, which gives you a lot more power over multi-select, autocomplete dropdown boxes. jPlayer is a ridiculously easy to use HTML5 audio and video player and is highly customizable. Feast your eyes on these screen grabs.

Results, Player, Playing

Genre selection, search results and playback.

Autocomplete in action.

Autocomplete in action.

And of course fully responsive.

And of course fully responsive.

The Demo

I’m pretty excited to share this project. Part of Joanna’s vision was to allow access to only her clients, so with her permission I’ve created a demo site to showcase our work, right over here. Sorry, demo has been removed.

As a hobby I do a fair amount of nature field recordings so for your listening pleasure I have uploaded some of my favorites to populate the demo. Enjoi.

Posted in: Portfolio  |  Tagged with:  |  Leave a comment

ProtonMail Turning One, Doubles Storage to 1GB

Posted on by jibran Posted in News, Technology

ProtonMail: End-to-end Encrypted Email

Me yes me; I am a ProtonMail fan. They provide end-to-end encrypted email and are based in Switzerland. I know there are those that feel privacy is dead or quickly default to the “I have nothing to hide” excuse, but I am a firm believer in privacy as an inalienable right. Ya know, that whole search and seizure passage in the constitution protecting us from unlawful intrusion. Yeah, it’s important. And I don’t stand up nearly enough, which is a whole other story.

That’s why any person or company that dares to innovate and challenge head-on the mass surveillance ideologies that are covering the globe I am in full support of. This isn’t earth shattering, but it’s a continued step in the right direction. ProtonMail has turned one and they are doubling storage of early adopters to 1GB. Read more about it, right over here.

The announcement for which I’m really waiting is hosting my own domain at ProtonMail. This is due later this year, which I heard directly from the horse’s mouth.

Posted in: News, Technology  |  Tagged with: ,  |  Leave a comment

Move Your Damn WordPress Debug Log so It’s Not Accessible via HTTP

Posted on by jibran Posted in Code Samples, Development

move-wordpress-debug-log-5

Every time I need to view a WordPress debug log, I get a little belly-side security loophole cringe. It completely depends on what information you stuff into it that would cause a security issue, but it’s so easy to mitigate this risk entirely—by storing (and writing to) the file outside of your document root. It drives me bat-shit crazy that anyone with a browser can simply navigate to the blatantly accessible file and view its contents.

In writing this post I searched a few high profile WordPress sites and found a few debug logs in the mix. Most of them returned with 404s, but I did find a few. No juicy debug information though, but I only spent like five minutes looking.

Anyways, I digress.

So, for some time now I’ve been implementing on any of my client websites the following solution to move the debug log to a safer, inaccessible location.

In your wp-config.php add the following:

define('WP_DEBUG', true);
if ( WP_DEBUG ) {

	// turn off wordpress debug (otherwise it will override)
	define( 'WP_DEBUG_LOG', false );

	// specify new safe path
	$path = realpath( $_SERVER["DOCUMENT_ROOT"] . '/..' ) . '/wp-logs/debug.log';
	    
	// enable php error log
	@ini_set( 'log_errors', 'On' ); // enable or disable php error logging (use 'On' or 'Off')
	@ini_set( 'error_log', $path );

}

A few notes:

  • All of this code assumes you have access to your servers root filesystem. Check with your host if you’re unsure.
  • The code in it’s current form assumes that the folder and file already exist.
  • If nothing is being written to the file after these changes, you may need to adjust the permissions of the folder and file once created.

And for a bonus I use the following awesome function to write to said log file. Compliments of Stu Miller.

if (!function_exists('write_log')) {
    function write_log ( $log )  {
        if ( true === WP_DEBUG ) {
        
			if( is_array( $log ) || is_object( $log ) ) {
				error_log( print_r( $log, true ) );
			} else {
				error_log( $log );
			}
        		
        }
    }
}
Posted in: Code Samples, Development  |  Tagged with: , ,  |  Leave a comment

Convert A Massive CSV To Many CSVs Using PHP

Posted on by jibran Posted in Code Samples, Development

One CSV Many CSVs
I’m in the process of working in a CSV file with roughly 26000+ rows and 20 some odd columns. Each row represents an historical purchase order, including information such as date, time, name, address, product name, product sku, etc. My end goal is to import all these transactions into an existing e-commerce system. The trouble is, is that my import tool can only process 1500 rows in one go, or I get connection reset errors from the server.

The solution here is to break up the master CSV file into discreet 1500 row chucks and save in separate files. So rather than hack my way through Excel, copying and pasting 1500 row chunks into new workbooks (ug…) I decided to write me a little PHP script to do the job. It took about 30 minutes. Not only is this a faster way to breakup these transactions, it is completely error free. There’s a high likelihood I would have missed or duplicated some rows having had done this manually.

Current code assumes your master file is in the same directory as the script. Adjust as necessary.


<?php

@ini_set( 'display_errors', 1 );

@ini_set('memory_limit','512M');

echo 'start <br/>';

$file_name_base = 'masterfile';
$master_file = "{$file_name_base}.csv";

$fh = fopen( $master_file, "r" );
	
if( $fh ) { // valid file?

	$i = 0;
	$records_per_file = 1500;
	$header_row = null;

	// loop through csv rows
	while ( ( $row = fgetcsv( $fh, 0, ',' ) ) !== false ) {
	
		if( $i == 0 ) { // first row?
		
			// save column names row
			$header_row = $row;
			
		}

		if( $i % $records_per_file == 0 ) { // time to create a new file?

			// some vars, duh
			$curr_file_name = "{$file_name_base}_{$i}.csv";
			$curr_fh = fopen( $curr_file_name ,"w" );
	
			if( isset( $header_row ) && !empty( $header_row ) && $i > 0 ) { // we cool?
			
				// yes, add header row
				fputcsv( $curr_fh, $header_row );
				
			}
			
			echo "{$i} - create new file: {$curr_file_name}. <br />";
		}
		
		fputcsv( $curr_fh, $row );
	
		$i++;
		
	}		
	
	echo $i . '<br/>';
				        
}

?>
Posted in: Code Samples, Development  |  Tagged with: , ,  |  Leave a comment

Launched: Valhalla DSP

Posted on by jibran Posted in Portfolio, Technology

Valhalla / Grand Canyon

Gosh, it has been far too long since I posted anything. And for that I’m only apologizing to myself, which is effectively moot because I’m also, at the same time, forgiving myself. I’m sorry, Jibran. You’re forgiven, Jibran. Phew, dodged that emotional bullet.

I have considered a new years resolution to post more stuff about things and stuff that I like, but that will just stress me out. The best I can do is promise to post more often than every three months. That’s a goal I can get behind. I work in the moment. And if the idea in that moment gets old and I don’t write it down semi-humorously then I lose interest and …

(Oh look another episode of Battlestar Galactica is on.)

And from that introduction, this post, is a big one. It’s a combination of eight months of blood, sweat, stress and code that kept me from posting and alas in the end all is certainly well: we have just launched my clients new website, ValhallaDSP.com. An incredibly simple, easy to use e-commerce site. Well, easy to use from the front-end. Quantum computer complicated, from the back-end. Well that might be an overstatement, but it’s all relative and I think it’s funny.

Who is Valhalla DSP?

Anyways, Valhalla DSP (where DSP stands for digital signal processing) is a Seattle-based company that writes incredibly popular audio plugins for home and professional recording studios all over the world. (And from Norse mythology, Valhalla is an enormous hall in some place called Asgard where the dead go to hangout with more dead people. And the image above is my interpretation, if you didn’t pick up on that.) So go forth and check out the site if you like.

I’m tired now and losing inter …

Posted in: Portfolio, Technology  |   |  Leave a comment

Run Your Own Damn Code after PayPal Calls WooCommerce Back

Posted on by jibran Posted in Code Samples, Development

PalPal Calls WooCommerce Back
This isn’t rocket science, but the WooCoommerce documentation (as robust and complete as it is) isn’t clear about how to hook into a PayPal IPN successful charge webhook call back thingy—yes, that’s its technical name. In fact “isn’t clear” insinuates some level of recognition from them that something like this can be done. But no, not one word about it. Geez. Anyways, like usual, I digress.

But, if you’re like me, you ASSUME everything can be done. I know this can be done. I just have to dig. Where is my shovel? But I’m also curious. Is this a hush hush thing? Like maybe, if they don’t ask, we won’t have to tell that it’s as easy as writing a function. Thank the dogs for StackOverflow—check out this post.

From information garnered from that post and some elbow grease, here is the function I wrote that allows you to run your own damn code after PayPal calls WooCommerce back, which is apparently super top secret. It’s also posted here.


add_action( 'valid-paypal-standard-ipn-request', 'handle_paypal_ipn_response', 10, 1 );

function handle_paypal_ipn_response( $formdata ) {
	
    if ( !empty( $formdata['invoice'] ) && !empty( $formdata['custom'] ) ) {
		
		if( $formdata['payment_status'] == 'Completed' ) {

			// unserialize data
			$order_data = unserialize( str_replace('\"', '"', $formdata['custom'] ) );

			// get order
			$order_id = $order_data[0];
			$order = new WC_Order( $order_id );
			
			// got something to work with?
			if ( $order ) {
		
				// get user id
				$user_id = get_post_meta( $order_id, '_customer_user', true );
				
				// get user data
				$user = get_userdata( $user_id );
						
				// get order items
				$items = $order->get_items();
				
				// loop thru each item
				foreach( $items as $order_item_id => $item ) {
				
					$product = new WC_Product( $item['product_id'] );
					
					// do extra work...

				}	
			}	
		}
	}
}
Posted in: Code Samples, Development  |  Tagged with: , , ,  |  Leave a comment

MySQL: Group By Column And Include Its Count

Posted on by jibran Posted in Code Samples, Development

Wave Crashing, MySQLWithout going into too much detail about the why, consider the below table. It’s a snippet of a table I’m working with for an e-commerce site. Any user (user_id) can have different products (product_id), but any user can also have multiple entries of the same product.

id user_id product_id
1 10 50
2 11 50
3 11 51
4 11 51
5 12 50
6 12 50

When displaying this data to a user, say user 11, I can’t do a simple flat list. This would confuse the user, showing multiple entries of the same product. Like this:

select id, user_id, product_id 
FROM wp_wc_licenses 
WHERE user_id = 11;

And the resulting table:

id user_id product_id
2 11 50
3 11 51
4 11 51

The solution
To give the user a more accurate display of their products, I want to group them by product, but also include a count of that product. Here is a much better way to get the data that makes sense.

SELECT id, user_id, product_id, count(*) AS `product_count`
FROM product
WHERE user_id = 11 group by product_id;

And the resulting table:

id user_id product_id product_count
2 11 50 1
2 11 51 2

I found this technique from this post. It saved me from myself, having to process this in PHP. That. Would. Have. Sucked.

Posted in: Code Samples, Development  |  Tagged with:  |  Leave a comment