Querying WordPress serialized custom post data Last updated:20 March 2013

Had a bit of a head-scratcher on this one this afternoon…

I have a couple of custom post types set up, category and product, and a number of custom field attached to each of these, managed by Elliot Condon’s outstanding Advanced Custom Fields plugin. In this case, I have a Relationship field defined on product, which allows the user to assign a product to one or more category. All very lovely, but I then started on a situation where I wanted to show all products which were related to a given category.

The WP codex is a little light on examples, here, but I eventually triumphed with the following:

$args = array( 
   'post_type' 		=> 'product', 
   'posts_per_page'	=> -1,
   'meta_query'  	=> array(
				     'key'           => 'category',
				     'value'         => '"'.$category.'"',//quotes to make sure category 23 does not match category 123, 230 etc
				     'compare'       => 'LIKE'
//get products
$wp_query = new WP_Query( $args );

To explain this a little, ‘category’ is the meta_key assigned to the field on product, and $category is the ID of the category that I’m passing in. It took me a little while to understand the SQL generated here. What happens is that meta_query casts the serialized data into characters, like this, where $category = 23:

SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 
AND wp_posts.post_type = 'product' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
AND ( (wp_postmeta.meta_key = 'category' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%\"23\"%') ) 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC

Once I realised what was going on it then becomes straightforward to use LIKE to find matching records. However, there’s a potential gotcha in there, which, in my example, would manifest if there was another category with and ID of 123, 230, 2300 etc etc. Without the quotes wrapped around my $category variable here, then the query would return products linked to any of these categories as well. It seems as if meta_query should allow an ‘in_array’ type comparison here, as the existing approach feels a bit clunky.

Opencart cart not cleared after Paypal purchase Last updated:23 February 2014

Yesterday we had a customer complaining that when they logged on to Opencart to place an order, their previous order was still showing in their cart.

On investigation, this occurs where a customer makes a Paypal payment, but then does not return to Opencart after completing the Paypal transaction. In fact most of our customers seem to do this – why bother returning, I suppose. However, it’s the checkout/success page which clears the customer’s cart. In the absence of this, the cart remains stored against the customer’s record, and hence appears next time they log on.

Now, this has never been mentioned before, and this particular Opencart installation has been live for over 18 months, so it’s obviously not a massive issue, but it’s still not quite what you’d want. We’re still running a (heavily customised) OC, but in fact the problem remains on OC 1.5.4, and presumably, although I haven’t tested, with any other payment gateway where the payment is handled offsite.

It seemed to me that it would be better to clear the cart when the IPN message is returned from Paypal (this is what sets the order status and updates stock etc). The important cases are where a payment comes through as Completed, or as Pending (complete but waiting Paypal confirmation), so I edited catalog/controller/payment/pp_standard.php, adding the code in red:

			case 'Completed':
				if ($this->order_info['customer_id']){//only run this if the customer has created an account, not for guest checkouts
				if ($verified) {
					if ($this->order_info['order_status_id'] == '0') {
						$this->model_checkout_order->confirm($order_id, $this->config->get('pp_standard_order_status_id'), 'Thank you for your Paypal payment');
					} elseif (isset($data['payment_type']) && $data['payment_type'] == 'echeck') {
						$this->model_checkout_order->update($order_id, $this->config->get('pp_standard_order_status_id'), $data['payment_status'], TRUE);
					} elseif ($this->order_info['order_status_id'] != $this->config->get('pp_standard_order_status_id')) {
						$this->model_checkout_order->update($order_id, $this->config->get('pp_standard_order_status_id'), $data['payment_status'], FALSE);

and similarly, a little further down:

			case 'Pending':
				if ($this->order_info['customer_id']){
				if ($this->order_info['order_status_id'] == '0') {
					$this->model_checkout_order->confirm($order_id, $this->config->get('pp_standard_order_status_id_pending'), 'This payment is subject to a review by Paypal');
				} else {
					$this->model_checkout_order->update($order_id, $this->config->get('pp_standard_order_status_id_pending'), $comment, TRUE);

In turn, this calls the following code appended to catalog/model/checkout/order.php:

	public function clearCustomerCart($customer_id){
		$this->db->query("UPDATE `" . DB_PREFIX . "customer` SET cart = '' WHERE customer_id = '" . (int)$customer_id . "'");
		$this->session->data['cart'] = array();

I’ve shown here direct edits in OC1.4.9.5, but you could just as easily apply this via vQmod. This fix has now been working for several weeks with no problem.

vQmod for OC1.5.x

This code works with minor adjustments in OC1.5.x. Here’s a vQmod, tested in OC1.5.6.1:

	<id>vQmods for Opencart mobile theme</id>
	<version>For OC 1.5.6</version>
	<author>Simon Battersby</author>
	<file name="catalog/controller/payment/pp_standard.php">
			<search position="after"><![CDATA[$order_status_id = $this->config->get('pp_standard_completed_status_id');]]></search>
				//Completed status
			<search position="after"><![CDATA[case 'Pending':]]></search>
				//Completed status
			<search position="after" offset="2"><![CDATA[curl_close($curl);]]></search>
				//function to clear cart
				public function clearCustomerCart($customer_id){
						$this->db->query("UPDATE `" . DB_PREFIX . "customer` SET cart = '' WHERE customer_id = '" . (int)$customer_id . "'");
						$this->session->data['cart'] = array();

Linked to this, I also noticed that the default session expiry, set via php.ini is 138 days:

session.gc_maxlifetime = 12000000;

This seems excessive….I’ve set mine to the more reasonable 24 hours.

Exporting to csv – ’ replacing ‘ Last updated:2 March 2014

Came across an interesting and frustrating issue last week. A client was exporting selected data from a WordPress database (actually using this plugin), and the user was seeing odd characters in the csv output. The most obvious was the replacement of single apostrophes: ‘ with this: ’.

Clearly an encoding problem but I couldn’t immediately see why. Pages displaying the data were fine, web pages and the database were all set to use utf-8. I finally solved the problem by forcing the encoding of the csv file via:

$text = mb_convert_encoding($text,'windows-1252');

Hope this helps someone else…

Whizzy features vs usability Last updated:27 February 2013

I was contacted a week or so ago by a client who was trying to develop a page which allowed coaches to assign members of their sports teams to athleti/track and field events. The client wanted to do this via a drag and drop interface (something like this).

Now the thing with drag and drop is it looks very nice, and when the order of dragged items is important, it’s a good way of approaching it. My guess is that a solution to the problem with drag and drop would have got some ‘wow’ comments first time around, but the next time things would have become, well, a drag…

What we built was a pretty non-whizzy grid of athletes against events, with a checkbox for each combination:

Screenshot of page showing athletes and events in a grid

This is so much easier to use and allows the coach to either work through each event and assign all the required athletes, or work through each athlete and assign events. The client’s response: “I’m dancing around the office Gangnam style! This is absolutely awesome! Beyond expectations.”

A good example of where consideration of usability delivers the best result.

Drop down menus on touchscreen devices Last updated:10 March 2013

I’ve got a little curious lately about the nature of support for css driven drop down or flyout menus on touchscreen devices. A bit of Googling yesterday failed to find anything very definitive and found a number of misunderstandings.

The problem

Drop down and flyout menus (like the flyout menu on this site) are driven by hovering the mouse over a parent element (try hovering ‘Photos’ on the left if you can). But of course there is no hover on a touchscreen device. Now, if you use Safari on Apple iOS devices (iPhones, iPads and iPods), it’s clever in this regard, and if the user taps on ‘Photos’ the flyout menu is displayed without moving to the new page. So the pages linked from the dropdown/flyout are still accessible. But on at least some Android tablets, this isn’t the case, if you tap on ‘Photos’ you’ll just go straight to the Photos page (just as you would if you were using a non touchscreen device).

For my particular site, this isn’t a big issue as the Photos ‘sublinks’ are displayed on the Photo page, so a user on an Android tablet can still reach the page, albeit via an intermediate one. But if your site has pages which are accessible only via a dropdown, then the user is both unaware of them and unable to access them.

So I don’t need to worry about Apple devices then?

It isn’t quite as easy as that I’m afraid. By experimentation, using Opera Mini on an iPhone does not give the behaviour above, the dropdown is never displayed. I accept that few people will use that combination however.


I’ve seen a few sites which claim to solve this by the addition of some javascript which in some way replicates the behaviour I’ve described above on Apple devices. The big issue here is that this is going to happen for all users, not just those on touchscreen devices, which introduces additional issues with usability, I think. You might try to detect a touchscreen with the javascript, and do something clever to only trigger this behaviour on a touchscreen device, but it seems to me that reliably detecting the increasing profusion of touchscreens is likely to be difficult (akin to browser detection). So really I’ve come to the conclusion that at the moment the only solution is to provide some sort of submenu that’s displayed on a page.

Summary of the behaviour I’ve noted

Sadly, I don’t have access to every mobile device known to man….but here’s what I can confirm.

Tap on top level menu item displays submenu Apple iPhone 3G/Safari, Apple iPad 3/Safari. Samsung Galaxy Ace/Android Gingerbread

Tap on top level menu item goes straight to that page, no sub-menu: Apple iPhone 1/Opera Mini, Android Tablet/Chrome

Further, if you’re very careful on Android Gingerbread, the dropdown menu does display on a longer hold and it’s then possible (but difficult) to slide your finger on to the submenu and tap on an item. You need to be know the dropdown is going to appear and be ready for it though….so not very user-friendly.

If you’re reading this page on a touchscreen, give it a try and tell me what happens. Especially interested in Windows 8 users.